Form Loads Too Slow with Set Filter 
Author Message
 Form Loads Too Slow with Set Filter

I can't understand why my form is loading so slow.  I have definitely narrowed
it down to the SET FILTER command because when I comment that out the form
loads really fast.

Here's the details:  The form can take as long as 7 seconds to load but it's
instantaneous without the set filter.  The table has approximately 7000 records
and it's only 2 megs and the cdx is only 250K.  No memo fields.

However, the table is tied to a grid.  The table is also indexed where I could
(and may have to) use Select SQL.  But, the table is not ever going to get that
big anyways and I thought I might be able to get away with a filter.  Here's
the surrouding code in the init method causing the slowness:

set order to whoto
set filter to tasks.whoto = staff.initials .and. caseno > 0

I seem to remember reading a post a while back about doing a GOTO TOP right
before issuing a set filter.  However, I'm not sure if it's the Set Filter that
is causing the slowness or the fact that the filtered table is tied to the
grid.   and it might be the refreshing of the grid causing the slowdown.

All help appreciated.

Mike



Mon, 23 Jun 2003 20:07:33 GMT  
 Form Loads Too Slow with Set Filter
Since the advent of parameterized views, I hardly ever use filtered
tables. Particularly when the filtered table is the recordsource for a grid
(the grid's vertical scrollbar doesn't behave intuitively). So it's a while
since I've used Set Filter.

However... Set Filter is Rushmore optimizable. If it isn't then indeed
the Set Filter command can be very slow. Assuming your tasks table
has indexes on whoto and caseno (essential for full optimization),
then try ...

Select tasks
Set Order to whoto
lcStaffInits=Staff.initials
Set Filter to whoto = lcStaffInits and caseno > 0
LOCATE && move record pointer to first record meeting filter criteria

Any better?

Where you place this code may also have a bearing on how fast
your form loads. If your form uses the already active default
datasession, then the code might best be placed in the form's load event.
If it uses a private datasession, then you might be better off placing
it in the Data Environment's Init event.

Like I said, I'm no "Set Filter" devotee. Just some thoughts.

HTH

Roger


Quote:
> I can't understand why my form is loading so slow.  I have definitely narrowed
> it down to the SET FILTER command because when I comment that out the form
> loads really fast.

> Here's the details:  The form can take as long as 7 seconds to load but it's
> instantaneous without the set filter.  The table has approximately 7000
records
> and it's only 2 megs and the cdx is only 250K.  No memo fields.

> However, the table is tied to a grid.  The table is also indexed where I could
> (and may have to) use Select SQL.  But, the table is not ever going to get
that
> big anyways and I thought I might be able to get away with a filter.  Here's
> the surrouding code in the init method causing the slowness:

> set order to whoto
> set filter to tasks.whoto = staff.initials .and. caseno > 0

> I seem to remember reading a post a while back about doing a GOTO TOP right
> before issuing a set filter.  However, I'm not sure if it's the Set Filter
that
> is causing the slowness or the fact that the filtered table is tied to the
> grid.   and it might be the refreshing of the grid causing the slowdown.

> All help appreciated.

> Mike



Mon, 23 Jun 2003 22:28:23 GMT  
 Form Loads Too Slow with Set Filter
Roger,

thanks for your response and you are right that the grids scrollbar does not
behave intuitively and I'm seriously considering a view.  But now that
introduces a whole new set of problems and I'm wondering how you handle them.

Here's the problem.  Okay, suppose I use a view to fill the grid on the form.
Now, there are buttons on the form to Add, Delete and Edit.  Obviously the view
needs to be refreshed every time they either Add, Delete or Edit.  Well, I have
to say refreshing a grid has always been a nightmare although I have not tried
it with a cursor.

Have you or what's your strategy for handling this situation?

Thanks.

Mike



Tue, 24 Jun 2003 15:41:10 GMT  
 Form Loads Too Slow with Set Filter
Hi Mike

I use the scenario you describe all the time.
Form uses private datasession.
The grid is used for display/navigation purposes only and the form has three
buttons (Add, Edit and Delete) for dealing with the record level The grid
uses an updateable paramaterized view with optimistic table buffering.
The Add and Edit buttons fire a modal form in which to create a new record
or edit an existing one.

Let's look at the click event for the Add button:

**Begin  Add button click event **
LOCAL lnCurrRec,lgCancelled,lcAlias,lcForm
lcAlias="myVieWName"
lnCurrRec=IIF(EOF(lcAlias) OR BOF(lcAlias),0,RECNO(lcAlias))
APPEND BLANK IN (lcAlias)
GO Bottom in (lcAlias)
DO FORM editForm WITH "NEW" TO lgCancelled
IF lgCancelled
 Tablerevert(.F.,lcAlias)
 IF !EMPTY(lnCurrRec)
  GO (lnCurrRec) IN (lcAlias)
 ELSE
  GO TOP IN (lcAlias)
 ENDIF
