Help needed on performance

[nirajj]

May sound stupid but can somebody tell me how would this code work out ? Is it really a good idea to implement something like this in VFP7 ?

Select WorkArea1 && Approx 20000 rows
SCAN
Select WorkArea2 && Approx 20000 rows
SET FILTER cExp && Approx 3-5 rows after filter
SCAN
Select WorkArea3 &&30-50 rows
SET FILTER cExp && 1-4 rows after filter
SCAN
ENDSCAN
ENDSCAN
ENDSCAN

Does this really look good ? Or will it result in a bad performance ?

Any idea guys ???


Thanks !!

 

[Mike Gagnon]

nirajjobanputra

Using SET FILTER TO will definately affect the performance. Consider use SQL statements instead.
Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[rgbean]

Does cExp ever change somewhere in this "loop"? If not, why not set the filters once outside? Is the filtering done on an index key expression?

What are you trying to end up with? Are you updating any of these records or just using them for reporting?

As Mike suggests, a good SQL query may be much quicker if it can use Rushmore Optimization. You might even find parameterized local views more efficient.

Rick

 

[nirajj]

Yes Rick, cExp does change everytime in the loop. Basically all the workarea are cursors. Its a few set of queries used to create them.

And no, filter isnt on index key. Infact cExp is on 3-4 fields at times.

I am not updating any of those values. Based on certain validations I am inserting values into some other cursor inside the loop. Need not necessarily be the inside most loop tho.


Basically I am trying to create a report. However I have to get the data from all those 3 cursors and arrange them in horizontal order. Since the end result cursor will also be used as a interface file.

Thanks !!

 

[wgcs]

Well, each time the inner, filtered SCAN's run, it will process Every record in the alias, since VFP has to evaluate each record to see if it matches the SET FILTER command.

So, even though you Know that only 3 or 4 records will match, all 20,000 records are still processed.

Rushmore doesn't require the entire filter expression to match an index key... it only needs the components of the expression to match Keys... it could take several Index Keys to optimize a single filter. Creating indexes that can be used by Rushmore may make the whole thing go significantly faster (since only the indexes would have to be checked for each record, instead of the actual data in the files).

Check out the SET RELATION and SET SKIP commands... using them you can hand-build an index-optimized reporting loop.

 

[nirajj]

Thank you all so much for the quick replies.

I have decided to take out the SET FILTER for sure.

Was wondering how does this look ?

Select WorkArea
*!* SET FILTER cExp
SCAN for cExp && cExp that I would use for FILTER
ENDSCAN

I was reading thru the help files and looks like Rushmore does optimize SCAN.

 

[Mike Gagnon]

nirajjobanputra

I was reading thru the help files and looks like Rushmore does optimize SCAN.

Yes it does optimizes providing your cExp is an indexed field..
Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[nirajj]

Looks like I have a better solution now. Credit goes to you guys.

Actually the cursor was created with sqlpassthru and I wasnt sure what the index key will be. However I found that I can set the key using CURSORSETPROP() function.

After the key is set I use the SCAN cExp with the index key.

I hope this will give me much better performance compared to the initial code.