Discussion on:

Optimize SQL Server queries with these advanced tuning techniques

Thanks again Tim. I've been following this line of posts because the topic has been speed. Thanks for pointing out the STATISTICS IO option. This is one of those "nuggets" that I always look forward to discovering and now that I know about it, I plan on using it often. Also, I never would have guessed that using a Transaction would actually speed up the execution. In fact, I would have put my money on the converse. I'm still learning (and feel I have a long way to go)!

One question. In your "note" about transactions: "If there are too many statements inside a transaction, it will increase the duration of the transaction, which increases the amount of time locks are held on the objects inside of the transaction". Do you have an article that discusses this further? While I feel I understand the basics of transactions, I'm looking for more input on them because I've recently created a procedure to update customer accounts during our off-hours and it currently takes 7 minutes to complete. I have no idea if this is good or bad from a speed standpoint, all I know is that it works. I know it runs in one transaction, so I assume for 7 minutes, all of our cust accounts are locked. I?m guessing that breaking it down into small groups of customer blocks (using a loop) would be safer and speed things up. Thanks for any references or input.

Glad you liked it. I've written a couple of articles that mentions how transactions work, but doesn't necessarily go into detail about best practices for designing them. Here are those links:
http://articles.techrepublic.com.com/5100-9592_11-6185492.html

http://articles.techrepublic.com.com/5100-9592_11-6186911.html

If you need any design help on your transactions, drop me an email.

Thanks,
Tim

I've been using SQL Server since 1990, I'm surprised to see a 'tip' stating to use JOIN instead of subquery. this is a BIG - HUGE mistake.
Whenever the SQL Server engine processes a JOIN it has to do a cartesian product among the tables, thus it uses a lot of resouces (memory). When the engine executes a query with a subquery the inner query will result in a small resultset, then the outer query will need to compare a column to those entries returned by the subquery, which will use much less server resources.
A Situation that you'll have dificulties using SubQuery is where you need to use multiple fields to relate both tables (you still be able to use Subquery if all fields are 'string' type through concatenation)

Wow...this is probably the most incorrect comment I've ever had posted on an article of mine. If you have worked with SQL Server for that long, you should know how joins work....which doesn't seem to be the case here. A JOIN does in NO WAY do a cartestian product. A CROSS JOIN does a cartesian product...which is basically joining all table rows from one table with all the rows from another. A JOIN finds those records in one that table match with records in another table. A subquery does limit your resultset, but at the cost of removing duplicates. I'll show the exact difference between the two execution plans in a future article.

I have a foot in both camps!

A specific subquery query in SQL7 returned a result in under a second,when the database was updated to SQL2005 the same query took over 10 seconds! I tried to optimize with new indices etc but no joy. Converting the query to a JOIN resulted in acceptable performance in SLQ2005.

If you have any explanation for this I would be interested.

Thats interesting that it was that large of a difference. I wouldn't be able to tell you for sure why without seeing the data structures and amount of data...but I'm guessing it might be some difference in the execution plan between the 2 versions. (Just a guess) Does the table that you were using for the subquery contain a lot of data?

I know I tried everything I could think of at the time and re-writing the query to eliminate the sub-query was the only option that worked.

the upgrade.

There was a known problem with 2005's optimisation engine being thoroughly mislead by 2000's stats.

Using the migrate turns off
AUTO_UPDATE_STATISTICS
you need to turn it back on to generate decent execution plans

You might also want to check the database compatibility mode

One last note 2005 is about 30% slower running ad-hoc queries than 2000.

and checked the compatibility mode. This was version 1 of SQL 2005, so maybe there were other issues too, I haven't tried to reproduce the problem with later versions.

Do you have a reference for 2005 being 30% slower than 2000? I must say that my subjective impression would agree with this figure, and I've had to create lots more indices to try and get decent performance!

I'll check if AUTO_UPDATE_STATISTICS is enabled.

then I performed my own test

Wrote a wee app

One table, one column, int and an identity.
Stuffed a thousand records in it

Generated a random number selected it from the table 10,000 times

Did that for prepared and unprepared on 2000 and 2005 on the same machine.

Also with both DBMS executing and one or the other.


Did it just about every favour I could, as far as I can make out the performance hit was a deliberate choice on MS's part.

MS response, use stored procedures.

Aside from having to rewrite 8 years of legacy code, there's a move to go DB agnostic and provide MySQL as an option as well, so that would cause more problems than it solved.

Cheers Bill. Thanks for f'all.

Thats odd. I've never noticed a performance hit since I've been using 2005...and it seems strange that MS would have suggested using sprocs...simply because they manner in which statements are cached doesn't really require the use of them. A statement a sproc runs will be stored in cache the same way a statement from a batch is ran. What was the output from STATISTICS IO when you ran the tests?

fantasic. pretty useful

Good article but if you want to really find out the ins and outs of SQL Query Tuning try this presentation by Bill Graziano (about an hour long).
https://msevents.microsoft.com/cui/WebCastEventDetails.aspx?culture=en-us&EventID=1032301639