Post reply

statement runs very slow what have I done wrong or what could I change to make it better

  • February 4, 2009 at 5:25 am

    #937839

    Christopher Stobbs (2/4/2009)

    HI Grant,

    How do I check this: "You still need to look at I/O, CPU, execution time, wait states... " for the different operations in the query plan?

    Basically turn on STATISTICS IO & STATISTICS TIME. You can do this through a TSQL statement prior to running the query or by setting them through the GUI in Management Studio.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • February 4, 2009 at 7:34 am

    #937962

    STATISTICS IO FROM MY PRODUCTION SERVER

    Table 'Worktable'. Scan count 5523, logical reads 32808, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'PS_BU_ITEMS_INV'. Scan count 3546, logical reads 12853, physical reads 267, read-ahead reads 132, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'PS_SF_PRDNID_HEADR'. Scan count 1, logical reads 505, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'PS_IN_DEMAND'. Scan count 290, logical reads 115262, physical reads 820, read-ahead reads 76786, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'PS_PL_ITEM_ATTRIB'. Scan count 1, logical reads 538, physical reads 1, read-ahead reads 534, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'PS_INV_ITEMS'. Scan count 1, logical reads 502, physical reads 0, read-ahead reads 480, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'PS_MASTER_ITEM_TBL'. Scan count 1, logical reads 618, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'PS_LOAD_INV'. Scan count 5, logical reads 190, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'PS_HM_SHIP_TP_DEFN'. Scan count 5, logical reads 10, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  • February 4, 2009 at 7:37 am

    #937963

    STATISTICS TIME

    SQL Server parse and compile time:

    CPU time = 3119 ms, elapsed time = 3119 ms.

    (1841 row(s) affected)

    SQL Server Execution Times:

    CPU time = 1250 ms, elapsed time = 20539 ms.

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 1 ms.

  • February 4, 2009 at 8:04 am

    #937985

    Table definition Added

  • February 4, 2009 at 9:19 am

    #938087

    twdavis (2/4/2009)

    Table definition Added

    Where?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 4, 2009 at 9:45 am

    #938124

    Again, this is PeopleSoft. What version of the software and peopletools are you using? Is this query maintained by the application or is it a custom query?

  • February 4, 2009 at 10:53 am

    #938191

    PeopleSoft 9.0 peopletools 8.48 query was created throught the PIA

  • February 4, 2009 at 11:01 am

    #938197

    Not the PeopleSoft Admin here, so forgive me, but what app, Finance or HR?

  • February 4, 2009 at 12:11 pm

    #938284

    Peoplesoft Enterprise

    Finance

  • February 4, 2009 at 12:22 pm

    #938301

    Our Finance system is at 8.9 but we are using SQL Server 2005 (HR is at 9.0 and required moving to SQL Server 2005).

    We had a performance issue with our upgrade to 2005 on Finance. Our PeopleSoft admin figured out how to run a trace on the PeopleSoft side and we found a table with 200,000 or so records that had no index. Why this didn't cause an issue with SQL Server 2000, no idea. Once we indexed that table our performance issue went away. Before that I had already added 17 separate indexes trying to resolve the issue. We have removed those additional indexes.

    Is this query maintained through your PeopleSoft tools or outside of the application?

  • February 4, 2009 at 12:59 pm

    #938332

    The query was created and is maintained in peoplesoft query manager.

  • February 4, 2009 at 1:14 pm

    #938348

    Based on that, I don't think there is much you can do to rewrite the query, the query tool would just undo it if I remember correcty (our PeopleSoft admin is in a meeting).

    I'd start checking the indexing on the tables.

  • February 4, 2009 at 1:27 pm

    #938361

    [font="Verdana"]Wow! I have to say reading through the responses and the amount of time people are taking to look at this, I am seriously impressed! Some good, thoughtful stuff here on how to tune SQL.

    Nice.[/font]

  • February 4, 2009 at 1:49 pm

    #938399

    I've been trying to get our PeopleSoft Admin to take a look and offer some assistance too, but she's been a bit busy.

  • February 4, 2009 at 6:04 pm

    #938546

    And I've been staying out of the way since this was identified as PeopleSoft. Same approach I take at work.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 15 posts - 16 through 30 (of 37 total)

You must be logged in to reply to this topic. Login to reply