Connect using linked servers

That depends ...

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?

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

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,

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.

@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

select * from ... inner join ..on SQLtable.ID = LinkedServertable.ID

Select allOpen in new window

Is that not correct?

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.

@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

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.

@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

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