An awful performance issue!

[eCasper]

Hi Everyone!



I'm facing an awful performance issue.



I create a READWRITE cursor (say fTmpDumCursor) over 100,000 records &
Indexed in the init event of the form.



Also I've a grid on the form and it's populated by a physical table (say
fDumTable) which contains 51000 records and optimistic row buffering is on.



When I set a filter for the above table (fDumTable), grid takes about 10
seconds to get refreshed.

But when I get rid of creating the cursor (fTmpDumCursor), the grid takes
only about 2 seconds to get refreshed.



Please consider that same expression has been used for filtering the table
and the fTmpDumCursor is nowhere else has been used though it's created.
Also this happens only when the form is loaded and the filter is applied for
as the first time only.



Can anyone give me a better explanation for what is exacting happening here?



Thanks in Advance.

[Craig Berntson]

You should take a different approach. 51000 records in a grid is too many
for the user to work with. Provide text boxes for query criteria, then do a
SQL SELECT (can be a view) to get the data set down to a manageable size.

Also see my blog post on filtering data in a grid.
http://www.craigberntson.com/blog/2008/08/dont-filter-data-in-grid.asp
--
----
Craig Berntson
Microsoft MVP

Post by eCasper
Hi Everyone!
I'm facing an awful performance issue.
I create a READWRITE cursor (say fTmpDumCursor) over 100,000 records &
Indexed in the init event of the form.
Also I've a grid on the form and it's populated by a physical table (say
fDumTable) which contains 51000 records and optimistic row buffering is on.
When I set a filter for the above table (fDumTable), grid takes about 10
seconds to get refreshed.
But when I get rid of creating the cursor (fTmpDumCursor), the grid takes
only about 2 seconds to get refreshed.
Please consider that same expression has been used for filtering the table
and the fTmpDumCursor is nowhere else has been used though it's created.
Also this happens only when the form is loaded and the filter is applied
for as the first time only.
Can anyone give me a better explanation for what is exacting happening here?
Thanks in Advance.

[eCasper]

Dear Craig Berntson,



Thank you for your respond.




But I don't think that you do understand my problem. I need to know how does
the efficiency(8 seconds difference) of the program increase when I close a
cursor (fTmpDumCursor) which contains over 100,000 records and nowhere it
had been used?

Post by eCasper
Hi Everyone!
I'm facing an awful performance issue.
I create a READWRITE cursor (say fTmpDumCursor) over 100,000 records &
Indexed in the init event of the form.
Also I've a grid on the form and it's populated by a physical table (say
fDumTable) which contains 51000 records and optimistic row buffering is on.
When I set a filter for the above table (fDumTable), grid takes about 10
seconds to get refreshed.
But when I get rid of creating the cursor (fTmpDumCursor), the grid takes
only about 2 seconds to get refreshed.
Please consider that same expression has been used for filtering the table
and the fTmpDumCursor is nowhere else has been used though it's created.
Also this happens only when the form is loaded and the filter is applied
for as the first time only.
Can anyone give me a better explanation for what is exacting happening here?
Thanks in Advance.

[tom knauf]

Hello,

I do not understand :
If you create the cursor in the init, that takes time, if you set the filter
there, the times for both (create /set filter and refresh) will be added.

Where and when do you set the filter ?
Why do you create the cursor, if you do not need it for the form (the grid)
?
Has your grid optimized set to true and do you have an index on the filter
expression ?

Another appoach
Look in the dataenvironment of the form, maybe you accidently put the Cursor
in the DE ?

Btw
51000 records in a grid seems to be a bit much.

Regards
tom

[eCasper]

Dear Tom,

Yes it does take time. But I don't have any doubt about it.
I apply the filter once the form is completly loaded(there is button on form
which loads the filter screen.). also there is no relation between setting
the fiter and the cursor.because I apply the filter for a different grid
table on the form(physical table).

I create the cursor because it's used only in another form

My problem is when I set the filter to the grid table of the form it takes
about 10 seconds when the cursor is created.

But when I get rid of creating this cursor and applying the filter takes
about 2 seconds to refresh the grid.

But I don't see any relation between this cursor and the table.

optimized = .F.

What do you mean by this "Look in the dataenvironment of the form, maybe you
accidently put the Cursor in the DE?"

Regards,

eCasper!

Post by tom knauf
Hello,
If you create the cursor in the init, that takes time, if you set the
filter there, the times for both (create /set filter and refresh) will be
added.
Where and when do you set the filter ?
Why do you create the cursor, if you do not need it for the form (the
grid) ?
Has your grid optimized set to true and do you have an index on the filter
expression ?
Another appoach
Look in the dataenvironment of the form, maybe you accidently put the
Cursor in the DE ?
Btw
51000 records in a grid seems to be a bit much.
Regards
tom

[tom knauf]

Hello,

now I think I understand.

It means IF (not when ?) the cursor was created in init(), als filteractions
AFTERWARDS take long.
If the cursor was not created, they work fast.
Maybe its a question of Memory (RAM). If the cursor ist created it will be
cached and occupies much Ram.
You can control this with the taskmanager.

Dataenvironement
Right click on form - Dataenvironment (looks like a dbc), should be empty
because you create / use the cursors by your own.

