How to improve performance of this update query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

How to improve performance of this update query

Author

Topic

SQL_SSIS_Dev
Starting Member

10 Posts

Posted - 2014-10-09 : 10:31:15

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....

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-10 : 09:04:15

what about the subquery? Run that on its own and observer the performance. Good? Bad? Are the columns in the JOIN and WHERE predicates indexed? also, fwiw, use a SQL formatter! Here's what poorsql.com does with your query (with a few adjustments):


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

easier to read, don't you think?

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2014-10-24 : 14:52:47

Here are a couple things you can do:

1.) Your where clause is a problem because you have OR with different columns involved in the expressions. The db engine can't optimize index utilization with this. I would advocate splitting this into multiple statements to handle the different conditions and ensuring you have proper indexing in place to support these filters.

Condition 1:


WHERE CFRQ.RecProcessed = 0
AND CFRQ.RecChange = 1
AND CHGRSNS.ImpactsRecKey = 1

Condition 2:


WHERE CFRQ.RecKey IS NULL OR CFRQ.RecKey = ''

To see the difference, look at the execution plan for the following statments:


-- I know this isn't the complete criteria, but want to show you the effect on the exection plan
SELECT COUNT(*)
FROM [RFRL_CGN_CFQ].[CashForQuotes].CFQ_Referrals AS CFRQ WITH (NOLOCK)
WHERE CFRQ.RecKey IS NULL
OR CFRQ.RecKey = ''
OR (
	CFRQ.RecProcessed = 0
AND CFRQ.RecChange = 1
	);

SELECT COUNT(*)
FROM [RFRL_CGN_CFQ].[CashForQuotes].CFQ_Referrals AS CFRQ WITH (NOLOCK)
WHERE CFRQ.RecProcessed = 0
AND CFRQ.RecChange = 1;

SELECT COUNT(*)
FROM [RFRL_CGN_CFQ].[CashForQuotes].CFQ_Referrals AS CFRQ WITH (NOLOCK)
WHERE CFRQ.RecKey IS NULL

SELECT COUNT(*)
FROM [RFRL_CGN_CFQ].[CashForQuotes].CFQ_Referrals AS CFRQ WITH (NOLOCK)
WHERE CFRQ.RecKey = ''

SELECT COUNT(*)
FROM [RFRL_CGN_CFQ].[CashForQuotes].CFQ_Referrals AS CFRQ WITH (NOLOCK)
WHERE CFRQ.RecKey = '' OR CFRQ.RecKey IS NULL

2.) Regarding condition 2, while it is a small optimization, ask yourself if you really expect either NULL or '' in the data set. Many times I have seen a similar condition, the application can reliably expect one or the other and not both. If you can eliminate one condition, it saves the engine some work. If you employ a technique such as COALESCE(CFRQ.RecKey,'') = '', it will be non-SARGable and force a scan.

3.) Check for proper indexing. You want your index to start with filter criteria. In this case, you would want one on CFQ_Referrals (RecProcessed,RecChange) and one on CFQ_Referrals (RecKey). You would probably want to INCLUDE at the minimum (RecChangeID,QuoteType) in both as they are used in the join criteria. If you still want it to be faster, you can include all the columns in your output list that you use in the SET section, but keep in mind there is a storage cost as well as a write cost to that. Also, you would want an index on t_Chg_Rsns (Chg_ReasID,ImpactsRecKey) [maybe reverse those, test both] and one on t_Qt_Typs (Qt_Typ) INCLUDE(Qt_Grp). Note that if you already have a CLUSTERED index on any of these, the leaf level automatically includes all columns in the table, so you don't need another NONCLUSTERED index with the include columns.

4.) If you are indexed properly, double-check all columns involved in join criteria are the same datatypes on both side of each expression. An implicit datatype conversion will force the terms to be non-SARGable and require a scan.

Clean-up / best practices:

5.) Don't put parentheses around things that don't actually need them. It just makes it harder to read, which will increase maintenance costs.

In the worst case example:


WHERE (
			((CFRQ.RecProcessed) = 0)
			AND ((CFRQ.RecChange) = 1)
			AND ((CHGRSNS.ImpactsRecKey) = 1)
			)
		OR (((CFRQ.RecKey) IS NULL))
		OR (((CFRQ.RecKey) = ''))

should be:


WHERE (
	CFRQ.RecProcessed = 0
	AND CFRQ.RecChange = 1
	AND CHGRSNS.ImpactsRecKey = 1
      )
      OR CFRQ.RecKey IS NULL
      OR CFRQ.RecKey = ''

This brings up another point that goes with #1: if you need to use parentheses, reevaluate why. Most of the time there is a performance problem that can be eliminated by finding a way to eliminate the parentheses.

6.) Eliminate the derived table and just use ANSI compliant join syntax. This simplifies your query and reduces your code:


UPDATE CFRQ
SET CFRQ.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

UPDATE CFRQ
SET CFRQ.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.RecKey IS NULL OR CFRQ.RecKey = ''

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources