Hi All, I am using a merge query to do an update(Please find the query below, There are around 30 columns in each table but i have just provided three here) There are about 30 million records in the source table and 300,000 records in the destination table. There is a index in the joining column in both table. However this query takes forever to complete.Please provide suggestions. MERGE DestTable AS Dest USING ( SELECT Col1 , Col2 , Col3 , CHECKSUM(Col1, Col2, Col3) AS [CheckSUM] FROM SourceTable ) AS SRC ON SRC.Col1 = Dest.Col1 WHEN MATCHED and SRC.[CheckSum] Dest.[CheckSum] THEN UPDATE SET Dest.Col2 = SRC.Col2 , Dest.Col3 = SRC.Col3 , Dest.CheckSum = SRC.Checksum;
As @Grant points out, there are several functions that are affecting this query. These are: * [dbo].Humana_OrgHub_ValidateDUNS * [dbo].Humana_OrgHub_ValidatePhoneNum * CHECKSUM * SUBSTRING * REPLACE I'm guessing that the ValidateDUNS function is a lookup; the ValidatePhoneNum is probably a stringslicer. What might be worth doing is something along these lines: 1. Create a temp table that contains the fields in the base table from the SRC part of the MERGE statement 2. Populating that table with records that match the DEST table, but without doing the function stuff above 3. using the temp table as the SRC of your MERGE, which would remain largely unchanged. The problem with optimising queries that use inline functions is that the query / exectuion plan doesn't show the impact of those individual functions.
The estimated number of rows is 50546400000. Either you're updating all the data in the table, or, your statistics are WAY out of date. Either way, it's going to be difficult to tune a query that is accessing that much information. The table [OrgHub].[dbo].[BusinessRecords] is a heap, meaning it doesn't have a clustered index, and there's no indication of any filtering on that table at all. You're joining to [OrgHub].[stg].[DnBOrgStg].[PK_DnBOrgStg_1] [Dest], but it doesn't have JOIN criteria, which you can see as a warning in the Loops operator in your plan. I would suggest modifying the query to include some type of JOIN information. Also, this operation: [Expr1004] = Scalar Operator([OrgHub].[dbo].[Humana_OrgHub_ValidateDUNS]([OrgHub].[dbo].[BusinessRecords].[DUNS])) Is absolutely preventing any sort of index use. Basically, you need to get some indexes on the BusinessRecords table and you need to more clearly define the JOIN criteria between those tables so that there is some filtering done at the index level. Otherwise, it's trying to update everything.