ENDIF
ThisForm.Lockscreen=.T.
ThisForm.Grd1.Setfocus
This.Setfocus
ThisForm.RefreshButtons
ThisForm.Lockscreen=.F.
**End Add button click event **

EditForm is passed tow parameters: "NEW", to tell the form
that it's dealing with a new record, and lgCancelled which EditForm
can use to signal that the new record has been cancelled by the user.
(This is passed back in EditForm's Unload event - if the user presses
the Cancel button).
The grid refresh is dead easy - it's handled between the LockScreen .T.
and .F. It simulates clicking on the grid, which is all that's required.
Finally there's a form method (RefreshButton) which is called to enable
or disable the Edit and Delete buttons

The code for the Edit button is exactly the same except for the Do Form
line which is : DO FORM editForm WITH "OLD" TO lgCancelled.

The Delete button just deletes the current record (naturally).

Now that's OK so far, but at this stage no update of the base table has been
made. So the Form has two more buttons - Save and Cancel. (Well actually
it doesn't, since I handle this via the menu and the form's QueryUnload
event - but for the sake of simplicity, let's use the button concept).
In the Save button's click event, all you need to do is create the primary keys
for any added records then issue a Tableupdate on the view.
The Cancel button could just do a ThisForm.Release - nothing gets
committed.

Hope this gives you a rough outline of how to go with this.
The easiest bit is refreshing the grid!!!

Let me know if you want any more detail.

Best.

Roger


Quote:
> Roger,

> thanks for your response and you are right that the grids scrollbar does not
> behave intuitively and I'm seriously considering a view.  But now that
> introduces a whole new set of problems and I'm wondering how you handle them.

> Here's the problem.  Okay, suppose I use a view to fill the grid on the form.
> Now, there are buttons on the form to Add, Delete and Edit.  Obviously the
view
> needs to be refreshed every time they either Add, Delete or Edit.  Well, I
have
> to say refreshing a grid has always been a nightmare although I have not tried
> it with a cursor.

> Have you or what's your strategy for handling this situation?

> Thanks.

> Mike



Tue, 24 Jun 2003 22:31:00 GMT  
 Form Loads Too Slow with Set Filter
Hi Roger,

thanks for your reply.  I got my form working beautifully and it's blindingly
fast.  I just saw your reply and I have a few comments and I want to explain
how I did mine.

First, I must be wrong about this, but from your code it appears to be adding a
record to the view (the Select SQL) since you have named the local variable
lcAlias = "myViewName."  I must be mistaken.  I don't even know if it's
possible to add a record to an SQL cursor.  If it is then it would seem to me
like everything is backwards.

What I am doing is this...I create a cursor and display it in the grid.  Then
if the user clicks the Edit button, I search the main table for the primary
key.  I then allow the user to edit the record.  Then I have to refresh the
grid again by doing another Select SQL statement.

In fact, I have to constantly create another cursor every time they want to
add, delete or edit.  But it's pretty quick.  I gain far more than I lose when
using Select SQL instead of the filter.

Okay, so here's what's lost.  When using the filter, the user could edit a
record and it was simply a matter of displaying the grid again and everything
was in it's proper place.  Since I now need to run the Select SQL statement
again, I have to go through some hoops to try and display the same record.
That's the easy part.  The hard part (which I really haven't figured out yet)
is how to display the record in the same position in the grid (with the
DoScroll event).  Was it the fourth record down in the grid, the fifth, the
sixth?  Just kind of a nuisance.

Regards,

Quote:
>Subject: Re: Form Loads Too Slow with Set Filter

>Date: 1/5/2001 6:31 AM Pacific Standard Time

>Hi Mike

>I use the scenario you describe all the time.
>Form uses private datasession.
>The grid is used for display/navigation purposes only and the form has three
>buttons (Add, Edit and Delete) for dealing with the record level The grid
>uses an updateable paramaterized view with optimistic table buffering.
>The Add and Edit buttons fire a modal form in which to create a new record
>or edit an existing one.

>Let's look at the click event for the Add button:

>**Begin  Add button click event **
>LOCAL lnCurrRec,lgCancelled,lcAlias,lcForm
>lcAlias="myVieWName"
>lnCurrRec=IIF(EOF(lcAlias) OR BOF(lcAlias),0,RECNO(lcAlias))
>APPEND BLANK IN (lcAlias)
>GO Bottom in (lcAlias)
>DO FORM editForm WITH "NEW" TO lgCancelled
>IF lgCancelled
> Tablerevert(.F.,lcAlias)
> IF !EMPTY(lnCurrRec)
>  GO (lnCurrRec) IN (lcAlias)
> ELSE
>  GO TOP IN (lcAlias)
> ENDIF
>ENDIF
>ThisForm.Lockscreen=.T.
>ThisForm.Grd1.Setfocus
>This.Setfocus
>ThisForm.RefreshButtons
>ThisForm.Lockscreen=.F.
>**End Add button click event **

