Why Use Linked Tables Instead Of Passthrough Queries?, Access 2007

[gnrmjd]

I'm running into some issues when attempting to design an Access front-end for a SQL server backend.



The problem is with my larger tables (some of them have millions of rows.)

Is there a reason to use a linked table, instead of a query that does "Select * from (table)" ? Because I needed a view to create a linked table since I couldn't link to a table with a long ID column (so the view is not indexed though the table is...I dunno if it still gets the benefits). I've been trying to make a query off of the linked table, joining with like 4-5 others. It's wayyyyy too slow (usually just freezes Access) so I want a query that will filter by date. My query to filter however is also extremely slow, so I want to use a pass-through query. It goes instantly if I query from the original table. So that makes me wonder why have a linked table at all if it's too slow to use? Should I be somehow doing this with pass-through queries?

[JoeM]

Pass-through queries are DEFINITELY more efficient than using Linked Tables. With linked tables, you are bringing all of the information from the tables, and then doing the query on the Access side. With a pass-through query, you are sending the SQL code over to the SQL server, doing the query there, and only bringing the results into Access.

Since SQL is so much more powerful than Access, pass-through queries are much more efficient than trying to do a query in Access on linked SQL tables.

Note, that I have used Access forms to enter criteria and dynamically build the SQL code that I need for my pass-through query "on-the-fly". That way, you still have the flexibility of using dynamic parameters/criteria in your pass-through query.

[gnrmjd]

Oh interesting. So you don't use linked tables at all?

How do you pass a parameter from a form to a pass-through query?

[JoeM]

Oh, I have used linked tables many times before too. Many of my projects involve small data tables where it is not an issue. But for the ones where we deal with large amounts of data, where performance takes a hit, we usually employ the pass-through queries.

Basically, any query you create in Access is really just a Form of SQL code. You can open up any query in Access, and if you switch to SQL View, it shows you the SQL code of that query. Now, Access' version of SQL code is very similar, but not quite exactly the same as actual SQL code, in all instances.

In any event, I create an Access Form which has textboxes for all the possible "criteria" or "parameters" I may use in my query. I then have VBA code which takes those values, and builds the SQL code I need to run my query. If you were just doing a straight up Access query, you could create a sample query in Access, switch to SQL View, and use that code as the template for the SQL code you need to build using VBA. For a SQL query, I usually first create an example of the query on my SQL Server until I get it working correctly, and then use that as my template for the code I need to build using VBA. Then, once you have built the VBA code, you just assign it to a pre-defined pass-through query you have set up in Access (CurrentDB.QueryDefs(queryName).SQL=...).

So basically, I have my one pass-through query set-up ahead of time in Access, and just keep overwriting the SQL code that query uses, and then run it.

Hopefully, that gets you started. If you need help with the technical details of it all, post back here.

[gnrmjd]

Ill check this more on Monday, but a quick update...I made a view in SQL (not even an indexed view) that joins the five tables together. Then I made a linked table to this view. Then I made a query in Access that uses this view and filters it by date. Surprisingly I saw a HUGE performance increase (instant results.) I dunno how that's possible since the view isn't indexed, but I'll test it more Monday. Maybe I don't even need a VBA SQL code idk.

[JoeM]

Yes, setting up Custom Views in SQL helps too.
In a nutshell, the more work you can do on the SQL side (and the less you have to bring over and do in Access), typically the better the performance.
SQL is a much more powerful engine than Access, so using it for most of the "heavy lifting" is optimal.

[pbaldy]

Pass-through queries are DEFINITELY more efficient than using Linked Tables. With linked tables, you are bringing all of the information from the tables, and then doing the query on the Access side. With a pass-through query, you are sending the SQL code over to the SQL server, doing the query there, and only bringing the results into Access.

This is a common misconception. Access is actually pretty good about passing SQL to the back end to process when it can. Leigh has a lot of good info here:

http://www.utteraccess.com/forum/Beg...-t1732935.html

i use SQL server a lot, and use Access queries against linked tables most of the time. I switch to pass through queries, stored procedures etc when necessary.

[JoeM]

This is a common misconception. Access is actually pretty good about passing SQL to the back end to process when it can. Leigh has a lot of good info here:

http://www.utteraccess.com/forum/Beg...-t1732935.html

i use SQL server a lot, and use Access queries against linked tables most of the time. I switch to pass through queries, stored procedures etc when necessary.

I have to admit, that some of the stuff in that link is a bit over my head, but I am always interested in learning more and understanding this stuff better. In regards to your last line, what would you consider "necessary"? Are there certain conditions to look for.

In browsing that article, it looks like they do say that Pass-Through queries are more efficient that Linked Tables. The also make reference to this article: http://www.utteraccess.com/forum/Beg...-t1732935.html, which has this quote in it:

In many cases you will find that, despite your best efforts, Office Access still retrieves some entire tables unnecessarily and performs final query processing locally. In these cases, the best strategy is to work with SQL Server views or ODBC pass-through queries to ensure that processing occurs on the server.

Speaking from my own personal experience, pass-through queries have helped me over the years. I have not had to use them often, but we have had projects where we need to create queries from large tables (some having millions of records in them). We had instances in which we tried using linked tables (or linked Views), and the queries built from them simply would not work. It would time-out or just hang forever. But switching to pass-through queries, we were able to get the results we needed. We have had other instances in which queries from the linked tables/views work, but take a long time, where switching to pass-through queries decreased the run-time.

That's just been my experience in my own little world...

[pbaldy]

Don't get me wrong, I wasn't saying you shouldn't use pass through queries. They are a great tool to have available. I simply wanted to correct the misconception that Access would always pull entire tables over. I generally start with an Access query and switch if the performance isn't up to par. I might go straight to a pass through if a query will be pulling from different SQL Server databases, as that would be a situation where Access would likely pull the entire tables over (working on one right now). I've created a couple of applications that I knew from the start were going to be used in a WAN environment (outside office connecting via VPN). I went with unbound forms, pass through queries and ADO command objects executing stored procedures to make absolutely sure the minimum amount of data was going over the wire.

In a related story, I just helped a client where a pass through query slowed down his process dramatically. He was connecting to a SQL Server table that had more than 255 fields. He had linked the table, but the linked table only showed the first 255. He created a pass through that showed the remaining fields. He had a complicated set of nested queries against linked tables that ran fairly quickly. In the last query if he joined to the pass through to get a field, it killed the performance (Access didn't understand that the linked table and the pass through's source were one in the same, so it had to pull the entire table over). My quick solution was to drop the pass through query from the query and create/call a function to get the desired field from the pass through. His query went from taking 20 minutes to execute to 4-5 seconds.

[JoeM]

Thanks for the clarification, that makes sense.
I typically don't go straight for the pass-through query right away either, only if the linked table process is to slow.

That second situation sounds like quite a doozy! Dare I ask if that table with over 255 fields was normalized?

[pbaldy]

I doubt it, but don't know. Had weird field names that didn't readily reveal their content/purpose, and he just brings me in to solve specific problems, so I didn't dig into it. He doesn't have control over the design of it anyway, he's just given the ability to connect to it read-only. Given the number of empty fields in records, I'd strongly suspect normalization was a foreign concept to somebody.

[JoeM]

Yeah, that's often a common theme. Someone gets stuck with someone else's poor design.
I have the same situation with some third party databases we use that need to create reports from.