Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Ravi Taxali
  Where is Ravi Taxali?
 Markham
 Canada
 Ravi Taxali
 To: Mayur Subbu
  Where is Mayur Subbu?
 Bangalore
 India
 Mayur Subbu
 Tags
Subject: RE: Join query slow
Thread ID: 228755 Message ID: 228786 # Views: 2 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: General VFP Topics
Date: Thursday, May 21, 2009 4:07:12 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

Use SYS(3054,1) before running your query and see what index tags VFP is using, if any. This may give you clues to take additional steps to further optimise your query.

- Ravi Taxali
Visual FoxPro Made Simple -- An excellent book for beginners as well as experienced programmers

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