performance issues

[Guest]

I am having great problems with database performance. It is taking 30+
seconds to open up reports, swap from design view to preview or back again. I
have tried compacting and reparing the database which has not had any effect.
I have started a new blank database and imported the reports tables and
queries in to it. The file is not huge - just over 1MB and there is very
little data in it, just a few records I have input for test purposes.
It has 5 tables and 4 linked tables. Some of the queries are quite
complicated in that they have 6 tables involved and reports can be based on
these queries.

We are running Access in a citrix environment, but I also have had access
installed on the local drive and this makes no difference.

Is there any way I can improve performance? At the moment it is unworkable
and I have had to give up on development. Could it be because of all the
tables in the underlying queries?

Any help or suggestions gratefully received.

Lynn atkinson

 

[Wayne Morgan]

The first thing to try would be to open the query that the report is based
on. If the query takes that long to open, then the report will also. If the
query is based on other queries, then open each of the other queries and see
which, if any, of them is slow. If none of them is slow, then the
consolidated query is slow. Post the SQL view of the lowest level, slow
query so that we can see what is being done.

 

[Guest]

Thanks for you reply. I have tried the queries and they run much faster than
anything to do with the reports. Even when adding fields to the report takes
a while.
here is the sql behind a particularly slow report.

SELECT progress.status, progress.[candidate ID], progress.[start date],
progress.award, employeedetails.surname, employeedetails.forename,
employeedetails.title, [post details].[post/role], [Project Codes].Project,
[Project Codes].locality, contractual.[old contract],
employeedetails.[employee ID new]
FROM (([post details] LEFT JOIN [Project Codes] ON [post details].[project
code]=[Project Codes].Code) INNER JOIN ((candidate INNER JOIN employeedetails
ON candidate.[employee ID]=employeedetails.[employee ID new]) INNER JOIN
contractual ON employeedetails.[employee ID new]=contractual.[employee ID])
ON [post details].[post ID]=contractual.[post ID]) INNER JOIN progress ON
candidate.[candidate number]=progress.[candidate ID]
WHERE (((progress.status)="active" Or (progress.status)="completed") AND
((progress.award)="care 4") AND ((contractual.[old contract])=False)) OR
(((progress.status)="active" Or (progress.status)="completed") AND
((progress.award)="management 4") AND ((contractual.[old contract])=False));

If you have any ideas, I would be grateful.

Regards

 

[Wayne Morgan]

Are you doing any Grouping or Sorting in the report itself? Are there
subreports? Are you doing a lot of calculations in the report, either in the
Control Source of controls or in the VBA code behind the form? Are you
modifying the formatting in the VBA code behind the form? Are you calling up
pictures from file paths stored in the data? If so, the pictures may take
time to display.

How many controls are on the report, if you start getting close to the
maximum, it will get sluggish, especially when editing the report.

--
Wayne Morgan
MS Access MVP

Thanks for you reply. I have tried the queries and they run much faster
than
anything to do with the reports. Even when adding fields to the report
takes
a while.
here is the sql behind a particularly slow report.

SELECT progress.status, progress.[candidate ID], progress.[start date],
progress.award, employeedetails.surname, employeedetails.forename,
employeedetails.title, [post details].[post/role], [Project
Codes].Project,
[Project Codes].locality, contractual.[old contract],
employeedetails.[employee ID new]
FROM (([post details] LEFT JOIN [Project Codes] ON [post details].[project
code]=[Project Codes].Code) INNER JOIN ((candidate INNER JOIN
employeedetails
ON candidate.[employee ID]=employeedetails.[employee ID new]) INNER JOIN
contractual ON employeedetails.[employee ID new]=contractual.[employee
ID])
ON [post details].[post ID]=contractual.[post ID]) INNER JOIN progress ON
candidate.[candidate number]=progress.[candidate ID]
WHERE (((progress.status)="active" Or (progress.status)="completed") AND
((progress.award)="care 4") AND ((contractual.[old contract])=False)) OR
(((progress.status)="active" Or (progress.status)="completed") AND
((progress.award)="management 4") AND ((contractual.[old
contract])=False));

If you have any ideas, I would be grateful.

Regards

The first thing to try would be to open the query that the report is
based
on. If the query takes that long to open, then the report will also. If
the
query is based on other queries, then open each of the other queries and
see
which, if any, of them is slow. If none of them is slow, then the
consolidated query is slow. Post the SQL view of the lowest level, slow
query so that we can see what is being done.