Writing Efficient Queries to Return Inventory Items

Congratulations to Ahmad Bahr Mohamed and Len Binns. Ahmad Bahr Mohamed, a DBA/Database developer at Applied Industrial Technology (AIT) in Cleveland, Ohio, won first prize of $100 for the best solution to the May Reader Challenge, "Running SQL Server 2000 Queries in SQL Server 2005." Len Binns, a Senior Programmer/Analyst at the University at Buffalo's School of Dental Medicine, won second prize of $50. Here’s a recap of the problem and the solution to the May Reader Challenge.

Problem:

David is a DBA who manages several SQL Server 2000 database servers. His current project is to test his applications against a database server running SQL Server 2005. As part of the evaluation, David needs to verify queries and stored procedure calls made from some of the applications that collect various statistics in the production environment. One particular SQL Server 2000 database query produces an error on the SQL Server 2005 server. The following snippet shows the query and error:

SELECT r.name, p.runid, p.attr, CAST(p.value AS int) AS measure 
FROM ProcStats AS p 
JOIN Runs AS r 
ON r.id = p.runid  
WHERE p.attr = r.primary_attr AND CAST(p.value AS int) > 20000  
/*
Server: Msg 245, Level 16, State 1, Line 1 
Syntax error converting the varchar value '1/1/2000' to a column of data type int.  
*/ 

The query uses the sample data in the following code:

CREATE TABLE Runs ( 
id int NOT NULL identity primary key, name varchar(30) NOT NULL, primary_attr varchar(30) 
NOT NULL 
)
CREATE TABLE ProcStats ( 
id int NOT NULL identity primary key, runid int NOT NULL references Runs(id), attr varchar(30) 
NOT NULL, value varchar(30) NOT NULL 
)   
INSERT INTO Runs values ('r1', 'a2') 
INSERT INTO Runs values ('r2', 'a2')  
INSERT INTO ProcStats values( 1, 'a1', '1/1/2000' ) 
INSERT INTO ProcStats values( 2, 'a1', '1/1/2000' )   
INSERT INTO ProcStats values( 1, 'a2', '8348' )  INSERT INTO ProcStats values( 2, 'a2', '192487' ) 

Without making any schema changes help David to do the following:
1. Identify the cause of the error.
2. Rewrite the query so that he can eliminate the error and ensure that the query runs on both SQL Server 2005 and 2000.

Solution:

David determines that evaluating the CAST expression on rows that don't contain the attr value a2 causes the error. Because the ProcStats table's value column stores different data types, the conversion to integer won't work for all rows. Generally, in SQL Server the order of predicate evaluation in the WHERE clause isn't guaranteed. The query optimizer can rearrange the predicates and evaluate them in different orders. (SQL Server 2005 has better optimization techniques than SQL Server 2000 for specifying which expressions to evaluate before others in predicate evaluation.) The error results from the conversion of the datetime value in the value column to an integer.

As shown in the following query, David must rewrite the predicate that contains the CAST expression:

SELECT r.name, p.runid, p.attr  , CASE ISNUMERIC(p.value) WHEN 1 THEN CAST(p.value AS int) end AS measure 
FROM ProcStats AS p 
JOIN Runs AS r 
ON r.id = p.runid 
HERE p.attr = r.primary_attr 
AND CASE ISNUMERIC(p.value) WHEN 1 THEN CAST(p.value as int) end > 20000   

The CASE expression ensures that the query performs the CAST only on rows that contain a numeric value in the attr column. If David reorders the predicate and evaluates it before the p.attr = r.primary_attr search condition, the CASE expression will return NULL for the rows instead of performing the CAST expression. In addition to the changes to the WHERE clause, the SELECT list should also perform the same check as a best practice.

The SQL Server 2005 Books Online (BOL) topic “Behavior Changes to Database Engine Features in SQL Server 2005” ( http://msdn2.microsoft.com/en-us/library/ms143359(SQL.90).aspx ) documents this query optimizer behavior change. To read about the change, open the BOL topic and click in the Transact-SQL table under the Expressions in queries feature.

In addition, the topic “Troubleshooting Errors and Warnings on Query Expressions” ( http://msdn2.microsoft.com/en-us/library/ms188295(SQL.90).aspx ) contains more detail about the behavior.

JUNE READER CHALLENGE:

Now, test your SQL Server savvy in the June Reader Challenge, "Writing Efficient Queries to Return Inventory Items" (below). Submit your solution in an email message to [email protected] by May 18. Umachandar Jayachandran, a SQL Server Magazine technical editor, will evaluate the responses. We’ll announce the winner in an upcoming SQL Server Magazine UPDATE. The first-place winner will receive $100, and the second-place winner will receive $50.

Problem:

Alex is a database architect who has designed an inventory application for his company. The application uses a SQL Server 2000 server to store the data, and the inventory database consists of two main tables: Suppliers and Items. The code below creates the tables containing the relevant columns and sample data.

CREATE TABLE Suppliers (SupplierId INT NOT NULL PRIMARY KEY, Weight INT 
NOT NULL CHECK(Weight >= 0) UNIQUE)
CREATE TABLE Items (Item VARCHAR(50) NOT NULL, SupplierId INT NOT NULL
REFERENCES Suppliers(SupplierId),  PRIMARY KEY(SupplierId, Item)) 
INSERT INTO Suppliers VALUES(1, 30) 
INSERT INTO Suppliers VALUES(2, 20) 
INSERT INTO Suppliers VALUES(3, 10) 
INSERT INTO Items VALUES ('item1', 1) 
INSERT INTO Items VALUES ('item1', 2) 
INSERT INTO Items VALUES ('item2', 2) 
INSERT INTO Items VALUES ('item2', 3)     

The Suppliers table stores each supplier of a particular item in the inventory. The inventory application uses an algorithm to assign a weight to each supplier. If multiple suppliers provide a particular item, the supplier that has the lowest weight has higher priority. (You can set the priority according to a combination of factors--e.g., availability, customer feedback, price. In this challenge, the priority indicates the item's availability.)

Help Alex write an efficient query that returns each item with the preferred supplier (i.e., the one with the lowest weight), and explain how he can use Query Analyzer tool to verify the various queries’ efficiency.