Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
timoqvist
Starting Member
8 Posts |
 Posted - 2006-09-20 : 04:39:08
|
| Hello, I haven't worked with databases or SQL much professionally, but I do have a masters degree in computer science so I'm interested in understanding views a little better regarding their performance. Basically, I'm wondering if there would be a performance penalty using views extensively as an abstraction layer (for reads, not updates) to tables that have been thoroughly normalized due to the excessive number of columns (and poor design in general). The background is this, I'm working with a system with maybe a 1500 tables (across 16 db)and twice that many SP, most of which take 10 if not 20 parameters, the tables on *average* have maybe 10-15 columns and relationwise everything is screwed up, redundancy is everywhere, nulls are all over the place etc etc.. the original designers didnt know or care about normalization. But normalizing even parts of this system would increase the number of tables (yes along with probably more serious changes) and I'm concerned about being able to come up with intuitive names for each and every new table (I havent done normalization on this scale before) hence my question. So, if I normalized a table with say 20 columns into say 3-5 tables and then used views for queries and SP's for updates, would I get a performance penalty for using the views? I'm thinking not because the normalization should affect performance positively, disk accesswise and index efficiency.. right?Hope you can shed some light on this!Regards,Timo |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-09-20 : 05:08:25
|
| IMHO, views should never be considered to provide performance advantage..View is something which is used to encapsulate complex query logic and provide limited access to the underlying table data. Views are only useful to avoid recoding of complex joins or subqueries in number of places.In your case, you can think of following things:1. Normalize database schema (but don't over-normalize)2. Add proper indexes (if not already in place)3. Try to Place divide tables and their corresponding indexes on different disks (if possible)Harsh AthalyeIndia."Nothing is Impossible" |
 |
|
|
timoqvist
Starting Member
8 Posts |
Posted - 2006-09-20 : 05:28:58
|
| Thank you for your reply.My intention is not to optimize in anyway by using views, rather I'm concerned if I would get a penalty from using views in this way (whether they are the right way to go or not). Also I think that views would serve the purpose you state they are for, currently the tables with so many columns try to represent very complex relations with just one table. On top of that most SPs in the system do joins on everything from 5 to 20 tables (of this kind) so normalizing those tables and using views would encapsulate the complex relations in the system and hide their representation.But would it be slower?Regards,Timo |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-09-20 : 05:46:36
|
quote:
Originally posted by timoqvist
Thank you for your reply.My intention is not to optimize in anyway by using views, rather I'm concerned if I would get a penalty from using views in this way (whether they are the right way to go or not). Also I think that views would serve the purpose you state they are for, currently the tables with so many columns try to represent very complex relations with just one table. On top of that most SPs in the system do joins on everything from 5 to 20 tables (of this kind) so normalizing those tables and using views would encapsulate the complex relations in the system and hide their representation.But would it be slower?Regards,Timo
As far as views are concerned, they wouldn't make the query any slower since view is essentially named query !Harsh AthalyeIndia."Nothing is Impossible" |
|
|
|
|
|
|
|
|
View Performance and normalization