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</font> TABLE #filetab (filename varchar(256)) </p>

CREATE</font> CLUSTERED INDEX IX_filetab ON #filetab (filename)</p>

INSERT</font> INTO #filetab VALUES ('dummy')</p>

INSERT</font> INTO #filetab VALUES ('ed7d1eda-025b-41be-b8b4-523dc9d53ece')</p>

INSERT</font> INTO #filetab VALUES ('b61abf2e-3252-423c-9ad7-cc63cb5e51bc')</p>

INSERT</font> INTO #filetab VALUES ('4224b7f8-120a-447b-a8ae-63cefce78255')

….(10,000 entries)…

INSERT</font> INTO #filetab VALUES ('7f38bed5-be32-42c8-986f-20b3c78950ad')</p>

SELECT</font> COUNT(*) FROM #filetab</p>

DROP</font> TABLE #filetab</p>

GO

</font>

Approach 2:

DECLARE</font> @filelisttab TABLE (filelist text)</p>

CREATE</font> TABLE #filetab (filename varchar(256)) </p>

CREATE</font> CLUSTERED INDEX IX_filetab ON #filetab (filename)</p>

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

DECLARE</font> @i int, @l int, @tl int</p>

SET</font> @i = 1</p>

SELECT</font> @tl = DATALENGTH(filelist) FROM @filelisttab</p>

WHILE</font> (@i < @tl)</p>

BEGIN

</font>    DECLARE @filename Varchar(256)</p>

</font>    DECLARE @nextcommat int</p>

</font>    SELECT @filename = SUBSTRING(filelist, @i, 128) FROM @filelisttab -- Use 128 character read buffer!</p>

</font>    SET @nextcommat = CHARINDEX(',', @filename)</p>

</font>    SET @l = CASE @nextcommat WHEN 0 THEN 128 ELSE (@nextcommat-1) END</p>

</font>    SET @filename = SUBSTRING(@filename, 1, @l)</p>

</font>    IF (LEN(@filename)>0) INSERT INTO #filetab (filename) VALUES (@filename)</p>

</font>    SET @i = @i + @l + 1</p>

END

SELECT</font> COUNT(*) FROM #filetab</p>

DROP</font> TABLE #filetab</p>

</font>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.

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.

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

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

EPF.lk launched

An okay start, they at least have some information on taking loans and stuff, the highlight being the forms are available for printing. The site though is doggone slow just to serve out plain static html.

http://www.epf.lk/

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.

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)

Sysinternals saved my day. Again!

So I got into office early today and was greeted with this error when I fired out Outlook: Cannot start Microsoft Office Outlook. Cannot open the Outlook window. The set of folders cannot be opened. The file D:\Merill\Mail\Personal Folders.pst cannot be opened.

The only promising link from searching (hmm.. I wanted to write googled, but since I used Live Search what should I say ‘lived’) around on the internet was this. But trying to start up in safe mode didn’t work.

I then fired up FileMon, made a filter to show only ‘outlook’ and then ran Outlook as expected the error occured again. I then went into FileMon to figure out what exactly was happening and there it was an Open request to D:\Merill\Mail\~Personal Folders.pst was returning a ‘Sharing Violation’.

Now having to figure out which process was holding on to this file I started up Process Explorer and did a search for the processes holding a handle to ‘personal folders’ and there it was, a rogue Outlook.exe (must have crashed earlier). So I killed of the outlook.exe process and got my Outlook running back again.

So what does this all prove. If you have any problems with files not being accessible first try restarting Windows.

Three reasons to attend Tech.Ed this year

  1. Sessions: There will be a total of 60 sessions and 8 (HOLs). This is nearly double the number of last year’s sessions. Your getting more bang for your buck this time around.
  2. Speakers: The speaker list this year is an impressive lineup with the likes of ‘Matty’ Matthew Hardman (voted best speaker for TechEd 2002), Steve Riley (the man hackers hate and it pro’s love), Dr. Nitin (super trainer), Kevin De Souza, Chad Hower, Vinod Kumar (dev guru), the list goes on…
  3. Career Calculus

In basic calculus we learned that the first derivative of a function is the "rate of change" of the value of that function with respect to another variable.  In the case of your career, the other variable is time.  The basic equation for a developer career looks like this:

C = G + LT

C is Cluefulness.  It is defined as an overall measure of your capabilities, expertise, wisdom and knowledge in the field of software development.  It is the measure of how valuable you are to an employer.  It is the measure of how successful your career is.  When you graph your career, C is on the vertical axis.

G is Gifting.  It is defined as the amount of natural cluefulness you were given "at the factory".  For each individual, G is a constant, but it definitely varies from person to person.

L is Learning.  It is defined as the rate at which you gain (or lose) cluefulness over time.

T is Time.  It is on the horizontal axis of your career graph.

As you can see above, your career success is determined by three variables, only one of which you can control:

  • You obviously can't control T.  Time marches forward mercilessly at the same rate for everyone.
  • You also can't control G.  The truth is that some people are just naturally smarter than you are, and that's the way it is.  But G is not the sole determiner of your success.  I have known some truly gifted programmers with lame careers, and I have also known some less-gifted folks who have become extremely successful.
  • You can make choices which affect the value of L.  In fact, you do make choices which affect the value of L, every day, whether you know it or not.
  • </ul></blockquote>

    Need I say more?

Find out who’s linking to you

Find out which web pages are linking to you by using the linkdomain: operator like this.

While the Live search caters to end users, Yahoo’s search is geared towards webmasters and gives much more detail and lets you switch between various views. 

Hmm.. the other search engine?