If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Improving queries resource usage
Hi
I have a 10 user access backend/front end app which runs sufficiently ok. Recently I deployed a winform/ado.net app that runs a sequence of queries on the access backend db using OleDb ExecuteNonQuery method. Problem is some users have started reporting frozen front end access app. I can not say for sure that my ado.net app is responsible but problem seems to have come up in the same time frame. Is there any way I can avoid this sort of blocking and specifically if I can have a shorter timeout period for queries if a record/table is being used and can not be made available to the query? Any other ideas to help the situation would be much appreciated. Many Thanks Regards |
#2
|
|||
|
|||
Improving queries resource usage
Ah, Access/JET is notorious for this kind of issue. When you hammer the
client engine, it postpones any changes to the shared database file until it goes into idle mode. Of course, a better choice might have been SQL Server Express, but I expect it's too late for that now. Make sure that you don't have too many indexes as that slows down the updates. You can set the Command timeout in the Command object--see the CommandTimeout property. -- __________________________________________________ ________________________ William R. Vaughn President and Founder Beta V Corporation Author, Mentor, Dad, Grandpa Microsoft MVP (425) 556-9205 (Pacific time) Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) __________________________________________________ __________________________________________ "John" wrote in message ... Hi I have a 10 user access backend/front end app which runs sufficiently ok. Recently I deployed a winform/ado.net app that runs a sequence of queries on the access backend db using OleDb ExecuteNonQuery method. Problem is some users have started reporting frozen front end access app. I can not say for sure that my ado.net app is responsible but problem seems to have come up in the same time frame. Is there any way I can avoid this sort of blocking and specifically if I can have a shorter timeout period for queries if a record/table is being used and can not be made available to the query? Any other ideas to help the situation would be much appreciated. Many Thanks Regards |
#3
|
|||
|
|||
Improving queries resource usage
Is it good idea to use transactions in this case?
Thanks Regards "William Vaughn (MVP)" wrote in message ... Ah, Access/JET is notorious for this kind of issue. When you hammer the client engine, it postpones any changes to the shared database file until it goes into idle mode. Of course, a better choice might have been SQL Server Express, but I expect it's too late for that now. Make sure that you don't have too many indexes as that slows down the updates. You can set the Command timeout in the Command object--see the CommandTimeout property. -- __________________________________________________ ________________________ William R. Vaughn President and Founder Beta V Corporation Author, Mentor, Dad, Grandpa Microsoft MVP (425) 556-9205 (Pacific time) Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) __________________________________________________ __________________________________________ "John" wrote in message ... Hi I have a 10 user access backend/front end app which runs sufficiently ok. Recently I deployed a winform/ado.net app that runs a sequence of queries on the access backend db using OleDb ExecuteNonQuery method. Problem is some users have started reporting frozen front end access app. I can not say for sure that my ado.net app is responsible but problem seems to have come up in the same time frame. Is there any way I can avoid this sort of blocking and specifically if I can have a shorter timeout period for queries if a record/table is being used and can not be made available to the query? Any other ideas to help the situation would be much appreciated. Many Thanks Regards |
#4
|
|||
|
|||
Improving queries resource usage
"John" wrote
I have a 10 user access backend/front end app which runs sufficiently ok. Recently I deployed a winform/ado.net app that runs a sequence of queries on the access backend db using OleDb ExecuteNonQuery method. Problem is some users have started reporting frozen front end access app. I can not say for sure that my ado.net app is responsible but problem seems to have come up in the same time frame. Is there any way I can avoid this sort of blocking and specifically if I can have a shorter timeout period for queries if a record/table is being used and can not be made available to the query? Any other ideas to help the situation would be much appreciated. For an application you can do in Winforms, there's a good likelihood that you should put down the glass of Kool-Aid, back away slowly, and create the application in Access to use the Jet or ACCDB database. Single user, workgroup applications, or client applications to server databases running on a LAN are just what Access was intended to handle, and it handles them very well indeed. And, Access Queries are processed by Jet, in the native language of the Jet database engine. It's far from being "notorious", and, by the way, from what you stated, it does not seem to me that Access nor Jet is involved at all, in any case. As far as I am aware neither ADO.NET nor OLEdb employs the Jet database engine to access a Jet-format database, nor, for that matter, an Access 2007 ACCDB-format database. But, as I don't use either ADO.NET nor OLEdb, my understanding could be wrong. If this were a client application using server DB tables (which I understand it is not), I'd advise (as does the Access team, now) using MDB and DAO for client applications with tables linked via ODBC. Bill's suggestion regarding indexes is good advice for any database I've used, though I didn't see anything in your post to indicate you had over-indexed. But if your application were an Access application, I'd suggest first looking at the locking options. Maybe you could specify that in ADO.NET or OLEdb. Larry Linson Microsoft Office Access MVP |
#5
|
|||
|
|||
Improving queries resource usage
Transactions would "serialize" the operations so that other users would be
blocked while the operation is taking place but force the engine to write immediately. Is this the behavior you want? IMHO, the problem you're facing is that JET is a local (client-based) DBMS engine and has to perform all physical IO over the network. Every disk seek, read and write has to be done over the network as if the file was local to the system--it's not. This process is repeated by every single user against the same file using the same network. These operations have to share the network bandwidth with every other operation on the wire. Note that the JET locking mechanism is fairly crude so it does not take much to lock out other users or lose something in the UPDATE process (as when some user gives up and turns off or reboots their system). I wish there was some magic bullet to fix these JET issues but there isn't--not short of redesigning your application to use SQL Server or some other serious DBMS. And no, this is not simply a matter of moving your data to SQL Server or some other service-based DBMS. While some take that approach, they often find that things are not much better as their fundamental design hinges on JET's strength and around JET's weaknesses. -- __________________________________________________ ________________________ William R. Vaughn President and Founder Beta V Corporation Author, Mentor, Dad, Grandpa Microsoft MVP (425) 556-9205 (Pacific time) Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) __________________________________________________ __________________________________________ "John" wrote in message ... Is it good idea to use transactions in this case? Thanks Regards "William Vaughn (MVP)" wrote in message ... Ah, Access/JET is notorious for this kind of issue. When you hammer the client engine, it postpones any changes to the shared database file until it goes into idle mode. Of course, a better choice might have been SQL Server Express, but I expect it's too late for that now. Make sure that you don't have too many indexes as that slows down the updates. You can set the Command timeout in the Command object--see the CommandTimeout property. -- __________________________________________________ ________________________ William R. Vaughn President and Founder Beta V Corporation Author, Mentor, Dad, Grandpa Microsoft MVP (425) 556-9205 (Pacific time) Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) __________________________________________________ __________________________________________ "John" wrote in message ... Hi I have a 10 user access backend/front end app which runs sufficiently ok. Recently I deployed a winform/ado.net app that runs a sequence of queries on the access backend db using OleDb ExecuteNonQuery method. Problem is some users have started reporting frozen front end access app. I can not say for sure that my ado.net app is responsible but problem seems to have come up in the same time frame. Is there any way I can avoid this sort of blocking and specifically if I can have a shorter timeout period for queries if a record/table is being used and can not be made available to the query? Any other ideas to help the situation would be much appreciated. Many Thanks Regards |
#6
|
|||
|
|||
Improving queries resource usage
Ah no. Accessing a pre-2007 "Access" database via OLE DB through ADO or
ADO.NET invokes a version of the JET engine specifically written for Access and those applications that need to access .MDB database files. For 2007 MS found the need to break away from the OS-installed JET engine and use the ACE engine which is an extension to JET. I've been documenting JET and VB since VB3 (where I wrote the VB2. 3, 4 and 5 Microsoft documentation) and written several books that discuss these very old issues. ADO.NET is not an engine at all, nor is OLE DB-both are data access interfaces designed to access specific back ends via a managed interface (as those built into the Framework or via an ODBC or OLE DB data driver or provider-specific to the target engine. At this point in time Access/JET databases are accessed through the .NET OleDb data access stack and the new ACE.OLEDB.12.0 provider which is new for the 2007 version of Access. It's derived from JET but is different than the JET now shipping with the OS (see this article). DAO is a data access interface specific to JET but I doubt if it will work against the 2007 version. Yes, as with any native interface, it can provide more functionality than any "one-size-fits-all" interface as exposed by OLE DB or ODBC. For me and my customers, Access has been the root cause of too many serious, complex and tough-to-solve issues over the last 15 years. Many of my books have large sections that talk about accessing JET and preventing or mitigating these pitfalls. I have worked with many customers (often large, sophisticated corporations with many, many small departments) that report users (often "paradevelopers" having tried to use Access databases and ended up with a totally undisciplined mess of unsecured, unreliable and poorly performing databases. These companies have characterized the proliferation of JET/Access databases as a virus and I agree. Some report that tens of thousands of JET databases are scattered all over the organization and the IT department cannot rein them in to protect them, back them up or replace them with something more suitable for their organizations. This means the users have data that should be protected (by law) but isn't, unhappy clients and unhappy IT people trying to sort it all out. Is this always the case? Hardly. Is this all Access' or JET's fault? Hardly. However, Access makes things so simple, less sophisticated "developers" (what I call paradevelopers) have created these applications thinking that they could go a lot further than they really could. There are lots of small companies, private organizations and individuals (myself included) that use Access databases without issue. My point is that Access has its limits and developers need to know these limits before they choose this approach for applications that have even the remotest possibility of scaling or where data must conform to IT standards or accounting standards or where a company's reputation depends on the performance, scalability and reliability. No, you're right. SQL Server is not a panacea-it is not the ultimate solution to all of the world's database ills. Nor is Visual Studio the ultimate front-end tool. IMHO it's far from it. I have worked for many many years both inside and outside of MS to get both of these to work better together and leverage some of the innovations that Access affords. SQL Server is, however a far more serious (feature-rich) DBMS that can better protect data and provide scalability from single-user to thousands of users and beyond. It can protect data as Access/JET cannot using sophisticated and time-tested logging and rights management paradigms. When it's part of the design, the developer has only to buy a more sophisticated license to leverage the additional horsepower of the advanced SQL Server engines. The problem (as I see it) with Access is that customers are led to believe that it's scalable from dozens to "up to 255" users. They're not told that their data is relatively insecure and subject to corruption through some very well-documented failure scenarios. While there are cases where a very sound Access/JET design and implementation can stretch into the dozens of users under the right circumstances, even those (excellent) implementations cannot be scaled further or take advantage of the copious features these customers need when their data requirements get more complex-as they so often do even though they had not expected or planned for them to do so. When customers build SQL Server solutions (with the exception of the SQL CE platform) they have a seamless upgrade path. That cannot be said about Access or Visual Basic applications built against its databases. -- __________________________________________________ ________________________ William R. Vaughn President and Founder Beta V Corporation Author, Mentor, Dad, Grandpa Microsoft MVP (425) 556-9205 (Pacific time) Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) __________________________________________________ __________________________________________ "Larry Linson" wrote in message ... "John" wrote I have a 10 user access backend/front end app which runs sufficiently ok. Recently I deployed a winform/ado.net app that runs a sequence of queries on the access backend db using OleDb ExecuteNonQuery method. Problem is some users have started reporting frozen front end access app. I can not say for sure that my ado.net app is responsible but problem seems to have come up in the same time frame. Is there any way I can avoid this sort of blocking and specifically if I can have a shorter timeout period for queries if a record/table is being used and can not be made available to the query? Any other ideas to help the situation would be much appreciated. For an application you can do in Winforms, there's a good likelihood that you should put down the glass of Kool-Aid, back away slowly, and create the application in Access to use the Jet or ACCDB database. Single user, workgroup applications, or client applications to server databases running on a LAN are just what Access was intended to handle, and it handles them very well indeed. And, Access Queries are processed by Jet, in the native language of the Jet database engine. It's far from being "notorious", and, by the way, from what you stated, it does not seem to me that Access nor Jet is involved at all, in any case. As far as I am aware neither ADO.NET nor OLEdb employs the Jet database engine to access a Jet-format database, nor, for that matter, an Access 2007 ACCDB-format database. But, as I don't use either ADO.NET nor OLEdb, my understanding could be wrong. If this were a client application using server DB tables (which I understand it is not), I'd advise (as does the Access team, now) using MDB and DAO for client applications with tables linked via ODBC. Bill's suggestion regarding indexes is good advice for any database I've used, though I didn't see anything in your post to indicate you had over-indexed. But if your application were an Access application, I'd suggest first looking at the locking options. Maybe you could specify that in ADO.NET or OLEdb. Larry Linson Microsoft Office Access MVP |
Thread Tools | |
Display Modes | |
|
|