October 9, 2014 at 8:29 am
Hello,
Below is the query:
UPDATE [RFRL_CGN_CFQ].[CashForQuotes].CFQ_Referrals
SET [RFRL_CGN_CFQ].[CashForQuotes].CFQ_Referrals.RecKey = dbo.BuildRecordKey(A.ReferralCode, A.[RetentionID],
A.[ClientFName],A.[ClientLName],
IIf(A.Qt_Grp='MOAT',A.[QuotePolicyType],A.[ClientZip]),
A.[QuoteType])
FROM
(
select CFRQ.RecChangeID, CFRQ.RetentionID, CFRQ.RecKey, CFRQ.ReferralCode, CFRQ.ClientFName, CFRQ.ClientLName, QTtypes.Qt_Grp, CFRQ.QuotePolicyType, CFRQ.ClientZip, CFRQ.QuoteType
from [RFRL_CGN_CFQ].[CashForQuotes].CFQ_Referrals as CFRQ
LEFT JOIN [RFRL_CGN_CFQ].[CashForQuotes].t_Chg_Rsns as CHGRSNS
ON CFRQ.RecChangeID = CHGRSNS.Chg_ReasID
INNER JOIN [RFRL_CGN_CFQ].[CashForQuotes].t_Qt_Typs QTtypes
ON CFRQ.QuoteType = QTtypes.Qt_Typ
WHERE (((CFRQ.RecProcessed)=0)
AND ((CFRQ.RecChange)=1)
AND ((CHGRSNS.ImpactsRecKey)=1))
OR (((CFRQ.RecKey) Is Null)) OR (((CFRQ.RecKey)=''))) A
WHERE [RFRL_CGN_CFQ].[CashForQuotes].CFQ_Referrals.RecChangeID = A.RecChangeID
However, when I run the below statement, it returns values in less than a second.
select dbo.BuildRecordKey ('93572','220116K2831808','PHILIP', 'CONGDON', IIf('AUTO'='MOAT','A','78132'),'aSNAP');
How to decrease the execution time of this query? I am using this query in one of my SSIS packages.
Any help is highly appreciated....
October 9, 2014 at 8:36 am
Table definitions, index definitions and execution plan please.
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
October 9, 2014 at 8:50 am
Here is this with some applied formatting so we can read it. You can maintain formatting on this site by using the IFCode shortcuts on the left side when posting.
UPDATE [RFRL_CGN_CFQ].[CashForQuotes].CFQ_Referrals
SET [RFRL_CGN_CFQ].[CashForQuotes].CFQ_Referrals.RecKey = dbo.BuildRecordKey(A.ReferralCode, A.[RetentionID], A.[ClientFName], A.[ClientLName], IIf(A.Qt_Grp = 'MOAT', A.[QuotePolicyType], A.[ClientZip]), A.[QuoteType])
FROM (
SELECT CFRQ.RecChangeID
,CFRQ.RetentionID
,CFRQ.RecKey
,CFRQ.ReferralCode
,CFRQ.ClientFName
,CFRQ.ClientLName
,QTtypes.Qt_Grp
,CFRQ.QuotePolicyType
,CFRQ.ClientZip
,CFRQ.QuoteType
FROM [RFRL_CGN_CFQ].[CashForQuotes].CFQ_Referrals AS CFRQ
LEFT JOIN [RFRL_CGN_CFQ].[CashForQuotes].t_Chg_Rsns AS CHGRSNS ON CFRQ.RecChangeID = CHGRSNS.Chg_ReasID
INNER JOIN [RFRL_CGN_CFQ].[CashForQuotes].t_Qt_Typs QTtypes ON CFRQ.QuoteType = QTtypes.Qt_Typ
WHERE (
((CFRQ.RecProcessed) = 0)
AND ((CFRQ.RecChange) = 1)
AND ((CHGRSNS.ImpactsRecKey) = 1)
)
OR (((CFRQ.RecKey) IS NULL))
OR (((CFRQ.RecKey) = ''))
) A
WHERE [RFRL_CGN_CFQ].[CashForQuotes].CFQ_Referrals.RecChangeID = A.RecChangeID
I suspect the performance issue is based on volume. Your query that runs "fast" is because you are executing your scalar one time. In your bigger update statement you are running that same scalar function for ever row in the FROM. Any chance you can get rid of the scalar function for something that will perform better? Also, you shouldn't be using a subquery as the basis of your data.
It seems you could drastically simplify this query to something like this.
UPDATE CFRQ
SET RecKey = dbo.BuildRecordKey(A.ReferralCode, A.[RetentionID], A.[ClientFName], A.[ClientLName], IIf(A.Qt_Grp = 'MOAT', A.[QuotePolicyType], A.[ClientZip]), A.[QuoteType])
FROM [RFRL_CGN_CFQ].[CashForQuotes].CFQ_Referrals AS CFRQ
LEFT JOIN [RFRL_CGN_CFQ].[CashForQuotes].t_Chg_Rsns AS CHGRSNS ON CFRQ.RecChangeID = CHGRSNS.Chg_ReasID
INNER JOIN [RFRL_CGN_CFQ].[CashForQuotes].t_Qt_Typs QTtypes ON CFRQ.QuoteType = QTtypes.Qt_Typ
WHERE
(
CFRQ.RecProcessed = 0
AND CFRQ.RecChange = 1
AND CHGRSNS.ImpactsRecKey) = 1
)
OR CFRQ.RecKey IS NULL
OR CFRQ.RecKey = ''
That still will have the performance issue of the scalar function but it is a lot easier to decipher.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 10, 2014 at 3:07 am
It can make wonders in performance to change the Scalar function into Inline table-valued function (if the function is simple enough that is).
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply