 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / September 2007Access performance under stress
I built a Database application that used Access as its DB backend for a library. Initially, the application was meant to support only routine tasks of the library staff, and was not available to the public. However, it has been so successful that the library has decided to allow people to use it for searching the library books. Suddenly, there are more than 8 clients accessing the server which I am told can rise further. Now with the file-serving nature of Access, is it possible for it to support so many clients? The application broadly has data access queries, & updation transactions, where a tranasaction on an average has 3-4 updation/insertion queries. How much transaction throughput should I expect, where the library roughly has 30000 books in its main table. Moreover, I have a reservation. With the file-serving nature of Access, is it always possible for it to ensure ACID properties of transactions with multiple clients updating the DB simultaneously. I mean, what if it has served the DB file to 2 clients, which update the same record. Obviously, only one updation has to get through. How would it resolve the outcome? > with > multiple clients updating the DB simultaneously. I mean, what if it has > served the DB file to 2 clients, which update the same record. Obviously, > only one updation has to get through. How would it resolve the outcome? Access uses several methods of locking. Pessimistic, which is the default, locks the page that the record is on. Optimistic, which locks the record after the update, from further edits, until the first user finishes that record, i.e. 2 users can edit the same record and the first one that commits wins, with the other receiving a notice. All records in a table can also be locked, useful for major table updates.  SignatureArvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com > Pessimistic, which is the default Er, isn't optimistic the default? SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ Can I control which locking to use? In my case, optimistic locking seems to be the better alternative, as normally two staff members would not be updating the same book simultaneously. I believe optimistic locking delivers higher performance, so can I direct access to use optimistic locking?? Also, there are no major table updates in daily tasks, so if turning support for them would enhance performance, I would like to turn-them off also, if possible. > Can I control which locking to use? In my case, optimistic locking > seems to be the better alternative, as normally two staff members [quoted text clipped - 5 lines] > turning support for them would enhance performance, I would like > to turn-them off also, if possible. It's a property of your data editing forms and defaults to optimistic, so you need do nothing if you haven't changed that setting. SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ From Access Help: LockEdits Property The setting or return value is a Boolean that indicates the type of locking, as specified in the following table. Value Description True Default. Pessimistic locking is in effect. The 2K page containing the record you're editing is locked as soon as you call the Edit method. False Optimistic locking is in effect for editing. The 2K page containing the record is not locked until the Update method is executed. SignatureArvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com >> Pessimistic, which is the default > > Er, isn't optimistic the default? Can you indicate how can I fetch or set the value of LockEdits property from a .NET 2.0 application using OLDB data provider classes?? Moreover, I am getting confused. As David advised me, I should use clear terminology. So, this locking is performed by Access, or the underlying JET DB engine? Obviously, as I connect to the JET engine, all my queries actually refer to JET engine tweaking when I refer to Access. I am sorry for any confusion that might have caused. > Moreover, I am getting confused. As David advised me, I should use > clear terminology. So, this locking is performed by Access, or the > underlying JET DB engine? DAO is the native interface for Jet. It is completely independent of Access. This page gives DAO to ADO equivalents: http://doc.ddart.net/mssql/sql2000/html/mdacxml/htm/wpmigratingappend ixa.htm and indicates that LockType is the corresponding ADO property. ADO's property is structured differently, with read-only as the default. adLockOptimistic has a value of 3, so that should get you optimistic locking in your ADO recordsets. It is set before you open the recordset: ... rs.LockType = adLockBatchOptimistic rs.Open ... > Obviously, as I connect to the JET engine, all my queries actually > refer to JET engine tweaking when I refer to Access. I am sorry > for any confusion that might have caused. It's OK -- Microsoft encourages this by doing it themselves. SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ > From Access Help: > [quoted text clipped - 11 lines] > containing the record is not locked until the Update method is > executed. I was talking about forms because I didn't realize we were discussion DAO access. SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.