SQL Server Performance Forum – Threads Archive

is a view faster then just querying a table

hi all, the db that i’m working on right now, has views for all most every thing (a lot of the views are just copys of tables not joins at all.) then the code querys the views. now is this faster or better then just querying the table and having inner join where you need them.
i only bring this up because one of the querys is running slow and it is of a view. KENNY

Unless the view is indexed, or you need it for security and reporting functions, it would generally be very slightly faster to run it directly against the tables. Why is the database setup like this? I don’t understand why you would do this if they do absolutely nothing but add a layer to the database. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

Have you checked the execution plan for that slow running query (on view)? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

I dont think Views can raise performance issue in your case.
bcoz views are not a storage , they are just a definition given by you based on your requirement.
so,whenever we query the Views then it fetchs the data based on definition from undelying tables. BTW,did u query directly with the table ? Check the execution Plan For TABLE & VIEW both.
Sandy (DB Developer)

A view serves quite different purposes. Like a stored procedure it’s kind of abstraction between the data and the "user". And therefore allows controlled access to the underlying data. Now if you have problems because of a view, I wouldn’t blame views in general, but rather investigate into this issue Have you changed the underlying table structures lately? If so, you might need to execute sp_refreshview. There are plenty of reasons why your view might have become slow. I think you need to give more detail, as "the query is running slow" isn’t very descriptive at all. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] –Frank http://www.insidesql.de</a>

quote:
…because one of the querys is running slow and it is of a view.

My quick take: A.
1. If the view is just a direct unjoined SELECT on a base TABLE, the execution plan does not change
2. If the plans are the same, you will not benefit from changing it to a direct SELECT on the table. B. Since performance (and not security) is problem here, try to investigate other causes of a slow query.
-A malformed query (SELECT *)
-Indexes that are not useful for the queries submitted
-Index statistics that are not up to date.
-Fragmentation
-Hardware
-etc C. By the way, what is "slow" in this case? What performance statistics are you expecting out of the mix of your query, the amount and nature of your data, existing indexes , hardware and network configuration? Nathan H.O.
Moderator
SQL-Server-Performance.com

Would usefull to see query in view.
I had a couple of case with slow views (no index capability) and the reason was poor codification.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

The datebase is running on a sql verson 7 so there is know index on the view. Would moveing the datebase to sql 2000 and indexing the view help in general. I’m looking at the diffrent ways i can change this section of the db to up the query speed.
Thanks KENNY

Something fromhttp://www.sql-server-performance.com/sql_server_performance_audit8.asp Views are best used for handling security-related issues, not as a lazy developer’s method to store often-used queries. For example, if you need to allow a user adhoc access to SQL Server data, then you might consider creating a view for that user (or group), then giving that user access to the view, and not the underlying tables. On the other hand, from within your application, there is no good reason to SELECT data from views, instead, use Transact-SQL code to SELECT exactly what you want from the tables directly. A view adds unnecessary overhead, and in many cases, causes more data than necessary to be returned, which uses up unnecessary overhead. For example, let’s say that you have a view that returns 10 columns from 2 joined tables. And that you want to retrieve 7 columns from the view with a SELECT statement. What in effect happens is that the query underlying the view runs first, returning data, and then your query runs against the data returned by the query. And since you only need 7 columns, not the 10 columns that are returned by the view, more data than necessary is being returned, wasting SQL Server resources. The rule you should follow in your applications is to always access the base tables directly, not through a view. — Marek ‘chopeen’ Grzenkowicz, MCP
Poland

]]>