I have a stored procedure which is retrieving data using 20 tables. Sample of the procedure:

     CREATE PROCEDURE GetEnquiries 
        @EnquiryDate    DATETIME        = NULL

     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   ,
                ) '
     SET    @querySELECT = '
                    e.EnquiryId     , 
                    e.Cost          ,
                    c.CustomerName  ,
                    e.EnquiryStatus ,
            FROM    Enquiry e
                    INNER JOIN Customers c ON e.CustomerId = c.CustomerId '

     -- WHERE 
     IF(@EnquiryDate IS NOT NULL)
            SET @queryWHERE = @queryWHERE + ' CONVERT(VARCHAR(10),e.EnquiryDate,23) >= '  + ''''+ CONVERT(VARCHAR(10),@EnquiryDate,23) + ''''

     --- 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 = '
                    EnquiryId       , 
                    Cost            ,
                    CustomerName    ,
                    EnquiryStatus   ,
                    ContactNumber   ,
            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.


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

  • 1
    Why do you want to avoid using a temp table? They are quite handy for simplifying code. – datagod Nov 18 '11 at 16:55
  • @datagod: Thanks.They are increasing the execution time. I have checked the Execution Plan & the insertion in the temp table is taking too much time. – Yaqub Ahmad Nov 18 '11 at 17:25
  • the same question on SO – Jack says try topanswers.xyz Nov 19 '11 at 14:45

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:

  • Using temporary tables is probably unnecessary. Is there a reason they were introduced?
  • You have probably over-indexed the table. Read Kimberly Tripp's "just because you can, doesn't mean you should" article on the topic.
  • Because you've over-indexed on individual columns, you're probably lacking good covering indexes. With so many aggregations and such a wide range of search conditions, these will be a challenge to get right.

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.

  • Great Thanks, I found your answer really helpful. – Yaqub Ahmad Nov 22 '11 at 10:19

A1: 1st using EXEC is a performance killer;

A2. Have you digged the execution plan or done some profilling?

A3. This might very slow the way you are aproching the problem. You can use UDF's to make things faster when getting periferal data;

A4. Dynamic means the need to use EXEC. This have heavy costs on MS SQL;

A5. Yes. See A3;

A6. Split your procedure on smaller problems, do performance tests and try to use UDF's as stated. Index's would be also something that will helpyou doing things faster.

If you supply the structure of the used tables and relations (with index's) maybe you can get a more precise help on this.

  • What's your basis for A1/A4? There is nothing about usage of EXEC in the article you've linked. – Mark Storey-Smith Nov 21 '11 at 19:41
  • 1
    Also, what's the basis for using UDFs in A3/A4/A5? The majority of us avoid UDFs as they can have a negative impact on performance. – Mark Storey-Smith Nov 21 '11 at 20:10
  • I've pasted the wrong link. Fixed. UDF's may have impact on performance based on the way we use them. The goal is to use more simplified queries in particular on stored procedures and avoid dynamic queries as most as possible. Each usage scenario requires may require a particular solution and UDF don't need to be ruled out from the start. – Rui Marques Nov 21 '11 at 22:24
  • 3
    I still disagree with your opinion of EXEC. Erland's article (your updated link) highlights dynamic search conditions as one of the situations where dynamic SQL and EXEC/sp_execute are the most appropriate solution. On UDF, I've never encountered a situation were performance was improved through their use. I would welcome an example case. – Mark Storey-Smith Nov 21 '11 at 22:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.