UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Reports Run Slow If Not Sysadmin, Access 2010    
 
   
SimeonD
post May 3 2016, 08:27 AM
Post#1



Posts: 3
Joined: 3-May 16



Hi
I'm hoping someone here has solved a similar issue. The backend is SQL Server 2008R2.
Recently moved from Access 2003 to Access 2010. Since moving to Access 2010, reports within the ADP are running very slow unless the user is a member of the Sysadmin role.
The SQL backend has not changed. I kept two PCs on Access 2003, and the reports still runs quickly there.
This slowness issue occurs with both runtime 2010 and the full version of Access 2010. The reports takes 18 seconds, used take 3 or 4 seconds.

This project needs to stay an ADP for now. I don't want to add all my users to the Sysadmin role, as that would be bad security.
I've seen several mentions of this issue when I search online, but no solutions.

I'm at my wits end trying to figure this out. If you have a solution, please let me know!
Thanks
Simeon
Go to the top of the page
 
GroverParkGeorge
post May 3 2016, 10:37 AM
Post#2


UA Admin
Posts: 31,191
Joined: 20-June 02
From: Newcastle, WA


Welcome to UtterAccess.

I've not heard of that issue before, but then ADPs were a different kind of tool, one that I never used.

How about creating a new Database Role for your users, with specific permissions for the users who use your ADP? I wonder if that might help with the performance issue.
Go to the top of the page
 
SimeonD
post May 4 2016, 04:38 AM
Post#3



Posts: 3
Joined: 3-May 16



Hi
I have created a role for the users, but unfortunately that has made no difference. Even added db_owner to that role, but still the speed issue.
Although I would have been surprised if it had worked - after all there is no problem from Access 2003. So the SQL Security is not an issue there.
Thanks
Simeon
Go to the top of the page
 
GroverParkGeorge
post May 4 2016, 03:42 PM
Post#4


UA Admin
Posts: 31,191
Joined: 20-June 02
From: Newcastle, WA


"... reports within the ADP are running very slow unless the user is a member of the Sysadmin role. "

Hence my suggestion to try a different database role to see if it could be related to that. It's probably not a SQL Security issue, but, well, troubleshooting involves trying to identify--and exclude--possible problems.

Too be clear, then, the speed problem is related to Access 2010, but differs depending on whether a user is in the Sysadmin role.

Have you tried to run SQL Profiler on this database? If so, can you identify anything anomalous about the performance that would account for speed problems?
Go to the top of the page
 
SimeonD
post May 5 2016, 11:16 AM
Post#5



Posts: 3
Joined: 3-May 16



Thanks George.
Actually, with regards to the Role, I should list when I did do. I've inherited this database, the users were set up under the db_owner role.
When the Access 2010 issue occurred, I tried changing the users to having the db_datareader and db_datawriter roles.
I also tried creating a new role, db_AccessUsers, and moving the users to that instead. But none of these made a speed difference, unfortunately.
Maybe there is another way I didn't think of, more than happy to try that.

I've tried starting SQL Profiler just as the report starts. Nothing stood out to me. But I must admit to having limited knowledge of Profiler, and I'm not sure of what I'm looking for.
Is there a way to add a start and end point to the profiler? Rather than running for a period of time, can I know for definite this is the start of the report generation, and the next point is the end? Then I could run it for an Access 2003 user and compare.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    10th December 2017 - 09:27 PM