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: 228760 # Views: 2 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: General VFP Topics
Date: Thursday, May 21, 2009 11:33:42 AM         
   


> 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


Try
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)

can be simplified to
 SELECT * FROM Aog WHERE mtrno NOT IN (SELECT mtrno FROM Aogmaster) 
 SELECT * FROM Aog WHERE NOT EXISTS (SELECT * FROM Aogmaster WHERE Aogmaster.mtrno=Aog.mtrno) 
 SELECT * FROM Aog WHERE mtrno > ANY (SELECT mtrno FROM Aogmaster) 
 SELECT A.* FROM Aog AS A LEFT JOIN Aogmaster AS B ON A.mtrno=B.mreni WHERE B.mtrno IS NULL

-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