Report too slow

[Sandy]

I am looking for ideas to help me speed up a report in my database. Here's
the scenerio:

I have a main report based on tblEmployees, where the report opens and
filters based on the EmployeeID or CoachID where EmployeeID or CoachID
matches the ID selected on the form used to access the report (the report is
for employee performance and the coach may pull 1 employee or his/her entire
team based on what cmdbttn they select).

The main report has a sub report where the master/child fields are joined on
EmployeeID. The main report shows the Employee Name, ID, Department, and
Coach Name. The sub report shows multiple records employee job performance,
in column layout.

The sub report is based on the following:

• 6 queries that roll up to a union query (we need 6 queries to make all
fields the same names and in the same order for the union)
• The union query is then joined to a table in the rptQuery.

The report pulls fine if there is only 1 user accessing the report, however,
once you add multiple users pulling the report at the same time the report
takes anywhere from 2 to 5 minutes to pull! It is the longest when the user
is trying to pull the report by entire team rather than by 1 employee.

Any suggestions are GREATLY appreciated. I have tried several things but the
only thing that has worked to increase speed is to base the report on a table
made by the union query. However, this creates redundant data in the database
and I do not like that one bit :-(

Thanks for your help!

Skaar

 

[Duane Hookom]

Are you using "UNION ALL" in your union query? This increases the
performance of union queries many fold. Another major slow-down of reports
with subreports or other "Can Grow" controls is using [Page] of [Pages] in
the report.

 

[Sandy]

I am currently using

SELECT *
FROM [query name]

UNION SELECT *
FROM [query name]

etc;

so you say use

SELECT*
FROM [query name]

UNION ALL
FROM [query name]

etc.;

and, yes we are using page n of m - is there a better way to achieve this -
maybe through vba?

thanks again for your ideas!

Are you using "UNION ALL" in your union query? This increases the
performance of union queries many fold. Another major slow-down of reports
with subreports or other "Can Grow" controls is using [Page] of [Pages] in
the report.

--
Duane Hookom
MS Access MVP

I am looking for ideas to help me speed up a report in my database. Here's
the scenerio:

I have a main report based on tblEmployees, where the report opens and
filters based on the EmployeeID or CoachID where EmployeeID or CoachID
matches the ID selected on the form used to access the report (the report
is
for employee performance and the coach may pull 1 employee or his/her
entire
team based on what cmdbttn they select).

The main report has a sub report where the master/child fields are joined
on
EmployeeID. The main report shows the Employee Name, ID, Department, and
Coach Name. The sub report shows multiple records employee job
performance,
in column layout.

The sub report is based on the following:

. 6 queries that roll up to a union query (we need 6 queries to make all
fields the same names and in the same order for the union)
. The union query is then joined to a table in the rptQuery.

The report pulls fine if there is only 1 user accessing the report,
however,
once you add multiple users pulling the report at the same time the report
takes anywhere from 2 to 5 minutes to pull! It is the longest when the
user
is trying to pull the report by entire team rather than by 1 employee.

Any suggestions are GREATLY appreciated. I have tried several things but
the
only thing that has worked to increase speed is to base the report on a
table
made by the union query. However, this creates redundant data in the
database
and I do not like that one bit :-(

Thanks for your help!

Skaar

 

[Duane Hookom]

Yes, use UNION ALL as I have stated. I don't know of a better method of
grabbing page of pages. Consider dropping at least the Pages to see if your
report runs faster.

I am currently using

SELECT *
FROM [query name]

UNION SELECT *
FROM [query name]

etc;

so you say use

SELECT*
FROM [query name]

UNION ALL
FROM [query name]

etc.;

and, yes we are using page n of m - is there a better way to achieve
this -
maybe through vba?

thanks again for your ideas!

Are you using "UNION ALL" in your union query? This increases the
performance of union queries many fold. Another major slow-down of
reports
with subreports or other "Can Grow" controls is using [Page] of [Pages]
in
the report.

--
Duane Hookom
MS Access MVP

I am looking for ideas to help me speed up a report in my database.
Here's
the scenerio:

I have a main report based on tblEmployees, where the report opens and
filters based on the EmployeeID or CoachID where EmployeeID or CoachID
matches the ID selected on the form used to access the report (the
report
is
for employee performance and the coach may pull 1 employee or his/her
entire
team based on what cmdbttn they select).

The main report has a sub report where the master/child fields are
joined
on
EmployeeID. The main report shows the Employee Name, ID, Department,
and
Coach Name. The sub report shows multiple records employee job
performance,
in column layout.

The sub report is based on the following:

. 6 queries that roll up to a union query (we need 6 queries to make
all
fields the same names and in the same order for the union)
. The union query is then joined to a table in the rptQuery.

The report pulls fine if there is only 1 user accessing the report,
however,
once you add multiple users pulling the report at the same time the
report
takes anywhere from 2 to 5 minutes to pull! It is the longest when the
user
is trying to pull the report by entire team rather than by 1 employee.

Any suggestions are GREATLY appreciated. I have tried several things
but
the
only thing that has worked to increase speed is to base the report on a
table
made by the union query. However, this creates redundant data in the
database
and I do not like that one bit :-(

Thanks for your help!

Skaar

 

[Sandy]

Duane,

This helped the speed up the report when filtering for the individual
associate. However, my real problem is when I filter for an entire team of
associates where their coach's ID is the same. The team filter runs quickly
as long as only one person is running the report, but the more people that
run the report concurrently, the longer it takes :-( I'm testing in a
multi-user enviornment so we've been playing a lot. Do you think if we
revamped our tables so that our union query can be based solely on the tables
rather than on "sub" queries, it would affect the run time?

Are you using "UNION ALL" in your union query? This increases the
performance of union queries many fold. Another major slow-down of reports
with subreports or other "Can Grow" controls is using [Page] of [Pages] in
the report.

--
Duane Hookom
MS Access MVP

I am looking for ideas to help me speed up a report in my database. Here's
the scenerio:

I have a main report based on tblEmployees, where the report opens and
filters based on the EmployeeID or CoachID where EmployeeID or CoachID
matches the ID selected on the form used to access the report (the report
is
for employee performance and the coach may pull 1 employee or his/her
entire
team based on what cmdbttn they select).

The main report has a sub report where the master/child fields are joined
on
EmployeeID. The main report shows the Employee Name, ID, Department, and
Coach Name. The sub report shows multiple records employee job
performance,
in column layout.

The sub report is based on the following:

. 6 queries that roll up to a union query (we need 6 queries to make all
fields the same names and in the same order for the union)
. The union query is then joined to a table in the rptQuery.

The report pulls fine if there is only 1 user accessing the report,
however,
once you add multiple users pulling the report at the same time the report
takes anywhere from 2 to 5 minutes to pull! It is the longest when the
user
is trying to pull the report by entire team rather than by 1 employee.

Any suggestions are GREATLY appreciated. I have tried several things but
the
only thing that has worked to increase speed is to base the report on a
table
made by the union query. However, this creates redundant data in the
database
and I do not like that one bit :-(

Thanks for your help!

Skaar

 

[Duane Hookom]

Do you have multiple users running the same front-end or does each user have
their own copy of the front-end (much preferable)?
Are all of the fields used as joins or criteria indexed?

--
Duane Hookom
MS Access MVP

Duane,

This helped the speed up the report when filtering for the individual
associate. However, my real problem is when I filter for an entire team of
associates where their coach's ID is the same. The team filter runs
quickly
as long as only one person is running the report, but the more people that
run the report concurrently, the longer it takes :-( I'm testing in a
multi-user enviornment so we've been playing a lot. Do you think if we
revamped our tables so that our union query can be based solely on the
tables
rather than on "sub" queries, it would affect the run time?

Are you using "UNION ALL" in your union query? This increases the
performance of union queries many fold. Another major slow-down of
reports
with subreports or other "Can Grow" controls is using [Page] of [Pages]
in
the report.

--
Duane Hookom
MS Access MVP

I am looking for ideas to help me speed up a report in my database.
Here's
the scenerio:

I have a main report based on tblEmployees, where the report opens and
filters based on the EmployeeID or CoachID where EmployeeID or CoachID
matches the ID selected on the form used to access the report (the
report
is
for employee performance and the coach may pull 1 employee or his/her
entire
team based on what cmdbttn they select).

The main report has a sub report where the master/child fields are
joined
on
EmployeeID. The main report shows the Employee Name, ID, Department,
and
Coach Name. The sub report shows multiple records employee job
performance,
in column layout.

The sub report is based on the following:

. 6 queries that roll up to a union query (we need 6 queries to make
all
fields the same names and in the same order for the union)
. The union query is then joined to a table in the rptQuery.

The report pulls fine if there is only 1 user accessing the report,
however,
once you add multiple users pulling the report at the same time the
report
takes anywhere from 2 to 5 minutes to pull! It is the longest when the
user
is trying to pull the report by entire team rather than by 1 employee.

Any suggestions are GREATLY appreciated. I have tried several things
but
the
only thing that has worked to increase speed is to base the report on a
table
made by the union query. However, this creates redundant data in the
database
and I do not like that one bit :-(

Thanks for your help!

Skaar

 

[Sandy]

We have multiple users accessing one .mdb file for the front-end. We don't
have any .mde files to push out to the users desktops and we are planning to
move to an SQL server next year so we are exploring other options for the
front-end so we're "stuck" with what we have right now as far as the
front-end file. Yes, our ID fields are indexed. We have five different tables
with monthly stats for each employee depending on their department. These
tables are brought together in a Union query. We were thinking about having
one table for all employees - the only problem with this is that some fields
will be empty because the employee will not have stats to populate the field
depending on their department. Do you think that having one table would be ok
in the case that it will speed up the query behind the report?

 

[Duane Hookom]

One table might speed up the report.

You don't have to use an MDE to distribute the front-end to multiple users.
Each user can have their own copy of the MDB front-end and connect to a
common MDB back-end on a share.