grid, filter, long delays!

[bs6600]

Why do I get long delays when I step outside a filtered set of records in a grid and is there anything I can do about it?.

I'm showing a subset of a table in a grid. Each subset is quite large (>10,000 records). It takes a few seconds to create and show the grid and to switch to a different subset.

But if I scroll or seek outside the subset it takes a very long time to re-establish the display.

Thanks in advance!

 

[bborissov]

You answered of your question
FILTER and BIG nomber of records are not a good combination. Why not use parametrised View or CursorAdapter as Grid source then just change the WHERE parameter and REQUERY() the Grid source.
Also if you didn't have apropriate indexes you can't have Rushmore optimization.

Borislav Borissov

 

[bs6600]

thank you bborisov, I was afraid that was the answer!

I have 'set filter to fieldx = yyy' and each index is 'index on fieldx + ...' so I hoped that Rushmore would be working.

My code has to fit in with some one else so I'm not sure about using views. I will have to study that a bit more

I tried creating a cursor with each subset but it took too long to initialise each cursor.

 

[Mike Lewis]

BS6600,

In fact, SET FILTER is Rushmore-optmisable, so you shouldn't be seeing these delays. Just make sure that the index expression exactly matches the SET FILTER expression.

Also, make sure you do not have an active index in force. If you do SET ORDER TO <any index> before you set the filter, this will be extrememly slow. Removing the controlling index will actually speed it up considerably.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[Mike Lewis]

Mike,

The grid on the other hand has never worked properly with a SET FILTER.

That's true. But my reading of BS6600's question is that it wasn't the grid that was causing the problem -- it was what was happening outside the grid. But I might read it wrong.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[bs6600]

Thanks everyone.

To elaborate a little. My file has 250,000 records divided between 25 members. I want to show the records for one member but I need to show them in some kind of order - date, reference etc - hence index on member+date, member+reference etc then set filter to member=???

Since starting this thread, I have found that the delays occur when I use the scroll bar or if my find option locates a record very near the end such as to show blank lines at the foot of the grid. Page down and seeks which result in a full grid are nearly instantaneous.

I might try to restrict my seeks to 'last record for this member' minus 'height of grid in rows' and tinker with the scrolling.

Forgive me - none of this was a problem with Clipper

 

[bs6600]

A question to help me understand Rushmore.

If I were to

set filter to member+date> xxx+space(8);
and member+date< xxx+"20991231"

would the results be any better?

BTW my name is Bill Spence.

 

[MarciaAkins]

bs6600:

You did not say which version of VFP you are using, but if you are using VFP 9, a filtered RecordSource in a grid is Rushmore optimizable. You need to set the grid's Optimize property to .T. (It is set to .F. by default for backward compatibility)

Marcia G. Akins

http://www.TightlineComputers.com

 

[bs6600]

Mike,

I changed all my 'set filter to ...' statements to invoke a method as follows

local cfilter
cfilter="set filter to "+key()+" = thisform.bs2_sn_account"
set exact off
&cfilter

This gives much faster initial presentation of the grid but still has the problem of falling off the end with scroll bar.

I do want to write back so I think I'll avoid creating any kind of extracted cursor; I'll spend the time I save explaining the limits to the user!

Thanks again for you help.



Bill Spence,
Dunfermline, Scotland

 

[Mike Lewis]

Bill,

Can we come back my point about having an active index and a filter on the same table. If I've read you post correctly, that's what you've got.

As I mentioned earlier, creating a filter on a table with an index in force will cause the initial record navigation to be extremely slow. Instead of jumping straight to the first record in the filtered subset, VFP will go first to the first record in index order, and then step through all the records until it finds the first one that meets the filter.

You can verify that by removing the controlling index. You should see an immediate improvement. By the way, this applies whether the index expression is the same as the filter expression, or is something different (as in your case).

I appreciate you want to show each member's record in a certain sequence. That suggests that you should be using a SELECT to extract the records to a cursor, then binding the cursor to the view. I would guess that would be the fastest solution, provided the SELECT is itself fully optimisable.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

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