February 27, 2008 at 1:20 pm
Luiz (2/27/2008)
Thank you for all your answers.I agree with what you guys told. But it seems that other people don't. See, for example, the discussion in http://www.sql-server-performance.com/tips/views_general_p1.aspx. Does anybody agree with it?
Luiz.
To test his argument, I created a simple view that returns three columns from three different tables, using FK-PK joins (one column from each table), with a simple Where clause. I then tested the view vs an identical select statement run as an ad-hoc query.
20 tests of each, identical io and time statistics. Both varied in CPU time between 31 ms and 60 ms, and had exactly the same number of scans, reads, etc. Same average, mean and deviation on run time.
Just to be sure, I created a proc with exactly the same select statement. Again, it ran exactly the same way (scans, reads, etc.), and in the same time range.
I then added twenty columns and two more tables to the view, but still only selected the first three columns from the view. I removed the Where clause from the view and added it to the outer select. Doubled the average run time of the select.
It's pretty straightforward.
If you have a small set of columns based on certain well-defined joins, that you routinely have to include in selects, then a view can make it more convenient to write code for that select. Just don't overdo it, don't include any more columns than you absolutely have to, etc.
Basically, it saves you the time of re-writing the same three/five/whatever joins over and over and over again. You have to weigh that against the cost of using it if you don't always need all the columns and tables you include in it.
- 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
Viewing post 16 (of 15 total)
You must be logged in to reply to this topic. Login to reply