• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 51
  • Last Modified:

ms access and sql server

Hi Folks,
I just need a very brief general response to the following.
When using linked (ODBC) SQL Server tables on an Access 2010 FE:
Does running an Access query usually bring all the data to the FE and then subset?
(as opposed to a stored procedure with parameters which sub-sets records on the server?)
i.e will stored-procedures on big datasets always outperform Access queries?
and is there a way of using Access on SQL Server to get better performance without stored-procedures? (pass-through queries?)

thanks in advance
0
COACHMAN99
Asked:
COACHMAN99
2 Solutions
 
Brian PringleSystems Analyst II, SCM, ERPCommented:
If you want better performance in Access, I would create views on the SQL server and then query against the view instead of the tables directly.
0
 
COACHMAN99Author Commented:
Does the view work better because it subsets/joins the data prior to querying it?
i.e. would the view help if there was only one table?
and
Does running an Access query usually bring all the data to the FE and then subset?
 (as opposed to a stored procedure with parameters which sub-sets records on the server?)
thanks
0
 
Nick67Commented:
very brief general response
How brief <grin>
Does running an Access query usually bring all the data to the FE and then subset?
Depends on what evil you have done in the query (functions, memo fields, Access specific stuff)
i.e will stored-procedures on big datasets always outperform Access queries?
A stored procedure will never perform worse than an Access query unless you build a bad stored procedure.
and is there a way of using Access on SQL Server to get better performance without stored-procedures?
All sorts of ways: Views, Indexed Views.  A stored procedure isn't the only thing SQL Server has to offer.  A query that you would normally store in Access can be done as a View in SQL Server.  If you index that View, it is persisted and can improve ALL queries involving some or all of its data for ALL clients requesting the data.  A sproc isn't the only game in town.
1
 
COACHMAN99Author Commented:
Thanks guys.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<Does running an Access query usually bring all the data to the FE and then subset?>>

 The answer is "it depends".   Just to add a comment or two:

1. An explicit pass-through will always run server side.

2. JET/ACE will always try to send a SQL statement to the BE RDBMS if possible.   But as Nick pointed out, doing "Access" type things, such as using VBA expressions, JET Specific SQL, or performing joins on local tables will force execution "client" side (where MSACCESS.EXE is running).

3. Even with that, JET will try and optimize as much as possible using indexes and sorts.  Working with the data is done where MSACCESS.EXE is running, but it may not pull all the data from the BE in order to get the job done.

Jim.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now