Avatar of bfuchs
bfuchs
Flag for United States of America asked on

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.
Microsoft AccessMicrosoft SQL Server

Avatar of undefined
Last Comment
bfuchs

8/22/2022 - Mon
Gustav Brock

That depends ...

However, in my experience, linked servers are not very fast, while queries often can be modified for better performance.

/gustav
bfuchs

ASKER
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
SOLUTION
Dale Fye

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Gustav Brock

> 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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
bfuchs

ASKER
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
Dale Fye

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.
bfuchs

ASKER
@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

Open in new window

Is that not correct?

Thanks,
Ben
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
PatHartman

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.
bfuchs

ASKER
@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
PatHartman

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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
bfuchs

ASKER
@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
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
bfuchs

ASKER
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