Solved

SQL View - PT vs. Table Link

Posted on 2007-03-30
4
208 Views
Last Modified: 2012-05-05
Access 97/2k,2k7, SQL 2k5
I currently have SQL views linked as tables in my access db. Are there any benefits to changing it from a link to the view to a pass through query executing the view? (Besides the con of the PT being read-only)

-adria
0
Comment
Question by:adraughn
  • 3
4 Comments
 
LVL 38

Expert Comment

by:Jim P.
ID: 18823548
I can't think of any others. Either way the query/view should be executed server side.
0
 
LVL 13

Author Comment

by:adraughn
ID: 18823656
so right now i just have my view linked as a table to access. so all of the work of creating the view is on the server. i just thought that if i changed it to a PT query in access, since it does not use jet at all with the PT, that there may possibly be a performance increase.

thoughts?
0
 
LVL 38

Accepted Solution

by:
Jim P. earned 500 total points
ID: 18824020
The only work client side using the view is the Order By or if you put further where clauses on the view when you open it.

If you have a view on the server such as IS_Emp_Vw:
SELECT *
FROM EMPLOYEE_TABLE
WHERE Active = 1
AND Dept = 1357

and link it to an Acc DB as a table all the work is done on the server. Now, if you link it as a table an then do a query against it as
SELECT *
FROM dbo_IS_Emp_Vw
WHERE Emp_Name like "P*"
The where clause is does client side, but the rest is done server side. The PT just makes the Emp_name clause server side as well.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 18824082
Glad to be of assistance. May all your days get brighter and brighter.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

774 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question