[AccessD] Optimizing query when select part requiresacalculation

David,
Don't disseminate rumors...
Maybe you just NOT have it IN.
Couldn’t resist and no offence meant ;-)
Asger

-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] På vegne af David McAfee
Sendt: 13. oktober 2011 23:41
Til: Access Developers discussion and problem solving
Emne: Re: [AccessD] Optimizing query when select part requiresacalculation

I have read that NOTIN() Can give erroneous results, but I forget why at the
moment.

I'm guilty of using it too :/



On Thu, Oct 13, 2011 at 2:30 PM, Asger Blond <ab-mi at post3.tele.dk> wrote:

> 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
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com