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 |
|
Lumbago
Norsk Yak Master
3271 Posts |
 Posted - 2004-07-07 : 12:23:17
|
| Guru yaks;I'm currently facing a (for me) major design challenge as I am about to redesign a vital part of our database for higher performance. The frontend functionality will be exactly the same as before but the current structure needs modification to support more transactions and more reads and I need some expert advice.What I have today is one main source-table that is read and updated quite frequently, about 200 reads and 10 updates per second dusing peak hours. Every read is a 4-5 table deep join, and with the use of 5 UDF's (reading additional tables) I am able to get the result I am looking for. Are there any rules-of-thumb I should follow when optimizing this thing? I have basically been thinking about denormalizing the table for faster reads but this means that there will be additional updates for every update, and I'm not sure what performs better. I have also been thinking about creating views of different sorts (indexed/partitioned? only have one db-server though), but I don't know too much about them to make a qualified decision. I know that the info here is not too good, but I was hoping there were some general recomendations I could start off with... And how does an indexed view perform over an indexed table? And is it possible to make an indexed view of another indexed view and say 2-3 extra tables?--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-07-07 : 13:30:51
|
| Can you post the DDl and the DML statements?Are they keys all surrogates?What do you need from the other 4 or 5 tables?And why are you using udfs to select data from tables?Brett8-) |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-07 : 13:45:42
|
| Are you talking Horizontal partitions?In case you are not familiar I think the trick MS do with CONSTRAINTs on columns in PKs is one of those slap-forehead events.BOL - "partitions, views and"Its a bit like the way that BACKUP works to allow Updates during backup. Actually they've changed it, and I no longer understand it, so its no longer a forehead-slaping event :-(BOL - "backups, fuzzy"Kristen |
|
|
|
|
|
|
Design recomendations