Join T1.distinct_identifier to T2."choice"_identifier

[aas4mis]

I've got a primary key on t1 (Item_Identifier) and on t2 I have 4 "item" fields. t1 is the master identifier and is derived from one of the 4 fields in t2. I'm currently trying to join on a COALESCE function, but it is taking way too long. I end up cancelling after about an hour of letting it run. Does anybody know a faster alternative to the following query?

SELECT ...
FROM    tbl_Enterprise_Master_Items item_master
LEFT JOIN vw_Enterprise_Item_Quantities quantities ON
        COALESCE(    CASE WHEN item_master.Item_Identifier = quantities.ID_1 THEN 1 END,
                    CASE WHEN item_master.Item_Identifier = quantities.ID_2 THEN 1 END,
                    CASE WHEN item_master.Item_Identifier = quantities.ID_3 THEN 1 END,
                    CASE WHEN item_master.Item_Identifier = quantities.ID_4 THEN 1 END
                ) = 1
 

--Side note
When joining on item_master.Item_Identifer to any of the single fields on the quantities table it takes about 4 minutes to complete.

--Thanks,
--aas4mis

[Rabbit]

Doing calculations on a field is going to slow things down. You can try and OR the conditions. But ORs can be slow as well. If so, try a UNION approach. But first and foremost, I hope you've created indexes for those fields.

You can find examples of the optimizations mentioned in this article: http://bytes.com/topic/sql-server/in...ze-sql-queries

[aas4mis]

There is an index on t1.item_identifer (item_master), but not on the view. The view combines quantities from multiple sources (Online,WMS, EDI, etc...), then joins the sources together and aggregates the total quantity. t2.ID_X may or may not exist in T1, which is why I used COALESCE to choose the correct identifier to match t1 on. I'm on a time crunch at the moment, so I created 4 procedures, each procedure joins and updates on t1.item_identifier = t2.ID_X. The procedures, executed 1 after the other, complete in under 5 minutes.

I'll revisit this another time, but I'm almost certain this is how it will have to be handled if I plan on taking advantage of the index. Thanks.

--aas4mis

[Rabbit]

Well, when you get a chance, try the OR approach or the UNION approach. One of them should work without having to run update queries.