SQL query optimization

Wow. Two SQL posts on the same day.

The SQL query processing team blog is quite a gem. I wonder how I missed this.

The query processing team – query optimization & execution – providing tips, tricks, advice and answers to frequently-asked questions in a continued effort to make your queries run faster and smoother in SQL Server.

BTW: I consider this short article a must read for everyone who writes a SELECT query. The reason is that we never learn about the query optimizer in school when learning about databases.

Let me prove this to you. My best friend Shasheendra wrote this code the other day and it stumped me. Imagine this, you want to insert 10,000 rows into a temporary table.  Let’s say we can take two approaches. The first one is to simply write 10,000 inserts the second could be to insert the entries into a table with a single field with the 10,000 being comma delimited. Then parse the string and insert each row at a time.

Which do you think runs faster? No hurries, just take a look at the code and then scroll down to see the time comparison.

Approach 1:

Write 10,000 insert statements. Like this

CREATE TABLE #filetab (filename varchar(256))

CREATE CLUSTERED INDEX IX_filetab ON #filetab (filename)

INSERT INTO #filetab VALUES (‘dummy’)

INSERT INTO #filetab VALUES (‘ed7d1eda-025b-41be-b8b4-523dc9d53ece’)

INSERT INTO #filetab VALUES (‘b61abf2e-3252-423c-9ad7-cc63cb5e51bc’)

INSERT INTO #filetab VALUES (‘4224b7f8-120a-447b-a8ae-63cefce78255’)

….(10,000 entries)…

INSERT INTO #filetab VALUES (‘7f38bed5-be32-42c8-986f-20b3c78950ad’)

SELECT COUNT(*) FROM #filetab

DROP TABLE #filetab

GO

Approach 2:

DECLARE @filelisttab TABLE (filelist text)

CREATE TABLE #filetab (filename varchar(256))

CREATE CLUSTERED INDEX IX_filetab ON #filetab (filename)

INSERT INTO @filelisttab (filelist) VALUES (‘dummy,ed7d1eda-025b-41be-b8b4-523dc9d53ece,b61abf2e ….(10,000 comma seperated entries)…,7f38bed5-be32-42c8-986f-20b3c78950ad’)

DECLARE @i int, @l int, @tl int

SET @i = 1

SELECT @tl = DATALENGTH(filelist) FROM @filelisttab

WHILE (@i < @tl)

BEGIN

    DECLARE @filename Varchar(256)

    DECLARE @nextcommat int

    SELECT @filename = SUBSTRING(filelist, @i, 128) FROM @filelisttab — Use 128 character read buffer!

    SET @nextcommat = CHARINDEX(‘,’, @filename)

    SET @l = CASE @nextcommat WHEN 0 THEN 128 ELSE (@nextcommat1) END

    SET @filename = SUBSTRING(@filename, 1, @l)

    IF (LEN(@filename)>0) INSERT INTO #filetab (filename) VALUES (@filename)

    SET @i = @i + @l + 1

END

SELECT COUNT(*) FROM #filetab

DROP TABLE #filetab

Query1.sql (722 KB)

Query2.sql (401 KB)

 

If you don’t believe me, you can download the two sql statements and try running them on your machine. These scripts create a temporary table, insert data and then drop the table.

Query 1 : First run 2 min 48 seconds, second run 2 min 28 seconds

Query 2:  First run 9 seconds, second run 5 seconds

And here I was thinking all these days that I knew all the basics on SQL query optimization.

SQL query optimization

Publishing your SQL Server 2005 database?

Ever wanted to MySQL feature that let’s you script out a database (including the data) when working with SQL Server?

Fear no more the Database Publishing Wizard is here to help.

The Database Publishing Wizard enables the deployment of SQL Server 2005 databases into a hosted environment on either a SQL Server 2000 or 2005 server. It generates a SQL script file which can be used to recreate the database in shared hosting environments where the only connectivity to a server is through a web-based control panel with a scripting window.

The Database Publishing Wizard is currently a command line tool, though future releases will add a graphical user interface.

Get the download from here. Keep in mind that it’s still a CTP and is being developed actively.

Publishing your SQL Server 2005 database?

Outlook Shortcuts

I spend a lot of time in Outlook and I’ve found these shortcuts to be invaluable.

  • Ctrl+Shift+M : Open a new email window
  • Ctrl+Enter   : Send the email that you’ve typed
  • Ctrl+Shift+I : Navigate to the Inbox from other folders
  • Ctrl+R       : Reply to an email
  • Ctrl+Shit+R  : Reply All
  • Ctrl+Shift+V : Move an email to another folder
Outlook Shortcuts

Steve Jobs on Design

I’ve been reading a lot of Steve Jobs quotes recently here is another one I can relate to with the products that I’m building.

“Most people make the mistake of thinking design is what it looks like. That’s not what we think design is. It’s not just what it looks like and feels like. Design is how it works.” – Steve Jobs

Steve Jobs on Design

Tech.Ed Keynote Review

My thoughts on the keynote that was presented at Tech.Ed Sri Lanka.

To start off with the keynote was way too long and went on for what felt like four hours. I expected the presenters to excite the large gathering of developers about the new innovations that will be comnig in the .NET platform. Instead the damn PeopleReady message was stuffed down our throats. Repeatedly.

What do I care about some marketing initiative bs from Microsoft? Is this the sign of things to come with SteveB taking over the reins? I sure hope not.

The demos by Matty on the other hand were a treat to watch.

Tech.Ed Keynote Review

Innovation Comes From Saying No

I was reading the free Getting Real book from 37Signals and came across this Steve Jobs quote. I love it!

“We Don’t Want a Thousand Features”

Steve Jobs gave a small private presentation about the iTunes Music Store to some independent record label people. My favorite line of the day was when people kept raising their hand saying, “Does it do [x]?”, “Do you plan to add [y]?”. Finally Jobs said, “Wait wait — put your hands down. Listen: I know you have a thousand ideas for all the cool features iTunes could have. So do we. But we don’t want a thousand features. That would be ugly. Innovation is not about saying yes to everything. It’s about saying NO to all but the most crucial features.”

—Steve Jobs, CEO, Apple (from The Seed of Apple’s Innovation)

Innovation Comes From Saying No