Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
Connect using linked servers
Hi Experts,
I have an Access app linked 60% to SQL and 40% to MS Access BE.
Now I have some queries that join tables from both sources, and I am wondering which way would be better for performance.
A- having access front end do the join and filters
B- Establish a linked server connection from SQL to the Access BE, and have the SQL server perform the task.
In case option 'B' is the better option, I would need some guidance how to perform that link properly.
FYI- the SQL database is in one server and the Access BE is in another server, all in one network.
I have an Access app linked 60% to SQL and 40% to MS Access BE.
Now I have some queries that join tables from both sources, and I am wondering which way would be better for performance.
A- having access front end do the join and filters
B- Establish a linked server connection from SQL to the Access BE, and have the SQL server perform the task.
In case option 'B' is the better option, I would need some guidance how to perform that link properly.
FYI- the SQL database is in one server and the Access BE is in another server, all in one network.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
That depends ...
However, in my experience, linked servers are not very fast, while queries often can be modified for better performance.
/gustav
However, in my experience, linked servers are not very fast, while queries often can be modified for better performance.
/gustav
Hi Gustav,
On what does it depends on?
The first option of using queries is performing slow, I guess as involves large SQL tables that need to be joined to the Access tables, and to perform that the entire table needs to be sent over to the access engine and there it performs the join and filter..
Perhaps I do need to test the second option and see which performs better in my case?
Thanks,
Ben
On what does it depends on?
The first option of using queries is performing slow, I guess as involves large SQL tables that need to be joined to the Access tables, and to perform that the entire table needs to be sent over to the access engine and there it performs the join and filter..
Perhaps I do need to test the second option and see which performs better in my case?
Thanks,
Ben
I've found that:
1. If you can pull a subset of the SQL data into an Access temp table, and then join to Access you will get pretty good performance.
2. If you can push data from the Access side into a staging table in SQL Server and do the rest of the query on SQL Server, that will also give you pretty good performance
3. Joining Access tables to Linked SQL Server tables tends to give pretty poor performance.
Since starting to work with SQL Server, I've found that storing most of my data on SQL Server and pulling small segments into Access temp tables tends to work well.
1. If you can pull a subset of the SQL data into an Access temp table, and then join to Access you will get pretty good performance.
2. If you can push data from the Access side into a staging table in SQL Server and do the rest of the query on SQL Server, that will also give you pretty good performance
3. Joining Access tables to Linked SQL Server tables tends to give pretty poor performance.
Since starting to work with SQL Server, I've found that storing most of my data on SQL Server and pulling small segments into Access temp tables tends to work well.
> On what does it depends on?
What you are doing, how many records in Access tables, how many records in SQL Server tables, read-only or read-write, power of workstations, network speed - to name some.
With just about zero information it is not possible to be specific.
Adding linked servers is another step in creating a more complicated scenario, that's why I would test alternatives before, like considering redesign of your current queries. At least in one case that made a big improvement for me by splitting queries and apply prefiltering to the data pulled from SQL Server.
/gustav
What you are doing, how many records in Access tables, how many records in SQL Server tables, read-only or read-write, power of workstations, network speed - to name some.
With just about zero information it is not possible to be specific.
Adding linked servers is another step in creating a more complicated scenario, that's why I would test alternatives before, like considering redesign of your current queries. At least in one case that made a big improvement for me by splitting queries and apply prefiltering to the data pulled from SQL Server.
/gustav
I didn't know you could define an Access db as a linked server but if you can, it may not give you better performance. As the others have mentioned, if you join an SQL table to an Access table, Access will bring down the SQL table to memory on your local PC (even if the Access db is running from a server). Access is smart enough to apply any selection criteria against the SQL table before bringing it down. For example if your criteria included Where State = 'CT' and State comes from the SQL server table, Access would pass that criteria through to the server and only bring down Connecticut accounts.
Using a linked server would have no impact if you use Access queries. You would have to switch all your queries to pass-through queries and that would make them not updateable. Even doing that, SQL Server has the same problem that Access has. But in this case, it is SQL server that will be dragging the Access data to a temp table on the server prior to the join.
Bottom line is - unless you do what Dale is suggesting - create local temp tables - it is extremely inefficient to join heterogeneous databases. It is magical that Access can actually do this but don't abuse the feature.
Your BEST option is to move all your local Access tables to SQL Server so everything is on the server and the server can do all the heavy lifting without schlepping data around your network.
Using a linked server would have no impact if you use Access queries. You would have to switch all your queries to pass-through queries and that would make them not updateable. Even doing that, SQL Server has the same problem that Access has. But in this case, it is SQL server that will be dragging the Access data to a temp table on the server prior to the join.
Bottom line is - unless you do what Dale is suggesting - create local temp tables - it is extremely inefficient to join heterogeneous databases. It is magical that Access can actually do this but don't abuse the feature.
Your BEST option is to move all your local Access tables to SQL Server so everything is on the server and the server can do all the heavy lifting without schlepping data around your network.
Hi Experts,
I see this is something I will have to post all the details in order for you to give accurate advise, I don't think will have the opportunity to get it this week, perhaps will leave it on hold for now & continue next week.
@Dale,
I think its currently working the way you described #1, will post more details as I get to it.
@Gustav, Pat,
Thanks,
Ben
I see this is something I will have to post all the details in order for you to give accurate advise, I don't think will have the opportunity to get it this week, perhaps will leave it on hold for now & continue next week.
@Dale,
I think its currently working the way you described #1, will post more details as I get to it.
@Gustav, Pat,
Adding linked servers is another step in creating a more complicated scenario
You would have to switch all your queries to pass-through queries and that would make them not updateable. Even doing that, SQL Server has the same problem that Access has. But in this case, it is SQL server that will be dragging the Access data to a temp table on the server prior to the join.This is exactly my point of question here, if the report in question is read only, and I would be using pass-through queries, why is it not worth to have the SQL server do the joining & filtering job instead of the local Access engine? (and we do have a quite powerful server in place with plenty of memory & other resources..)
Thanks,
Ben
Ben,
If for a report, then pass-through would be fine, but you cannot run a pass-through query with tables that are in Access and SQL Server, all of the data has to be on the server. This would actually be the best option.
If for a report, then pass-through would be fine, but you cannot run a pass-through query with tables that are in Access and SQL Server, all of the data has to be on the server. This would actually be the best option.
@Dale,
I was assuming that creating a linked server in SQL is like creating a link in an Access app to an external data source, and thereafter it will act like a local table (not as fast tough), so I should be able to have the following statement sent over to SQL
Thanks,
Ben
I was assuming that creating a linked server in SQL is like creating a link in an Access app to an external data source, and thereafter it will act like a local table (not as fast tough), so I should be able to have the following statement sent over to SQL
select * from ... inner join ..on SQLtable.ID = LinkedServertable.ID
Thanks,
Ben
Ben, you could probably do that (I don't know anything about linked servers) BUT, the query MUST be a pass-through since that is the only way you could force the Server to use the linked server. An Access query would join the server to the local tables.
Why don't you want to move all the tables? That is really the most efficient scenario.
Why don't you want to move all the tables? That is really the most efficient scenario.
@Pat,
I know from past experience that this is not just a simple move, there are many features in the app that stop function normally after we upgrade, and therefore this will require intensive testing & fixing in order to accomplish that..for the time being we cannot afford that.
(I wish there would be such a tool that upgrades Front & BE without a hassle)
Eventually we will definitely move all our tables to SQL!
Thanks,
Ben
I know from past experience that this is not just a simple move, there are many features in the app that stop function normally after we upgrade, and therefore this will require intensive testing & fixing in order to accomplish that..for the time being we cannot afford that.
(I wish there would be such a tool that upgrades Front & BE without a hassle)
Eventually we will definitely move all our tables to SQL!
Thanks,
Ben
Unless you really have to solve a slow query problem and you are desperate, I would not even attempt the linked server option. It isn't likely to be any faster since it would force SQL Server to upload Access data to temp tables in the server in order to perform the join.
Just wait until all the tables are on the server. In the mean time, make sure criteria is applied against the server-side tables whenever possible. At lest Access will restrict the data it asks for since it is smart enough to recognize the criteria.
Just wait until all the tables are on the server. In the mean time, make sure criteria is applied against the server-side tables whenever possible. At lest Access will restrict the data it asks for since it is smart enough to recognize the criteria.
@Pat,
As mentioned, I will leave it for next week when I can get a close look at the app and perhaps describe in more details what is the scenario,
Meanwhile thanks to all for replying & have a nice weekend!
Thanks,
Ben
As mentioned, I will leave it for next week when I can get a close look at the app and perhaps describe in more details what is the scenario,
Meanwhile thanks to all for replying & have a nice weekend!
Thanks,
Ben
> Is that not correct?
Yes it is. We use that intensively at one client for connection to two legacy databases. That way our main app only needs a connection to the SQL Server. It works fine, about 99.99%, as we have had twice during the years to take steps to reconnect the linked servers.
But it is in no way "fast". What we do is to import and export all necessary data between the SQL Server and the linked servers. This way all other handling of data - including running all sorts of queries - is done by SQL Server only.
/gustav
Yes it is. We use that intensively at one client for connection to two legacy databases. That way our main app only needs a connection to the SQL Server. It works fine, about 99.99%, as we have had twice during the years to take steps to reconnect the linked servers.
But it is in no way "fast". What we do is to import and export all necessary data between the SQL Server and the linked servers. This way all other handling of data - including running all sorts of queries - is done by SQL Server only.
/gustav
Hi Experts,
I see it will take me some time to get all the details involved here...
Therefore I would be finalizing this for now, and perhaps post a new question once I get a chance.
However I got my answer, that is I should not attempt to use linked server in order to improve performance..
Thanks to all participants!
Ben
I see it will take me some time to get all the details involved here...
Therefore I would be finalizing this for now, and perhaps post a new question once I get a chance.
However I got my answer, that is I should not attempt to use linked server in order to improve performance..
Thanks to all participants!
Ben
Premium Content
You need an Expert Office subscription to comment.Start Free Trial