Creating an Easy Query Mechanism for Gathering Customer Data

Congratulations to Ahmad Bahr Mohamed, who recently won the May Reader Challenge. Ahmad Bahr Mohamed, a DBA/Database developer at Applied Industrial Technology (AIT) in Cleveland, Ohio, won an honorable mention for the best solution to the June Reader Challenge, "Writing Efficient Queries to Return Inventory Items." Here’s a recap of the problem and the solution to the June Reader Challenge.

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.

Solution:

Alex can get a list of items and their preferred suppliers by using the following query:

SELECT is2.Item, s2.supplieridFROM Suppliers AS s2JOIN (    SELECT i.Item, MIN(s1.Weight) AS LOWEST_weight    FROM Items AS i    JOIN Suppliers AS s1    ON i.SupplierId = s1.SupplierId    GROUP BY i.Item) AS is2ON is2.lowest_weight = s2.Weight;

This query determines the item and finds the supplier that has the lowest weight of all the suppliers that provide the item. This query is represented as the derived table "is2". Alex can join this query's result with the Suppliers table to get the associated SupplierId because the weight is unique for each supplier.

However, Alex realizes that he can simplify the query so that the self-join with the Supplier table can be eliminated. To achieve this single join, Alex combines the Weight and SupplierId column values to determine the minimum value. One way to combine the column values is to convert them into binary values and concatenate them. To connect the item to the minimum weight and SupplierId values, Alex can use the following query:

SELECT i.Item, MIN(CAST(s.Weight AS binary(4)) + CAST(s.SupplierId AS binary(4))) AS wsFROM Suppliers AS sJOIN Items AS iON i.SupplierId = s.SupplierIdGROUP BY i.Item

The values’ binary representation sorts the integer values from minimum to maximum. The MIN aggregate function returns the value that contains the lowest weight and the associated supplier. Now Alex can get the SupplierId from the concatenated binary value by using the following SUBSTRING function:

SELECT si.Item, CAST(SUBSTRING(si.ws, 5, 4) AS int) AS SupplierIdFROM (    SELECT i.Item, MIN(CAST(s.Weight AS binary(4)) + CAST(s.SupplierId AS binary(4))) AS ws    FROM Suppliers AS s    JOIN Items AS i    ON s.SupplierId = i.SupplierId    GROUP BY i.Item) AS si

Alex can verify the query’s efficiency by comparing the estimated cost of each query in a batch. A quick comparison in Query Analyzer (using the Display Estimated Execution Plan) reveals that in a batch consisting of the two queries, Query 2 costs about 4 percent less than Query 1. And by looking at the Server Trace output in Query Analyzer, Alex can verify that Query 2 performs fewer reads than Query 1.

JULY READER CHALLENGE:

Now, test your SQL Server savvy in the July Reader Challenge, "Creating an Easy Query Mechanism for Gathering Customer Data" (below). Submit your solution in an email message to [email protected] by June 15. 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:

Adam is a DBA for a hosting company that provides free and paid database access for its customers. The databases reside on SQL Server 2000 instances. Each instance can host up to 100 databases and contains a dbmaster database, which collects and maintains various statistics from the databases. A database-creation utility creates each customer's database and an AdminConfig table in each database. The AdminConfig table maintains different configuration information that the customer creates.

For ad-hoc reporting purposes or as part of a monitoring application, Adam often queries the AdminConfig information from each hosted database. Help Adam create a simple query mechanism in the dbmaster database while adhering to the following requirements:

To create the dbmaster database and several databases with the AdminConfig table, use the is as following sample setup:

CREATE DATABASE dbmasterCREATE DATABASE db1CREATE DATABASE db2CREATE DATABASE db3GOCREATE TABLE db1.dbo.AdminConfig ( id INT NOT NULL PRIMARY KEY, configname VARCHAR(30) NOT NULL, dbname AS db_name() )INSERT INTO db1.dbo.AdminConfig VALUES(1, 'a')CREATE TABLE db2.dbo.AdminConfig ( id INT NOT NULL PRIMARY KEY, configname VARCHAR(30) NOT NULL, dbname AS db_name() )INSERT INTO db2.dbo.AdminConfig VALUES(1, 'a')CREATE TABLE db3.dbo.AdminConfig ( id INT NOT NULL PRIMARY KEY, configname VARCHAR(30) NOT NULL, dbname AS db_name() )INSERT INTO db3.dbo.AdminConfig VALUES(1, 'a')GO