Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Cetin Basoz
  Where is Cetin Basoz?
 Izmir
 Turkey
 Cetin Basoz
 To: Mike Gagnon
  Where is Mike Gagnon?
 Pointe Claire
 Canada
 Mike Gagnon
 Tags
Subject: RE: Help with SQL statement
Thread ID: 154422 Message ID: 154630 # Views: 2 # Ratings: 1
Version: Visual FoxPro 9 Category: Databases, Tables and SQL Server
Date: Tuesday, December 18, 2007 1:14:39 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.
>
>
>
> Mike Gagnon
> Refox XI +(English version)

Hi Mike,
Instead of empty use values representing empty values for that field datatyep so it can utilize existing indexes. ie:

myNumField = 0
myDate_Or_DT_Field = {}
myCharField == ""
!myLogical


Second put fields that have indexes defined and/or likely to evaluate false-(with and)/true-(with or) in front in where clause to utilize VFP's shortcut feature.

Something like:
Select Project.* From Project
   Where Project.cprojno In  ;
   (;
     Select Data.cprojno ;
   	From ('Data') dt ;
   	inner join ('param') prm on prm.cparamurn = dt.cparamurn ;
   	Where ;
   	  Prm.niddoctype = 0 and ;
 	  dt.nquantity = 0 And ;
 	  !Dt.lprojspec And ;
 	  Dt.mnotes == "" And ;
 	  Dt.cbox1 == "" And ;
	... ;
        !(dt.cparamdata == "") ;
   ) ;
 	ORDER By Project.cprojno ;
 	Into Cursor _2920FR6OG ;
 	Readwrite


PS: Cursor name is a little hard to read sys15 value, I would use a menaingfull name:)

Cetin Basoz

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