Andy Lacey
andy at minstersystems.co.uk
Thu May 1 01:46:14 CDT 2008
Wow, thanks for going to so much trouble Stuart. I'll try both methods and assess their performance with real data. I love having 2 solutions. -- Andy Lacey http://www.minstersystems.co.uk >-----Original Message----- >From: accessd-bounces at databaseadvisors.com >[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of >Stuart McLachlan >Sent: 01 May 2008 03:33 >To: Access Developers discussion and problem solving >Subject: Re: [AccessD] Query Question > > >Not necessarily. Jet's Rushmore technology is pretty smart >at optimising queries > >You can't see the optimised execution plan in Access, , but I >set the tables up in SQL Server >with appropriate indexes and did an execution plan there. >(100 values in Table1, 3 in LKUP) > >In SQL Server ,my version uses a Table Scan on LKUP (which >would normally be fairly >small) and one Index Seek on Table1. Drew's version uses an >Index Scan on Table1 and >two Index Seeks on LKUP. > >Doing an Execution Plan on a Union of the two versions, my >version's sub-branch cost >0.0446 while Drew's cost 0.0598. (34% more expensive) > >Cheers, >Stuart > >On 30 Apr 2008 at 16:36, Charlotte Foust wrote: > >> But an Outer Join WHERE has to examine all records. A subquery has >> already filtered them down. >> >> Charlotte Foust >> >> -----Original Message----- >> From: accessd-bounces at databaseadvisors.com >> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart >> McLachlan >> Sent: Wednesday, April 30, 2008 4:01 PM >> To: Access Developers discussion and problem solving >> Subject: Re: [AccessD] Query Question >> >> You can return both results and you don't need a subquery, >just use an >> Outer Join with >> "Where": >> >> SELECT Table1.LookupValue,Result1,Result2 >> FROM LKUP, Table1 >> WHERE (RangeStart<=LookupValue AND RangeEnd>=[LookupValue]) >> >> Cheers, >> Stuart >> >> >> On 30 Apr 2008 at 14:04, Drew Wutka wrote: >> >> > Nope, a subquery will only work returning one record for one >> > field...unless it's in the WHERE statement, where you can >use 'IN' if >> > you want. >> > >> > You'd have a problem reversing this by having the result >fields show >> > up in the main query, because then it will only run the number of >> > records in LKUP, not the number in Table1. >> > >> > Drew >> > >> > -----Original Message----- >> > From: accessd-bounces at databaseadvisors.com >> > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Andy >> > Lacey >> > Sent: Wednesday, April 30, 2008 12:55 PM >> > To: 'Access Developers discussion and problem solving' >> > Subject: Re: [AccessD] Query Question >> > >> > Works like a dream Drew, you're a star as ever. Is there any way of >> > streamlining it to return more than 1 value from the subquery? >> > Something like >> > >> > SELECT T1.LookupValue, (SELECT Result1, Result2 FROM LKUP WHERE >> > RangeStart<=T1.LookupValue AND RangeEnd>=T1.LookupValue) >FROM Table1 >> > as T1 >> > >> > Only I know that doesn't work cos I've tried it. Says something >> > about >> > the EXISTS keyword, and I don't know about that. >> > >> > -- Andy Lacey >> > http://www.minstersystems.co.uk >> > >> > >> > >> > >-----Original Message----- >> > >From: accessd-bounces at databaseadvisors.com >> > >[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Drew >> > >Wutka >> > >Sent: 30 April 2008 17:26 >> > >To: Access Developers discussion and problem solving >> > >Subject: Re: [AccessD] Query Question >> > > >> > > >> > >If your database is properly indexed yes. They do great. >> > > >> > >Drew >> > > >> > >-----Original Message----- >> > >From: accessd-bounces at databaseadvisors.com >> > >[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Andy >> > >Lacey >> > >Sent: Wednesday, April 30, 2008 11:02 AM >> > >To: Access Developers discussion and problem solving >> > >Subject: Re: [AccessD] Query Question >> > > >> > >There you see, that's just the sort of thing I had in mind. >> > >Thanks Drew. I just knew the answer was something like this but >> > >couldn't have come up with the syntax. Do these >subqueries perform ok >> >> > >though? >> > > >> > >-- >> > >Andy Lacey >> > >http://www.minstersystems.co.uk >> > > >> > > >> > > >> > >The information contained in this transmission is >intended only for >> > >the person or entity to which it is addressed and may >contain II-VI >> > >Proprietary and/or II-VI Business Sensitive material. If >you are not >> > >the intended recipient, please contact the sender immediately and >> > >destroy the material in its entirety, whether electronic or hard >> > >copy. You are notified that any review, retransmission, copying, >> > >disclosure, dissemination, or other use of, or taking of >any action >> > >in reliance upon this information by persons or entities >other than >> > >the intended recipient is prohibited. >> > > >> > > >> > >-- >> > >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 >> > The information contained in this transmission is intended only for >> the person or entity to which it is addressed and may contain II-VI >> Proprietary and/or II-VI Business Sensitive material. If you are not >> the intended recipient, please contact the sender immediately and >> destroy the material in its entirety, whether electronic or >hard copy. >> You are notified that any review, retransmission, copying, >disclosure, >> dissemination, or other use of, or taking of any action in reliance >> upon this information by persons or entities other than the intended >> recipient is prohibited. >> > >> > >> > -- >> > 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 > >