[AccessD] Optimizing query when select part requiresacalculation

:)



Here's how Nulls affect NOTIN()

http://www.simple-talk.com/sql/t-sql-programming/ten-common-sql-programming-mistakes/



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

>
> Well, I wasn't writing about feelings. But when asked so frankly then:
> Certainly NOT...
> Asger
>
> -----Oprindelig meddelelse-----
> Fra: accessd-bounces at databaseadvisors.com [mailto:
> accessd-bounces at databaseadvisors.com] På vegne af David McAfee
> Sendt: 14. oktober 2011 00:19
> Til: Access Developers discussion and problem solving
> Emne: Re: [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
> >
> --
> 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
>