View question

Question

View question

varunkum

Default port

Points: 1419
More actions
January 12, 2011 at 9:32 am

#233129

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 ???

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 1

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

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 2

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass