The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

SQL Server locking with Access front end

We've got a bit of an argument with the SQL Server DBA's here about record locking.

We want to link MS Access to SQL Server tables with read-only privileges in a corporate production environment. The DBA's are fearful that queries written against the linked tables could cause a record lock.

Has anyone seen any evidence/documentation which could prove this either way?

Thanks
Marcus from Melbourne
Tuesday, July 03, 2007
 
 
Queries won't, but if the Access app opens a recordset and uses a server-side cursor, it could hold a record lock.  I'm not sure what the default is for drag and drop designed Access front ends, but it is possible that it is designed well and possible that it is designed poorly.  Just knowing it's Access isn't enough information.
JSmith Send private email
Tuesday, July 03, 2007
 
 
Thanks JSmith,

All the SQL Tables are read-only.

All data access via the MS Access front-end would be done via queries (QueryDefs).
Marcus from Melbourne
Tuesday, July 03, 2007
 
 
Link to a view on the SQL Server table(s) with the NOLOCK hint on the tables.  Then the DBAs are happy AND they can change the database schema and just change the views instead of changing the app.  The view also gives you more permissions options instead of giving permissions on the table level and the ability to easily hide columns, join tables with better performance (done on the server) and filter vertically or horizontally, all before Access gets a hold of the data.  Plus all your views can be named appropriately and grouped (or even put in their own schema!)

And then all you need to do is not use Access, and you've got a really great little system.
Cade Roux Send private email
Tuesday, July 03, 2007
 
 
BTW, why not access the SQL Server natively with an ADP instead of linking it to an MDB?
JSmith Send private email
Tuesday, July 03, 2007
 
 
Hi JSmith,

ADP or alternative GUI?

I agree entirely. Unfortunately this is a migration of an existing Access/Excel application and the business is adamant they will retain their existing GUI in order to be able to maintain it with involving IT or consultants.
Marcus from Melbourne
Wednesday, July 04, 2007
 
 
Access doing full table scans is a problem. Non-trivial joining of an Access table with a SQL table would be implemented by Access doing a table scan on the SQL table with all rows returned to Access. There were times we needed to kill Access sessions in SQL Manager because it was locking out other users.
FYI
Wednesday, July 04, 2007
 
 
Can you give an idea of the table row counts, and the estimated number of concurrent queries?
rond
Wednesday, July 04, 2007
 
 
I still think linking to views are a good performant way to handle this.

Another possibility is to link to a read-only replica of the actual production data - since you only want read-only access, why link to the live data anyway?
Cade Roux Send private email
Wednesday, July 04, 2007
 
 
Hi rond,

Very small number of rows across 60ish table. Most are in the thousands. The largest has 350,000.

Tops around half a dozen concurrent users - neglibible. The back-end to an existing database app is being migrated from Access to SQL Server. The IT dept wants the business unit to develop a new front end GUI. The business unit has said 'get stuffed' (at least, words to that effect) and simply want to redirect table links from Access to SQL Server.

The DBA's are resistant and I'm looking for evidence (empirical not anecdotal) to support the request. As you can probably guage, I'm a consultant working for the business.

Cheers
Marcus from Melbourne
Thursday, July 05, 2007
 
 
Cade Roux has it exactly right.

As a visible test, open up two Access sessions pointed to the same SQL Server db. Perform a query against the 350K row table that is known to take a while to return. Try to query from the second Access session right after you launch the first one - you'll be waiting for results until the first query is complete. That's what happens during the locking/blocking that occurs during table scans.

Do the same test using a view with a NOLOCK hint. You'll see the difference. The view can be as simple as "SELECT * FROM BigTable WITH (NOLOCK)".

Unless the tables are large, the problems don't usually occur too often with the simple "SELECT *" statements from Access, it's when several tables are dropped into the query designer and linked together that bad things happen. When the resulting query takes 15 minutes to return results and locks are held for that long, DBA's get understandibly irritable.

Note that reading up on the NOLOCK hint and understanding the timing of inserted records and when they are included in the results would be a good idea.
Mike Saeger Send private email
Thursday, July 05, 2007
 
 
I did run into a problem with Access locking an SQL Server table. We had an SQL Server database that a VB application wrote to, and we also had an Access database with links to the tables in SQL. Whenever someone had the table open in Access the VB program would freeze up when trying to write to the table until the user closed the Access database. I am pretty sure it also happened when they had a Access Query opened.

Dan
Dan Boris Send private email
Thursday, July 05, 2007
 
 
And to further elaborate on Mike's point about letting Jet do the joins via monster table scans and how to help that after you've moved your tables to linked tables - let's say your Access app has the 60 tables BUT ALSO a number of Access "Queries".  Change the queries to be Access linked tables to SQL Server views which duplicate the functionality you were getting from the Access queries and your performance will increase dramatically.  If you're Access front end does not use queries, but instead has either inline SQL or SQL stored in datasource properties, you CAN STILL IMPROVE performance (still without changing any code yet) by making your views a little smarter based on knowledge you have - like horizontally filtering out old data, or records marked deleted which are commonly already excluded in your inline SQL for example.

Using SQL in Access is slow and non-performing and non-scalable - and that's whether you are using direct storage, linked tables to other databases or linked tables to other datasources.  In native tables, it will pull the entire table from the file server.  You'll notice it caches that data, either in the filesystem or in Jet, so subsequent queries on large tables are fast until you close Access.

DAO/ISAM-style access (.Index=,.Seek, etc.) in Jet is actually quite fast, but obviously not portable, and definitely not preferable over a real RDBMS.
Cade Roux Send private email
Thursday, July 05, 2007
 
 
you're should be your, obviously...
Cade Roux Send private email
Thursday, July 05, 2007
 
 
marcus, my email address is my username + "@microsoft.com". I'm the guy in Melbourne who is currently has technical responsibility for Office (including Access). Before joining Microsoft I was DBA Manager for an Australian bank. If you drop me an eMail, I'll be able to help you resolve this situation with your DBAs.

Ron.
rond
Thursday, July 05, 2007
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz