March 11, 2005 at 9:47 am
I'm looking for any tips or advice on improving the efficiency of this code. The optimizer is currently invoking a nested loop, followed by a hash match aggregate. I belive the <> operator is killing performance. The purpose of the code is to update a table from a staging table where the date has changed.
Thanks --
UPDATE IMPORT_DATAINVENTORY
set Inventory = ISNULL(A.ResortCode,'') + ' ' + ISNULL(A.Villa,'') + ' ' + ISNULL(A.Week ,'') + ' ' + ISNULL(A.EOY,'')
, ResortName = A.ResortName
, Season = A.Season
, IIN = A.IIN
, BRANDID = A.BRAND
, LastUpdateDate = A.CreatedDate
FROM Import_Stage_Owner A WITH (NOLOCK)
WHERE OwnerID = A.OwnerID
AND IMPORT_DATAINVENTORY.LastUpdateDate <> A.CreatedDate
March 11, 2005 at 9:59 am
Does that even compile ?
The table you're updating is not in the FROM anywhere ...
March 11, 2005 at 10:12 am
Yes, it does complie. I've added the IMPORT_DATAINVENTORY table to the FROM clause and the results do not change.
March 11, 2005 at 10:40 am
The issue is the not equal to <>
This is an expensive join operation and causes a table scan.
What is the primary key of both tables. Is it just OwnerID ?
March 11, 2005 at 10:57 am
I've tried playing with some different indexes. I even did a clustered unique covering index. I was able to avoid the table scans, but not nearly as much as when I made a change to the operator. I replaced <> with < to compare the dates. This decreased the query cost by 14 times. Is there better logic to compare the dates?
March 11, 2005 at 11:16 am
>>Is there better logic to compare the dates?
Depends on keys and data. That's why I asked about OwnerID as the key. Does OwnerID only exist once in each table as a unique identifier of each record ? If yes, then an EXISTS sub-query using equality would be more efficient than a straight join testing inequality.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply