Query performs, Report not (1 Viewer)

K

Kowalski

Registered User.

Local time

Today, 04:27

Joined

Jul 5, 2007

Messages

121

• Mar 5, 2014

• #1

I'm using Access 2010 to connect to a SQL database.
I created a query that reads data from 5 subqueries (all in Access). The query performs great and return the 30 odd rows in 1 second.

But when I create a brand new report, and add my query as the RecordSource (no filters or groupings), the report takes about 20 seconds to open.

What can this be? And obviously, how can I fix it?

 

spikepl

Eledittingent Beliped

Local time

Today, 03:27

Joined

Nov 3, 2010

Messages

6,142

• Mar 5, 2014

• #2

Make another new report with some other query as a test. If that is slow too, check out the default printers for those reports.

 

K

Kowalski

Registered User.

Local time

Today, 04:27

Joined

Jul 5, 2007

Messages

121

• Mar 5, 2014

• #3

Thanks - but that's not it. All my other reports perform as expected, it's only that one. If I add it for a form, the form is also slow.
Strange.

 

N

namliam

The Mailman - AWF VIP

Local time

Today, 03:27

Joined

Aug 11, 2003

Messages

11,695

• Mar 5, 2014

• #4

My guess is that when executing as query the engine is smart enough to push all the individual part queries as one big chunk query into the SQL database.
When you stick the query into a report, it looses sight of the commonality and pulls the queries into Access first, where the final processing is done.... Which is an obvious performance hit.

Then again 20 seconds, I have seen worse performing reports by far

 

spikepl

Eledittingent Beliped

Local time

Today, 03:27

Joined

Nov 3, 2010

Messages

6,142

• Mar 5, 2014

• #5

"all my other reports" .... that was not what I asked. Did you make a new one and tested that? Printer settings (especially for printers that cannot be found or network printers) can affect the report in surprising ways.