> Hello Every Experts,
>
> This time for me "SPEED DOES MATTER". I have folldowing Qry which works fine
> at development time considering apprx 2000 transaction. Taking less then 3 secenods.
>
> While at Client site with apprx 80000 transaction it consumes about 30 minutes.
>
> what could be done to more optimize.
>
>
** sreg.prg: For Sales Register
> ** Data base : \DATA9\AC_BILLS.DBC
> ** Cbj_srl: Doc_Date,Doc_no
> ** Cbj : Amt,Ac_code,Vat_code
> ** Items : item_code,Qnty,Rates,Adjst_amt
>
> ** Memory Variables **
> store Date() to F_DATE,t_date
>
> F_date=ctod('01.04.2008')
> t_date=date()
>
> *CLOSE ALL
> **OPEN DATABASE \retail\data9\ac_bills.dbc
>
> STORE TIME() TO start_time
>
> SELECT cbj_srl.doc_Date,;
> cbj_srl.doc_no,;
> cbj_srl.vch_type,;
> cbj_srl.link2cbj,;
> cbj.ac_code,;
> items.item_code,;
> items.Qnty,;
> items.pty_code,;
> items.rates,;
> items.adjst_amt,;
> allt(item_mst.item_name)+"~~"+Item_mst.Makes as Iname,;
> allt(led_mst.led_name) as pty_name;
> FROM cbj_srl, Items, item_mst, led_mst ,cbj ;
> WHERE Cbj_srl.vch_type ='SALES' AND ;
> cbj_srl.link2cbj = items.link2cbj AND ;
> cbj_srl.link2cbj = cbj.link2cbj AND ;
> items.item_code = item_mst.item_codes AND ;
> items.pty_code = led_mst.led_code AND ;
> BETWEEN(cbj_srl.doc_date, m.f_date, m.t_date);
> Order by cbj_srl.doc_date, cbj_srl.doc_no ;
> INTO TABLE Sreg
>
> STORE TIME() TO End_time
>
> SELECT Sreg
> messagebox(RECCOUNT())
> ** eof() PRG
>
>
> Thanx in Advance
>
>
>
>
> *Exchange of $1 Create only $1, But Exchage of one Ideas Makes different two Ideas*
Hi Binod,
You must have indexes on the columns in the WHERE clase for VFP to be able to optimize query performance. See SYS(3054) in VFP's Help.
SELECT Cbj_srl
INDEX ON vch_type TAG vch_type
SELECT cbj_srl
INDEX ON doc_date TAG doc_date
All keyfields used for joining table should be indexed.
SET DELETED ON/OFF can make a difference.
VFP decides in what order to open and join the tables as it builds the intermediate sets of data,
but it can be worthwhile to arrange the joins so that you get small dataset quickly.
Suppose the join between cbj and cbj_slr cuts the final output down by 70 procent, then it would likely be advantageous to perform that join very early. Place it at the top.
You could modernize your code.
If you created that query in the Query Designer of VFP9 you would have code like this:
SELECT cbj_srl.doc_Date,;
cbj_srl.doc_no,;
cbj_srl.vch_type,;
cbj_srl.link2cbj,;
cbj.ac_code,;
items.item_code,;
items.Qnty,;
items.pty_code,;
items.rates,;
items.adjst_amt,;
allt(item_mst.item_name)+"~~"+Item_mst.Makes as Iname,;
allt(led_mst.led_name) as pty_name;
FROM cbj_srl ;
JOIN Items ON cbj_srl.link2cbj = items.link2cbj
JOIN item_mst ON items.item_code = item_mst.item_codes
JOIN led_mst ON items.pty_code = led_mst.led_code
JOIN cbj ON cbj_srl.link2cbj = cbj.link2cbj
WHERE Cbj_srl.vch_type ='SALES'
AND cbj_srl.doc_date BETWEEN m.f_date AND m.t_date;
Order by cbj_srl.doc_date, cbj_srl.doc_no ;
INTO TABLE Sreg