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: 228872 # Views: 2 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: General VFP Topics
Date: Friday, May 22, 2009 11:35:39 AM         
   


> >
> > Still 100 seconds? It should execute in a few seconds.
> > Those are the four available SQL queries in Standard SQL for your purpose.
> > Are you running this on a network or on a single computer.
> > What did the SYS(3054) test for optimization return?
> > Is mtrno a Numeric, Integer, or Character column.
> > Is SET DELETED ON or OFF.
> >
> > -Anders
>
>
>
> Hi Anders,
>
>
> The query and database are on the local machine.
> The query is still executing in about 85 to 90 seconds.
> Set deleted is ON
> Sys(3054, 1) says no optimisation for both tables (really baffling)
> Mtrno is a varchar field
>
> Thank you for the help.
>
> Mayur


SYS(3054,1) only reports on WHERE clause optimization.
SYS(3054,11) reports on ON clause optimization.
What's the index key? INDEX OM mtrno, INDEX ON UPPER(mtrno) or what?
Try a run with SET DELETED OFF.
To get FULL optimization from SYS(3054) with SET DELETED ON, you need to INDEX ON DELETED() or INDEX ON DELETED() BINARY. Otherwise you will not get more than PARTIAL.
That's not guaranteed to speed things up, though.

-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