May 18, 2015 at 8:41 am
Hi All,
My query when run I get a merge join with SORT. I want to avoid this sorting which is expensive. How do I tune It
select @QTY_cost = (select round(SUM(QTY),0)QTY
from(
SELECT TXN.Rubel_ID,
TXN.RUBEL_TRANSACTION_ID,
txn.TRANSACTION_CODE,
txn.SEC_TYPE_CODE1,
txn.SEC_TYPE_CODE2,
txn.SECURITY_ID1 securityid,
txn.SECURITY_ID2 securityid2,
txn.Fn_bacashcalculation(TXN.Transaction_Code, TXN.SEC_TYPE_CODE1, TXN.Sec_Type_Code2, TXN.TRADE_AMOUNT, 'Y') AS QTY
FROM TXN.TRANSACTION_DETAILS TXN with (nolock)
WHERE txn.RUBEL_ID = @Rubel_ID
AND txn.SECURITY_ID1 = @Security_ID
and NOT (TXN.TRANSACTION_CODE IN ('dp','wd') and txn.SECURITY_ID1 = txn.SECURITY_ID2)
and TXN.TRADE_DATE <= @Report_dt
and not (TXN.TRANSACTION_CODE IN ('sa','pa') and ISNULL(SETTLE_DATE,TRADE_DATE) > @Report_dt)
AND TXN.Transaction_Code not in ( 'in','dv')
and txn.END_DT is null
UNION ALL
SELECT txn.Rubel_ID,
txn.RUBEL_TRANSACTION_ID,
txn.TRANSACTION_CODE,
txn.SEC_TYPE_CODE1,
txn.SEC_TYPE_CODE2,
txn.security_id2 securityid,
txn.SECURITY_ID1 securityid2 ,
txn.Fn_bacashcalculation(TXN.TRANSACTION_CODE, TXN.SEC_TYPE_CODE1, TXN.SEC_TYPE_CODE2, TXN.TRADE_AMOUNT, 'N') AS QTY
FROM TXN.TRANSACTION_DETAILS TXN with (nolock)
WHERE txn.RUBEL_ID = @Rubel_ID
AND txn.SECURITY_ID2 = @Security_ID
and NOT (TXN.TRANSACTION_CODE IN ('dp','wd') and txn.SECURITY_ID1 = txn.SECURITY_ID2)
and TRADE_DATE <= @Report_dt
and not (TXN.TRANSACTION_CODE IN ('sa','pa') and ISNULL(SETTLE_DATE,TRADE_DATE)> @Report_dt)
and txn.END_DT is null
)X )
"More Green More Oxygen !! Plant a tree today"
May 18, 2015 at 8:43 am
Table definitions, index definitions and execution plan please.
Why do you think the sort is a problem?
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
May 18, 2015 at 8:46 am
And here it is again for those who would prefer to see the query formatted:
select @QTY_cost = (select round(sum(QTY), 0) QTY
from (select TXN.Rubel_ID
,TXN.RUBEL_TRANSACTION_ID
,TXN.TRANSACTION_CODE
,TXN.SEC_TYPE_CODE1
,TXN.SEC_TYPE_CODE2
,TXN.SECURITY_ID1 securityid
,TXN.SECURITY_ID2 securityid2
,TXN.Fn_bacashcalculation(TXN.Transaction_Code, TXN.SEC_TYPE_CODE1, TXN.Sec_Type_Code2, TXN.TRADE_AMOUNT, 'Y') as QTY
from TXN.TRANSACTION_DETAILS TXN with (nolock)
where TXN.RUBEL_ID = @Rubel_ID
and TXN.SECURITY_ID1 = @Security_ID
and not (TXN.TRANSACTION_CODE in ('dp', 'wd')
and TXN.SECURITY_ID1 = TXN.SECURITY_ID2
)
and TXN.TRADE_DATE <= @Report_dt
and not (TXN.TRANSACTION_CODE in ('sa', 'pa')
and isnull(SETTLE_DATE, TRADE_DATE) > @Report_dt
)
and TXN.Transaction_Code not in ('in', 'dv')
and TXN.END_DT is null
union all
select TXN.Rubel_ID
,TXN.RUBEL_TRANSACTION_ID
,TXN.TRANSACTION_CODE
,TXN.SEC_TYPE_CODE1
,TXN.SEC_TYPE_CODE2
,TXN.security_id2 securityid
,TXN.SECURITY_ID1 securityid2
,TXN.Fn_bacashcalculation(TXN.TRANSACTION_CODE, TXN.SEC_TYPE_CODE1, TXN.SEC_TYPE_CODE2, TXN.TRADE_AMOUNT, 'N') as QTY
from TXN.TRANSACTION_DETAILS TXN with (nolock)
where TXN.RUBEL_ID = @Rubel_ID
and TXN.SECURITY_ID2 = @Security_ID
and not (TXN.TRANSACTION_CODE in ('dp', 'wd')
and TXN.SECURITY_ID1 = TXN.SECURITY_ID2
)
and TRADE_DATE <= @Report_dt
and not (TXN.TRANSACTION_CODE in ('sa', 'pa')
and isnull(SETTLE_DATE, TRADE_DATE) > @Report_dt
)
and TXN.END_DT is null
) X
);
I suggest you get ready to defend your use of (NOLOCK) 😉
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 18, 2015 at 8:58 am
Quick review reveals Scalar UDFs, NOT INs and Functions Around Columns in WHERE clause. 2 of those 3 are BAD BAD BAD things todo in a query and the other (NOT IN) can really cause issues with effective optimization. With all that lord only knows what else is suboptimal on the server/database/schema/application.
And I agree, what about the SORT makes it so bad you want to get rid of it?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 18, 2015 at 9:01 am
TheSQLGuru (5/18/2015)
And I agree, what about the SORT makes it so bad you want to get rid of it?
Personally I can't even see where a merge join would be coming from, since both branches of the UNION have a single table in them. Unless it's actually a view.
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
May 18, 2015 at 10:14 am
GilaMonster (5/18/2015)
TheSQLGuru (5/18/2015)
And I agree, what about the SORT makes it so bad you want to get rid of it?Personally I can't even see where a merge join would be coming from, since both branches of the UNION have a single table in them. Unless it's actually a view.
Could be view, or maybe inside the scalar udf?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 18, 2015 at 10:36 am
And then of course, there is the NOLOCK hint with a query that appears to be working with financial data.
May 18, 2015 at 10:48 am
This might at least help a bit, but it won't address the main problems of the query (user defined scalar function and function on columns in the where clause or others that we can't see).
select @QTY_cost = (select round(sum(TXN.Fn_bacashcalculation(TXN.Transaction_Code,
TXN.SEC_TYPE_CODE1,
TXN.Sec_Type_Code2,
TXN.TRADE_AMOUNT,
'Y') +
TXN.Fn_bacashcalculation(TXN.TRANSACTION_CODE,
TXN.SEC_TYPE_CODE1,
TXN.SEC_TYPE_CODE2,
TXN.TRADE_AMOUNT,
'N')), 0) as QTY
from TXN.TRANSACTION_DETAILS TXN with (nolock) --Why NOLOCK? Are you fine with inconsistent results?
where TXN.RUBEL_ID = @Rubel_ID
and TXN.SECURITY_ID2 = @Security_ID
and not (TXN.TRANSACTION_CODE in ('dp', 'wd')
and TXN.SECURITY_ID1 = TXN.SECURITY_ID2
)
and TRADE_DATE <= @Report_dt
and not (TXN.TRANSACTION_CODE in ('sa', 'pa')
and isnull(SETTLE_DATE, TRADE_DATE) > @Report_dt
)
and TXN.END_DT is null
);
June 23, 2015 at 9:41 pm
Thanks for replying back now, I am going to optimize the code as suggested and update the group.
"More Green More Oxygen !! Plant a tree today"
June 23, 2015 at 9:53 pm
Minaz Amin (6/23/2015)
Thanks for replying back now, I am going to optimize the code as suggested and update the group.
Do you realize that you still have performance issues that are not going away with the posted solutions?
June 24, 2015 at 2:44 am
TheSQLGuru (5/18/2015)
GilaMonster (5/18/2015)
TheSQLGuru (5/18/2015)
And I agree, what about the SORT makes it so bad you want to get rid of it?Personally I can't even see where a merge join would be coming from, since both branches of the UNION have a single table in them. Unless it's actually a view.
Could be view, or maybe inside the scalar udf?
Won't be inside the UDF, since the exec plan will just show a Compute Scalar for that. View would be my guess.
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
June 24, 2015 at 4:38 am
I will test on DEV first and follow up with all the env.
"More Green More Oxygen !! Plant a tree today"
June 24, 2015 at 6:04 am
Minaz Amin (6/24/2015)
I will test on DEV first and follow up with all the env.
Can you post the Actual Execution Plan as a .sqlplan attachment? This would help folks evaluate tuning opportunities.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply