November 2, 2007 at 8:11 am
Hi
I like using SQL 2005 as much as possible, but I have one MSAccess.mdb that uses linked tables to the SQL Server. Would the over all performance be better to use the passthru queries instead of the linked tables. What are some of the pro's and Con's
Thanks
Grant
November 2, 2007 at 8:20 am
The major Pro for using Linked Tables is the reduction in the amount of effort needed. The use of Passthru queries does require the dba to give Read, Update and Delete permissions on the objects that are the subject of the queries.
A better answer all round, if possible, is the use of the Access adp project which uses OLE DB rather than ODBC.
David
November 2, 2007 at 8:45 am
They're two entirely different animals.
Pass-through queries are roughly the equivalent of running ad-hoc SQL statements from Query analyzer: your machine sends the server a question, and the server send your back the answer. Pass-through query results are read-only by design in Access, so you can't open a pass-through query in Access and start modifying data. On the other hand, for read-only results - it will be a substantially more efficient query. You can ALSO use a pass-through query to execute a stored procedure on the server (so you can pass what you want updated to the stored proc and IT will update the server).
Linked Tables are unfiltered pointers into the SQL server. They allow for (assuming your account has the permissions on SQL server) updates, delete and inserts. The issue is that it tends to create very inefficient data access patterns, and has a tendency to create record and page locks on tables, so linked tables are very often a big reason for causing a SQL database to not perform well. If you want to run a query using linked tables, your machine will ask SQL for the ENTIRE table and then will perform the filtering locally. So if your table has 1 million records, and you're looking for just 1 - you will essentially "download" all 1 million records, and throw out 999999 of them. Not real efficient. This tends to take out the "server" part of "client-server" processing.
An ADP project is more performant, because the data objects live on the SQL Server, so the data processing happens "server-side". It has essentially nothing to do with OLE DB vs ODBC (you can do linked tables using ODBC or OLE drivers, with no difference in performance). Access ADP "queries" are stored as SQL Views or Stored Procedures and are processed there, the results are then shipped to the Access front-end for display (really a lot like a ASP page would do it).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply