Link to a Consolidated Product table-which is best ?

[syswizard]

We have the need to add a join to a simple table that has a small set of product numbers and their corresponding consolidated product number.
So the order table links to this table, and if a consolidated product number is Not Null, then it will be the GROUP BY, otherwise the regular product number is the GROUP BY.
So the question from an SQL perspective:
Is it more efficient to do a UNION with an INNER JOIN of equality and then inequality....
or to do a LEFT OUTER JOIN and use IIF() to determine which product number to SUM/GROUP BY.
The latter is a bit ugly as one must use the IIF() in the GROUP BY statement.
My gut says the latter will be a bit more efficient, but my thinking has always been that UNION forces a double pass thru the tables....but that's not really true, is it ?

 

[Rx_]

How many records, how big is the recordset in Kb or Mb? Pulled across a network? Are the tables well normalized? Are the fields being joined efficiently indexed?

A key word to searh on this might be a "Cursor".
Does the cursor need to evaluate the If/then on a record by record basis - or would letting the SQL Statement join the data be faster?

If-then - Fetch records - each record calls the if/then - 10 records - 10 if/thens 100,000 records 100,000 if then might be called.
SQL Language - Fetch Selection - organize logic - return result set.
Typically, larger data sets warrant SQL.

So, of course the answer is "It Depends"

In general, SQL is better. However, there is a lot to be said for the power of a well designed cursor rule base process using temp tables.

 

[syswizard]

The tables are small (<500) and they are indexed properly anyway.
No need for a cursor...the logic is simple:
INNER JOIN productnum to productnum in the consolidation table, group by the consolidated product num; then outer join on where the consolidated product num is null and group by regular product num. Union the two result sets.

 

[Rx_]

For indexed tables that size, my guess is that they are pretty much loaded with one hard disk head read, a couple of network packages, and run in memory.
You would probably be hard pressed to show the difference between the SQL processing overhead and a cursor solution.

At ten thousand to millions of records, a couple of cursor can take a huge amount of time.