Slow form opening form

[amills]

I have a form that contains client information and comments per client. Currently I am using the SET FILER TO command on the comments database to display the comments in a list box for any given client#. As the file has grown from 5000 records to 250,000 records the opening of the form takes longer and longer. I thought using an sql command to create a temp file to display the comments would solve my problem. However, I also have to be able to add comments to the file (which is done on the form) and have them show up in the listbox. Any suggestions on how to accomplish this ?


Andy

 

[craigsboyd]

Could you give us a quick idea of the structure of the tables involved...what are the names of the tables, what are the fields and their sizes. Also what is the relationship between the client information table(s) and the comments table(s)...I am assuming that there is a primary key of sorts for the client and that is then used as a foreign key over in the comments table(s)...I could just spout some stuff that may give you ideas (however a little more detail from your end would probably yield the absolute best results, with only 250,000 records time should not be an issue as it is). What kind of control(s) are you using to display the comment information?

Those questions having been asked...

As you have seen Set Filter is not always a good solution but it is Rushmore Optimizable, so with a good use of Indexes your filter should still be relatively fast (i am assuming alot here given the lack of details from you), the trick is to make sure that the expression in your index matches the expression you are using in the TO clause of the Set Filter statement.

1. using an SQL statement to generate a READWRITE cursor is an option you may wish to explore, however there again the speed of this is going to be contingent on how well your WHERE clause matches up with the indexes available on the perspective table(s). I say READWRITE cursor because you expressed concern above about being able to add the comments that the user enters so they can see them. This would negate the need to rerun your SQL statement every time a user changed something (a sort of double entry if you will for the sake of speed).

2. Another option is an Updateable view, this would most likely necessitate your changing the controlsources/recordsources/what-have-you on the form's controls to a large extent, but would give you the ability to show changes as they happen and also save the data to the original table(s).

3. Build a string variable on the fly and use it as the controlsource for the editbox or whatever you are showing the comments for the currently selected client in. This string could be created extremely fast by seeking or scanning through the table for the relevant comments based on an indexed id field, just fill the string up and your done...however this has a draw back if the comments the user enters while viewing them are added anywhere but the end or the beginning of the string...i mean if you have to put them in the middle somewhere then it may be problematic...alot depends once again on what your set up is.

Slighthaze = NULL
[sub]craig1442@mchsi.com[/sub][sup]

"Whom computers would destroy, they must first drive mad." - Anon​

[/sup]

 

[Mike Lewis]

Andy,

I can't fault the excellent advice that Slighthaze has given you. But just to emphasise one point: I would guess that this is a Rushmore optimisation issue. If you could let us know what your filter condition is, and which, if any, fields in the filter have indexes, that would help us to suggest a solution.

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk