January 11, 2015 at 3:34 am
Hi,
I am struggling a bit to reduce the execution time for the below query. kindly help me to tune this and to get a optimized performance.
as of now the query is taking 19 seconds to bring the result set with pagination for 300454 records.
Query:
select
PONCT.TransactionreferenceNumber as TicketNumber,
S.SubsidiaryName as Subsidiary,
PO.SequenceNumber as BatchNumber,
cast(NCT.[Amount] as decimal(18,2)) as Amount,
CU.CurrencyShortCode as Currency,
isnull(PONCT.RetryAttemptNo,0) as [NumberOfRetries],
NCT.PaymentStatus,
count(PONCT.TransactionreferenceNumber) over(order by (select '1')) as TicketNumberCount
/*older tuned from clause*/
--from [TFSPPTESTDB].[TFS].[PaymentOrder] PO
--inner join [TFSPPTESTDB].[TFS].[PaymentOrderNonCashTransactions] PONCT
-- On PO.PaymentOrderGUID=PONCT.PaymentOrderGUID
--inner join [TFSPPTESTDB].[TFS].[NonCashTransactions] NCT
-- On PONCT.TransactionGUID=NCT.TransactionGUID
-- and PONCT.TransactionTypeCode=NCT.TransactionTypeCode
--inner join [TFSPPTESTDB].[TFS].[TaxFreeFormDetails] TFFD
-- on TFFD.TaxFreeFormGUID=NCT.TaxFreeFormGUID
--inner join [TFSPPTESTDB].[TFS].[PaymentChannel] PC --this can be excluded if business logic cannot be affected
-- on PC.PaymentChannelCode=NCT.PaymentChannelCode
--inner join [TFSPPTESTDB].[TFS].[Subsidiary] S
-- on S.SubsidiaryCode=TFFD.SubsidiaryCode
--inner join [TFSPPTESTDB].[TFS].[Currency] CU
-- On NCT.CurrencyISOCode=CU.CurrencyISOCode
/*version1 tuned from clause*/
from [TFS].Subsidiary s
inner join [TFS].TaxFreeFormDetails tffd
on s.SubsidiaryCode=tffd.SubsidiaryCode
inner join [TFS].NonCashTransactions nct
on tffd.TaxFreeFormGUID=nct.TaxFreeFormGUID
inner join [TFS].Currency cu
on nct.CurrencyISOCode=cu.CurrencyISOCode
inner join [TFS].PaymentOrderNonCashTransactions ponct
on nct.TransactionGUID=ponct.TransactionGUID
inner join [TFS].PaymentOrder po
on ponct.PaymentOrderGUID=po.PaymentOrderGUID
inner join [TFS].PaymentChannel pc
on po.PaymentChannelCode=pc.PaymentChannelCode
where ((@PaymentChannelName = '') or @PaymentChannelCode=PC.PaymentChannelCode)
and ((@PaymentStatus = '') or @PaymentStatus = NCT.PaymentStatus)
and ((@BatchNumber Is Null) Or @BatchNumber=PO.SequenceNumber)
and ((@TicketNumber Is Null) Or @TicketNumber=PONCT.TransactionreferenceNumber)
and iif((@StartDate = '1900-01-01' or @EndDate = '1900-01-01'), '1900-01-01',PO.CalendarDate)
between iif((@StartDate = '1900-01-01' or @EndDate = '1900-01-01'),'1900-01-01',@StartDate) and iif((@StartDate = '1900-01-01' or @EndDate = '1900-01-01'),'1900-01-01',@EndDate)
order by PONCT.TransactionreferenceNumber
offset @IndexNo rows
fetch next @RecordNo rows only
please find the execution plan with the attachment.
January 11, 2015 at 3:37 am
Friends,
this is bit urgent and I would feel very greatful if I get any immediate replies.
thanks in advance.
Prabhu.st
January 11, 2015 at 5:21 am
prabhu.st (1/11/2015)
Hi,I am struggling a bit to reduce the execution time for the below query. kindly help me to tune this and to get a optimized performance.
as of now the query is taking 19 seconds to bring the result set with pagination for 300454 records.
Query:
select
PONCT.TransactionreferenceNumber as TicketNumber,
S.SubsidiaryName as Subsidiary,
PO.SequenceNumber as BatchNumber,
cast(NCT.[Amount] as decimal(18,2)) as Amount,
CU.CurrencyShortCode as Currency,
isnull(PONCT.RetryAttemptNo,0) as [NumberOfRetries],
NCT.PaymentStatus,
count(PONCT.TransactionreferenceNumber) over(order by (select '1')) as TicketNumberCount
/*older tuned from clause*/
--from [TFSPPTESTDB].[TFS].[PaymentOrder] PO
--inner join [TFSPPTESTDB].[TFS].[PaymentOrderNonCashTransactions] PONCT
-- On PO.PaymentOrderGUID=PONCT.PaymentOrderGUID
--inner join [TFSPPTESTDB].[TFS].[NonCashTransactions] NCT
-- On PONCT.TransactionGUID=NCT.TransactionGUID
-- and PONCT.TransactionTypeCode=NCT.TransactionTypeCode
--inner join [TFSPPTESTDB].[TFS].[TaxFreeFormDetails] TFFD
-- on TFFD.TaxFreeFormGUID=NCT.TaxFreeFormGUID
--inner join [TFSPPTESTDB].[TFS].[PaymentChannel] PC --this can be excluded if business logic cannot be affected
-- on PC.PaymentChannelCode=NCT.PaymentChannelCode
--inner join [TFSPPTESTDB].[TFS].[Subsidiary] S
-- on S.SubsidiaryCode=TFFD.SubsidiaryCode
--inner join [TFSPPTESTDB].[TFS].[Currency] CU
-- On NCT.CurrencyISOCode=CU.CurrencyISOCode
/*version1 tuned from clause*/
from [TFS].Subsidiary s
inner join [TFS].TaxFreeFormDetails tffd
on s.SubsidiaryCode=tffd.SubsidiaryCode
inner join [TFS].NonCashTransactions nct
on tffd.TaxFreeFormGUID=nct.TaxFreeFormGUID
inner join [TFS].Currency cu
on nct.CurrencyISOCode=cu.CurrencyISOCode
inner join [TFS].PaymentOrderNonCashTransactions ponct
on nct.TransactionGUID=ponct.TransactionGUID
inner join [TFS].PaymentOrder po
on ponct.PaymentOrderGUID=po.PaymentOrderGUID
inner join [TFS].PaymentChannel pc
on po.PaymentChannelCode=pc.PaymentChannelCode
where ((@PaymentChannelName = '') or @PaymentChannelCode=PC.PaymentChannelCode)
and ((@PaymentStatus = '') or @PaymentStatus = NCT.PaymentStatus)
and ((@BatchNumber Is Null) Or @BatchNumber=PO.SequenceNumber)
and ((@TicketNumber Is Null) Or @TicketNumber=PONCT.TransactionreferenceNumber)
and iif((@StartDate = '1900-01-01' or @EndDate = '1900-01-01'), '1900-01-01',PO.CalendarDate)
between iif((@StartDate = '1900-01-01' or @EndDate = '1900-01-01'),'1900-01-01',@StartDate) and iif((@StartDate = '1900-01-01' or @EndDate = '1900-01-01'),'1900-01-01',@EndDate)
order by PONCT.TransactionreferenceNumber
offset @IndexNo rows
fetch next @RecordNo rows only
please find the execution plan with the attachment.
Quick thought, not a single non-clustered index used in the plan, you should look into the missing indexes.
Secondly consider separating the source query and the pagination, i.e. insert the results into a (temp) table and page it from there as almost all the effort is repeated for every page.
The third obvious thing is the [TFS].[PaymentOrderNonCashTransactions], add a POC (Partition Order Cover) index for the TicketNumberCount and the ORDER BY clause, this section has probably 90-95% of the cost in addition to spill the sort into tempdb.
😎
January 11, 2015 at 5:44 am
Hooovv !! its a good tips boss, I would have a look at it and let you know shortly.. thanks a lot
January 11, 2015 at 6:15 am
Functions on columns like this are going to lead to scans on your indexes
and iif((@StartDate = '1900-01-01' or @EndDate = '1900-01-01'), '1900-01-01',PO.CalendarDate)
between iif((@StartDate = '1900-01-01' or @EndDate = '1900-01-01'),'1900-01-01',@StartDate) and iif((@StartDate = '1900-01-01' or @EndDate = '1900-01-01'),'1900-01-01',@EndDate)
You're going to need to change the logic in some fashion so that the you're not doing that. You can see the scan all the way at the top right of the execution plan.
Also, you've got estimed number of rows at 1 and actual at 300,000 throughout most of this query. That sure suggests that your statistics are off. That also explains why it's trying to do Nested Loops joins across 300k rows. Your scan is suggesting it's only going to return 1 row when it's returning 154. That one row estimate is the beginning of the problem from what I can see. Update your statistics, possibly using a full scan.
"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
January 11, 2015 at 8:36 am
prabhu.st (1/11/2015)
as of now the query is taking 19 seconds to bring the result set with pagination for 300454 records.
Are you saying that (and it looks like it from the two transaction blocks furthest to the right in the execution plan) there are 300454 rows that meet your criteria and you're allowing people to page through those with what looks like 10 rows at a time? If so, I believe I'd get some clarification on that requirement because that seems a whole lot crazy.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 11, 2015 at 9:04 am
Jeff Moden (1/11/2015)
prabhu.st (1/11/2015)
as of now the query is taking 19 seconds to bring the result set with pagination for 300454 records.Are you saying that (and it looks like it from the two transaction blocks furthest to the right in the execution plan) there are 300454 rows that meet your criteria and you're allowing people to page through those with what looks like 10 rows at a time? If so, I believe I'd get some clarification on that requirement because that seems a whole lot crazy.
Further on this, the pagination is only going to kick in after these three right-most operators and the sort, all the work repeated for each page, hence the suggestion of inserting the results unpaged into a intermediate/temp table and page it from there, probably the quickest gain as it's about 95% of the work.
😎
January 11, 2015 at 9:52 am
This is the dreaded IS NULL OR scenario (with paging thrown in for good measure). Best solution BY FAR is dynamic SQL. It could also possibly benefit from just getting keys for fetched records first into a temp object and then going back for all columns needed. I have done this for probably a dozen different scenarios for numerous clients over the years and usually pick up 3-5 ORDERS OF MAGNITUTE performance improvement!!!
See Gail Shaw's catch-all query post too: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
You can prove that my notion will work by hard-coding a query with actual values. For those parameters where variable is NULL you can completely remove that filter. Now, if you are truly hitting a large percentage of rows with your filter and you cannot use index seeks to get to your specific range of rows early in the plan you have no choice but bigger hardware. That does assume that you HAVE appropriate indexing in place already.
BTW, if this is so important for you that you need to make a follow-up post 3 MINUTES after your initial post (and in the very early morning hours) then it MUST be important enough to hire a consultant to give you an hour or day to get this done right!! 😎 You could go back and forth for days on a (supported-by-volunteers) forum and still not have a workable solution.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 11, 2015 at 6:59 pm
Eirikur Eiriksson (1/11/2015)
Jeff Moden (1/11/2015)
prabhu.st (1/11/2015)
as of now the query is taking 19 seconds to bring the result set with pagination for 300454 records.Are you saying that (and it looks like it from the two transaction blocks furthest to the right in the execution plan) there are 300454 rows that meet your criteria and you're allowing people to page through those with what looks like 10 rows at a time? If so, I believe I'd get some clarification on that requirement because that seems a whole lot crazy.
Further on this, the pagination is only going to kick in after these three right-most operators and the sort, all the work repeated for each page, hence the suggestion of inserting the results unpaged into a intermediate/temp table and page it from there, probably the quickest gain as it's about 95% of the work.
😎
You still have the problem with the original requirements. First, it's a bit insane to materialize 300K rows per session just so someone can page through it. Second, it's insane that anyone would think that someone should even be provided with the tools to page through more than 300K rows. From the provided execution plan, it looks like the pagination is based on 10 rows at a time. That would be 30K pages to scroll through. Even at 50 rows per page, it would still be 6,000 pages to page through. That's just nuts. :hehe:
--Jeff Moden
Change is inevitable... Change for the better is not.
January 12, 2015 at 2:45 am
@Grant,
yeah, I am also in your side, the business logic is to exclude that filter condition in start and end dates or any one has not provided. so I have re-write that particular condition as like below.
and ((PO.CalendarDate between @StartDate and @EndDate) or (@StartDate is null or @EndDate is null))
Thanks,
Prabhu
January 12, 2015 at 3:33 am
@Erikur,
Further on this, the pagination is only going to kick in after these three right-most operators and the sort, all the work repeated for each page, hence the suggestion of inserting the results unpaged into a intermediate/temp table and page it from there, probably the quickest gain as it's about 95% of the work.
i have tried that version as well but it is consuming more time than the existing query. just working on updating the statisticts as Jeff told here and parallelly trying with Dynamic Query approach as per the GAIL's post suggest in this post.
for the search of "No values" to any parameters (i.e, simply the SELECT without WHERE clause) total rows are 300454 and per page they are planning to display 10 (might be expected to increase upto 100)
I think its worth to update the statistics as the estimated number of rows = 1 where it is actually 300354.
Thank you guys,
Jeff, Grant, Erikur, SQLGuru
-Prabhu
January 12, 2015 at 7:53 am
In that case, I wouldn't allow the "no values to any parameters" scenario.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 12, 2015 at 8:03 pm
if you have many data in each table, insert real table into temp table before and join each table in temp table
January 22, 2015 at 4:15 am
Freinds,
Finally the confusion has got endup and based on the advice from you guys (bit from each of you) have achieved the query to run faster and completed in 2 seconds (without any index implementation)
what i did is:
1)the query used to take count and to fetch the rows are the same, so populated that into a temp table with identity column.
2)taken the max of the identity from @@identity instead of executing the query for one more time
3)paging was implemented on the temp table with offset and fetch next row option in 2012
4)excluded the XML parsing (as input parameter) and substituted with individual parameters.
thus executing in 2 seconds.
thanks guys.
Prabhu.st
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply