Blog de Humpty

Writing efficient SQL article

Writing Efficient SQL: Set-Based Speed Phreakery

Just read a fantastic article that dissected some SQL code that generated a running total report over a million rows in less than a second; thought I'd pass it on. Even if you're not "a SQL guy or gal" I think it could be of benefit, if nothing else for learning the techniques summarized at the end of the article so you can use them in other environments.

If you do decide to follow along with the article to better understand everything but don't have the tools necessary to do so, don't fret! I used SQL Server 2008 Express Edition. After you get it setup you can download the sample data via links in the article. And since they don't explain how to actually import all that data – assuming you go for the million row sample data download – use the bcp command.

1. Create a database on your newly setup server, name it whatever you want.
2. Create a new query window and issue the CREATE TABLE command from the article.
3. Now you can use bcp (Bulk Copy Provider?). From a command prompt window type something like the following:
   
   bcp Play.dbo.Registrations in registrations.bcp -S humpty\sqlexpress -T
   
   Where Play is the database name I used and humpty\sqlexpress is the server name on my machine. Just press enter at all the prompts you get asked until the import starts.