Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Jim Carls
  Where is Jim Carls?
 Nashville
 Tennessee - United States
 Jim Carls
 To: Cetin Basoz
  Where is Cetin Basoz?
 Izmir
 Turkey
 Cetin Basoz
 Tags
Subject: RE: Slow SELECT with subquery
Thread ID: 288745 Message ID: 288865 # Views: 23 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Friday, December 24, 2010 8:34:58 PM         
   


Thanks! Your reply gave me a lot to think about. As it happens, I've gotten the response time down to only a second or two (from 25 seconds) by changing this line:


FROM FFEZ!Orders JOIN FFEZ!Vendors ON Vendors.vndr_key = Orders.vndr_key ;


to this:

FROM FFEZ!Orders JOIN FFEZ!Vendors ON Vendors.vndr_key = Orders.vndr_key ;
and (&?gcSpecialFilter) 


My primary concern was to speed up the response on the two most common filters that a user would need on a daily basis when working with the purchase orders list ("open" orders and "project" orders). Just to clarify the SQL structure you were seeing, the joining of other tables is there because this particular view is the basis for a grid that is used for both navigation and for showing query results on the screen for review or passing to a report. So the JOINS provide the ability for a user to create a query that references any related table, and what I was trying to accomplish was to have the subquery only return the record set that needed to be joined to the rest of the view structure. Even if I break the view into two or more pieces, the SUM results have to be complete before the grid can be displayed, so it all has to be done either within the view or within a requery method. Nevertheless, your other points suggest that I would benefit from reviewing other aspects of how I am creating and using this view. Thanks for your time!

JC

> Since all fields are not there I am not sure if there were a need to include all those joins. With the partial field names looks like most of them are not needed. I didn't understand the purpose of joins in the subquery. Wouldn't it be just EXISTS subqueries instead of joins? Also I find it rather susceptible when a query has an aggregation operator like Sum() and yet contains joins. Maybe you should first do the query and sum() later or the other way around? That subquery may be something like:
>
>
SELECT sumSet.po_key, sumSet.proj_key, ... ;
> from (;
> SELECT Orders.po_key, ;
> 		Orders.proj_key, ;
> ItemSplits.Qty_Ship, ItemSplits.Split_QTY, Items.ORDERCOST, ;
> ItemSplits.Bill_Var, ItemSplits.Qty_Var,Items.ORDERSELL ;
> FROM FFEZ!Orders ;
>  LEFT JOIN FFEZ!Items ON Orders.po_key = Items.po_key ;
>  LEFT JOIN FFEZ!ItemSplits ON Items.PO_ITM_KEY = ItemSplits.PO_ITM_KEY ;
> where ;
> exists (select 1 from FFEZ!Vendors where Vendors.vndr_key = Orders.vndr_key) and ;
> exists (select 1 from FFEZ!Clients where Orders.clnt_key = Clients.clnt_key) and ;
> ItemSplits.Split_ID = 0 and ;
> (&?gcActiveFilter) AND &?GCGROUPFILTER ;
> ) sumSet ;
> 	GROUP BY sumSet.po_key, sumSet.proj_key

>
> You are using Empty(ItemSplits.Split_ID) which wouldn't be optimized. Instead use something like:
>
>
ItemSplits.Split_ID == ''
> ItemSplits.Split_ID = 0
> 

> depending on type. OTOH I didn't understand the purpose of that filtering:
>
>
* ...
>  LEFT JOIN FFEZ!ItemSplits ON Items.PO_ITM_KEY = ItemSplits.PO_ITM_KEY ;
> *...
>  WHERE ... EMPTY(ItemSplits.Split_ID) ;
> 

>
> You accept nulls but filter out empty ones??? Maybe it is the way you need it but sounded like a dangerous filter to me.
>
> Since it is unlikely that you would use such a view for updating, do you ever need to use a view? If you do not then you could use multiple successive SQLs which might be much more optimized (divide and conquer) instead of trying to get all the data, create a big cursor and then apply the really slow 'distinct' operator.
>
> There is a chance that with multiple SQLs this might be very quick. I would say do not completely trust into Rushmore optimization either. Sometimes a query showing full optimization is in fact not optimized at all (rather put the data on network and keep an eye on network traffic, try different SQLs).
>
> Also if this is important enough, you could create a matching model on SQL server express, run the query and check execution plans. The plans may not match how VFP executes but gives idea at least on optimizing.
>
> Cetin Basoz
>
> .Net has got better.Think about moving - check my blog:
> Blog (main)
> Blog (mirror)

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