I am attempting a single column merge between two tables. The first table (
VisitorSession) has 40,000,000 rows. The second (
ShoppingCart) has 9,000,000 rows.
In my development environment, the query takes just under 8 minutes. But in the production environment, it should take significantly less (much more powerful machine). However, I anticipate the query taking at least 2 minutes to run in production. I know that this query was causing timeouts for other developers in the development environment, which means it could easily cause timeouts for customers. Is there a safer and/or faster way to perform this query?
declare @dt datetime = cast(dateadd(month, -6, getdate()) as date); merge ShoppingCart as TargetTable -- 07:55 to complete in Dev using ( select * from -- 04:55 to run select, resulting in 12,727,927 rows in Dev ( select visitorid -- int, not null, foreign key ,useripaddress -- varchar(55), null ,row_number() over (partition by visitorid order by createdate desc) as [row] from VisitorSession (nolock) where UserIPAddress is not null and CreateDate > @dt -- createdate is a datetime, not null ) as subTbl where subTbl.[row] = 1 ) as SourceTable on (TargetTable.VisitorID = SourceTable.VisitorID) -- visitorid is not a primary key when matched then update set TargetTable.UserIpAddress = SourceTable.UserIpAddress;