HTH
Tom

Post by eCasper
Dear Tom,
Yes it does take time. But I don't have any doubt about it.
I apply the filter once the form is completly loaded(there is button on
form which loads the filter screen.). also there is no relation between
setting the fiter and the cursor.because I apply the filter for a
different grid table on the form(physical table).
I create the cursor because it's used only in another form
My problem is when I set the filter to the grid table of the form it takes
about 10 seconds when the cursor is created.
But when I get rid of creating this cursor and applying the filter takes
about 2 seconds to refresh the grid.
But I don't see any relation between this cursor and the table.
optimized = .F.
What do you mean by this "Look in the dataenvironment of the form, maybe
you accidently put the Cursor in the DE?"
Regards,
eCasper!

Post by tom knauf
Hello,
If you create the cursor in the init, that takes time, if you set the
filter there, the times for both (create /set filter and refresh) will be
added.
Where and when do you set the filter ?
Why do you create the cursor, if you do not need it for the form (the
grid) ?
Has your grid optimized set to true and do you have an index on the
filter expression ?
Another appoach
Look in the dataenvironment of the form, maybe you accidently put the
Cursor in the DE ?
Btw
51000 records in a grid seems to be a bit much.
Regards
tom

[Villi Bernaroli]

Post by tom knauf
Hello,
now I think I understand.
It means IF (not when ?) the cursor was created in init(), als
filteractions AFTERWARDS take long.
If the cursor was not created, they work fast.
Maybe its a question of Memory (RAM). If the cursor ist created it
will be cached and occupies much Ram.
You can control this with the taskmanager.

I agree on this, probably the huge cursor is using so much RAM that the
system has to use the swap-file.

[Jürgen Wondzinski]

Hi eCasper

Post by eCasper
optimized = .F.

This one should always be set to .T. since it activates Rushmore inside the
grid when accessing filtered data.

The only time you would want to set it to .F. is similar to the use of the
"NOOPTIMIZE" keyword at certain commands: i.e you would either be directly
modifying the index field, which would then lead to a different sorting
squence, or you want to experience the slowness of CLIPPER <gd&r>.
--
wOOdy
Visual FoxPro Evangelist
Microsoft "Most Valuable Professional" 1996 to 2009



"*´¨)
¸.·´¸.·*´¨) ¸.·*¨)
(¸.·´. (¸.·` *
..·`.Visual FoxPro: It's magic !
(¸.·``··*

[Craig Berntson]

The cursor is HUGE and takes a HUGE amount of RAM. VFP could be swapping to
disk.

But I totally understood your question. However, you wanted to understand
the symptoms, not fix the real cause of the problem.
--
----
Craig Berntson
Microsoft MVP

Post by eCasper
Dear Craig Berntson,
Thank you for your respond.
But I don't think that you do understand my problem. I need to know how
does the efficiency(8 seconds difference) of the program increase when I
close a cursor (fTmpDumCursor) which contains over 100,000 records and
nowhere it had been used?

[eCasper]

Hi!

Thank you for all(tom, Craig, Villi)
Yes when I checked now, I notice that it does takes much memory when the
cursor is created.
But I've one more quesiton.
i.e. This problem exist when the filter runs as the first time after form
loads.
I wonder how it is possible.

Regards,

eCasper!

Post by eCasper
Hi Everyone!
I'm facing an awful performance issue.
I create a READWRITE cursor (say fTmpDumCursor) over 100,000 records &
Indexed in the init event of the form.
Also I've a grid on the form and it's populated by a physical table (say
fDumTable) which contains 51000 records and optimistic row buffering is on.
When I set a filter for the above table (fDumTable), grid takes about 10
seconds to get refreshed.
But when I get rid of creating the cursor (fTmpDumCursor), the grid takes
only about 2 seconds to get refreshed.
Please consider that same expression has been used for filtering the table
and the fTmpDumCursor is nowhere else has been used though it's created.
Also this happens only when the form is loaded and the filter is applied
for as the first time only.
Can anyone give me a better explanation for what is exacting happening here?
Thanks in Advance.

[Stefan Wuebbe]

Post by eCasper
Hi!
Thank you for all(tom, Craig, Villi)
Yes when I checked now, I notice that it does takes much memory when the
cursor is created.
But I've one more quesiton.
i.e. This problem exist when the filter runs as the first time after form
loads.
I wonder how it is possible.

Vfp is trying to cache data in the background, e.g. index content,
parts of tables that have already been fetched locally, in order
to optimize performance for you. So that a subsequent attempt to
read the same data again might be faster than the first.


hth
-Stefan

[eCasper]

Hi Stefan,

That's great!

Thank you very much for your reply.

Regards,

eCasper!

Post by Stefan Wuebbe

Post by eCasper
Hi!
Thank you for all(tom, Craig, Villi)
Yes when I checked now, I notice that it does takes much memory when the
cursor is created.
But I've one more quesiton.
i.e. This problem exist when the filter runs as the first time after form
loads.
I wonder how it is possible.

Vfp is trying to cache data in the background, e.g. index content,
parts of tables that have already been fetched locally, in order
to optimize performance for you. So that a subsequent attempt to
read the same data again might be faster than the first.
hth
-Stefan