Form slow to open

  • Thread starter Thread starter SusanV
  • Start date Start date
S

SusanV

Hi all,

I have a form which recently started taking an inordinate amount of time to
open. The source of the form is:

SELECT tblLSGCode.*, [tblCauseCodes].[CauseCode] FROM tblLSGCode INNER JOIN
tblCauseCodes ON [tblLSGCode].[LCode]=[tblCauseCodes].[LSGCode] ORDER BY
[LCode];

This same SQL put into a query opens instantly - but the form takes anywhere
from 20 seconds to 3 minutes to open. I haven't modified the table structure
or the form itself in months, and the number of records has barely increased
(perhaps by 50 or so) so I can't imagine what might be causing this sudden
slowness. Decompiling, compacting and recompiling helped some, but it's
still much slower than it used to be. Overall the database performance is
the same as it always has been, except this one form.

Any ideas on what would cause this?

TIA,

Susan
 
Try saving the SQL as a query and putting the query name in the record source
property.
Also, do you need to return all the columns in the tblLSGCode table, if not,
eliminate the .* and just include the columns you need.
Are the columns being compared in the query (LCode, LSGCode) indexed? If
not, index them.

You could also try creating a new database and importing all the objects to
it and see if this improves the speed of the form.

Dorian
 
Any subforms, listBoxes or comboBoxes on the form? They can also cause
problems as the row source for each control is executed when the form loads.
Try saving the SQL as a query and putting the query name in the record source
property.
Also, do you need to return all the columns in the tblLSGCode table, if not,
eliminate the .* and just include the columns you need.
Are the columns being compared in the query (LCode, LSGCode) indexed? If
not, index them.

You could also try creating a new database and importing all the objects to
it and see if this improves the speed of the form.

Dorian


:

Hi all,

I have a form which recently started taking an inordinate amount of time to
open. The source of the form is:

SELECT tblLSGCode.*, [tblCauseCodes].[CauseCode] FROM tblLSGCode INNER JOIN
tblCauseCodes ON [tblLSGCode].[LCode]=[tblCauseCodes].[LSGCode] ORDER BY
[LCode];

This same SQL put into a query opens instantly - but the form takes anywhere
from 20 seconds to 3 minutes to open. I haven't modified the table structure
or the form itself in months, and the number of records has barely increased
(perhaps by 50 or so) so I can't imagine what might be causing this sudden
slowness. Decompiling, compacting and recompiling helped some, but it's
still much slower than it used to be. Overall the database performance is
the same as it always has been, except this one form.

Any ideas on what would cause this?

TIA,

Susan
Click to expand...
Click to expand...
 
A couple of other things to check...
Check if there is any code attached to either the OPEN or ACTIVATE event of
the form. If there is, post the code here.
Also is the datatype of the two columns you are comparing in the query
(LCODE and LSGCODE) the same or different?

Its generally a good idea to have a form only read one record at a time from
the underlying table rather than an entire table of records as your record
source does.

Dorian
 
Back
Top