Tore,

I think you didn't look into my code close enough. I do open all of them before running select statements with appropriate indexes according to our previous discussion and Hugo's suggestion. Still joining 3 tables is extremely slow and all my attempts of breaking down this select statement into pieces were fruitless.

Perhaps I have to use procedural code instead with slight optimizations in the original procedure...

>Have you tried to USE all the involved tables before the select? Unless the IDXs are already in use, there is no way for VFP to know about their existemce. Something like

>Use styles in 0 index Styleidx1,Styleidx2,Styleidx3..
>Use or_items in 0 index Or_idx1,idx2,Or_idx3..
>Use curstyles in 0 index cur_idx1,cur_idx2,cur_idx3..
>Use orders in 0 index ordersidx1,ordersidx2,ordersidx3..
>..
>Select ...

>
>
>>Hi everybody,
>>
>>I'm using VFP8 SP1. I have the following tables: Styles with IDX Styles with index on Style field, Orders with PON field and IDX on it and Or_Items with Style and PON field with indexes on Style and PON + Style (IDX).
>>
>>My task is to find styles with 0 or negative in stock, where in_stock is a field in Styles table, but we may have special kind of Orders where PON = "HOUSE", which means these styles are going to be brought in.
>>
>>Here is my current code with various attempts to speed the process, but all unsuccessful so far:
>>
>>[One select statement takes ~230 sec., 4 select statements with index creation take 145 sec., which is still too slow].
>>
>>

>>lSelectWay = AskNo("Do you want to use SQL-Select solution?") &&.T. && Set to true if you prefer select solution
>>
>>IF m.lSelectWay
>>	ltStartTime = DATETIME()
>>	LOCAL lcWhere, lcOldANSI
>>	lcOldANSI = SET("Ansi")
>>	SET ANSI OFF
>>
>>	lcWhere = [Styles.Style NOT IN ("F","S","X","D","M", "CD", "PK") ] + ;
>>		[and NOT Styles.Style LIKE "%.%"] && [Styles.In_Stock <=1 ]
>>
>>	DO CASE
>>	CASE m.cact = "A" && Active
>>		lcWhere = m.lcWhere + [ and Styles.DisContinu = "A"]
>>
>>	CASE m.cact = "I" && Inactive
>>		lcWhere = m.lcWhere + [ and Styles.DisContinu = "I"]
>>
>>	CASE m.cact = "B" && Both
>>		lcWhere = m.lcWhere + [ and Styles.DisContinu IN ("A","I")]
>>	ENDCASE
>>
>>	USE styles INDEX styles IN 0 && Style
>>	USE or_items INDEX or_style, or_items IN 0 && Style, PON + Style
>>	USE orders INDEX orders IN 0 && PON
>>
>>	LOCAL loThermo, lcClass
>>	lcClass = "cusSQLThermo"
>>	loThermo = CREATEOBJECT(m.lcClass) && we need visual indication for long select
>>	
>>	SELECT styles.STYLE as Style, styles.RESERVED AS nReserved, styles.in_stock AS nInStock ;
>>	FROM Styles WHERE &lcWhere AND Reserved >= In_Stock INTO CURSOR curStyles readwrite
>>*	INDEX ON Style TAG cStyle
>>*!*		
>>*!*		SELECT PON, Style as cStyle, qty, shiped, can_qty FROM Or_Items ;
>>*!*		WHERE Style in (select cStyle FROM curStyles) INTO CURSOR curOr_Items readwrite
>>*!*		INDEX ON PON TAG PON
>>*!*		INDEX ON cStyle TAG cStyle
>>*!*		
>>*!*		SELECT PON, Acc, Approved, App_Date FROM Orders ;
>>*!*		WHERE PON in (select PON FROM curOr_Items) ;
>>*!*		INTO CURSOR curOrders readwrite
>>*!*		INDEX ON PON TAG PON
>>*!*		
>>*!*		SELECT curStyles.cStyle, curStyles.nReserved, curStyles.nInStock, ;
>>*!*		SUM(IIF(curOrders.acc = "HOUSE" AND (NOT curOrders.Approved == "" OR NOT curOrders.app_date = {}), ;
>>*!*			curOr_items.qty - curor_items.shiped - curor_items.can_qty, 000000000)) AS nHouseOrder ;
>>*!*		FROM curStyles INNER JOIN curOr_Items ON curStyles.cStyle = curOr_Items.cStyle ;
>>*!*		INNER JOIN curOrders ON curOr_Items.PON = curOrders.PON ;
>>*!*		GROUP BY 1,2,3 HAVING (nHouseOrder + nInStock) - nReserved <=0 ;
>>*!*			INTO CURSOR dummy READWRITE
>>*!*		** Close unused cursors
>>*!*		USE IN curOr_Items
>>*!*		USE IN curStyles
>>*!*		USE IN curOrders		
>>*!*		
>>	SELECT or_Items.Style, sum(or_items.qty-or_items.shiped-or_items.can_qty) as nHouseOrder ;
>>	FROM Or_Items WHERE Or_Items.Style IN (select Style FROM curStyles) ;
>>	AND Or_Items.PON + Or_Items.Style IN ;
>>	(select PON FROM Orders WHERE orders.acc = "HOUSE" AND (NOT approved == "" OR NOT app_date = {})) ;
>>	GROUP BY 1 INTO CURSOR curOr_Items
>>	
>>	SELECT curStyles.Style FROM curStyles LEFT JOIN curOr_Items ON ;
>>	curStyles.Style = curOr_Items.Style ;
>>	WHERE (nReserved - nStock) >= NVL(nHouseOrder,0) INTO CURSOR Dummy ;
>>	ORDER BY 1
>>	
>>*!*		SELECT or_items.Style, SUM(IIF(orders.acc = "HOUSE" AND (NOT approved == "" OR NOT app_date = {}), ;
>>*!*			or_items.qty-or_items.shiped-or_items.can_qty, 000000000)) AS nHouseOrder ;
>>*!*			FROM orders INNER JOIN Or_Items ON Orders.PON = Or_Items.PON ;
>>*!*			WHERE Style in (select Style FROM curStyles) ;
>>*!*			GROUP BY 1 INTO CURSOR curTemp READWRITE
>>*!*			
>>*!*		INDEX ON Style TAG cStyle
>>*!*		
>>*!*		SELECT curStyles.Style FROM curStyles ;
>>*!*		INNER JOIN curTemp ON curStyles.Style = curTemp.Style ;
>>*!*		WHERE (nHouseOrder + nInStock) - nReserved <=0 INTO CURSOR Dummy
>>	RELEASE loThermo
>>	USE IN curStyles
>>	USE IN curOr_Items
>>	SELECT dummy
>>