Hurdles and Wurdles...

I learnt a very important lesson today, and here it is:

... WHERE NOT Exists (Select SomeField FROM SomeRecords WHERE SomeCriteria)... Can be really really bad.

And I mean REALLY bad.

I wrote a SQL Server db to do some analysis for me. I wrote about it here back when I was trying to do the same thing in Access. Needless to say I gave up on that plan and rewrote the lot for SQL Server. Now up until yesterday, I hadn't really tried producing the output and everything all at once. I always did little bits, saw they worked and moved on. I think I did something yesterday to break it, somehow, though. Don't ask me what, just something.

So I fire up this stored procedure that does some crunching and produces some tables of numbers. Normally I reckon it'd take about 1 or 2 minutes to do it. I'm sure on one of my earlier versions it did.

I fire it up.

And wait...

And wait...

And wait...

After 3 hours I start thinking there's sopmething going wrong, so I look through perfmon looking at the usual SQL Server suspects (Disk IO queues, Pages being written out to disk, processor time and a whole bunch of other crap). All the numbers look to be what Star Trek types call 'Normal Operational Parameters'. No obvious problem. Nothing hardware's choking the system. Hmmm...

So at about hour 5, I think there's something up with my code. I kind of figured there would be, but I was hoping that I could get a pointer to what was taking the time from PerfMon. I was wrong.

So I was left taking wach and every bit of code apart. Every subquery. Every view, every criterion. Bugger.

Although having said that, I did manage to find the bit that was tripping it up. There was a 'WHERE EXISTS' clause in one of the views, and the subquery in there took ages and ages to run. So I'm guessing that that subquery was trying to run for each and every line in the view's source dataset. All 860,000 rows. No wonder it was taking its time.

So I changed that one bit. Instead of doing it that way, I just did a left join on the subquery and picked out the rows without a corresponding record in the subquery.

Doh!

It all tuned out nice in the end, though. It did mean that I cut down my stored procedure from 6 hrs+ (I never let it run all the way through in the end) to about 3 minutes.

Nice!

Just wanted to share.