access report performance

[mrdagz]

Hi i have an issue with an MDB and an ADP that links to a SQl DB.

I have migrated an access application so that the data is now stored in a MS SQL DB, over all the application runs much better but i have a set of reports that are in a separate MDB that run slower than the once connedted to an Access DB.

I have tried converting the MDB to a ADP but this did not seem to imporve the performance in fact it made it worse!

the reports are linked to views in the ADP or a linked table in the mdb these seem to open ok but the report seems to take longer to render than in the access DB version.

Can any help?

 

[dhookom]

Consider using pass-through queries as the Record Sources of your reports.

Duane
Hook'D on Access
MS Access MVP

 

[mrdagz]

hi, i have tried a pass through but it is not quicker.
opening a report with about 200K lines it it on hte linked tabel version takes 2 Mins, Pass Through is about the same.
ADP takes more than 5 minuets.

Open is the linked table / Query that feeds to report takes 7 to 10 seconds so it seems to be the report that is slow.

the report does have some formating.

 

[dhookom]

Do you have subreports? Is there a control with [Page] or [Pages]? If so, what happens if you remove them?

Is there code running in your report?

Do you have sections with "Keep together"?

Duane
Hook'D on Access
MS Access MVP

 

[mrdagz]

hi and thanks for the reply.

I have spent all morning play around with a report that I know is the slowest to try to identify the bottleneck.

There was a footer that has a lot of Sums on I have removed this and linked it to a sub report that get SQL server to do the calculations for it, this has taken a chunk of time off it. about 10 seconds.

I also found that by removing some VBA code from behind the report it took a further 15 seconds.

so i have the report down from 1:45 to around 1:25 which is helping.

these reports are run for each customer in the system which is around 5000.

 

[dhookom]

A "lot of Sums" shouldn't normally take a lot of time unless they are DSum()s.

I asked 3-4 questions that you didn't answer. Did you not check them out or did you check them and not bother to answer?

Duane
Hook'D on Access
MS Access MVP

 

[mrdagz]

sorry, there is a sub report, but this was to replace the Sums in the footer and has speed up to report.

there is also page in the footer, removing this does speed it up a lot but we need page numbering on the report.

no sections which keep together.

I have removed all of the formating the done this in T-sql which has improved the speed by 30% but still seems to take a while to render the report.

 

[dhookom]

[Page] doesn't slow down a report as much as [Pages].

Are you applying a criteria in the p-t or are you pulling a lot of records in the p-t and then filter them in Access?

Quite often Sum()s within the report will not take more time to run than subreports since subreports require a new record source while Sum() works against the existing records already retrieved.

Duane
Hook'D on Access
MS Access MVP