January 12, 2011 at 9:32 am
I have the table with 2 million rows and i have the same data in a view(the same 2 million rows). I used the view to join the tables, it takes around 20minutes to execute. Can i use the table, will that make the execution faster ?? Views are supposed to be faster right ???
January 12, 2011 at 9:44 am
Views are usually(always?) slower, and I would question if the view does any additional logic. A view is a layer on top of the table and by-and-large only ADDS time to execution.
I would see what the view is doing, if it is SELECT * FROM dbo.tablename I would ask why it exists AT ALL.
If you can use the table directly, I probably would.
CEWII
January 12, 2011 at 9:54 am
It kind of depends on what you're doing with the view because the optimizer is pretty smart about their use. But when you start talking about JOINs with a view... that's when stuff can start to get strange quickly and you'll get poor perfrmance. But it really does depend on what you're doing.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 12, 2011 at 12:21 pm
varunkum (1/12/2011)
Can i use the table, will that make the execution faster ??
Maybe. Depends what the view is. If it's just a straightforward select with some conditions, unlikely.
Views are supposed to be faster right ???
No. Same speed at best, slower at worst, never faster. All a view is is a saved select statement. Nothing more
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
January 12, 2011 at 12:24 pm
NOW I UNDERSTAND. Thank you all so much.
January 12, 2011 at 12:29 pm
No problem..
CEWII
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply