Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Tamar Granor
  Where is Tamar Granor?
 Elkins Park
 Pennsylvania - United States
 Tamar Granor
 To: Mike Gagnon
  Where is Mike Gagnon?
 Pointe Claire
 Canada
 Mike Gagnon
 Tags
Subject: RE: Help with SQL statement
Thread ID: 154422 Message ID: 154557 # Views: 2 # Ratings: 0
Version: Visual FoxPro 9 Category: Databases, Tables and SQL Server
Date: Monday, December 17, 2007 10:24:26 PM         
   


> I have this query I am tring to optimize, and I have trouble with a part of it. Perhaps someoine can shed some light.
>
Select Project.* From Project   Where Project.cprojno In  (Select Data.cprojno 	From Data,;
> 	param 	Where Param.cparamurn = Data.cparamurn ;
> 	AND Not (Empty(Data.cparamdata) .And. ;
> 	EMPTY(Data.mnotes) .And. Data.lprojspec = .F. .And.;
> 	data.nquantity = 0 .And. Empty(Data.cbox1) .And. ;
> 	EMPTY(Data.cbox2) .And. Empty(Data.cbox3) .And.;
> 	EMPTY(Data.cbox4) .And. Empty(Data.cbox5) .And.;
> 	EMPTY(Data.cbox6) .And. Empty(Data.cbox7) .And.;
> 	EMPTY(Data.cbox8) .And. Empty(Data.cbox9) .And. ;
> 	EMPTY(Data.cbox10) .And. Empty(Data.cbox11) .And. ;
> 	EMPTY(Data.cbox12)) And Param.niddoctype = 0) ;
> 	ORDER By Project.cprojno Into Cursor _2920FR6OG Readwrite
> 

>
> I seem to be getting a full optimization on the inner statement, but the outer one gives me aftyer listing all the indexes, it gives me a full optimization on the data table, the param table also has a full optimization, and so does the project table, which are the three tables used in the query. The last statemnent is :
> "Rushmore optimaztion level for intermediate result : none"
>
> If any one can help.
>

It really shouldn't matter, but try using a join between the two tabls rather than a subquery.

Tamar

ENTIRE THREAD

Help with SQL statement Posted by Mike Gagnon @ 12/15/2007 1:30:11 PM
RE: Help with SQL statement Posted by Borislav Borissov @ 12/15/2007 2:54:07 PM
RE: Help with SQL statement Posted by Mike Gagnon @ 12/15/2007 3:05:27 PM
RE: Help with SQL statement Posted by Borislav Borissov @ 12/15/2007 3:20:50 PM
RE: Help with SQL statement Posted by Bernard Bout @ 12/15/2007 3:20:21 PM
RE: Help with SQL statement Posted by Mike Gagnon @ 12/15/2007 3:56:26 PM
RE: Help with SQL statement Posted by suhas hegde @ 12/16/2007 4:43:13 PM
RE: Help with SQL statement Posted by Tamar Granor @ 12/17/2007 10:24:26 PM
RE: Help with SQL statement Posted by Mike Gagnon @ 12/17/2007 11:23:35 PM
RE: Help with SQL statement Posted by Cetin Basoz @ 12/18/2007 1:14:39 PM
RE: Help with SQL statement Posted by suhas hegde @ 12/18/2007 2:48:28 PM
RE: Help with SQL statement Posted by Cetin Basoz @ 12/18/2007 9:06:34 PM
RE: Help with SQL statement Posted by suhas hegde @ 12/19/2007 4:03:39 AM
RE: Help with SQL statement Posted by Cetin Basoz @ 12/19/2007 12:30:56 PM
RE: Help with SQL statement Posted by suhas hegde @ 12/19/2007 12:53:16 PM
RE: Help with SQL statement Posted by Cetin Basoz @ 12/19/2007 1:22:51 PM
RE: Help with SQL statement Posted by suhas hegde @ 12/19/2007 3:55:12 PM
RE: Help with SQL statement Posted by Mike Gagnon @ 12/18/2007 4:04:56 PM