Reports running slow for other users (1 Viewer)

Awesomo · Feb 9, 2016

Hi
Last week I moved our users from runtime Access 2003 to runtime 2010.
I created a new ADP in Office 2010, then imported in all the objects from the 2003 database. Then created a new ADE using Office 2010.
All users are logging on to terminal Server 2008. (or Remote Desktop Service as its now called)

The system is used to generate two reports for clients. Both reports ran at normal speed for me.
But users then phoned me to say the reports were running slow. I wrote down the client codes the users gave me, and generated the reports myself. All fine. Logged on to the same server as the user, reports ran in normal speed for me. Which wasn't of much use to the people phoning me...
So I created a table which stores the server name, client code, and the time taken for the DoCmd.OpenReport to complete.
Now I've checked the table, I can see I run the report in 3 seconds but its taking users between 12 and 14 seconds. I run the report for the same clients that I see in the table, on the same server,so the time should be the same.

I can't figure out why the report would take longer to generate for other users. I'm not sure what step to take next.
Any advice?

Thanks
Diarmuid

The_Doc_Man · Feb 9, 2016

The first question is whether, when you get fast response, you are also coming in through the RDS during your test or you are locally connected to that system.

Awesomo · Feb 10, 2016

That is a good question!
I'm connecting via RDS, same as the other users. In fact, I got a user to logon at my PC, and the results were the same - it was still as slow for that user.

The_Doc_Man · Feb 10, 2016

Fast for you logged in via RDS but slow for another user when logged in via RDS from the same terminal? Wow, that is bizarre - but it points more to the database app than to the RDS or network connections.

Does the database make ANY ATTEMPT (other than just outright asking for an ID) to determine who the user is? And is there anything in the database that tries to be sensitive to that user's role within the database? Is there such a thing as an Area of Responsibility (AOR) in which some users are responsible for more data than other users, and if so, can you correlate speed vs. size of the AOR?

When you log in through RDS, are you and your users in different local (i.e. server-based) or global (i.e. domain-based) groups?

In essence, this detective story is like a "Find the differences" picture puzzle, except that it is in three dimensions (or more) and requires detailed comparisons along each step of the way.

Awesomo · Feb 11, 2016

The is no AOR, all users can view all clients.
To the best of my knowledge, there were no security changes to the underlying tables when we moved from Office 2003 to Office 2010. Unless the 2010 version makes a change somehow? The RDS settings weren't changed either.
The one difference I do see is that I would have a different default printer.

Are there general steps to improve ADP performance? Like turn off spell check or some other setting? Maybe that would help.

Minty · Feb 11, 2016

Are you connected to a SQL backend ?
If you are, check which version of the SQL client each RDS login is using, I've noticed significant speed difference between the original SQL ODBC client and the Native Client ver 10.0 or 11.0

Awesomo · Feb 11, 2016

Its a MS SQL 2008 backend. How would I check the SQL client? Its runtime access, so I can't go into connection properties.
If the version can be displayed using VBA, let me know the code and I could have a form display it.
Thanks!

Minty · Feb 11, 2016

