SQL Server Performance Forum – Threads Archive

Query taking more than 2 hours

Hello experts, I have a query as below UPDATE [Edge].[dbo].[tblCustomerFile] SET BillingCurrCd=Stg.EdgeCd ,UpdateDt=GETDATE() ,UpdateId=1 ,AppId=1 ,FunctionId=1 FROM [Edge_Staging].[dbo].[IC2_CustomerFileBillingCurrencyCd] As Stg INNER JOIN [Edge].[dbo].[tblCustomerFile] As Tcf ON Stg.CustFileId=Tcf.CustFileId AND Stg.ConvStatus=0 In this query I am trying to update the target table, and the record count is near a million. I know this is lot of an ask to look into my issue…but I need you guys help. I tried all the combination but nothing seems effective on the table indexing and query optimizer. Can somebody take sometime and help in this? There is a table tblCustomerFile at the bottom of this posting. I can’t add any indexes to this table, and the changes can be done only on IC2_CustomerFileBillingCurrencyCd. Execution Plan: UPDATE [Edge].[dbo].[tblCustomerFile] SET BillingCurrCd=Stg.EdgeCd ,UpdateDt=GETDATE() ,UpdateId=1 ,AppId=1 ,FunctionId=1 FROM [Edge_Staging].[dbo].[IC2_CustomerFileBillingCurrencyCd] As Stg I710NULLNULL1NULL |–Clustered Index Update(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Edge].[dbo].[tblCustomerFile].[PK_tblCustomerFile]), SET<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[tblCustomerFile].[BillingCurrCd]=RaiseIfNull([Stg].[EdgeCd]), [tblCustomerFile].[FunctionId]=RaiseIfNull(1), [tblCustomerFile].[UpdateDt]=RaiseIfNull(getdate())721Clustered Index UpdateUpdateOBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Edge].[dbo].[tblCustomerFile].[PK_tblCustomerFile]), SET<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[tblCustomerFile].[BillingCurrCd]=RaiseIfNull([Stg].[EdgeCd]), [tblCustomerFile].[FunctionId]=RaiseIfNull(1), [tblCustomerFile].[UpdateDt]=RaiseIfNull(getdate()), [tblCustomerFile].[UpdateINULL |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Expr1008]=getdate()))732Compute ScalarCompute ScalarDEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Expr1008]=getdate())[Expr1008]=getdate() |–Top(ROWCOUNT est 0)743TopTopNULLNULL |–Hash Match(Aggregate, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Bmk1000]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Bmk1000]=[Bmk1000]) DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Stg].[EdgeCd]=ANY([Stg].[EdgeCd])))754Hash MatchAggregateHASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Bmk1000]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Bmk1000]=[Bmk1000])[Stg].[EdgeCd]=ANY([Stg].[EdgeCd]) |–Hash Match(Inner Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Tcf].[CustFileId])=([Stg].[CustFileId]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Tcf].[CustFileId]=[Stg].[CustFileId]))765Hash MatchInner JoinHASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Tcf].[CustFileId])=([Stg].[CustFileId]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Tcf].[CustFileId]=[Stg].[CustFileId])NULL |–Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Edge].[dbo].[tblCustomerFile].[IDXCustomerFile4] AS [Tcf]), ORDERED FORWARD)776Index ScanIndex ScanOBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Edge].[dbo].[tblCustomerFile].[IDXCustomerFile4] AS [Tcf]), ORDERED FORWARD[Bmk1000], [Tcf].[CustFileId] |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Edge_Staging].[dbo].[IC2_CustomerFileBillingCurrencyCd].[IX_IC2_CustomerFileBillingCurrencyCd] AS [Stg]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Stg].[convstatus]=0))786Clustered Index ScanClustered Index ScanOBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Edge_Staging].[dbo].[IC2_CustomerFileBillingCurrencyCd].[IX_IC2_CustomerFileBillingCurrencyCd] AS [Stg]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Stg].[convstatus]=0)[Stg].[convstatus], [Stg].[CustFileId], [Stg].[EdgeCd] Table Structures: CREATE TABLE [dbo].[IC2_CustomerFileBillingCurrencyCd] ( [FileId] [int] NOT NULL , [TYPECD] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [EdgeCd] [int] NULL , [CustFileId] [int] NULL , [BillingCurrCd] [int] NULL , [UpdateDt] [datetime] NULL , [UpdateId] [int] NULL , [AppId] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [FunctionId] [int] NULL , [convstatus] [int] NULL ) ON [PRIMARY] GO CREATE CLUSTERED INDEX [IX_IC2_CustomerFileBillingCurrencyCd] ON [dbo].[IC2_CustomerFileBillingCurrencyCd]([CustFileId]) WITH FILLFACTOR = 99 ON [PRIMARY] GO CREATE INDEX [IX_IC2_CustomerFileBillingCurrencyCd_1] ON [dbo].[IC2_CustomerFileBillingCurrencyCd]([convstatus]) WITH FILLFACTOR = 100 ON [PRIMARY] GO CREATE TABLE [dbo].[tblCustomerFile] ( [CustFileId] [int] NOT NULL , [BusnPartEmpId] [int] NOT NULL , [GroupMoveId] [int] NOT NULL , [BusnPartTmId] [int] NOT NULL , [RepaymentAgreeId] [int] NOT NULL , [CustFileStatusCd] [int] NOT NULL , [CustFileStatusDt] [datetime] NOT NULL , [CustFileStartDt] [datetime] NULL , [CustFileEndDt] [datetime] NULL , [TransId] [int] NULL , [TransEntryDt] [datetime] NULL , [EffXferDt] [datetime] NULL , [AccompNonDependentFlg] [bit] NOT NULL , [AccompDependentCnt] [int] NULL , [AccompNonDependentCnt] [int] NULL , [CustTypCd] [int] NOT NULL , [CustMonthlySalaryAmt] [money] NULL , [CustTaxFilingExemptionsCnt] [int] NULL , [TaxFilingStatusCd] [int] NOT NULL , [TransferDt] [datetime] NULL , [RevenueLocTypCd] [int] NOT NULL , [PriorityTypCd] [int] NOT NULL , [MoveTypCd] [int] NOT NULL , [CustFileCmnt] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [DeptrDeptNm] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [DeptrCityNm] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [DeptrStProvNm] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [DeptrStAbbr] [nchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [DeptrCountryNm] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [DeptrCountryCd] [int] NOT NULL , [DestDeptNm] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [DestCityNm] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [DestStProvNm] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [DestStAbbr] [nchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [DestCountryNm] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [DestCountryCd] [int] NOT NULL , [GovtAllowLimitMIDAAmt] [money] NULL , [GovtAllowLimitRDAAmt] [money] NULL , [IntlFlg] [bit] NOT NULL , [ClientIntlFlg] [bit] NOT NULL , [NewHireFlg] [bit] NOT NULL , [ConfidentialFlg] [bit] NOT NULL , [IntlMoveTypCd] [int] NOT NULL , [DeprtResidenceOwnCd] [int] NOT NULL , [EmpTaxMoveDt] [datetime] NULL , [TempLivingBeginDt] [datetime] NULL , [TempLivingEndDt] [datetime] NULL , [FARTrackingFlg] [bit] NOT NULL , [LumpSumAmt] [money] NULL , [GeoOriginTypCd] [int] NOT NULL , [GeoOriginDesc] [nvarchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [OnlineExpRptEnabledFlg] [bit] NOT NULL , [RepaymentAgreeReceiveFlg] [bit] NOT NULL , [RepaymentAgreeSignedDt] [datetime] NULL , [RepaymentAgreeReceiveDt] [datetime] NULL , [EstMileageBetweenLocsCnt] [int] NULL , [SplInstructionsCmnt] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [TransferAllowAmt] [money] NULL , [HoldUntilDt] [datetime] NULL , [CancelCd] [int] NOT NULL , [PHHFileNo] [nchar] (1<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [EquinoxNo] [nchar] (1<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [BusnSrcCd] [int] NOT NULL , [ExposureCd] [int] NOT NULL , [InterestCd] [int] NOT NULL , [JobCandFlg] [bit] NOT NULL , [JobCandAcceptDt] [datetime] NULL , [VIPFlg] [bit] NOT NULL , [LocalMoveFlg] [bit] NOT NULL , [CallRepId] [int] NOT NULL , [CallRepNm] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [CustContactPrefCd] [int] NOT NULL , [RevenueLocCd] [int] NOT NULL , [GlobalCustId] [int] NOT NULL , [CustInfoObtainedFlg] [bit] NOT NULL , [GovtFileFlg] [bit] NOT NULL , [EmpAssignFlg] [bit] NOT NULL , [InitInterviewCompFlg] [bit] NOT NULL , [MortgagePayoffFlg] [bit] NOT NULL , [PreviousHomeOwnerFlg] [bit] NOT NULL , [WEBEnabledFlg] [bit] NOT NULL , [LegacyPolicyExcptnFlg] [bit] NOT NULL , [LegacyOperationalPolicyExcptnFlg] [bit] NOT NULL , [LegacyPolicyExcptnDesc] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [LegacyPolicyExcptnAmt] [money] NULL , [NewHirePolicyCd] [int] NOT NULL , [LegacyCustomerSiteNo] [nchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [PrimaryReloContactCd] [int] NULL , [CustPrevCloseDt] [datetime] NULL , [CreateId] [int] NOT NULL , [CreateDt] [datetime] NOT NULL , [UpdateId] [int] NOT NULL , [UpdateDt] [datetime] NOT NULL , [FunctionId] [int] NOT NULL , [AppId] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [DBAPartitionKey] [int] NOT NULL , [CustViewPolicyCd] [int] NOT NULL , [CustViewHandbookCd] [int] NOT NULL , [CustViewExceptionCd] [int] NOT NULL , [ConciergeIndCd] [int] NOT NULL , [ExecutiveOfficerIndCd] [int] NOT NULL , [DeptrJobDesc] [nvarchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [DestJobDesc] [nvarchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [ClientAuthDt] [datetime] NULL , [AuthRecdDt] [datetime] NULL , [CustAuthTakeoverSrcCd] [int] NOT NULL , [CustAuthTakeoverOtherDesc] [nvarchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [AuthRecdTime] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [JobLevelCd] [int] NOT NULL , [JobClassCd] [int] NOT NULL , [AuthCreateById] [int] NOT NULL , [AuthCreateDt] [datetime] NULL , [AuthUpdDt] [datetime] NULL , [AuthUpdById] [int] NOT NULL , [AuthRequestByCd] [int] NOT NULL , [BillingCurrCd] [int] NOT NULL , [ClientCustFileIndCd] [int] NOT NULL , [CustCappedMoveIndCd] [int] NOT NULL , [CustCappedMoveAmt] [real] NULL , [CustMilitaryRankCd] [int] NOT NULL , [DestResidenceOwnCd] [int] NOT NULL ) ON [PRIMARY] GO CREATE INDEX [IDXCustomerFile1] ON [dbo].[tblCustomerFile]([RepaymentAgreeId]) WITH FILLFACTOR = 99 ON [PRIMARY] GO CREATE INDEX [IDXCustomerFile2] ON [dbo].[tblCustomerFile]([BusnPartEmpId]) WITH FILLFACTOR = 99 ON [PRIMARY] GO CREATE INDEX [IDXCustomerFile3] ON [dbo].[tblCustomerFile]([BusnPartTmId]) WITH FILLFACTOR = 99 ON [PRIMARY] GO CREATE INDEX [IDXCustomerFile4] ON [dbo].[tblCustomerFile]([GroupMoveId]) WITH FILLFACTOR = 99 ON [PRIMARY] GO CREATE INDEX [IDXCustomerFile5] ON [dbo].[tblCustomerFile]([TransId]) WITH FILLFACTOR = 99 ON [PRIMARY] GO Thanks in advance!

Have you tried a WHERE EXISTS clause instead of the INNER JOIN? Compare the response time on SELECT queries, that should give you some indication for the time that will be required for UPDATE. SELECT CF.CustFileId
FROM [Edge].[dbo].[tblCustomerFile] CF
WHERE EXISTS
(SELECT Stg.* FROM [Edge_Staging].[dbo].[IC2_CustomerFileBillingCurrencyCd] As Stg
WHERE Stg.CustFileId = CF.CustFileId
AND Stg.ConvStatus=0) Also check: SELECT Stg.ConvStatus, COUNT(*)
FROM [Edge_Staging].[dbo].[IC2_CustomerFileBillingCurrencyCd] As Stg
GROUP BY Stg.ConvStatus If there is a relatively small number of rows with ConvStatus=0, then an IN clause with a subquery might perform better than the EXISTS version: SELECT CF.CustFileId
FROM [Edge].[dbo].[tblCustomerFile] CF
WHERE CF.CustFileId IN
(SELECT Stg.CustFileId FROM [Edge_Staging].[dbo].[IC2_CustomerFileBillingCurrencyCd] As Stg
WHERE Stg.ConvStatus=0)

is there any real need to use nvarchar instead of varchar and datetime instead of smalldatetime. —————————————-

quote:
CREATE CLUSTERED INDEX [IX_IC2_CustomerFileBillingCurrencyCd] ON [dbo].[IC2_CustomerFileBillingCurrencyCd]([CustFileId]) WITH FILLFACTOR = 99 ON [PRIMARY]
GO CREATE INDEX [IX_IC2_CustomerFileBillingCurrencyCd_1] ON [dbo].[IC2_CustomerFileBillingCurrencyCd]([convstatus]) WITH FILLFACTOR = 100 ON [PRIMARY]
GO

Have you tried lowering the fillfactor to a default value (90) or even lesser than that (85 or 80)?
It is not advisable to have fillfactor 100 when you have frequent/moderately frequent inserts and updates in your table. this will result in lot of page splits which may affect performance. Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"

Adriaan and others, I got it working as a charm. I used EXISTS and the update runs within 6 mins. Great! I got my work done for the day. Thanks to all of your suggestions. RoyalSher.
*********
The world is the great gymnasium where we come to make ourselves strong.

]]>