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.
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.
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.
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?