question

jowens0520 avatar image
jowens0520 asked

How to optimize this database diagram

I have been given the task to improve this database and I need to know how to optimize this database diagram. The current application that uses this database is running slow and the reporting is complicated. Any ideas would be helpful. ![alt text][1] [1]: /storage/temp/3394-db-diagram-2.png
optimizationdatabase-diagramsdiagrams
db-diagram-2.png (430.6 KiB)
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I've heard a story about Kimberly Tripp, where, after delivering an address at a conference, she was asked by a man brandishing a MASSIVE ER diagram. He pleaded with her for a few minutes of her time to help him "optimise" it. She stared at it for a little while and then shook her head. She then had to explain to him that the diagram has very little to do with performance. You need to understand what queries are being run, how often they are being run, what indexes are being used and so on. In short "Know your data."
0 Likes 0 ·

1 Answer

· Write an Answer
David Wimbush avatar image
David Wimbush answered
It seems to be fairly well organised. I'd question whether Areas and Areas_1 need to be separate tables but that's a minor point. It's not likely to be slow because of the table design. Performance tuning is a big subject. Start by looking at the waits to see why it's slow. Paul Randal has a query for that here ( http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/) as well as good advice on how to interpret the results and how to treat the problem. Unless you have really underpowered or badly configured hardware, it's probably about the SQL Server configuration, indexes, and queries. There is a ton of information in SQL Server if you know where to look. I would recommend several of the free Red Gate books that you can find here ( http://www.red-gate.com/community/books/?gclid=CNe2m8-hxcwCFS8z0wod8mgEyA#sqldba): - Troubleshooting SQL Server: A Guide for the Accidental DBA - Performance Tuning with SQL Server Dynamic Management Views - SQL Server Execution Plans (Disclaimer: Red Gate runs this site but this is my opinion. These books are excellent.) It's basically about identifying the resources that are under pressure, finding he queries that are hammering those resources and fixing them. It's often about adding the right index or including more columns in an existing index. Or just tweaking the query a bit. It's quite a lot to take in but this is a good opportunity for you to enhance your skills and impress your boss! Reporting is another story. The database structure that works well for day-to-day business is rarely well suited for reporting. But defining the structure that does work for an organisation is all about what aspects of the data are important and useful, and how people want to look at it. It's not something that can easily be covered here but there are some good books out there on the subject.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 : nicely answered!
2 Likes 2 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.