>I'm trying to create a view progamatically, but it run so slow, the 1st table (quoteprx.dbf) only have 700 record and 2nd (Order.dbf) got 68 record in it, can someone check what wrong in my code ?
>
>CREATE SQL VIEW quote_editor AS ;
>SELECT DISTINCT QuotePrx.prxdate,QuotePrx.goods,;
>ALLT(TRANSFORM(QuotePrx.prx,'9,999,999.99'))+'/'+ALLT(QuotePrx.Unit);
>+'='+ALLT(TRANSFORM(QuotePrx.content,'999,999,999'))+' '+;
>ALLT(QuotePrx.ucontent) AS cPrx,;
>ALLT(TRANSFORM(QuotePrx.con,'999,999.99'));
>+' '+ALLT(QuotePrx.unit_con)+'/'+;
>IIF(QuotePrx.con_per=1,SPACE(0),;
>ALLT(TRANSFORM(QuotePrx.con_per,'999,999')))+' '+ALLT(Order.Cat) AS cCon_per,;
>PAYMENT;
>(QuotePrx.con/QuotePrx.content/QuotePrx.con_per,QuotePrx.tol/100,1) AS conperpcs,;
>IIF(QuotePrx.Ratio<1,;
>PAYMENT;
>(QuotePrx.con/QuotePrx.content/QuotePrx.con_per,QuotePrx.tol/100,1);
>*(QuotePrx.Ratio*Order.Qty),;
>CEILING;
>(PAYMENT;
>(QuotePrx.con/QuotePrx.content/QuotePrx.con_per,QuotePrx.tol/100,1)*;
>(QuotePrx.Ratio*Order.Qty))) AS totalcon, ;
>PAYMENT;
>(QuotePrx.con/QuotePrx.content/QuotePrx.con_per,QuotePrx.tol/100,1);
>*QuotePrx.prx AS amoperpcs,;
>IIF(QuotePrx.Ratio<1,PAYMENT;
>(QuotePrx.con/QuotePrx.content/QuotePrx.con_per,QuotePrx.tol/100,1)*;
>(QuotePrx.Ratio*Order.Qty),CEILING(PAYMENT;
>(QuotePrx.con/QuotePrx.content/QuotePrx.con_per,QuotePrx.tol/100,1)*;
>(QuotePrx.Ratio*Order.Qty)))*QuotePrx.prx AS totalamo, ;
>QuotePrx.prx,QuotePrx.con_per,QuotePrx.con,QuotePrx.orderno,;
>QuotePrx.unit_con,QuotePrx.tol,QuotePrx.ccode,QuotePrx.ccolor,;
>QuotePrx.cunit,QuotePrx.ratio,;
>QuotePrx.part,QuotePrx.dataid,QuotePrx.color, QuotePrx.unit,;
>QuotePrx.content, QuotePrx.ucontent, ;
>Order.vcode, Order.art, Order.style, Order.desc,;
>Order.qty, Order.sale, Order.cat AS order_cat, Order.dated,;
>QuotePrx.Ratio*Order.Qty AS QtyPerColor ;
>FROM main!QuotePrx, main!order;
>WHERE ;
>UPPER(ALLTRIM(QuotePrx.orderno))==UPPER(ALLTRIM(Order.orderno)) = .T.;
> AND !DELETED()=.T.
>
>TIA :)
Few common SQL/Rushmore considerations:
1. Any function in field list will force record-by-record propcesiing, i.e. delay query.
2. Rushmore expect to see exactly (literally) the same expressions in SQl-Where clause and in Index tag, i.e. your tables supposed to be indexed exactly on 'UPPER(ALLTRIM(....'.
3. You should have tag on DELETED(), instead of adding DELETED check in query.
Some interface consideration: in many cases it's not necessarily to make all calculations, joints inside the SELECT-SQL. They might be done faster by interface means. It depends on how you're planning to use this view.
Edward Pikman
Independent Consultant