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
 Tags
Subject: Join query slow
Thread ID: 228755 Message ID: 228755 # Views: 4 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: General VFP Topics
Date: Thursday, May 21, 2009 10:55:59 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

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