[AccessD] Query Question

Looking at the execution plan, they both use one Table San on LKUP and one Index Seek on 
Table1, but with my sampe data 0f 3 lookup rowws and 100 rows in Table 1, the Cartesian 
method has an estimated row count of 24 while the Inner Join has an estimated row count of 
36.   

The cost of the the two queries is .0448 for the Inner Join and .0446 for the Cartesian Join so 
the Cartesian appears to be slightly more efficient .




On 5 May 2008 at 20:12, A.D.Tejpal wrote:

>     Could there be any particular reason for preferring a Cartesian join filtered by WHERE clause? Why not simply use an inner join as follows (T_1 and T_LkUp are the names of two tables)?
> 
> ====================================
> SELECT T_1.LookUpValue, T_LkUp.*  
> FROM T_1 INNER JOIN T_LkUp ON (T_1.LookUpValue <= T_LkUp.RangeEnd)  AND (T_1.LookUpValue >=  T_LkUp.RangeStart);
> ====================================
> 
> A.D.Tejpal
> ------------
> 
>   ----- Original Message ----- 
>   From: Stuart McLachlan 
>   To: Access Developers discussion and problem solving 
>   Sent: Wednesday, April 30, 2008 19:33
>   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
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com