Performance of Views Vs. Pass-Throughs

Performance of Views Vs. Pass-Throughs

Post by Rick Brand » Sat, 03 Jun 2000 04:00:00



Can anyone tell me what differences are expected between a view stored on
the server and a Pass-Through query on the client with the exact same SQL?
I would have expected similar performance since they are the same
statements, both executed on the server.

If anything I would think the view would be faster since the server would be
better able to optimize it when it is created.

I am seeing the opposite.  If I execute the SQL in an adhoc query in
Enterprise Manager I get the same performance as the Pass-Through on the
client (a few seconds).  Opening a link to the View created on the server
produces a long hourglass followed by a timeout error (client is Access 97
using ODBC).

 
 
 

Performance of Views Vs. Pass-Throughs

Post by BPMargoli » Sat, 03 Jun 2000 04:00:00


Rick,

Quote:> If anything I would think the view would be faster since the server would be
> better able to optimize it when it is created.

A view is not optimized when it is created. The text of the view is stored, and
when a query references the view, the text of the view is substituted
essentially verbatim into the query, and the entire query, with the view
reference resolved, is then passed to the SQL Server query optimizer.

---------------------------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc) which can be
cut and pasted into Query Analyzer is appreciated.


Quote:> Can anyone tell me what differences are expected between a view stored on
> the server and a Pass-Through query on the client with the exact same SQL?
> I would have expected similar performance since they are the same
> statements, both executed on the server.

> If anything I would think the view would be faster since the server would be
> better able to optimize it when it is created.

> I am seeing the opposite.  If I execute the SQL in an adhoc query in
> Enterprise Manager I get the same performance as the Pass-Through on the
> client (a few seconds).  Opening a link to the View created on the server
> produces a long hourglass followed by a timeout error (client is Access 97
> using ODBC).


 
 
 

Performance of Views Vs. Pass-Throughs

Post by Mike Schule » Thu, 08 Jun 2000 04:00:00


Try it on a very simple query/corresponding view. I think you have a connection
problem, not an execution problem.


> Can anyone tell me what differences are expected between a view stored on
> the server and a Pass-Through query on the client with the exact same SQL?
> I would have expected similar performance since they are the same
> statements, both executed on the server.

> If anything I would think the view would be faster since the server would be
> better able to optimize it when it is created.

> I am seeing the opposite.  If I execute the SQL in an adhoc query in
> Enterprise Manager I get the same performance as the Pass-Through on the
> client (a few seconds).  Opening a link to the View created on the server
> produces a long hourglass followed by a timeout error (client is Access 97
> using ODBC).

--
-------------------------------------
Mike Schuler
http://www.dbsurfer.com
sponsors of http://www.sqlschool.org
Voice (604)926-2676
Catch the SQL wave - with DBsurfer.
 
 
 

1. limitation of number of pass-throughs?

i have a form which is populated with the results of 5 recordset
objects.  on one of the recordsets, i open the rst as a dbSQLPassThrough
type so i can convert a text field to datetime prior to bringing it into
vb.  i need to do this on another of the recordsets but everytime i
change it to dbSQLPassThrough, the whole thing bombs out on me.  i know
the syntax works cuz i pasted it in an isql window and it works fine.

is there some sort of limitation that says you can only use one
dbSQLPassThrough per sub?  when i change this back to dbOpenSnapshot
everything is fine.

your help is greatly appreciated.

Andre

2. DTS randomly skips tables?

3. Pass-through queries vs SQL server views

4. INCREDIBLE problem with Fox win2.6a in WIN2K !-(

5. SQL pass thru vs. View

6. Query using calculated field

7. Performance : SP vs View

8. US-VA-SOFTWARE ENGINEER IV / ORACLE

9. Performance of Views vs SQL

10. Performance of views vs stored procedures

11. Performance Trigger vs Indexed view

12. Performance of VIEW vs SP

13. Strange performance problem view Vs plain Sql