>EditForm is passed tow parameters: "NEW", to tell the form
>that it's dealing with a new record, and lgCancelled which EditForm
>can use to signal that the new record has been cancelled by the user.
>(This is passed back in EditForm's Unload event - if the user presses
>the Cancel button).
>The grid refresh is dead easy - it's handled between the LockScreen .T.
>and .F. It simulates clicking on the grid, which is all that's required.
>Finally there's a form method (RefreshButton) which is called to enable
>or disable the Edit and Delete buttons

>The code for the Edit button is exactly the same except for the Do Form
>line which is : DO FORM editForm WITH "OLD" TO lgCancelled.

>The Delete button just deletes the current record (naturally).

>Now that's OK so far, but at this stage no update of the base table has been
>made. So the Form has two more buttons - Save and Cancel. (Well actually
>it doesn't, since I handle this via the menu and the form's QueryUnload
>event - but for the sake of simplicity, let's use the button concept).
>In the Save button's click event, all you need to do is create the primary
>keys
>for any added records then issue a Tableupdate on the view.
>The Cancel button could just do a ThisForm.Release - nothing gets
>committed.

>Hope this gives you a rough outline of how to go with this.
>The easiest bit is refreshing the grid!!!

>Let me know if you want any more detail.

>Best.

>Roger

Mike


Thu, 26 Jun 2003 16:32:31 GMT  
 Form Loads Too Slow with Set Filter
I believe Roger was referring to adding a record to a view not a cursor. I
think it would make you form easier to manage if you use a view since you
don't have to recreate it every time you add or edit. And it's create like a
cursor but you need to store it in a database.
From VFP Help:

view
A customized virtual table definition that can be local, remote, or
parameterized. Views reference one or more tables, or other views. They can
be updated, and they can reference remote tables.


Quote:
> Hi Roger,

> thanks for your reply.  I got my form working beautifully and it's
blindingly
> fast.  I just saw your reply and I have a few comments and I want to
explain
> how I did mine.

> First, I must be wrong about this, but from your code it appears to be
adding a
> record to the view (the Select SQL) since you have named the local
variable
> lcAlias = "myViewName."  I must be mistaken.  I don't even know if it's
> possible to add a record to an SQL cursor.  If it is then it would seem to
me
> like everything is backwards.

> What I am doing is this...I create a cursor and display it in the grid.
Then
> if the user clicks the Edit button, I search the main table for the
primary
> key.  I then allow the user to edit the record.  Then I have to refresh
the
> grid again by doing another Select SQL statement.

> In fact, I have to constantly create another cursor every time they want
to
> add, delete or edit.  But it's pretty quick.  I gain far more than I lose
when
> using Select SQL instead of the filter.

> Okay, so here's what's lost.  When using the filter, the user could edit a
> record and it was simply a matter of displaying the grid again and
everything
> was in it's proper place.  Since I now need to run the Select SQL
statement
> again, I have to go through some hoops to try and display the same record.
> That's the easy part.  The hard part (which I really haven't figured out
yet)
> is how to display the record in the same position in the grid (with the
> DoScroll event).  Was it the fourth record down in the grid, the fifth,
the
> sixth?  Just kind of a nuisance.

> Regards,

> >Subject: Re: Form Loads Too Slow with Set Filter

> >Date: 1/5/2001 6:31 AM Pacific Standard Time

> >Hi Mike

> >I use the scenario you describe all the time.
> >Form uses private datasession.
> >The grid is used for display/navigation purposes only and the form has
three
> >buttons (Add, Edit and Delete) for dealing with the record level The grid
> >uses an updateable paramaterized view with optimistic table buffering.
> >The Add and Edit buttons fire a modal form in which to create a new
record
> >or edit an existing one.

> >Let's look at the click event for the Add button:

> >**Begin  Add button click event **
> >LOCAL lnCurrRec,lgCancelled,lcAlias,lcForm
> >lcAlias="myVieWName"
> >lnCurrRec=IIF(EOF(lcAlias) OR BOF(lcAlias),0,RECNO(lcAlias))
> >APPEND BLANK IN (lcAlias)
> >GO Bottom in (lcAlias)
> >DO FORM editForm WITH "NEW" TO lgCancelled
> >IF lgCancelled
> > Tablerevert(.F.,lcAlias)
> > IF !EMPTY(lnCurrRec)
> >  GO (lnCurrRec) IN (lcAlias)
> > ELSE
> >  GO TOP IN (lcAlias)
> > ENDIF
> >ENDIF
> >ThisForm.Lockscreen=.T.
> >ThisForm.Grd1.Setfocus
> >This.Setfocus
> >ThisForm.RefreshButtons
> >ThisForm.Lockscreen=.F.
> >**End Add button click event **