Open the 32-bit ODBC connection properties (assuming you haven't got 64 bit office) they are in Windows\syswow64 rather oddly.

The top one highlighted is the original old one - 10.0 is a later one but I believe there may even be a 13 version knocking around. Google which is preferred for your version of SQL Server.

kevlray · Feb 11, 2016

SQL Server is for SQL 2005 (backwards compatible)
SQL Server Native Client 10.0 if for SQL 2008 (and R2)

I do not know about 13 version, but it would not surprise me since there is SQL 2016 available. FYI: From what I have seen from SQL Server Native Client 11, it automatically assumes you have read/write access (option in the ODBC setup). Normally this is probably not an issue. But we have a 'special' user for reporting purposes that only has read access to the databases, if we forget to change this option, the ODBC will fail for this 'special' user.

Awesomo · Feb 12, 2016

The OS is Windows Server 2008, so the exe I system32\odbcad32.exe
I've attached the screenshot. There is only one SQL Server driver, its version 6.00.6002. The SQL backend is MS SQL Server 2008 R2.

So lets say I download the SQL Server Native Client 10.0 ( it seems to be under Microsoft SQL Server 2008 R2 SP1 Feature Pack on the MS site)

How do I change the ADP Data Link Properties to use the new ODBC driver instead?

Minty · Feb 12, 2016

You would remove the old version - then it would have to use th new one.

Awesomo · Feb 12, 2016

Tested this on one server, so that SQL Server Native Client 10.0 is the only SQL driver.
Unfortunately it didn't solve the speed problem for other users. Likely a good idea to have the latest driver anyway, so I think it was still worth while.

But now I need other suggestions on what to try next. Hmm.

Awesomo · Feb 18, 2016

This seems to be a problem with Access 2010.
I kept a PC with Office 2003. The report runs for users on that PC within 4 seconds.
But if I log the same user onto a PC or Remote Desktop session with Office 2010, the report will take around 15 seconds. This applies even if using the original 2003 adp or the 2010 converted version.

I don't want to move back to runtime 2003, and I think runtime 2013 will not work with ADPs.
Any one got a suggestion on where I can go from here?

Minty · Feb 18, 2016

Have you tried a decompile re-compile - I can't see it making any huge difference, but maybe worth a go. Have you looked at the underlying queries for the report to see if there is anything that maybe a bit inefficient?

Does the report have embedded images or sub reports?

We use a mixture of 2010 and 2013 run-times with 2010 accdb's without any obvious performance issues...

Awesomo · Feb 18, 2016

I'll try the decompile option, had forgotten about that.
The report does have around 20 sub reports, with the company logo as an image on the first page.

Minty · Feb 18, 2016

I suspect the Sub reports are what will be killing it, especially if its a sub ¬ sub ¬ sub type of layout.
I have a three level equipment tree style layout for one of my reports and if there is a lot of kit on the build it takes eons (minutes) to run. I think it literally creates each sub report individually on the fly. I keep meaning to revisit the way its pulled together to try and force the layout in the query and do away with the sub reports, but other projects are always more important...

It used to be an embedded Crystal report but 2010 doesn't support the old crystal objects so I recreated it in Access.

Awesomo · Feb 19, 2016

Thanks for you advice on this so far.
There are sub ¬ sub ¬ sub reports.
There is still the kicker though - why would the report still run quickly for me?
After all, the sub report issue would still exist, and I'm using the same PC/server.

Is it possible that I set some SQL property or value that would cause this issue?

How about the "The Extended Properties" in SQL. If they were set from Access 2010, could they be tied to my profile somehow?
Or could their be properties that Access 2003 didn't use, but do have an effect in Access2010?

Awesomo · Feb 24, 2016

Interesting post I came across today:
http://www.pcreview.co.uk/threads/a...s-cached-on-first-view-or-table-open.3832564/

In that case, the ADP ran slow for the non Admin users. The poster reckoned it was because of Subdatasheet Name set to [None].

The solution posted is:
We RESOLVED this problem for ourselves. For PUBLIC or NON -
SYSTEM_ADMINISTATOR users, we PROHIBITed the SELECT permission on the SYSFERENCES table. This does not appear to have created any additional issues at this time.

Assuming he means SYSREFERENCES, which I think stopped being used after SQL Server 2000. Any idea what the equivalent would be in SQL Server 2008?

Awesomo · Mar 22, 2016

Further update on this. While posting a different issue on these forums, I found how to solve it.
In short, the report will run quickly if a user has sysadmin permissions.
See http://www.access-programmers.co.uk/forums/showthread.php?t=285326 for the other issue that was linked.

Reports running slow for other users (1 Viewer)

Registered User.

Immoderate Moderator

Registered User.

Immoderate Moderator

Registered User.

AWF VIP

Registered User.

AWF VIP

Attachments

Registered User.

Registered User.

Attachments

AWF VIP

Registered User.

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

Registered User.

Registered User.

Similar threads

Users who are viewing this thread