SQL Server Performance

How store procedure use temporary table

Discussion in 'T-SQL Performance Tuning for Developers' started by zhenyuanDeng, Sep 15, 2006.

  1. zhenyuanDeng New Member

    Hi,

    When i call the procedure sp_jysczld_new from the Analysis Query, it runs slowly.
    But when i extract the sql scripts from the procedure sp_jysczld_new, it runs quickly.
    Why? I don't understand.

    store procedure sp_jysczld_new:
    --------------------------------------------------
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS OFF
    GO

    CREATE PROCEDURE sp_jysczld_new
    @gs_userid CHAR(16),
    @location char(10),
    @pri char (10)
    AS

    SELECT Z.ORDER_NUM,ZI.ORDER_ITEM,Z.CUSTOM_ID,ZI.PRODUCT_ID,ZI.PRODUCT_NAME,

    ZI.LENGTH,ZI.CDGC,ZI.HEIGHT,ZI.L_HEIGHT,ZI.U_HEIGHT,ZI.CATEGORY,ZI.COLOR_ID,ZI.SGQTYS,ZI.plannum, ZI.totalwgt,
    ZI.banglong,ZI.bangzs,ZI.qgzs ,ZI.BANG_GRADE,ZI.PRI, ZI.LOCATION,Z.END_DATE, ZI.NOTESZ AS NOTES--,ZI.MJCOUNT
    INTO #Z
    FROM ORDERZ Z INNER JOIN ORD_ITEMZ ZI ON Z.ORDER_ID=ZI.ORDER_ID
    WHERE (ZI.over_work in ( 'A','B','D') ) AND ZI.PC_MODE = 'Y' and ZI.sgqtys > 0
    AND (ZI.location = @location or @location = 'ALL')
    AND (ZI.pri = @pri or @pri = 'ALL')
    AND Z.order_num in ( select order_num from temp_order_id where username = @gs_userid)


    drop table #Z


    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO
    ------------------------------------------------------

    The scripts which i extract from the store procedure(the same as the store procedure):
    ---------------------------------------------------

    declare @gs_userid char(16)
    declare @location char(10)
    declare @pri char(10)

    set @gs_userid = 'mmm'
    set @location = 'ALL'
    set @pri = 'ALL'

    SELECT Z.ORDER_NUM,ZI.ORDER_ITEM,Z.CUSTOM_ID,ZI.PRODUCT_ID,ZI.PRODUCT_NAME,

    ZI.LENGTH,ZI.CDGC,ZI.HEIGHT,ZI.L_HEIGHT,ZI.U_HEIGHT,ZI.CATEGORY,ZI.COLOR_ID,ZI.SGQTYS,ZI.plannum, ZI.totalwgt,
    ZI.banglong,ZI.bangzs,ZI.qgzs ,ZI.BANG_GRADE,ZI.PRI, ZI.LOCATION,Z.END_DATE, ZI.NOTESZ AS NOTES--,ZI.MJCOUNT
    INTO #Z
    FROM ORDERZ Z INNER JOIN ORD_ITEMZ ZI ON Z.ORDER_ID=ZI.ORDER_ID
    WHERE (ZI.over_work in ( 'A','B','D') ) AND ZI.PC_MODE = 'Y' and ZI.sgqtys > 0
    AND (ZI.location = @location or @location = 'ALL')
    AND (ZI.pri = @pri or @pri = 'ALL')
    AND Z.order_num in ( select order_num from temp_order_id where username = @gs_userid)
    drop table #z

    ---------------------------------------------------

  2. satya Moderator

    Have you compiled the stored procedure and triggers too on the tables that are involved?
    Also what is the index strategy and their defragmentation methods.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  3. Adriaan New Member

    See if the response time improves if you use CREATE TABLE for the #Z temp table, rather than the SELECT INTO syntax:<br /><br />CREATE TABLE #Z (&lt;column definitions&gt<img src='/community/emoticons/emotion-5.gif' alt=';)' /><br /><br />INSERT INTO #Z (&lt;column list&gt<img src='/community/emoticons/emotion-5.gif' alt=';)' /><br />SELECT &lt;column list&gt;<br />FROM .....<br /><br />Your syntax can cause locking issues.<br /><br />Other than that, follow up on Satya's suggestions.
  4. Madhivanan Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br /><br />See if the response time improves if you use CREATE TABLE for the #Z temp table, rather than the SELECT INTO syntax:<br /><br />CREATE TABLE #Z (&lt;column definitions&gt<img src='/community/emoticons/emotion-5.gif' alt=';)' /><br /><br />INSERT INTO #Z (&lt;column list&gt<img src='/community/emoticons/emotion-5.gif' alt=';)' /><br />SELECT &lt;column list&gt;<br />FROM .....<br /><br />Your syntax can cause locking issues.<br /><br />Other than that, follow up on Satya's suggestions.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Both Select into and create table takes the same amount of time<br />In first case, Is locking the matter?<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  5. mmarovic Active Member

    Have you checked execution plans for both methods?
  6. Madhivanan Moderator

    I didnt check it but I should have checked [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  7. zhenyuanDeng 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 Adriaan</i><br /><br />See if the response time improves if you use CREATE TABLE for the #Z temp table, rather than the SELECT INTO syntax:<br /><br />CREATE TABLE #Z (&lt;column definitions&gt<img src='/community/emoticons/emotion-5.gif' alt=';)' /><br /><br />INSERT INTO #Z (&lt;column list&gt<img src='/community/emoticons/emotion-5.gif' alt=';)' /><br />SELECT &lt;column list&gt;<br />FROM .....<br /><br />Your syntax can cause locking issues.<br /><br />Other than that, follow up on Satya's suggestions.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Thanks everybody.<br />I had been try like Adriaan said, but it didn't work.And the database dose not has a triggers.<br />Will the execution plan different when involved in different environment, (such as the Query Analyzer, and the client application involved the store procedure)?<br />The index had not been changed all the time.
  8. zhenyuanDeng New Member

    When i use the profile to monitor the event, i found some message<img src='/community/emoticons/emotion-6.gif' alt=':(' />TextData)<br />"some event not report to the profile, because the server has been reach the max memory of the process"<br />and a lot of "Lock:Acquied", "Lock:Released" event class.<br /><br />My question is: if it reach the max memory of the process, shall i config the process's memory bigger than the setting now?
  9. zhenyuanDeng New Member

    And the cpu is 100%.
    Is there a block or lock?

    Any suggestions will be useful.Thanks
  10. zhenyuanDeng 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 Madhivanan</i><br /><br />I didnt check it but I should have checked [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />As Madhivanan said, the plan is not good, but i just want to know what happen, why the performances will be so different.
  11. mmarovic Active Member

    What I mean is that we can know more about the reason if we compare both execution plans.
  12. zhenyuanDeng New Member

    But how can i get the execution plans, there is no response when i call the store procedure.
    And when i write the sql in the procedure like that:
    /////////////////////
    SELECT Z.ORDER_NUM,ZI.ORDER_ITEM,Z.CUSTOM_ID,ZI.PRODUCT_ID,ZI.PRODUCT_NAME,

    ZI.LENGTH,ZI.CDGC,ZI.HEIGHT,ZI.L_HEIGHT,ZI.U_HEIGHT,ZI.CATEGORY,ZI.COLOR_ID,ZI.SGQTYS,ZI.plannum, ZI.totalwgt,
    ZI.banglong,ZI.bangzs,ZI.qgzs ,ZI.BANG_GRADE,ZI.PRI, ZI.LOCATION,Z.END_DATE, ZI.NOTESZ AS NOTES--,ZI.MJCOUNT
    INTO #Z
    FROM ORDERZ Z INNER JOIN ORD_ITEMZ ZI ON Z.ORDER_ID=ZI.ORDER_ID
    WHERE (ZI.over_work in ( 'A','B','D') ) AND ZI.PC_MODE = 'Y' and ZI.sgqtys > 0
    --AND (ZI.location = @location or @location = 'ALL')
    --AND (ZI.pri = @pri or @pri = 'ALL')
    AND Z.order_num in ( select order_num from temp_order_id where username = @gs_userid)

    ////////////////////
    Than, everything is ok. The performance is as quickly as the scripts execute in Query Analyzer.
    My question is why?
  13. zhenyuanDeng New Member

    database size is almost 1.2G
    Table orderz 2840kb
    Table ord_itemz 218,568kb
    Table temp_order_id 88kb

    The version of my SQL Server 2000 is evaluation edition

    cpu: intel x86 1.0G
    Memory: 512M
    Virtual memory: 768M
  14. Adriaan New Member

    Are the columns that you deactivated in yout WHERE statement - ZI.Location and ZO.pri - covered by an index?
  15. zhenyuanDeng New Member

    Hi, adriaan. Thanks for your reply.

    I haven't consider the index. What i want to know is why the performance executing in Query Analyzer is better than calling from the store procedure.

    The Query Analyzer can output the rowsets(9058 row), but the store procedure didn't do that, just make the cpu 100%, and the memory grow up all the time.
  16. Adriaan New Member

    In QA, did you try with the criteria for ZI.Location and ZO.pri - did it hang too?

    Also, are you comparing a production server (probably multiple CPUs) with a test server (probably single CPU)? In that case try adding this as the very last part of your query for the production server:

    OPTION (MAXDOP 1)

    This disables parallellism - there is a known bug in SQL Server where it will use parallellism for a relatively simple query, and the query hangs. Option only helps on multi-CPU servers, but there is no harm when using it on a single CPU server.
  17. Twan New Member

    The reason is that the stored proc, will use the parameter values when it is first called to determine its execution plan.

    In your case if the first time the proc is called, it is passed a valid location and pri, then it is likely to choose a different plan than if ALL are passed.

    The statement should be split into the four possible cases. location=ALL and pri=ALL, either one =ALL, and then neither=ALL

    as in

    if @location='ALL' and @pri='ALL'
    -- select statement without the two clauses in the where clause
    else if @location='ALL'
    -- select statement with only the pri clause in the where clause
    else if @pri='ALL'
    -- select statement with only the location clause in the where clause
    else
    -- select statement with both clauses in the where clause

    alternatively adding a WITH RECOMPILE to the proc creation should 'fix' the problem too, but with the overhead of compiling the proc for every single call

    Cheers
    Twan
  18. zhenyuanDeng New Member

    When i add the criteria in the store procedure, it hang, but QA not hang.
    ////
    AND (ZI.location = @location or @location = 'ALL')
    AND (ZI.pri = @pri or @pri = 'ALL')
    ////

    I know that's not a good plan. And i only want to know what happen.
    I also post the topic on msdn, but nobody reply to me.
    Puzzling.

Share This Page