February 10, 2009 at 7:06 am
Is select statement perform better or view in general?
select col1, col2,col3 from table1
order by col1
select col1, col2,col3 from view1
thanks
February 10, 2009 at 7:31 am
The two aren't equal. You would need to include the ORDER by in the select from the view.
A view is just a select statement. Nothing magic.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 10, 2009 at 7:42 am
so if I put the order by in the view...both select statement in general perform the same?
thanks
February 10, 2009 at 7:55 am
Without knowing precisely what's in the view, I couldn't say. But let's take this:
--view definition
CREATE VIEW dbo.View1
AS
SELECT t1.col1
,t1.col2
,t1.col3
FROM dbo.table1 t1;
GO
--select from table
SELECT t1.col1
,t1.col2
,t1.col3
FROM dbo.table1 t1
ORDER BY t1.col1;
--compare to the first query
SELECT v1.col1
,v1.col2
,v1.col3
FROM dbo.View1 v1
ORDER BY v1.col1;
These are identical. You've just masked the table behind the view.
EDIT: Fixed a copy & paste error.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 10, 2009 at 8:24 am
tran008 (2/10/2009)
so if I put the order by in the view...both select statement in general perform the same?
If you put just an order by on a select in a view, you'll get an error. If you do top 100% ... order by, SQL will ignore the order by statement.
order by is only valid in the outer-most select statement, ie, the one that selects from the view.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 10, 2009 at 8:25 am
GilaMonster (2/10/2009)
tran008 (2/10/2009)
so if I put the order by in the view...both select statement in general perform the same?If you put just an order by one a select in a view, you'll get an error. If you do top 100% ... order by, SQL will ignore the order by statement.
order by is only valid in the outer-most select statement, ie, the one that selects from the view.
OH bugger. I did a copy & paste & missed that. Sorry.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 10, 2009 at 8:26 am
Grant Fritchey (2/10/2009)
GilaMonster (2/10/2009)
tran008 (2/10/2009)
so if I put the order by in the view...both select statement in general perform the same?If you put just an order by one a select in a view, you'll get an error. If you do top 100% ... order by, SQL will ignore the order by statement.
order by is only valid in the outer-most select statement, ie, the one that selects from the view.
OH bugger. I did a copy & paste & missed that. Sorry.
Wait, no I didn't. More coffee, that's the answer.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 10, 2009 at 8:43 am
Grant Fritchey (2/10/2009)
Wait, no I didn't. More coffee, that's the answer.
More coffee is an acceptable answer to many problems. I didn't read your post in detail, so I don't know what you corrected.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 10, 2009 at 8:45 am
GilaMonster (2/10/2009)
Grant Fritchey (2/10/2009)
Wait, no I didn't. More coffee, that's the answer.More coffee is an acceptable answer to many problems. I didn't read your post in detail, so I don't know what you corrected.
I "corrected" the wrong thing and then recorrected the wrong thing only to realize I had the first view definition correct... I give up.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 10, 2009 at 8:51 am
Grant Fritchey (2/10/2009)
I "corrected" the wrong thing and then recorrected the wrong thing only to realize I had the first view definition correct... I give up.
Lol. More coffee needed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 10, 2009 at 9:11 am
Yes.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 10, 2009 at 12:58 pm
[font="Verdana"]Other than the issue of the "order by" already mentioned (with or without extra coffee), there's are a couple of other minor differences between using views and not.
You can create indexes over (some) views. So if you have a complicated view that is doing summaries or selections or projections, then the data the view generates can actually be saved as part of a clustered index. That means that selecting from such a view can be significantly faster than the same select from the base tables.
Edited to remove some comments about query caching that were in fact quite wrong. See comments below.[/font]
February 10, 2009 at 1:24 pm
Bruce W Cassidy (2/10/2009)
SQL Server can cache parts of the query plan for views. So when you make use of the view, the overhead can be lower.
I do sooo apologise for been the critic again, and hope you can forgive me, however....
Views do not have plans cached. Stored procedures do. Views are inlined into the query that calls them before the optimisation step of query execution. That happens as part of the parsing and algebratisation. So by the time the query that references the view reaches the query optimiser, all reference to the view is gone.
To give you a rough example.
CREATE View MyView AS
SELECT Col1,Col2, Col3 FROM SomeMassiveTable WHERE SomeCondition = 'true'
GO
SELECT * FROM MyView
What the optimiser sees (except as an expression tree) is something of the form
SELECT * FROM (
SELECT Col1,Col2, Col3 FROM SomeMassiveTable WHERE SomeCondition = 'true'
) AS MyView
and it will optimise that as a single entity, not the view first and then the query on the view.
So there is a hint you can provide to force SQL Server to expand the view, and (hopefully) generate a better query plan overall.
Those hints (Expand Views and NoExpand) are only for indexed views. Normal views are always expanded, they have to be, they're just saved select statements.
Here's another difference: you can create indexes over (some) views.
You can, and when done the views are materialised as if they were tables. It's not always a good idea, it will slow down modifications of the underlying tables, and there's a 'laundry list' about 2 pages long of limitations and restrictions of indexes views.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 10, 2009 at 1:28 pm
Yep, Gail's right on this one too (you can usually just assume that).
The only thing cached is the expanded query, which again, the query against the table is cached as well.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 10, 2009 at 1:31 pm
[font="Verdana"]Gotcha. I did a little subsidiary reading on Books Online, and you're quite right -- cached query plans don't apply for views. Ah well. I learn something every day!
(I suspect I am confusing some of the Oracle stuff... doh! Blame Oracle, that's my excuse.)
[/font]
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply