May 21, 2012 at 9:13 pm
I searched this topic online and I found a wide range of answers. However, although I changed many thing on the access front end still having issues.
My system used to be an Access front end and an Access backend and since we have reached 60 users and over 110,000 records on a few tables, it was taking up to two minutes to open a form with a simple select query.
It boils down to this: When i was in development stage with a new SQL backend I was able to bring the performance to amazing performance using "views" on the SQL server.
But, once I started having more then one user opening each their own frontend at the same time, it shows POOOOOOR performance. Monitoring the server showed that for those 20 seconds, the Servers CPU was running at 100%.
I must say, that the first form being loaded is running a local query for the results matching the Employee ID (the person logging in) with a subform linked by EID running a table linked to a view.
This poor performance is happening with only two users, I cant imagine what will happen once I have all 60 users start working.
Note: someone mentioned to my that their might be a issue with table locking.
Anyone care to comment?
Thanks in advance,
/\/\
May 23, 2012 at 4:32 am
If it's only a select query you're running initially, try putting :-
set transaction isolation level read uncommitted
as the first line of your query. I've noticed it helps to alleviate table locking.
May 23, 2012 at 7:51 am
richardmgreen1 (5/23/2012)
If it's only a select query you're running initially, try putting :-set transaction isolation level read uncommitted
as the first line of your query. I've noticed it helps to alleviate table locking.
Hmm... So you are okay with dirty data? If you were making business decisions based on data, do you want it fast and inaccurate or slow and accurate? If I only had the 2 choices, I can guarantee the answer would be slow and accurate. There is a 3rd option. Fast and accurate. However, we have not been given enough information to give a solution for that.
How do you have Access communicating with the SQL Server?
Jared
CE - Microsoft
May 23, 2012 at 12:21 pm
richardmgreen1 (5/23/2012)
If it's only a select query you're running initially, try putting :-set transaction isolation level read uncommitted
as the first line of your query. I've noticed it helps to alleviate table locking.
This is certainly one approach. Not one I would recommend as a first approach.
The important questions are: Is your database tuned for the queries you are using? Got indexes, partitions etc? Are you returning only the data you need or blasting the complete table back with a SELECT *?
So many things can lead to poor performance, schema locking is a result of that and masking it with the above advice just puts off the inevitable.
May 24, 2012 at 12:29 am
I never said it was a good idea, I said it was one option to see if it improved things.
May 29, 2012 at 10:11 am
Thank you guys for your help.
@sqlknowitall - Jared: The access is connected to the SQL server via an ODBC connection.
@DiverKas: All tables are indexed. I don't know about partitions (Ill try that) and i will mix the queries not to call SELECT *.
Thanks again and Ill keep you posted.
/\/\
May 30, 2012 at 11:13 am
Typical Access databases display the entire set of data in a table and let you scroll through it, search it, etc. When the table is in SQL Server that could very well mean that the entire table is downloaded to Access on the client. This can result in table scans and very possibly locks.
It might be helpful to use SQL Server Profiler to see what SQL is actually being sent to the server and the effects of that. I'm not very knowledgeable of profiler so I'll let some of the "experts" here help you with that.
Also you can use the Activity monitor to see what is going on, especially locks sometimes.
May 30, 2012 at 12:31 pm
I don't know if this will work on any MS Access past version 2003, but try changing your MS Access front end into an .adp instead of an actually database. Then putting it on a terminal server for multiple people to have access.
It may be a headache dealing with the code at first, but if it's done correctly, you won't have the perpetual migraines of your current performance issues.
May 30, 2012 at 4:47 pm
Way back when I used to program a lot of Access front end applications I found that using any kind of "bound" form or report would perform poorly due to locking and how Access needed to tightly control the records used by the forms etc.
What worked incredibly well was using unbound forms and VBA. Code placed in the 'on open' property of the form would create a recordset from the database and populate the empty text boxes with values from the recordset. Since it is cached the recordset is very quick and doesn't hold any locks in the database.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply