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:

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:

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.

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.

From the Blogs

Don’t let bad data sneak up on you when and where you least expect it. Ferret out bad data with Melissa Data’s newest Profiling Component for SSIS. Learn how to take control of your data using knowledge-base-driven metadata. The truth shall set you free!...More

Now that we’ve outlined the process to let servers in a SQL Server AlwaysOn Availability Group "talk to each other" by means of setting up linked servers, it’s possible to set up some additional or improved checks on Availability Group Health....More

In my previous post, I provided a high-level outline of the core logic (and rationale behind that logic) that would be needed to set up regular synchronization checks on SQL Server Agent Jobs for servers where AlwaysOn Availability Groups have been deployed. In this post, I’ll walk through the steps--and the code--needed to setup those checks....More