Sort operation

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

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

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

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

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

Thanks
Satya

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.

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

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

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?

Adriaan

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

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

Thanks
Satya

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

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

"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

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='' />]<br /><br /> Satya

see my post,
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=19595

without the execution plan etc, everything is a guessing game
and there are many possibilities to guess at

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.

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.

Satya, can you show PK field on tblpayment, tblpaymentworkrepost and tblworkreport table ?
Thank you.

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

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.

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