Optimizing function for quick output

[trick66]

I have the below function that takes quite long to run on
a report. Is there a way for me to optimized it to perform
the query quicker. I appreciate any help anyone can give me.
Thanks.

Rick

PARAMETER HID,STW,STL
LOCAL F,RECTRANS
F=ALIAS()
RECTRANS=''
SET OPTIMIZE ON
SELECT DATE_STAMP,PO,LINE_NO,QUANTITY,PRICE,E_ID FROM;
   INVTRANS WHERE TYPE='1' AND ID=HID AND STOCK_W=STW AND;
   STOCK_L=STL AND TRANS_TYPE=2 AND DATE_STAMP>DATE()-365;
   ORDER BY DATE_STAMP INTO CURSOR IT
SELECT IT
SCAN
STOCK_L=STL AND TRANS_TYPE=2 AND DATE_STAMP>DATE()-365
RECTRANS=RECTRANS+ALLTRIM(STR(MONTH(DATE_STAMP)))+'/';
+ALLTRIM(STR(DAY(DATE_STAMP)))+'/'+ALLTRIM(STR(YEAR(DATE_STAMP)));
+SPACE(2)+PO+SPACE(2)+LINE_NO+SPACE(2)+STR(QUANTITY,11,3)+SPACE(2);
+STR(PRICE,12,3)+SPACE(2)+E_ID+CHR(13)	
ENDSCAN
USE IN IT
SELECT (F)
RETURN RECTRANS

 

[trick66]

Sorry, I forgot to mention that this is for VFP7.
Rick

 

[craigsboyd]

trick66,

A few things... first put Date()-365 into a variable...

ldYearAgo = Date() - 365

you also might consider revising to (depending on what you are trying to do)...

ldYearAgo = GOMONTH(date(), -12)

...then for further optimization you should consider putting indexes on some of the fields in INVTRANS that you are using in the where clause of your select statement...

Type, ID, Stock_W, Stock_L, Trans_Type, and/or Date_Stamp

...for sure I would put one on Date_Stamp and as for the others it depends on what the data set looks like for those fields.

OK, another thing... what does the following do?
STOCK_L=STL AND TRANS_TYPE=2 AND DATE_STAMP>DATE()-365

...just so you know, this will evaluate (STL AND TRANS_TYPE=2 AND DATE_STAMP>DATE()-365) and place either a .T. or .F. into Stock_L and since your result set is made up of only records that will meet this expression the value of Stock_L is always going to be .T. I think this was perhaps the FOR portion of the SCAN...ENDSCAN and it just got thrown into the code above?

Also, if the purpose is to just scan through the table INVTRANS and concatenate the RECTRANS variable then you might consider foregoing the SQL Select altogether and just scan through the table for the records that meet your conditions.

http://www.sweetpotatosoftware.com

 

[Mike Lewis]

Rick,

The most important thing you can do is to ensure that there is an index on each of the fields in the WHERE clause, and also the one in the ORDER clause. If you haven't already done so, read the part of the Help file that describes Rushmore optimisation. It will help you determine the right index expressions to use.

Like Craig, I am mystified by the code within the SCAN / ENDSCAN construct. It doesn't appear to do anything.

I'd also suggest that you try to determine which part of the function is slowing it down. Do some timings on the SELECT, and then on the SCAN / ENDSCAN. That will tell you where you should be concentrating your efforts.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[Olaf Doschke]

Hi Rick,

Craig and Mike already have told you the main points. You may consider using the coverage profiler to analize what portion of the code is slow. If it's many records in the region of thousands, then even just concatenating strings into a variable RECTRANS might be even slower than writing each record to disc with STRTOFILE() additve and afterwards reading in RECTRANS with FILETOSTR().

And an expression like this:

ALLTRIM(STR(MONTH(DATE_STAMP)))+'/';
+ALLTRIM(STR(DAY(DATE_STAMP)))+'/'+ALLTRIM(STR(YEAR(DATE_STAMP)))

can surely be done with a single TRANSFORM().

Bye, Olaf.