i have here a part of my code below which work fine for me...but the only problem is that it takes time to show the result. My product profile has only 8,500 records & the tables that were search for exact match has approximately 50,000 or more records added every month. Maybe my coding is not the right way.
Can anyone teach me the right code just to shortened the time in accessing the actual output.
LOCAL EachItemPurchases, lcpurchases, lcpurchaseReturn, EachItemSold, cashsold, chargesold, salesreturn
STORE
0 TO EachItemPurchases, lcpurchases, lcpurchaseReturn, EachItemSold, cashsold, chargesold, salesreturnSELECT
products.item_code, products.unit_cost ;FROM products;
ORDER BY products.item_code INTO CURSOR mycursor
IF _TALLY>0
SCAN
* //PURCHASES
SELECT (purchase.unit_qty*mycursor.unit_cost)AS totalpurchase;
FROM purch_hd inner JOIN purchase ;
ON purch_hd.or_number=purchase.or_number ;
WHERE ALLTRIM(purchase.item_code)==ALLTRIM(mycursor.item_code) ;
INTO CURSOR mycursor1
IF _TALLY > 0
SUM mycursor1.totalpurchase TO lcpurchases
ENDIF
USE IN mycursor1
* / /PURCHASE RETURN
FROM purcreth inner JOIN purcretc ;
ON purcreth.or_number=purcretc.or_number ;
WHERE ALLTRIM(purcretc.item_code)==ALLTRIM(mycursor.item_code) ;
INTO CURSOR mycursor1
IF _TALLY > 0
SUM mycursor1.totalpurchase TO lcpurchaseReturn
ENDIF
USE IN mycursor1
* //CASH SALES
* //ACCOUNT SALES
* //SALES RETURN
EachItemPurchases=EachItemPurchases +(lcpurchases -lcpurchaseReturn)
EachItemSold = EachItemSold +((cashsold+chargesold)-salesreturn)
STORE 0 TO lcpurchases, lcpurchaseReturn,cashsold,chargesold,
salesreturn
ENDSCAN
ENDIF
USE IN mycursor
RELEASE EachItemPurchases, lcpurchases, lcpurchaseReturn, EachItemSold, cashsold, chargesold, salesreturn
Thank you
how could i shortened the time of looping in an SCAN...ENDSCAN?
andreas-fsc
thank you for your immediately reply... it works faster now
have a nice day to all
Cortexbomber
If you have 1000 distinct product codes than effectively you're doing at least maybe 4000 selects (half select, half sum) with this code. Part of code could be rewritten as:
Select Sum
(purchase.unit_qty*products.unit_cost) As purchases ;FROM purch_hd ;
inner Join purchase On purch_hd.or_number=purchase.or_number ;
inner Join products On purchase.item_code == products.item_code ;
INTO Array aPurchases
lnItemPurchases = Iif( _Tally = 0, 0, Nvl(aPurchases,0))
Select Sum(purcretc.unit_qty*products.unit_cost) purchaseReturn ;
FROM purcreth ;
inner Join purcretc On purcreth.or_number = purcretc.or_number ;
inner Join products On purcretc.item_code == products.item_code ;
INTO Array aPurchaseReturns
lnItemPurchaseReturns = Iif( _Tally = 0, 0, Nvl(aPurchaseReturns,0))
EachItemPurchases = m.lnItemPurchases - m.lnItemPurchaseReturns
Note that initial select and scan...endscan is removed. Even if this 2 selects run 100 times slower (I don't mean it'd be 100 times slower-just to signify importance of avoiding unneccessary repetition) still it'd be running 20 times faster.
AGSP