I may go back to this tonight. I was working through logmeIn yesterday at night after 1am..., perhaps will do it again.



>>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
>>

>
>post sys(3054,12) and RECCOUNT() info