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

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
___________________________

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