A Dashboard Application

Congratulations to Dan Barrett and Anith Sen, an independent database consultant in Cordova, Tennessee. Dan won first prize of $100 for the best solution to the December Reader Challenge, "A Dashboard Application." Anith won second prize of $50. Here’s a recap of the problem and the solution to the December Reader Challenge.

Problem:

Terry, a systems analyst for the IT department, is developing a Web-based management dashboard. The data for the dashboard is stored in a SQL Server 2000 database. As part of one administrative function in the dashboard, the application requires the database to store user-defined attributes. The following code shows the table’s schema including the relevant columns to store the attribute values for various objects in the system.

CREATE TABLE AttribVals (   Id int NOT NULL,   Val varchar(30) NOT NULL,   PRIMARY KEY( Id, Val ))

The column ID in the AttribVals table represents generic objects in the dashboard application. The following script creates sample data for the table.

INSERT INTO AttribVals (Id, Val)VALUES ( 1, 'A' )INSERT INTO  AttribVals (Id, Val)VALUES ( 1, 'B' )INSERT INTO  AttribVals (Id, Val)VALUES ( 1, 'C' )INSERT INTO  AttribVals (Id, Val)VALUES ( 2, 'B' )INSERT INTO  AttribVals (Id, Val)VALUES ( 3, 'A' )INSERT INTO  AttribVals (Id, Val)VALUES ( 3, 'C' )

Terry is designing a stored procedure that the Web page will call to manage the user-defined attributes. The Web page needs to pass a list of attributes and get the corresponding objects’ ID value. Help Terry define the stored procedure with the required parameters. The stored procedure has to handle any number of attributes. For simplicity, assume that the length of all the values will be less than 8000. The stored procedure also has to retrieve the ID only if all the specified attributes are configured for the ID value. Using the sample data shown earlier, specifying A and C as attribute values should return 1 and 3. Similarly, specifying B should return 1 and 2.

Solution:

Terry can define the stored procedure with a varchar data type parameter, he uses to pass a list of attribute values as a comma-separated string to the stored procedure. This lets the stored procedure process any number of attribute values as specified by the Web page. To retrieve the IDs based on two attribute values, A and C, Terry can use the following query:

SELECT DISTINCT a1.Id   FROM AttribVals AS a1 WHERE NOT EXISTS (SELECT *      FROM (SELECT 'A' UNION ALL SELECT 'B'      UNION ALL SELECT 'C') AS f(Value)      WHERE NOT EXISTS(SELECT *   FROM AttribVals AS a2      WHERE a1.[ID] = a2.[ID]   AND f.Value = a2.Val))

The query checks that there are no values being searched for which are also not present in the table. This double-negative approach gives only the matching IDs that have all values searched for. Terry can also use a query like the following to do the same:

SELECT a.Id  FROM AttribVals AS a WHERE a.Val IN ( 'A', 'C' ) GROUP BY a.IdHAVING count(*) = 2

This query can be generalized as following:

SELECT a.Id  FROM AttribVals AS a WHERE a.Val IN (  ) GROUP BY a.IdHAVING count(*) = 

The comma-separated string of attribute values can be parsed into individual values by using a table-valued function. This lets Terry normalize the string into a set of values. The table-valued function that performs this operation follows:

IF objectproperty( object_id( 'fn_SplitStr' ) , 'IsTableFunction' ) = 1    DROP FUNCTION fn_SplitStrGOCREATE FUNCTION fn_SplitStr (    @SourceString varchar( 8000 ) ,    @Delimiter varchar( 5 ) = ',')RETURNS @VALUES TABLE( Position smallint IDENTITY NOT NULL, Value varchar( 8000 ) NOT NULL )ASBEGIN    DECLARE @Start smallint, @End smallint    SET @Delimiter = coalesce( @Delimiter, ',' )    SET @Start     = len( @Delimiter )    IF LEFT( @SourceString, len( @Delimiter ) )  @Delimiter      SET @SourceString = @Delimiter + @SourceString    IF RIGHT( @SourceString, len( @Delimiter ) )  @Delimiter      SET @SourceString = @SourceString + @Delimiter    WHILE( 1 = 1 )    BEGIN        SET @End = charindex( @Delimiter, @SourceString, @Start + 1 )        IF  @End = 0 BREAK        INSERT INTO @VALUES( Value ) VALUES( substring( @SourceString, @Start + 1, @End - @Start - 1 ) )        SET @SourceString = stuff( @SourceString, 1, @End - 1, '' )    END    RETURNENDGO

