March 30, 2009 at 10:47 am
Hi folks,
I just took over a web application that has a SQL backend. I reverse engineered the database to get a data model, the results were a mess. The database has around 90 tables, about 40 of them are not linked to any other table. Whats a good starting point to fix this, and is this a big slowdown in the application.
I also ran some checks on the views, none of the queries have been optimized. The database is a mess design-wise. Any input would be appreciated to where I should begin with this one.
Thanks,
Dennis
March 30, 2009 at 10:56 am
Dharris, as I understand it links have very little impact (note that I did not say none, especially on the insert and update side, but little) on the speed of the database.
They do on the other hand have a great deal to do with data integrity. Unless you are prepared to entirely trust the applications and interfaces to ensure data integrity, you should definitely begin establishing the links, and where possible bringing the design to the 3NF. But those links are there primarily for integrity.
What will have a major impact on your performance are the indexes. If links were not set up, then most likely indexes have not been set up. Index optimization is a very deep topic and there are other people more qualified than I to advise you there, but one safe rule of thumb is that if a column should be involved in a foreign-key-relationship (whether or not that link explicitly exists) then you probably want it to have an index.
Of course, if you are worried about optimization, then after setting up the obvious indexes, you may want to start taking a look at the code. The way the procedures and programs are written can have an enormous impact on speed.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
March 30, 2009 at 12:26 pm
First, if you can define them, get foreign key constraints in place. As the last post said, you're looking at a loss of data integrity. That's worse than any performance problem you'll ever hit.
Then, I'd check the tables to see that most, if not all, had clustered indexes. I'd check this very carefully to ensure that you're not just putting the cluster on the PK, but on the most frequently used data access path (that means looking at the queries). So much hangs off the clustered index in SQL Server, that this has to be done right.
After that you can go through the normal process of performance tuning & optimization.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply