We were doing a little bit of experimenting in the labs today with large SQL datasets and complex nested queries. We're talking about 1.5 million rows of data in our primary source table. a few blocks of subselects in the query...

Running it straight up as a CFQUERY using CFQUERYPARAM we were seeing about 7 second response times. Eh. not good enough.

Took the code. dropped it in a stored procedure...same result.

So we told Query Analyzer to show us the Execution Plan. Saw about 80% of the cost was a series of index scans - typical. not a big deal. but I figured we could tweak the indexes a bit. We added a couple extra ones manually but that actually increased response times from 7 to about 10 seconds. Oops!

It was at this moment we figured we'd see what happened when we let Microsoft drive. There's an option on the toolbar for the Index Tuning Wizard. We set it to run using the stored proc as the workload to be tuned. A few seconds later it reported it was done with an estimated 99% improvement. Yeah. right!

Imagine our surprise when running that proc was returning data in less than 1 second!

More experimenting will be done with other queries. The hope is that we didn't kill performance for the rest of the (mostly unwritten) application just to meet this one need. Either way. we're pleasantly surprised with how well this worked out.

Just goes to show you that we as developers have a LOT to benefit by learning more about proper database tuning. I know we've barely scratched the surface. There's a reason a good DBA makes a lot of money!