[AccessD] Optimizing query when select part requiresacalculation

Did you feel that?

Feel what?  :)



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

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