SQL Server Performance

Best practices for general search stored procs

Discussion in 'SQL Server 2005 General Developer Questions' started by Heartsbane, Oct 24, 2009.

  1. Heartsbane New Member

    I have a couple of questions about how to handle a basic/common scenario. Say you are developing a stored procedure that takes input parameters and then filters records in a table based on those inputs. For example, consider a table for Books with over 10 million rows in it. You create a stored procedure that takes the following inputs: @author, @title, @publish_date_start, @publish_date_end, @isbn, @price_min, @price_max. The parameters can pass a null value in which case you don't want to filter on that field/column. Also, once you've filtered the rows that match the input criteria, you want to not only return that result but also perform other SQL on that subset of data (such as a query that shows # books & total price by author using aggregate functions and GROUP BY).
    The typical approach I've seen to this problem is to create a temp table (e.g. #Books) and then declare a string that is used to create a T-SQL statement dynamically (including non-null parameters in a WHERE clause) to populate the temp table with the rows that match the input criteria. You can then select the entire result from the temp table and also then query the temp table as needed to perform any aggregate/summing or other additional operations.
    There are a few things I don't like about this approach and I'm looking for alternative ways to handle this scenario that improve performance. First, the result could include just a few rows or all rows, and if the result set is large (e.g. 99.9% of the table) then writing all those rows to a temp table (even if just the PK) can create performance/disk problems. Would a table variable be a better alternative or would this just create RAM problems? Is there another alternative, such a placing a virtual "marker" on rows that meet the input criteria, then running subsequent SQL on only "marked" rows? The closest I can think of would be using a temp table or table variable but only write PK values (not other columns) to minimize the size of these temp structures, but I'd like to know if there are other approaches I've not considered.
    Second, since the SQL statement for performing the initial filtering is being built dynamically in a varchar then executed, no compile-time execution plan can be established, etc. Are there ways to handle this type of search (including optional parameters) without using dynamically generated SQL strings to improve performance?
    Any thoughts?
  2. preethi Member

    I may prefer not to put everything immediately into temporary table. I may prefer to do some search on some "key" columns and they try on temp table for further search if necessary.
    Writing one statement which can do all wonders may not be practical unless you do not worry much about performance. Also, it is not possible to write different SQL statements for each combination of parameters. (If you have 10 optional parameters you need to 1024 different statements) I will take a balance here, finding the indexed columns and try to write a query based on them and do the search.
    On temp tables, the disadvantage of temp table is that you will cause more IO on tempdb and the overall duration of the search process will increase. But on the other hand, it reduced the lock time on your main table so the system is free to do other operations if needed.You need to strike a balance here.

  3. davidfarr Member

    Have you considered using Indexed Views ? A stored procedure can be made to dynamically create (or re-create) an Indexed View which can have either a unique name or a common name shared by all users. The parameters passed to the SP can be used to define and create the view, and subsequent queries can then be executed against that view. I have not done this very often myself, I have not had the need, so I cannot guarantee a performance benefit, but it may be worth looking into.
  4. Heartsbane New Member

    Hmmm - indexed views may be a good option in this case. Since the stored proc could be called concurrently by multiple users using different input parameters, I'll need to make sure the scope of the created index view is limited to the stored proc that created it (similar to a temp table) -- is this possible? I haven't worked with index views but I appreciate the suggestion! I'll check it out.
  5. satya Moderator

    Also for better search functionality of data FULLTEXT indexes are helpful too....

Share This Page