I have a stored procedure which is retrieving data using 20 tables.
Sample of the procedure:
CREATE PROCEDURE GetEnquiries
(
@EnquiryDate DATETIME = NULL
)
AS
DECLARE @querySELECT VARCHAR(MAX) = ''
DECLARE @queryWHERE VARCHAR(MAX) = ''
DECLARE @queryExtraColumns VARCHAR(MAX) = ''
DECLARE @queryReturnResults VARCHAR(MAX) = ''
-----------------------------------------------------
--Create temp table
-----------------------------------------------------
SET @querySELECT = '
CREATE TABLE #tempResults
(
EnquiryId INT,
Cost Decimal(18,2),
CustomerName VARCHAR(50),
EnquiryStatus VARCHAR(50),
ContactNumber VARCHAR(50),
NumberOfVisits INT
) '
-----------------------------------------------------
--Insert into temp table
-----------------------------------------------------
SET @querySELECT = '
INSERT INTO #tempResults
(
EnquiryId ,
Cost ,
CustomerName ,
EnquiryStatus ,
ContactNumber
) '
-----------------------------------------------------
--SELECT
-----------------------------------------------------
SET @querySELECT = '
SELECT
e.EnquiryId ,
e.Cost ,
c.CustomerName ,
e.EnquiryStatus ,
c.ContactNumber
FROM Enquiry e
INNER JOIN Customers c ON e.CustomerId = c.CustomerId '
-----------------------------------------------------
-- WHERE
-----------------------------------------------------
IF(@EnquiryDate IS NOT NULL)
BEGIN
SET @queryWHERE = @queryWHERE + ' CONVERT(VARCHAR(10),e.EnquiryDate,23) >= ' + ''''+ CONVERT(VARCHAR(10),@EnquiryDate,23) + ''''
END
--- There are at least 14 parameters used in WHERE operation the above is just one of them
-----------------------------------------------------
-- Count NumberOfVisits
-----------------------------------------------------
SET @queryExtraColumns = '
;WITH NumberOfVisits AS
(
SELECT t.EnquiryId, COUNT(EnquiryId) AS NumberOfVisits
FROM NumberOfVisits v
INNER JOIN #tempResults t ON v.EnquiryId = t.EnquiryId
GROUP BY t.EnquiryId
)
UPDATE #tempResults
SET NumberOfVisits = u.NumberOfVisits
FROM #tempResults t
INNER JOIN NumberOfVisits u ON u.EnquiryId = t.EnquiryId
'
-----------------------------------------------------
-- return the results
-----------------------------------------------------
SET @queryReturnResults = '
SELECT
EnquiryId ,
Cost ,
CustomerName ,
EnquiryStatus ,
ContactNumber ,
NumberOfVisits
FROM #tempResults t
'
-----------------------------------------------------
-- Combine all the strings + DROP the temp table
-----------------------------------------------------
-- PRINT( @querySELECT + ' WHERE ' + @queryWHERE + @queryExtraColumns + @queryReturnResults + ' DROP TABLE #tempResults ')
EXEC( @querySELECT + ' WHERE ' + @queryWHERE + @queryExtraColumns + @queryReturnResults + ' DROP TABLE #tempResults ')
Some facts:
-
The above procedure is the simple form of the Stored procedure i am working on.
-
I am using SQL Server 2008
-
My Actual procedure has 15 parameters, all of them are used in WHERE clause. If the value is provided for a parameter, the parameter is included in the WHERE clause otherwise not.
-
There are at least 10 columns whos value comes from the GROUP BY condition like the one "NumberOfVisits" given in the above procedure.
-
I have indexes on all the Primary Keys & Foreign Keys.
-
I have indexes on all the columns that are used in the WHERE clause.
-
I have indexes on all the columns that are used in the GROUP BY clause.
Questions:
-
Q1: Is this is according to the best practice to create dynamic stored procedures following above pattern?
-
Q2: I got the output SQL of this procedure by using:
— PRINT( @querySELECT + ' WHERE ' + @queryWHERE + @queryExtraColumns + @queryReturnResults + ' DROP TABLE #tempResults ')
when i run that SQL it took the same time that was taken by the stored procedure, why? is the SQL should take less time? -
Q3: Is the above is the best practice to get the value of summary columns("NumberOfVisits") ?
-
Q4: is the above is the best way to create the WHERE clause dynamically?
-
Q5: Can i avoid the use of Temporary table by using some alternate in the above scenario?
-
Q6: What can i do to optimize this procedure?
Please forgive me, if my question is NOT clear or not a proper question.
Thanks for your valuable time & help.
Best Answer
I'm going to skip past your questions and try to offer broader guidelines/advice instead.
The definitive/canonical guide to dynamic SQL, the situations where it is applicable and where it can be avoided, is Erland Sommarskog's Dynamic Search Conditions in T-SQL. Read it, re-read, run through Erland's examples, make sure you understand the reasoning behind the recommendations.
You're dealing with a fairly common scenario and the approach you've taken is not unusual. A couple of points worth highlighting:
Now the most important part of getting these kinds of searches right... apply the 80/20 rule.
The majority of calls to your procedure are likely to comprise a relatively small number of the possible variations of parameters. You cannot create optimal indexes for all combinations of 15 parameters, so identify the most common patterns, create static stored procedures for these and index for them appropriately. Deal with the remaining combinations with dynamic SQL, following Erland's best practices.
In these scenarios, you will often find the usage patterns closer to 95/5 than 80/20 so the additional work of creating static procedures is not as labour intensive as it seems at first glance.