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: Jim Carls
  Where is Jim Carls?
 Nashville
 Tennessee - United States
 Jim Carls
 Tags
Subject: RE: Slow SELECT with subquery
Thread ID: 288745 Message ID: 288867 # Views: 22 # Ratings: 1
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Friday, December 24, 2010 10:38:28 PM         
   


> >
> > What does SYS(3054) tell you? Try using SYS(3054, 12) and see where it says you're not optimized.
> >
> > Tamar
>
> I tried it by running the view directly with the macro condition variables set to ".t." and got the results below (the first time I ran it, I realized that not every table had a "deleted()" index and fixed that). I'm not really sure why I'm getting "partial" on some of these tables, since the links are all primary integer keys for which an index exists. I'm also curious about the partial optimization for the ItemSplits table where I've marked. Relations from this table to other tables are all done via primary integer keys in the parents, however this table does not have a primary key of its own and is a child table to all the others to which it is related.
>
> Using index tag Deleted to rushmore optimize table Orders
> Rushmore optimization level for table Orders: partial <====
> Using index tag Deleted to rushmore optimize table Vendors
> Rushmore optimization level for table Vendors: full
> Using index tag Deleted to rushmore optimize table Clients
> Rushmore optimization level for table Clients: full
> Using index tag Deleted to rushmore optimize table Projects
> Rushmore optimization level for table Projects: full
> Using index tag Deleted to rushmore optimize table Items
> Rushmore optimization level for table Items: full
> Using index tag Deleted to rushmore optimize table Splits
> Rushmore optimization level for table Splits: partial <====
> Using index tag Deleted to rushmore optimize table ShipVendor
> Rushmore optimization level for table ShipVendor: full
> Using index tag Deleted to rushmore optimize table ConsVendor
> Rushmore optimization level for table ConsVendor: full
> Using index tag Deleted to rushmore optimize table Invoices
> Rushmore optimization level for table Invoices: full
> Using index tag Deleted to rushmore optimize table ChkReqs
> Rushmore optimization level for table ChkReqs: full
> Joining table Clients and table Orders using index tag Clnt_key
> Joining table Vendors and intermediate result using temp index
> Joining intermediate result and table Projects using index tag Proj_key
> Joining intermediate result and table Items using index tag Po_key
> Joining intermediate result and table Splits using index tag Po_itm_key
> Joining intermediate result and table ShipVendor using index tag Vndr_key
> Joining intermediate result and table ConsVendor using index tag Vndr_key
> Joining intermediate result and table Invoices using index tag Inv_key
> Joining intermediate result and table ChkReqs using index tag Requestkey
> Rushmore optimization level for intermediate result: none
> Using index tag Deleted to rushmore optimize table Orders
> Rushmore optimization level for table Orders: full
> Using index tag Deleted to rushmore optimize table Vendors
> Rushmore optimization level for table Vendors: full
> Using index tag Deleted to rushmore optimize table Clients
> Rushmore optimization level for table Clients: full
> Using index tag Deleted to rushmore optimize table Projects
> Rushmore optimization level for table Projects: full
> Using index tag Deleted to rushmore optimize table ShipVendor
> Rushmore optimization level for table ShipVendor: full
> Using index tag Deleted to rushmore optimize table ConsVendor
> Rushmore optimization level for table ConsVendor: full
> Joining table Clients and table Orders using index tag Clnt_key
> Joining table Vendors and intermediate result using temp index
> Joining intermediate result and intermediate result using temp index
> Joining intermediate result and table Projects using index tag Proj_key
> Joining intermediate result and table ShipVendor using index tag Vndr_key
> Joining intermediate result and table ConsVendor using index tag Vndr_key

I see that you've made considerable speed-ups already, but figured I'd add some info on reading SYS(3054) results.

First, all the lines like this one:

Rushmore optimization level for table Orders: partial

refer to filters, not joins. IOW, in this case, based on whatever filters you have for Orders, it's partially optimized. Note that you'll see "None" when there are no filters for a table.

The later part of the output covers joins. Most of the time, what you want to see is that Rushmore's using an existing index rather than a temp index. Sometimes, a temp index is a better choice (because some intermediate result is large and indexing it will give you better optimization than using an existing tag on a much smaller table), but whenever you see "temp index," especially early in the process, it's a sign to look deeper.

Tamar

ENTIRE THREAD

Slow SELECT with subquery Posted by Jim Carls @ 12/23/2010 4:06:07 PM
RE: Slow SELECT with subquery Posted by Tamar Granor @ 12/23/2010 10:04:55 PM
RE: Slow SELECT with subquery Posted by Jim Carls @ 12/23/2010 11:59:20 PM
RE: Slow SELECT with subquery Posted by Tamar Granor @ 12/24/2010 10:38:28 PM
RE: Slow SELECT with subquery Posted by Ugur YILMAZ @ 12/24/2010 1:05:01 PM
RE: Slow SELECT with subquery Posted by Cetin Basoz @ 12/24/2010 4:16:30 PM
RE: Slow SELECT with subquery Posted by Jim Carls @ 12/24/2010 8:34:58 PM