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: Ravi Taxali
  Where is Ravi Taxali?
 Markham
 Canada
 Ravi Taxali
 Tags
Subject: RE: Join query slow
Thread ID: 228755 Message ID: 228801 # Views: 2 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: General VFP Topics
Date: Thursday, May 21, 2009 4:46:16 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

Hello Mr Taxali,

I just purchased the VFP book that u have authored. Wish u could dwell upon the reporting features more as this feature has received the max facelift.

As regarding my query, though both tables are indexed on expression used in the Join condition, Sys(3054, 1) report no optimisation on both tables.

Any suggestions.

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