Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Aug 24, 1999

SQL Sam and the Evil Twin - Part 1 - Page 3

By Steve Hontz

SQL Sam and the Evil Twin - Solution!

SQL Sam knew that the query optimizer generates a query plan for a stored procedure the first time it is executed- and then sticks with it. Thus, if the parameters passed in are not typical of most uses of the procedure, the query plan chosen may not be optimal. For example, with one set of parameters, the optimizer may decide that a table scan is the way to go. In another case, a clustered index might be a better choice.

In Fred's case, it's even worse. Fred essentially combined two unrelated queries into one procedure. The optimizer generates the query plan based on the first execution - the one Fred did with the product type as 'BUSINESS'. The optimizer made a query plan for each SELECT statement, based on the value of ARG1 and the index distribution statistics. The optimizer couldn't make use of an index on color for the SELECT statement that handles @TYPE='HOUSE' because the value of ARG1 was a price the first time the procedure was called. So, it opted to use a table scan for any time it had to do a HOUSE product lookup. All subsequent users of the procedure paid the price for Fred's cleverness.

Sam suggested that the procedure be split into three separate procedures, with the main one calling either a HOUSE product lookup procedure or a BUSINESS lookup procedure. Sam also mentioned that Fred should look into the WITH RECOMPILE option. If the values that Fred uses in stored procedures are widely varying, and could result in different query plans, the WITH RECOMPILE option could save time by insuring that the optimizer picks a plan that is most appropriate for the current data.

Go read more exciting SQL Sam Cases!

See the story behind the story in Behind the SQL: The Making of SQL Sam!


Steve Hontz is President of The Bit Corner, Inc., a Phoenix-based consulting firm specializing in Windows NT, C++, and SQL Server development. You can reach him at steve@bitcorner.com.




MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date