SQL Server Performance

Sort operation

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by satya.sqldba, Jun 13, 2007.

  1. satya.sqldba New Member

    I have a complex query that is taking 2 and half minutes to display the results.

    When I look at the execution plan, I find that a sort operation is costing about 72% of the total cost.

    So I thought of running the query without the sort operation so that it can be sorted at the client side rather than at the server side.

    But, even after I remove the sort operation from the query, it is taking exactly the same amount of time to execute.

    Just wanted to know the reason for this.

    Thanks
    Satya
  2. condeba92 New Member

    Hi Satya!
    I'm searching about the query analizer cost behavior.
    Can you show me the code pls.
    Thank's
  3. satya.sqldba New Member

    Here is the code:




    SELECT P.BillingEntityAS BillingEntity,
    E.LegalNameAS Employer,
    WR.EmployerLocationNoAS EmployerLocationNo,
    EPL.LocationNameAS EmployerLocationName,
    P.ReceiptDateAS PaymentReceiptDate,
    PWR.InternalControlNoAS InternalControlNo,
    WR.CBANoAS CBANo,
    WR.SigIDAS SigID,
    TSS.ShowNameAS ShowName,
    ISNULL(FT.Description,' ') AS FeeType,
    WR.EstimatedGrossWageAS ICNGross,
    ISNULL(WR.AgreementRate,0)AS CBARate,
    WR.EstimatedContributionAS ICNContr,
    WR.ReportGrossWageAS ReportGrossWage,
    ISNULL(WR.ReportRate,0)AS ReportRate,
    WR.ReportContributionAS ReportContribution,
    ( WR.ReportContribution
    - WR.EstimatedContribution ) AS RptErr,
    P.BillingEntityAS BillingEntity,
    PWR.TransNoAS TransNo,
    P.ReceivedPaymentAS ReceivedPayment,
    RS.DescriptionAS ReportStatus,
    WR.ReportStatusDateAS StatusDate,
    UL.NameAS Local
    FROM dbo.tblPayment AS P
    JOINdbo.tblPaymentWorkReportAS PWRON P.TransNo = PWR.TransNo
    LEFT OUTER JOINdbo.tblWorkReportAS WRON WR.InternalControlNo = PWR.InternalControlNo
    LEFT OUTER JOIN dbo.tblEmployerPayrlLocationAS EPLON (WR.CreditedEmployerNo = EPL.EmployerNo AND WR.EmployerLocationNo = EPL.LocationNo )
    LEFT OUTER JOIN dbo.tblEmployerAS EON P.CreditedEmployerNo = E.EmployerNo
    LEFT OUTER JOIN dbo.lstFeeTypeAS FTON WR.FeeType = FT.FeeType
    LEFT OUTER JOIN dbo.tblLocalAS ULON WR.CreditedLocalNo = UL.LocalNo
    LEFT OUTER JOINdbo.lstReportStatusAS RSON WR.ReportStatus = RS.ReportStatus
    LEFT OUTER JOIN dbo.tblSignatoryAS SON WR.SigID = S.SigID
    LEFT OUTER JOIN dbo.tblSigShowAS TSSON S.ShowID = TSS.ShowID
    ORDER BY WR.EmployerLocationNo,
    P.ReceiptDate,
    WR.InternalControlNo

    In the above code:

    1. tblworkreport has about 2 million rows.
    2. tblpayment has about 1 million rows
    3. tbl paymentworkreport has about 2 million rows

    All the rest are relatively small tables

    Creating an index on those large tables isn't helpful either.

    I looked at the join conditions and tried creating indexes and still I find no improvement in the performance.

    Previously some queries with long execution time used to get resolved with indexes, but in this case, there isn't even small improvement

    Wondering if some changes in the code could boost the performance ...

    Thanks
    Satya
  4. satya.sqldba New Member

    The execution plan shows the following:

    For the sort operation (75% of the total cost):

    Estimated I/O cost:877
    Estimated CPU cost :29

    When I create non clustered index on the apt columns of tblworkreport, still it is doing an index scan on the table and not index seek.

    Thanks
    Satya
  5. satya.sqldba New Member

    I am waiting to hear some suggestions on this....

    Thanks
    Satya
  6. Adriaan New Member

    Check if you really need outer joins. For instance, if there is always a matching row in tblWorkReport then make it an inner join instead of a left join. Only keep a left join if you want the results from the 'left' table to be shown even if there is no match on the 'right' table.

    Have you noticed the number of rows being returned? If you;re not sitting at the console, those rows have to be returned over the network - which might take a little time.

    About the index scan/seek - there really is no point for SQL to do an index seek if there's no filtering, so it chooses to do an index scan.
  7. condeba92 New Member

    ADRIAAN suggest something good to do, do you had good result with this tips?.
    Tell me about your experience. If you obtain good result the problem is over.
    I think you have to consider re-write your query.
    I have some good result in this kind of problem uploading big tables to memory into temporal tables and then executing the query, but its's depends the amount of memory you have.
    Can you show me the structure of your index ? This way i'll try to boost the perfomance of it.
    Thanks
    Condeba92
  8. satya.sqldba New Member

    Adriaan and Condeba

    Thanks for the suggestions.

    As you said, when I tried to execute the same on the server rather than on a different box, it came down to 1 min 4 secs, but still it is bad execution time.

    I tried creating the following indexes:



    CREATE NONCLUSTERED INDEX [index_tblPayment] ON [dbo].[tblPayment]
    (
    [CreditedEmployerNo] ASC,
    [TransNo] ASC,
    [ReceiptDate] ASC
    )
    INCLUDE ( [ReceivedPayment],[BillingEntity])
    WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

    CREATE NONCLUSTERED INDEX [index_tblWorkReport] ON [dbo].[tblWorkReport]
    (
    [CreditedEmployerNo] ASC,
    [EmployerLocationNo] ASC,
    [SigID] ASC,
    [ReportStatus] ASC,
    [CreditedLocalNo] ASC,
    [FeeType] ASC,
    [InternalControlNo] ASC
    )
    INCLUDE ( [CBANo],
    [AgreementRate],
    [ReportRate],
    [ReportGrossWage],
    [EstimatedGrossWage],
    [EstimatedContribution],
    [ReportStatusDate]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]



    But the performance is not at all better, its the same.

    Creating an index on tblpaymentworkreport is making the performance still worse.

    That's the situation...

    Thanks
    Satya
  9. Adriaan New Member

    How many rows are returned after 1 min 4 secs? If you're talking millions, that's not really bad.

    Try doing a SELECT COUNT(*) instead of the column list - how long does that take?
  10. satya.sqldba New Member

    Adriaan

    1. It is returning around 2 million rows after 1 min execution,

    2. select count (*) returns result in 0 sec.

    Thanks
    Satya
  11. condeba92 New Member

    Satya:
    Why you use this index structure ? Can you change it ?
    Try with a index structure where field TransNo in index TBLPAYMENT and field InternalControlNo in index TBLWORKREPORT will be located in the first position of this indexes.
    Then an index in field TransNo of TBLPAYMENTWORKREPORT can help.
    Thank's
  12. satya.sqldba New Member

    Sorry for the late reply Conedeba; got busy with something else.

    Well I understand what you say, but this was an interesting question I had in my mind from the past 3 or 4 months. Does the order in which the colums are placed have an affect on performance? If so how, I wanted to find some articles related to this particular case but couldn't.

    Let me know how that works. Because I tried testing it on a simple table and studied the execution plan changing the order of columns in which indexes are created. In my testing, it showed clustered index seek in one case and 'parallelism' on the other. I had this question in mind from that time.

    Thanks
    Satya

  13. satya.sqldba New Member

    "Does the order in which the colums are placed while creating an index have an affect on performance? "

    I have been waiting for this clarification from a long time... any one?

    Thanks
    Satya
  14. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by satya.sqldba</i><br /><br />"Does the order in which the colums are placed while creating an index have an affect on performance? "<br /><br />I have been waiting for this clarification from a long time... any one?<br /><br />Thanks<br />Satya<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Hey, you should be a little bit more patient. After all, we're all doing this here in our spare time. [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />Short answer: Yes, it does!<br /><br />Little bit longer answer (still abbreviated): <br />Suppose you have a table with 10.000 rows. <br />Suppose you have an index on col1, col2, col3 (with col1 having 10 distinct values, col2 100, and col3 1000 distinct values). In such case the optimizer might come to the conclusion that such an index is not very useful and rather go for a scan. If you reorder the index to col3, col2, col1 and place the most selective column first, the optimizer might come up with the idea that such an index is appropriate for a query and uses it.<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a><br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  15. satya.sqldba New Member

    Frank<br /><br /> Thanks for the reply. That clarifies my doubt.<br /><br /> By the way, I am a very patient guy [8D] [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br /> Satya
  16. FrankKalis Moderator

    [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a><br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  17. joechang New Member

  18. condeba92 New Member

    Yes Satya, the order in which the colums are placed while creating an index have an affect on performance.
    I think you have to add or re-define your index in those tables:
    1. tblworkreport has about 2 million rows.
    2. tblpayment has about 1 million rows
    3. tblpaymentworkreport has about 2 million rows

    Maybe a set of new indexes can work well.
    Please tell if the field TransNo is a PK in tblpayment or tblPaymentWorkReportAS and field InternalControlNo is PK in tblworkreport or tblPaymentWorkReportAS.
    Thank you.
  19. condeba92 New Member

    Satya, can you tell me if your tables tblpayment and tblpaymentworkreport have PK on transno field? And, what about tblworkreport and tblpaymentworkreport table about the internalcontrolno field ?
    Thank you.
  20. condeba92 New Member

    Satya, can you show PK field on tblpayment, tblpaymentworkrepost and tblworkreport table ?
    Thank you.
  21. satya.sqldba New Member

    Yes Condeba

    TransNo is the PK on tblpayment
    InternalControlNo is the PK on tblworkreport
    On tblpayment work report PK is on both TransNo and InternalControlNo

    Thanks
    Satya



  22. condeba92 New Member

    ok, Satya.
    I think you need to see if those indexes can help you:

    create index pk_1 on tblpayment(transno)
    WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF)
    ON [PRIMARY]

    create index pk_2 on tblpaymentworkreport(transno,internalcontrolno)
    WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF)
    ON [PRIMARY]

    create index pk_3 on tblworkreport(internalcontrolno)
    WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF)
    ON [PRIMARY]

    Test this indexes with your existent indexes and let your query execution plan to decide.
    Maybe your big problem is in this code:
    ...
    FROM
    dbo.tblPayment AS P JOIN dbo.tblPaymentWorkReportAS PWR
    ON P.TransNo = PWR.TransNo
    LEFT OUTER JOINdbo.tblWorkReport AS WR
    ON WR.InternalControlNo = PWR.InternalControlNo
    ...
    Thank you.



  23. condeba92 New Member

    Error: Forgets pk_1 and pk_3 indexes and keep pk_2 index.
    Thank you.

Share This Page