Simplified take on an Access Database query SQL ‘ROW_NUMBER’/ranking equivalent

Earlier this week I needed the ability to write a query in an Access Database similar to the functionality provided by MSSQL’s ROW_NUMBER (OVER …) keyword. It took some thought and a little googling and wasn’t apparenty even though there were a few examples out there but they were kinda muddied up and not obvious (too much extra crap) so I thought I’d provide a simpler version here. So in my case, I just needed to read from a table with unknown rows a list of names… but I needed a unique identifier for each name (row) which I could use with an incrementing integer. While I did have an ID column available, it couldn’t be depended on to be sequential due to deletes that may have occurred.

The resulting Access query is this:

SELECT [Name],
(SELECT Count([ID]) AS C
FROM [tblNames] AS A
WHERE A.[ID] > [tblNames].[ID]) AS [Row]
FROM [tblIssues]

From this, I can add a WHERE clause like ‘WHERE [Row] = 0′, ‘WHERE [Row] = 1′, etc. to specifically get each individual name from the table. Nice and easy (and more readable logic than most of the examples I found out there).

Another good benefit of using Application Name in a connection string

I’ve seen a plethora of posts like this one which mention the benefits of using “Application Name” in your connection string. I thought I’d add on to this another neat item that people could use. While it isn’t exactly reinventing the wheel, the technique is commonly forgotten about. We use it in WayPoint since it can run multiple imports into a CaseLogistix library (SQL database) at the same time.

So, first we add the “Application Name” to the SQL connection string but we append a GUID to the end of it (to make sure it’s unique). Then, if our application needs to be aware of multiple instances connected to the same SQL database, we simply run this query:

select count(*) from master.dbo.sysprocesses JOIN master.dbo.sysdatabases ON
master.dbo.sysprocesses.dbid = master.dbo.sysdatabases.dbid
where IsNull(program_name, '') <> ''
AND program_name like 'WayPoint-%'
AND program_name <> 'WayPoint-<%% Our current GUID here %%>'
AND master.dbo.sysdatabases.name = '<%% Our current database name %%>'

This tells us the count of processes of other WayPoints running. Even better, we can dive further into this query to get info about the other processes and even kill them off if needed or do whatever with them. Not rocket science but a good thing to have in your back pocket if you need it.

Brought over the Projects section populated with my nice little Create Delimited Text File SQL2005 stored procedure

It only took 3 months but I finally brought over the projects section from my old blog. Right now, this only consists of the SQL stored procedure that I wrote a year ago and have maintained with fairly regular updates which allows you to create a delimited text file from a SQL table, SQL view, Access Database, Excel file, or DBASE file. It’s a great little script that is very, very fast. I had a few comments about it on my previous blog where it helped out a handful of folks so hopefully it’ll continue to be of aid. Here’s the link to it but you can also just click on the new Projects section at the top of the page to get to it as well.

Copy and paste to Visual Studio ASP.NET page from SQL Management Studio

As I’m bringing over some older posts from my previous blog, I thought this one might be helpful to some (notice I was using VS2005 at the time but this also works for VS2008).

So I was doing some grunt work of making a few textboxes which will be filled from a SQL table from a remote database. I decided to pop open SQL Management Studio in my second monitor and copy the field names straight from the table and paste them onto the ASP.Net page. I clicked on a field name and unknowingly left the whole row selected, hit CTRL-C, and then pasted right into the ASP.Net page. To my surprise, a Gridview showed up and its related datasource. Also, connection string was added to my Web.config. I was not aware of this functionality at all but I thought I’d post it as it could save some time for a few people.

1. With Visual Studio 2005 .NET open to an ASPX page, open SQL Management Studio.
2. Right click on the table you want to create a Gridview with and select ‘Design’.
3. Highlight the field(s) you want to show in the Gridview (use shift-select for multiples).
4. Press CTRL-C to copy to the clipboard.
5. Place the cursor in the ASPX page where you want the Gridview to appear and press CTRL-V.
6. That’s it! You will have a simple Gridview with the fields you selected, a SqlDataSource underneath it, and a connection string in your Web.Config.

Here’s an example of the resulting ASPX output:

<asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource2"
EmptyDataText="There are no data records to display." AutoGenerateColumns="False">
<Columns>
<asp:BoundField DataField="Processor" SortExpression="Processor" HeaderText="Processor"></asp:BoundField>
<asp:BoundField DataField="MinAmount" SortExpression="MinAmount" HeaderText="MinAmount"></asp:BoundField>
<asp:BoundField DataField="Requestor" SortExpression="Requestor" HeaderText="Requestor"></asp:BoundField>
<asp:BoundField DataField="OutputFilesPath" SortExpression="OutputFilesPath"
HeaderText="OutputFilesPath"></asp:BoundField>
</Columns>
</asp:GridView>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" SelectCommand="SELECT [Processor],
 [MinAmount], [Requestor], [OutputFilesPath] FROM [tbl_Settings]”
ConnectionString=”<%$ ConnectionStrings:MPConnectionString1 %>”
ProviderName=”<%$ ConnectionStrings:MPConnectionString1.ProviderName %>”>
</asp:SqlDataSource>

SQL Bulkcopying performance work

Some of my time spent in application performance tuning around out SQL backend products is reviewing code that could otherwise be performed on the SQL server with much faster results. Over the years, I’ve spent a lot of time with .NET’s SQLBulkCopy and SQLXML BulkLoad. I’ve spent countless hours scheming up new ways to get our data processing techniques out of  un-scalable C# code of row by row (or chunks) processing of the data and into the blazing fast world that is SQL BulkCopy/BulkLoad.

One recent opportunity arose where we literally had a piece of C# code iterating row after row, performing some crazy logic, and then copying data from one table to another, and throwing around some more logic and performed updates back on the 2 tables. To be a little more specific, we had a table full of filenames in a certain format and we needed to copy this data into a separate table with more metadata about the filenames. We then wanted to copy some data from this table to yet another table. Very generic statements I know but this isn’t exactly an open source product.

This code block took over 3 hours to run for a measly 10,000 rows. We applied a few BulkCopies and were very happy with the results. We actually added code to dynamically create a working table (I want to avoid the word “temp table” since it is not a true SQL temp table but in the scope of the application, since it is added and removed, it is a “temp table”). With this working table, we could then convert the C# Logic to logic performed in a SELECT statement which also prevents UPDATE statements from being executed so that this can be an INSERT only (much faster in the SQL world). So finally, we converted a couple hundred lines of C# code to 3 BulkCopies (1 for the working table, 2 for our tables). All the logic is performed in 3 SELECT statements optimized to their fullest (dirty reads where possible, LEFT JOINs instead of IN/NOT IN, limited subqueries, etc.).

Overall, in the grand scheme of things, to be fair, the complete process of some of which is not mentioned here took about a thousand lines of code which took over 3 hours to run for 10,000 rows to 5 bulkcopies using 3 working tables and the result is over 100,000 rows takes 3 seconds. Three seconds… to be fair with a direct comparison, the original 10,000 rows takes 2 seconds, most of which is spent creating the “temporary working tables” or opening the SQL connection/SQL DataReaders. Awesome stuff!