Either might win. If your tables return a large recordset (hundreds of thousands of rows) or a very small recordset (dozens) then the join is probably better. If your tables return a moderate sized record set, then the case might be better. Of course, for small recordsets, it doesn't matter much either way.
Personally, I believe that the 5th join is better in the long run, because you can more easily modify the entries in the table. (for instance, do you need Canadian stuff, Virgin Islands, Puerto Rico, etc.) Also, Iraq might be the 51st state this time next year! Bad jokes aside, the join idea is generally better. If you don't like a join in the middle of a big mess, then do the 4 tables to a (smaller?) temp table, then apply the 5th state join to just that result set in a 2nd step. This is very frequently faster. -----Original Message----- From: Peterson, Chris [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 20, 2004 10:29 To: SQL Subject: Best SQL query method Question on Optimization in SQL: What do you think would be faster, a CASE statement for all 50 states to group them into regions, or inner joining to a file that has the regions setup and each state defined? With CASE, I would not have to have the 5th file joined to this query, and as the origin state field is non-indexed, I think the inner join would hamper the query pretty badly. Anyone have any 'best practice' advice? Here is my current query.... SELECT SUM(CASE WHEN F2.ORBAMT > 0 THEN F2.ORBAMT ELSE ((SELECT SUM(DISMNF) FROM LOAD WHERE LOAD.DIODR## = F2.ORODR##) * 1.35) END) as Revenue, MONTH(SUBSTR(DIGITS(0+F1.DIDATE),2,7)) as Month, YEAR(SUBSTR(DIGITS(0+F1.DIDATE),2,7)) as Year, F4.NMAREG as Region, SUM(F1.DISMNF) as LoadedMiles, SUM(F1.DIEMIL) as EmptyMiles, COUNT(F2.ORODR##) as OrderCount FROM LOADDATE F1 INNER JOIN ORDBILL F2 ON F1.DIODR## = F2.ORODR## INNER JOIN ORDER F3 ON F1.DIODR## = F3.ORODR## INNER JOIN NMSAREA F4 ON Trim(F3.OROST) = Trim(F4.NMACOD) WHERE F1.DIDATE >= 2004001 AND F1.DIDATE <= 2004274 AND F1.DIDISP = '01' AND ORSTAT IN ('D', 'E') GROUP BY YEAR(SUBSTR(DIGITS(0+F1.DIDATE),2,7)), MONTH(SUBSTR(DIGITS(0+F1.DIDATE),2,7)), F4.NMAREG ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Sams Teach Yourself Regular Expressions in 10 Minutes by Ben Forta http://www.houseoffusion.com/banners/view.cfm?bannerid=40 Message: http://www.houseoffusion.com/lists.cfm/link=i:6:2063 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/6 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:6 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.6 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
