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.
As @Grant points out, there are several functions that are affecting this query. These are:
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:
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.
answered Jun 14, 2013 at 10:54 AM
Grant Fritchey ♦♦