Asger Blond
ab-mi at post3.tele.dk
Thu Oct 13 16:30:59 CDT 2011
Did you make some performance tests using this method? IMO your method is just a kind of manually construction of what the query optimizer automatically does for you when use an IN clause. And my guess is that the query optimizer would do the job more efficient. Asger -----Oprindelig meddelelse----- Fra: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] På vegne af Arthur Fuller Sendt: 13. oktober 2011 16:41 Til: Access Developers discussion and problem solving Emne: Re: [AccessD] Optimizing query when select part requires acalculation Pursuant to IN(). An often-preferred alternative is to create a temp-table consisting of the values contained within the IN(), and then to join this to the real tables. The time taken to construct the temp-table is negligible. Once it's created, the natural joins do all the heavy lifting for you. Since you want to join on multiple columns, I suggest that you create a calculated (virtual) column on both tables, and then do the join on those columns. It might require a little addition to your documentation, but it cleans up the mess rather nicely, IMO. HTH, Arthur -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com