> >EditForm is passed tow parameters: "NEW", to tell the form
> >that it's dealing with a new record, and lgCancelled which EditForm
> >can use to signal that the new record has been cancelled by the user.
> >(This is passed back in EditForm's Unload event - if the user presses
> >the Cancel button).
> >The grid refresh is dead easy - it's handled between the LockScreen .T.
> >and .F. It simulates clicking on the grid, which is all that's required.
> >Finally there's a form method (RefreshButton) which is called to enable
> >or disable the Edit and Delete buttons

> >The code for the Edit button is exactly the same except for the Do Form
> >line which is : DO FORM editForm WITH "OLD" TO lgCancelled.

> >The Delete button just deletes the current record (naturally).

> >Now that's OK so far, but at this stage no update of the base table has
been
> >made. So the Form has two more buttons - Save and Cancel. (Well actually
> >it doesn't, since I handle this via the menu and the form's QueryUnload
> >event - but for the sake of simplicity, let's use the button concept).
> >In the Save button's click event, all you need to do is create the
primary
> >keys
> >for any added records then issue a Tableupdate on the view.
> >The Cancel button could just do a ThisForm.Release - nothing gets
> >committed.

> >Hope this gives you a rough outline of how to go with this.
> >The easiest bit is refreshing the grid!!!

> >Let me know if you want any more detail.

> >Best.

> >Roger

> Mike



Thu, 26 Jun 2003 19:08:19 GMT  
 Form Loads Too Slow with Set Filter
Hi Mike (Appell)

Mike Gagnon was correct. My form uses an updatable view.
You can do the same things wth views as you can with tables (except
pack and zap). But (depending on the buffermode) you can choose
when to commit changes to the base table(s). With optimistic table
buffering you could allow the user to make many changes (appends,
deletes and edits) and then commit all the changes or bail out at the user's
discretion. With optimistic row buffering on the view, you can commit or not
on a "per record" basis.

Don't forget, you can create views based on free tables (if that's an issue).

Your hassles with using a cursor (having to do a lookup in the base table
and trying to figure out which record to highlight in the grid after editing)
are
precisely what makes views so useful.  If you haven't used views before,
I strongly recommend you spend some time reading the help and playing
around with their capabilties - not in the app your currently involved in
but in a test environment. The time spent (maybe a day or two) will pay
great dividends.

Give a shout if you want more info.

Regards

Roger


Quote:
> Hi Roger,

> thanks for your reply.  I got my form working beautifully and it's blindingly
> fast.  I just saw your reply and I have a few comments and I want to explain
> how I did mine.

> First, I must be wrong about this, but from your code it appears to be adding
a
> record to the view (the Select SQL) since you have named the local variable
> lcAlias = "myViewName."  I must be mistaken.  I don't even know if it's
> possible to add a record to an SQL cursor.  If it is then it would seem to me
> like everything is backwards.

> What I am doing is this...I create a cursor and display it in the grid.  Then
> if the user clicks the Edit button, I search the main table for the primary
> key.  I then allow the user to edit the record.  Then I have to refresh the
> grid again by doing another Select SQL statement.

> In fact, I have to constantly create another cursor every time they want to
> add, delete or edit.  But it's pretty quick.  I gain far more than I lose when
> using Select SQL instead of the filter.

> Okay, so here's what's lost.  When using the filter, the user could edit a
> record and it was simply a matter of displaying the grid again and everything
> was in it's proper place.  Since I now need to run the Select SQL statement
> again, I have to go through some hoops to try and display the same record.
> That's the easy part.  The hard part (which I really haven't figured out yet)
> is how to display the record in the same position in the grid (with the
> DoScroll event).  Was it the fourth record down in the grid, the fifth, the
> sixth?  Just kind of a nuisance.

> Regards,

[snipped rest]


Thu, 26 Jun 2003 23:08:26 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. Setting filters is slow?

2. grids - set filter to slow

3. Help With Slow Loading Form

4. Slow loading form in 6.0

5. Help With Slow-Loading Form

6. Slow Loading Forms

7. Multiple pages causing slow loading of forms

8. using set filter and dataenvironment cursor filter

9. Setting cursor to a specific text box in a form after loading

10. setting form's datasession to private cause slow query data

11. SET KEY TO more efficient than SET FILTER TO

12. SET RELATION and SET FILTER

 

 
Powered by phpBB® Forum Software