nofilter clause, a matter of taste? 1

[Koen Piller]

Hi,

There exists a NOFILTER in the SQL Select command. Just learned how handy this NOFILTER can be in case you want to reuse the created cursor. My question anything wrong to use this command as default when creating a cursor. Does it affect performance or do cursors created with NOFILTER behave basicly different than cursors created without other than that you are able to reuse?

Thanks for replying,

Jockey(2)

 

[Mike Lewis]

Jockey,

It most certainly does affect performance.

As you have probably found in your researches, VFP will in certain circumstrances refrain from creating a "real" cursor, but will instead open a new instance of the table with a filter in force. This is considerably faster than copying all the records to a cursor.

By adding NOFILTER, you override that behaviour. This will be slower, but is sometimes necessary, especially when you want to input the cursor into another SELECT (which I think was the case with the problem you posted in another thread).

So, the answer is ... don't add NOFILTER unless you have a specific reason for doing so.

However, in most cases, the SELECT create a normal cursor anyway, and the setting is irrelevant. This will be the case if the cursor has a different schema from the base table (including calculated fields or different field names) or if the filter can't be Rushmore-optimised. In these cases, NOFILTER has no effect, so it won't matter whether you include it or not.

Hope this helps.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Koen Piller]

Mike,

very clear thanks.

Best regards,

Jockey(2)

 

[Mike Lewis]

Mike,

Include NOFILTER all the time and never get stung by a cursor that is simply a filter of a table.

True, but the point of the question was that this could have a (very) detrimental effect on performance.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Mike Lewis]

Mike,

I see what you mean, and I don't disagree with you.

However, in your first example, the fact that the second SELECT is based on the DBF() of the cursor means that the results of the first SELECT are immaterial. Therefore, wouldn't the presence or absence of NOFILTER in the first SELECT also be immaterial?

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Olaf Doschke]

Hi all,

I came across the issue of using NOFILTER in quite a different way: I copied data (today I would of course do it much easier) by selecting into a cursor and then append that to the table of another database.

While doing this I closed the source DBC and opened the destinations DBC. This worked okay for quite a while, one day the CLOSE DATABASE also closed my cursor, as it was a filtered table of that database of course, and tables of the database are closed together with it. I didn't have the hint you get in an error message, if you use the filtered cursor in a further SQL select, so that was a tough one.

Since then I go with what myearwood says and use NOFILTER or even READWRITE, which also makes the result cursor writeable.

And to round up the behaviour of filter cursors: You can of course Scan..Endscan on them, as if they were real cursors, so whenever you query a cursor to scan through it only, you can benefit of *not* using the NOFILTER or READWRITE clause.

Bye, Olaf.

 

[Mike Lewis]

Well, I can only say I'm still not convinced.

For me, the performance gains outweigh any possible benefits of consistency. After all, if you've got two different ways of doing something, doesn't it make sense to choose the more efficient option, rather than always choosing the same option regardless of the circumstances?

As for the risks of unexpected behaviour (and I've been the victim of that myself re NOFILTER more than once), surely it's our job as programmers to understand the issues and to react accordingly.

If you took the argument one stage further, you would never use DBCs, because there's always the risk of them getting out of sync with the table structures; you would never use a grid, because of the problems caused when the record source is closed; you would never use views because you risk getting the "base table has changed" error. There are many more examples I could give.

But I won't try to convince you. I'm happy to go my way on this question, and leave it to others to go theirs.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk