Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Anders Altberg
  Where is Anders Altberg?
 Uppsala
 Sweden
 Anders Altberg
 To: Mayur Subbu
  Where is Mayur Subbu?
 Bangalore
 India
 Mayur Subbu
 Tags
Subject: RE: Join query slow
Thread ID: 228755 Message ID: 228790 # Views: 2 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: General VFP Topics
Date: Thursday, May 21, 2009 4:20:19 PM         
   


> >
> > Hi Mayur;
> > As far as you noticed that the query without the Join clause is faster you can do the selection like this:
> >
> >
> > Select a.mtrno, a.mtrdt, a.itemcode, a.qty from aog a, aogmaster b
> > where a.mtrno = b.mtrno AND isnull(b.mtrno)
> > 

> >
> > Although am not understanding why you have nulls in your index? and filtering on nulls!
> > can you explain what the query is supposed to select??
> >
> > can you explain more what this query is used for?
> >
> > Ammar Hadi ................IRAQ
> >
> > My Foxite Weblog
> >
> > ---------------
> > I I I really love foxite
> > ___________________________
>
>
>
> Hi Ammar,
>
> I bascally want to select all records from AOG table that do not exist in the AOGMASTER table. A normal SQL statement like does the job on a SQL Server in seconds
>
Select a.mtrno, a.mtrdt, a.itemcode, a.qty from aog a left join aogmaster b
> 
> Thanx

VFP always performs a second query on the same data and conditions much faster because data is already cached in local memory. VFP can only optimize the query if there are indexes on the columns that are referenced in the ON cluse and he WHERE clause .

This query is meaningless: 
 Select a.mtrno, a.mtrdt, a.itemcode, a.qty from aog a, aogmaster b
 where a.mtrno = b.mtrno AND isnull(b.mtrno) 

because the test for NULL to select or exclude non-matching keys can only be used in LEFT|RIGHT JOIN.

-Anders

ENTIRE THREAD

Join query slow Posted by Mayur Subbu @ 5/21/2009 10:55:59 AM
RE: Join query slow Posted by Ammar Hadi @ 5/21/2009 11:29:59 AM
RE: Join query slow Posted by Mayur Subbu @ 5/21/2009 3:59:01 PM
RE: Join query slow Posted by Anders Altberg @ 5/21/2009 4:20:19 PM
RE: Join query slow Posted by Anders Altberg @ 5/21/2009 4:32:29 PM
RE: Join query slow Posted by Anders Altberg @ 5/21/2009 11:33:42 AM
RE: Join query slow Posted by Mayur Subbu @ 5/21/2009 4:22:23 PM
RE: Join query slow Posted by Anders Altberg @ 5/21/2009 4:43:36 PM
RE: Join query slow Posted by Mayur Subbu @ 5/22/2009 4:01:56 AM
RE: Join query slow Posted by tushar @ 5/22/2009 7:59:31 AM
RE: Join query slow Posted by Anders Altberg @ 5/22/2009 11:54:02 AM
RE: Join query slow Posted by tushar @ 5/22/2009 12:17:35 PM
RE: Join query slow Posted by Anders Altberg @ 5/22/2009 9:31:24 PM
RE: Join query slow Posted by tushar @ 5/23/2009 6:56:11 AM
RE: Join query slow Posted by tushar @ 5/22/2009 12:30:31 PM
RE: Join query slow Posted by Anders Altberg @ 5/22/2009 2:24:11 PM
RE: Join query slow Posted by tushar @ 5/22/2009 4:14:42 PM
RE: Join query slow Posted by Mayur Subbu @ 5/22/2009 4:55:53 PM
RE: Join query slow Posted by Anders Altberg @ 5/22/2009 8:47:14 PM
RE: Join query slow Posted by Mayur Subbu @ 5/27/2009 4:44:53 AM
RE: Join query slow Posted by Anders Altberg @ 5/27/2009 8:10:28 AM
RE: Join query slow Posted by Anders Altberg @ 5/22/2009 11:35:39 AM
RE: Join query slow Posted by Mayur Subbu @ 5/22/2009 4:48:42 PM
RE: Join query slow Posted by Anders Altberg @ 5/22/2009 9:15:37 PM
RE: Join query slow Posted by Ravi Taxali @ 5/21/2009 4:07:12 PM
RE: Join query slow Posted by Mayur Subbu @ 5/21/2009 4:46:16 PM
RE: Join query slow Posted by Ravi Taxali @ 5/22/2009 4:16:01 PM