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.Id
HAVING count(*) = 2

This query can be generalized as following:

SELECT a.Id
  FROM AttribVals AS a
 WHERE a.Val IN (  )
 GROUP BY a.Id
HAVING 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_SplitStr
GO
CREATE FUNCTION fn_SplitStr (
    @SourceString varchar( 8000 ) ,
    @Delimiter varchar( 5 ) = ','
)
RETURNS @VALUES TABLE( Position smallint IDENTITY NOT NULL, Value varchar( 8000 ) NOT NULL )
AS
BEGIN
    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
    RETURN
END
GO

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))
AS
BEGIN
    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))
END
GO

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))
AS
BEGIN
   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)
END
GO

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 Nbrs
GO
SELECT TOP 8000 IDENTITY(INT) "n"
  INTO Nbrs
  FROM master..syscolumns s1, master..syscolumns s2
GO

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))
AS
BEGIN
   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 Northwind
GO
IF object_id('dbo.ReportOrdersByProduct') IS NOT NULL
    DROP PROCEDURE dbo.ReportOrdersByProduct
GO
CREATE PROCEDURE dbo.ReportOrdersByProduct
AS
BEGIN
    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(*) >= 25
END
GO

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?

Comments

Plain text