Performance in Design View

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm encountering an issue where the performance of Access slows significantly
while I'd editing the design of reports based on either cross-tab or
pass-through queries. Unless I "break" the report by renaming the record
source property, it will stop responding for up to a minute every time
*anything* changes. Change the width of a control, wait , click on a new
control, wait, click on another control, wait, wait for the cursor to change
to drag a so the section can be resized, wait... you get the idea.

It seems like Access has to constantly validate the source of the report by
constantly running the query as the design is modified. If that's the case,
is there a fix? If that not what's happening, where should I look to solve
this?

If you'd like the specifics, read on.

I'm running queries using either linked tables on a Sybase server via ODBC,
or pass-through queries directly to the same server. In many cases, the
pass-through queries are used to join and group multiple tables, and then a
regular select query will run to group data for the report. The one that
caused Access to stop responding completely today is:

Pass-through Query (Edited for brevity):

SELECT Distinct l.id, l.stat, l.idc, l.num, d.dtn, l.cd, case when /really
ugly set of conditionals to get pieces of data from strings based on the
beginning of the string/ end As Lct, o.zon, l,nbr
FROM (((idtable as l INNER JOIN doorlink as d ON ...) LEFT JOIN (pidtable as
pl INNER JOIN plink as lp ON ...) ON ...) LEFT JOIN (midtable as ml INNER
JOIN mlink as lm ON ...) ON ...) LEFT JOIN ordtable as o on ... AND ...
WHERE l.cd Not In (...) AND l.nbr=0 AND l.acd!=y and l.ack = 0

Access select query joining above with two static tables containing
descriptive text for report (Again, edited heavily):

SELECT Count(q1.id), idc, [tbld].[zdesc]
FROM q1 LEFT JOIN tbld ON ...
WHERE (((q1.lstat)=x)) OR (((nz(q1.cstat,0))=x))
GROUP BY idc, [tbld].[zdesc];

Using the pass through query to select the id's and tie them to the lct runs
considerably faster than the two separate queries I would need in Access
(because of the count(distinct x) feature that Access lacks), but I seem to
be paying for it in the report design. Any suggestions will be greatly
appreciated.

Nick
 
Consider using your report's record source query to "make" a table that can
be used as a "temporary" recordsource. When your report is designed fully,
switch the record source back to the original record source.

To speed up crosstab report design, add all potential column headings into
the column headings property.
 
be used as a "temporary" recordsource. When your report is designed
fully,
switch the record source back to the original record source.
Click to expand...

Or continue to use the temp table, and put code in the open event to
clear and re-fill the temp table from the record source.

(david)


Duane Hookom said:
Consider using your report's record source query to "make" a table that can
be used as a "temporary" recordsource. When your report is designed fully,
switch the record source back to the original record source.

To speed up crosstab report design, add all potential column headings into
the column headings property.

--
Duane Hookom
MS Access MVP


Nick said:
I'm encountering an issue where the performance of Access slows
significantly
while I'd editing the design of reports based on either cross-tab or
pass-through queries. Unless I "break" the report by renaming the record
source property, it will stop responding for up to a minute every time
*anything* changes. Change the width of a control, wait , click on a new
control, wait, click on another control, wait, wait for the cursor to
change
to drag a so the section can be resized, wait... you get the idea.

It seems like Access has to constantly validate the source of the report
by
constantly running the query as the design is modified. If that's the
case,
is there a fix? If that not what's happening, where should I look to
solve
this?

If you'd like the specifics, read on.

I'm running queries using either linked tables on a Sybase server via
ODBC,
or pass-through queries directly to the same server. In many cases, the
pass-through queries are used to join and group multiple tables, and then
a
regular select query will run to group data for the report. The one that
caused Access to stop responding completely today is:

Pass-through Query (Edited for brevity):

SELECT Distinct l.id, l.stat, l.idc, l.num, d.dtn, l.cd, case when /really
ugly set of conditionals to get pieces of data from strings based on the
beginning of the string/ end As Lct, o.zon, l,nbr
FROM (((idtable as l INNER JOIN doorlink as d ON ...) LEFT JOIN (pidtable
as
pl INNER JOIN plink as lp ON ...) ON ...) LEFT JOIN (midtable as ml INNER
JOIN mlink as lm ON ...) ON ...) LEFT JOIN ordtable as o on ... AND ...
WHERE l.cd Not In (...) AND l.nbr=0 AND l.acd!=y and l.ack = 0

Access select query joining above with two static tables containing
descriptive text for report (Again, edited heavily):

SELECT Count(q1.id), idc, [tbld].[zdesc]
FROM q1 LEFT JOIN tbld ON ...
WHERE (((q1.lstat)=x)) OR (((nz(q1.cstat,0))=x))
GROUP BY idc, [tbld].[zdesc];

Using the pass through query to select the id's and tie them to the lct
runs
considerably faster than the two separate queries I would need in Access
(because of the count(distinct x) feature that Access lacks), but I seem
to
be paying for it in the report design. Any suggestions will be greatly
appreciated.

Nick
Click to expand...
Click to expand...
 
I'd rather not do that (continue to use a temp table as the record source)
since this data will be accessed frequently, meaning multiple
deletions/populations of the table. That leads (in my experience) to a
database that grows quickly and needs regular compaction. Compact on exit
works, but causes other (user) problems.

Nick
 
My best practice has been to have code to create a temp mdb for each user.
Add tables to and link tables from the temp mdb. Push records into the temp
mdb for reporting. When you need to run reports again, delete and recreate
the temp mdb. This entire process takes only a few seconds. My users have
never had a clue what was going on and had only noticed a huge increase in
performance.
 
Back
Top