Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Mayur Subbu
  Where is Mayur Subbu?
 Bangalore
 India
 Mayur Subbu
 To: Ammar Hadi
  Where is Ammar Hadi?
 Al-Samawah
 Iraq
 Ammar Hadi
 Tags
Subject: RE: Join query slow
Thread ID: 228755 Message ID: 228783 # Views: 3 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: General VFP Topics
Date: Thursday, May 21, 2009 3:59:01 PM         
   


> > Hi friends,
> >
> > I have query like so :-
> >
> >
Select a.mtrno, a.mtrdt, a.itemcode, a.qty from aog a left join aogmaster b
> > on a.mtrno = b.mtrno where isnull(b.mtrno)

> > or
> >
Select a.mtrno, a.mtrdt, a.itemcode, a.qty from aog a left join aogmaster b
> > on a.mtrno = b.mtrno where a.mtrno not in (select mtrno from aogmaster)

> >
> > AOGMASTER table has 24000 recs and the AOG table has a equal number. Both tables are indexed on MTRNO. Both queries takes almost 100 seconds to execute. I have observed that even a simple query like :-
> >
> >
Select a.mtrno, a.mtrdt, a.itemcode, a.qty from aog a inner join aogmaster b
> > on a.mtrno = b.mtrno
takes a while. However the following executes very fast :-
> >
> >
Select a.mtrno, a.mtrdt, a.itemcode, a.qty from aog a, aogmaster b
> > where a.mtrno = b.mtrno

> >
> > Could someone help me in writing a faster query.
> >
> > Thanks
>
> 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
> > on a.mtrno = b.mtrno where a.mtrno not in (select mtrno from aogmaster)


Thanx

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