February 22, 2011 at 10:28 am
Hello,
I have a quick question about reading data from a table. I have a join that has about 25 joins to multiple tables and all i need is a couple of fields from a table that is being used in that view. My question is, and pardon my ignorance about this, but if i only needed a couple of fields from that table, wouldn't it be better to pull straight from the table instead of using the view?
Your input is appreciated.
thanks.
February 22, 2011 at 11:35 am
Definitely avoid the view whenever possible.
Views are just pre-defined select statements. That kind of view, with lots of tables and (I'm guessing) tons of columns, will kill performance if you only need some of the tables.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 22, 2011 at 12:04 pm
Thats what I thought ... made sense to me to just read straight from the table rather than hit the view which would generate hundreds of columns when i only needed 2 columns from A single table out of the 25 joins the view has.
This coworker "schooled" me on how not to EVER do that and always use the view regardless .... hmmm... funny 🙂
February 22, 2011 at 3:17 pm
Well, it might not be crazy. The optimizer can, note the key word, eliminate tables that are not in use in an execution plan so that if you're only referring to one or two tables in a view, those are the only tables accessed. I would at least test it and see what the execution plan looks like.
Plus, it's possible that you're supposed to use the view because of security settings or something else. Best to have the whole picture before you make the decision.
"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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply