Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: tushar Kanvinde
  Where is tushar Kanvinde?
 Kolhapur, Maharastra
 India
 tushar Kanvinde
 To: Mayur Subbu
  Where is Mayur Subbu?
 Bangalore
 India
 Mayur Subbu
 Tags
Subject: RE: Join query slow
Thread ID: 228755 Message ID: 228857 # Views: 2 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: General VFP Topics
Date: Friday, May 22, 2009 7:59:31 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

You need to check sys(3054,11) instead of sys(3054,1). Sys(3054,1) checks the optimisation of the WHERE clause. SYS(3054,11) checks the optimisation of the WHERE clause.

Variable length index keys are not a great idea in VFP. change the tag to PADR(MTRNO, ) in both tables and then JOIN on PADR(Table1.MTRNO, ) = PADR(table2.MTRNO, )

I can't think of any reason to join 2 tables on a varchar field. Maybe you should be checking your database design.

Regards
Tushar

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