SQL Server Performance

how to optimize this query

Discussion in 'Performance Tuning for DBAs' started by samham, Apr 5, 2003.

  1. samham New Member

    this is a search stored procedure having as parameter a dynamic sql query to be executed.

    is using a temporary table to perform paging within the procedure a good way to do so or may i use other ways? here's the procedure:

    CREATE PROCEDURE Search
    @query nvarchar(4000),
    @page int,
    @size int,
    @totalSize int output
    AS
    set nocount on

    DECLARE @Start int, @End int
    SET @Start = (((@Page - 1) * @Size) + 1)
    SET @End = (@Start + @Size - 1)

    create table #temp(rowID int IDENTITY(1,1) PRIMARY KEY,paraID int,paratitle nvarchar(400),paraDate datetime,source nvarchar(200),para nvarchar(200))
    insert into #temp(paraID,paratitle,paraDate,source,para)
    exec sp_executesql @Query
    set @totalSize=@@rowcount

    select #temp.paraID,paratitle,paraDate,source,para from #temp
    where (RowID >= @Start) AND (RowID <= @End)

    DROP TABLE #Temp
    set nocount off

  2. bambola New Member

    Wheather it is possible or not to optimize this procedure depends much on what you have in your SQL string, and on how many rows are returned from the query. With a better understanding of how your query looks like, it might be possible to help more.

    In the mean I can think about a few things to consider.

    when using sp_executesql 1- you are exosed to SQL Injection, especially if you are not using paramaters but passing a string. 2 - you have to give a SELECT permission to the table. The permission problem can be resolved by crating a view with only the columns you need.

    Creating a temp table locks temp database and when possible it is better to avoid it when possible. Sometimes you can bebefit from using the table datatype. Those tables are built in the memory and not physically created and they are many times faster.

    In this case, since you are doing INSERT INTO... exec... you cannot use a table datatype. But if you come up with a "normal" query, you can try to test it and see what works better for you.

    Try to limit the number of columns you are inserting into the table. It can be enough to have an IDENTITY column and the primary key of your_table. Then you can join them both to get all the columns you need only for the rows you are returning. Make sure the column you are joining has an index (the one on your_table). Again this is something you should test.

    BTW, you can use the value in @@rowcount after the INSERT to calculate the number of pages returned from the query.

    HTH,

    Bambola.
  3. samham New Member

    thank you for ur reply,

    about the query, it is built dynamicaly based on search items, what i am doing is joining 6 tables to the main table (these tables represent intermediate tables for a many to many relation with the main table) and i am only retreiving fields from the main table.

    I am wondering if it's better to not join these tables and instead use subqueries since i am not retreiving any of their fields i am only using them to narrow my search.

    like for example instead of using:

    select paraID,paraTitle from para,country where para.paraID=country.paraID and country.countryID in (1,2,3)

    use:

    select paraID,paraTitle from para where paraID in (select paraID from country where countryID in (1,2,3))


    as for the temp table i will only fill 2 fields (Identity and paraID) as u suggested

    and to limit my search results i will use a top N in the query where N represents the last row i will retreive (@End) and then take results from @start to @end


    Thanks again
  4. bambola New Member

    quote:Originally posted by samham
    like for example instead of using:
    select paraID,paraTitle from para,country where para.paraID=country.paraID and country.countryID in (1,2,3)
    use:
    select paraID,paraTitle from para where paraID in (select paraID from country where countryID in (1,2,3))
    In this case try to use EXISTS


    SELECT paraID, paraTitle
    FROM para a
    WHERE EXISTS
    (
    SELECT NULL
    FROM country c
    a.paraID = c.paraID
    and countryID in (1,2,3)
    )

    quote:
    and to limit my search results i will use a top N in the query where N represents the last row i will retreive (@End) and then take results from @start to @end
    This will work as long as you are not using ORDER BY. Otherwise the whole result set will be prefered anyway.
    Note that if using TOP you will not be able to return the actual number of rows returned from query, therefore could not calculate number of pages. I don't know if you need it or not...

    Bambola.
  5. gaurav_bindlish New Member

    I would like to add at this point - Create the temporary table in a saperate query having just the primary key of the table obtained from the query. Now in another stored procedure (fetchin the data pagewise), have a join of the temp table with the resultset of the stored procedure to get the results. If it is difficult to join this way, u can write the query as a function as functions can be used in joins.

    Saperating the creation and population of temp table will have distict advantages
    - The population of temporary table will be done only once and so it saves lot of processing time.
    - The execution plan of the stored procedure used for obtaining paged data will be cached. Creating a temp table within the sp will lead to recomplie of the exec plan every time the query is eecuted.
    - Here note that the a temp table can be replaced by a permanent table as well which can be truncated at the end of the query. This will help increase the concurrency of application. See new performance tip on main page. Having a permamnent table will give u the advantage that u can crete permanent indexes on the table which will improve join performance.

    As bambola said, having only the primary key in the temp table will have the advatage of handling less data but yeah even if it is not possible to do so, the temp table should be populated only once and that also saperate from the select query.

    HTH...

    Gaurav
  6. bambola New Member

    quote: The population of temporary table will be done only once and so it saves lot of processing time.
    temp table is being populated only once.

    quote:Having a permamnent table will give u the advantage that u can crete permanent indexes on the table which will improve join performance.
    The only index you need on the temp/datatype table is the one on the IDENTITY column, since the first where codition (WHERE RowID BETWEEN @start AND @end) will leave us with a small number or rows. Creating another index can be costly (it will be created on all the result set) and is not needed.

    Bambola.
  7. samham New Member

    ok so i will use the EXISTS and for the TOP u r right i need to calculate the number of pages so i cant use it.

    and concerning the temp table:

    if as bambola said the temp table is beeing populated only once so there's no need for another procedure to create the temp table

    so my procedure will look like this:

    CREATE PROCEDURE Search
    @query nvarchar(4000),
    @page int,
    @size int,
    @totalSize int output
    AS
    set nocount on

    DECLARE @Start int, @End int
    SET @Start = (((@Page - 1) * @Size) + 1)
    SET @End = (@Start + @Size - 1)

    create table #temp(rowID int IDENTITY(1,1) PRIMARY KEY,paraID int)
    insert into #temp(paraID)
    exec sp_executesql @Query
    set @totalSize=@@rowcount

    select para.paraID,para.paratitle,para.paraDate,para.para
    from #temp,para
    where para.paraID=#temp.paraID
    and #temp.RowID between @Start AND @End

    DROP TABLE #Temp
    set nocount off


    where the @query selects only the paraID and uses EXISTS
    is everything right?
  8. bambola New Member

    it looks ok. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />One little thing. I would change the order in the where clause to <br /><br />where #temp.RowID between @Start AND @End<br />and para.paraID = #temp.paraID<br /><br />Bambola.<br /><br />
  9. samham New Member

    Ok

    thanks a lot for ur help
  10. gaurav_bindlish New Member

    I would again like to mention that creating a emp table in the stored procedure will lad to recompile of the plan everytim the sp is executed.

    Gaurav
  11. samham New Member

    Gaurav can u please elaborate ur idea about seperating the 2 procedures.

    so u mean in a first procedure i only create the temp table having 2 columns rowID and paraID and then insert the data in the second procedure and join it with the main table to get the results ?

    if this is not what u meant can u please provide some basic sample code.
    Thank you.
  12. gaurav_bindlish New Member

    See What i understood initially from the procedure was that u will be calling it time and again to get the data for various pages. I mean u will be passing the page no. and size required to be displayed by the application and obtain the resultset to be shown to user. When the user wants the nexp page to be displayed, u'll again call this stored procedure to get the net page. So I asked you to saperate the logc of creating and populkating the sp in another procedure so that the selection can be done from the temp table and so i don't have to populate the data time and again. That would have meant that you execute a tored procedure which will create temp table and poplute the data in one stored procedure. And when the user needs to see any data, u shall query the table to get the results.

    But from the discussions in this forum, I concluded that probably u need to execute the proc only once and so I finally gave the suggestion of saperating the table creation logic from the data selection logic.

    This again means the same thing that u first execte a stored procedure to poulate the data in temp table and then execute another sp to page thro' the data. For this u can take help from this sp-

    GetPage - paging functionality
    http://www.sqlservercentral.com/scripts/contributions/305.asp


    HTH....

    Gaurav
  13. samham New Member


    ok thx i will try it.
  14. bambola New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by gaurav_bindlish</i><br /><br />I would again like to mention that creating a emp table in the stored procedure will lad to recompile of the plan everytim the sp is executed.<br />Gaurav<br /><hr height="1" noshade id="quote"></blockquote id="quote"></font id="quote"><br />No it will not. Try to open a trace, add SP<img src='/community/emoticons/emotion-7.gif' alt=':S' />tarting SP:Completed SP:Recompile and see for yourself.<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />Here note that the a temp table can be replaced by a permanent table as well which can be truncated at the end of the query. This will help increase the concurrency of application. See new performance tip on main page. Having a permamnent table will give u the advantage that u can crete permanent indexes on the table which will improve join performance.<br /><hr height="1" noshade id="quote"></blockquote id="quote"></font id="quote"><br />I fail to see the advantage of a permanent table over a temp one. Actually, I can think about a few reason why one should not use permanent table, including the index creation you are stating as an advantage. <br /><br />Gaurav - In an ideal world we be better to avoid using temporary tables cursors etc. But we are not living in an ideal world, and sometimes temporary table or cursors are the only or the better way to perform your task. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Bambola.
  15. gaurav_bindlish New Member

    If you have gone thro the tip that I mentioned, if the sp mentioned is exceuted inside a transaction, the system tables in tempdb will be locked during the sp execution. This means that other users will not be able to execute the same sp while one user is executing the same. That is why I mentioned hacving a permanent table has a better chance of increasing concurrency.

    Gaurav
  16. gaurav_bindlish New Member

    Clearing the point on the recompilation of stored procedures when DDL is present inside the procedure - The stored procedure is recompiled only when the structure of the tables involved in the query changes. This applies to change in the index as well. So if i create a clustered index on the table, the stored procedure is recompiled.

    I mentioned this here although this is not applicable in thic case just to clarify the confusion.

    Gaurav
  17. bambola New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote">... the system tables in tempdb will be locked during the sp execution. This means <br />that other users will not be able to execute the same sp while one user is executing the <br />same. That is why I mentioned hacving a permanent table has a better chance of increasing <br />concurrency.<hr height="1" noshade id="quote"></blockquote id="quote"></font id="quote"><br />I'm not sure I know to which article do you refer. I have seen one that was talking about <br />lock problems in tempdb in SQL Server 6.5 and 7. I assumed we are talking about SQL 2000 <br />(table datatypes do not exist in previous versions).<br /><br />As far as I know, and maybe I am wrong, the lock is placed on system tables only during <br />creation of the table. <br />To test it I created the following procedure:<br /><pre><br />use pubs<br />GO<br /><br />CREATE PROCEDURE testing_locks<br />AS<br />BEGIN TRANSACTION t1<br />SELECT TOP 100 *<br />INTO #test_tempdb<br />FROM authors WITH (NOLOCK)<br /><br />WAITFOR DELAY '00:00:05'<br /><br />DROP TABLE #test_tempdb<br />COMMIT TRANSACTION t1<br />GO<br /></pre><br />I opened a trace with the follwing flags SP<img src='/community/emoticons/emotion-7.gif' alt=':S' />tarting SP:Completed SP<img src='/community/emoticons/emotion-7.gif' alt=':S' />tmtStarting and <br />SP<img src='/community/emoticons/emotion-7.gif' alt=':S' />tmtCompleted. The I ran the procedure from 2 different windows in QA one after another.<br /><br />The trace shows that they are both executing at the same time, and not one at a time.<br />Try it and see for yourself <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br /><br />A few other things to take into consideration before taking the permanent table approach.<br />I might be wrong about something, so if anyone wants to correct or add to it I would be more than happy to learn something new.<br /><br />- Although temporary table are logged, they are only logged in a minimal way that will <br />enable rolling back a transaction. The are logged in the tempdb. <br />This means that those operations will be done faster, and that your user database log will not be affected by it as it would with permanent table.<br /><br />I am not really clear on how you were planning to use permanent table here. You have mentioned truncating them at the end of the query. <br />If you were planning it to be used by only one user at any given time, you will<br />definately be facing some lock ptoblems. <br /><br />Try to create this procedure, open the same trace and again run it one after another from 2 different windows in SQ.<br /><br /><pre>use pubs<br />GO<br /><br />SELECT *<br />INTO test_tempdb<br />FROM dbo.authors<br />WHERE 1 = 0<br />GO<br /><br />CREATE PROCEDURE testing_locks_permanent<br />AS<br />BEGIN TRANSACTION t1<br />WAITFOR DELAY '00:00:01'<br /><br />INSERT INTO test_tempdb<br />SELECT TOP 100 *<br />FROM authors WITH (NOLOCK)<br /><br />truncate table test_tempdb<br /><br />COMMIT TRANSACTION t1<br /><br />--drop table test_tempdb</pre><br /><br />You will see that the process that first reaches the INSERT will go on until transation is commited, and only then the other process can go on with it's transaction. <br /><br />You might have meant for the table to serve more than one user at a time, and maybe also keeping the result set there for him to avoid populating it again and again (and I will not refer to data modifications that can occure after the result set was built). <br />This means even more problems.<br /><br />To begin with, you will have to add a column to indicate which rows belong to which user. We have to take into consideration that a user might open more than one session, so this should be handles as well. In this case, there is no way you can avoid indexes or you will end up with table scans for each query. A clustered index might be more appropriate since you will be doing range queries. Of course it will be created on the whole result set even if not really needed (remember that using the temp table left us with very few rows to join to the main table).<br /><br />You will also have to find a way to know what were the current search parameters. Another things to manage.<br />And of course, you would want to have some job running on the table and DELETING (since truncate in this case will not be possible) all rows older then some_date. <br />All those operations, will be logged of course (unless you are using simple recovery model). Obviously, some of the operations here will result a table lock.<br /><br />Keeping in mind all these problems, working with temp table doesn't seem such a terrible idea to me. I would prefer working with table_datatype or a view when possible, but in this situation temp table seem to me like a good enough solution. Certainly better and easier to maintain than permanent table. <br /><br />I think I have another possible solution to this problem (the initial one <img src='/community/emoticons/emotion-1.gif' alt=':)' /> but I'll post this first. It's long enough anyway. <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />Bambola.
  18. gaurav_bindlish New Member

    I was refering to the article-

    If you have to use a temp table, don't create it from within a transaction. If you do, then it will lock some system tables (syscolumns, sysindexes, syscomments), preventing others from executing the same query, greatly hurting concurrency and performance. In effect, this turns your application into a single-user application.

    To avoid this problem, create the temporary table before the transaction. This way, the system tables are not locked and multiple users will have the ability to run this same query at the same time, helping concurrency and performance. [7.0, 2000]

    on pagehttp://www.sql-server-performance.com/temp_tables.asp

    Let me explain this. This behaviour was very prominent in SQL 6.5 because row level locking was not supported. So the creation of temp tables lead to locking of the entire page of tempdb system tables and hence the loss of concurrency was observed. Now this behaviour will not be that prominrnt in SQL 2k. But when the no. of concurrent users increase, this will definately make a difference.

    Gaurav
  19. bambola New Member

    quote:If you have to use a temp table, don't create it from within a transaction

    And where in the code above (samham's) did you see a transaction ????????

    Bambola.

Share This Page