A sample usage of this user-defined function (UDF) follows.

SELECT * FROM dbo.fn_SplitStr( 'a,b,c', default )SELECT * FROM dbo.fn_SplitStr( 'x~y~z', '~' )

This table-valued function can now be used in the stored procedure to return the list of IDs in a generic manner. The definition of the stored procedure follows:

CREATE PROCEDURE GetIdsForAttributes(@AttribVals varchar(8000))ASBEGIN    SELECT DISTINCT a1.Id      FROM AttribVals AS a1     WHERE NOT EXISTS (SELECT *      FROM fn_SplitStr(@AttribVals, DEFAULT) AS f     WHERE NOT EXISTS(SELECT *      FROM AttribVals AS a2     WHERE a1.[ID] = a2.[ID]   AND f.Value = a2.Val))ENDGO

The stored procedure GetIdsForAttributes can now be invoked by the Web page with a comma-separated list of values to get the matching IDs. Some of the sample calls for the stored procedure follow:

EXEC GetIdsForAttributes 'A,C'EXEC GetIdsForAttributes 'A,B,C'

The following is another implementation of the stored procedure using the GROUP BY approach:

CREATE PROCEDURE GetIdsForAttributes2(@AttribVals varchar(8000))ASBEGIN   SELECT a.Id          FROM AttribVals AS a         WHERE a.Val IN (SELECT f1.Value          FROM fn_SplitStr(@AttribVals, DEFAULT) AS f1)      GROUP BY a.Id        HAVING count(*) = (SELECT count(*)          FROM fn_SplitStr(@AttribVals, DEFAULT) AS f2)ENDGO

Lastly, the comma-separated list of values can also be parsed using a set-based logic with a table of numbers. The numbers table for this purpose can be created using the following statements:

IF OBJECT_ID('Nbrs') IS NOT NULL   DROP TABLE NbrsGOSELECT TOP 8000 IDENTITY(INT) "n"  INTO Nbrs  FROM master..syscolumns s1, master..syscolumns s2GO 

The stored procedure that uses the table of numbers to parse the comma-separated list of values in a GROUP BY query to get the results follows:

CREATE PROCEDURE GetIdsForAttributes3(@AttribVals varchar(8000))ASBEGIN   DECLARE @delimiter char(1)   SET @delimiter = ','   SELECT a1.Id      FROM AttribVals a1      JOIN Nbrs        ON a1.Val = SUBSTRING( @AttribVals, n,         CHARINDEX( @delimiter, @AttribVals + @delimiter, n ) - n )     WHERE SUBSTRING(@delimiter + @AttribVals, n, 1 ) = @delimiter       AND n 

JANUARY READER CHALLENGE:

Now, test your SQL Server savvy in the January Reader Challenge, "Eliminating Recompilation" (below). Submit your solution in an email message to [email protected] by December 16. 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:

Sam, a database architect for a company that auctions products online has a database hosted primarily on SQL Server 2000. The Web site for the auctions is transactional in nature. While investigating a performance problem, Sam notices lots of stored procedures performing various operations with temporary tables. By using SQL Server Profiler, Sam determines that several large stored procedures' performance problems are caused by excessive recompilations during execution. To see the kind of problem Sam's dealing with, run the sample stored procedure:

USE NorthwindGOIF object_id('dbo.ReportOrdersByProduct') IS NOT NULL    DROP PROCEDURE dbo.ReportOrdersByProductGOCREATE PROCEDURE dbo.ReportOrdersByProductASBEGIN    CREATE TABLE #od1 ( OrderID int, ProductID int )    INSERT INTO #od1 ( OrderID, ProductID )    SELECT od.OrderID, od.ProductID      FROM "Order Details" AS od    SELECT p.ProductName, count(*) AS #Orders      FROM #od1 AS od      JOIN Products AS p        ON p.ProductID = od.ProductID  GROUP BY p.ProductName    HAVING count(*) >= 25ENDGO

You can observe the behavior in Sam's production environment by setting up a Profiler trace that has the following events:

   SP:Starting   RPC:Starting   SP:StmtStarting   SP:Recompile   SP:Completed   RPC:Complete   Objects:Auto Stats

Observe the events during the sample stored procedure's first execution because the sample code is simple, so subsequent executions will use the cached plan. The recompilation of the stored procedure when it hits the SELECT statement mimics the behavior in the production system. Assuming that Sam wants to retain the temporary table logic and make minimal changes to the stored procedure, how can he reduce or eliminate the recompilation of the stored procedure for the SELECT statements that are accessing the temporary tables?