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] : /storage/temp/3394-db-diagram-2.png
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.