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

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Aug 25, 1999

SQL Sam and the Evil Twin - Part 1 (with clues) - 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

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