This is my first question here, so I would love help to make it work for SO:DBA.

I inherited an older relational system that is sitting on a SQL Server 2005 box and the database is at SQL2000 compatibility. In my initial inspection, this system is loaded with cursor functions and very cumbersome code (including *= joins). I have a feeling that this is because every table has one primary key column that is non-identity, and there are no PK-FK relationships built into any but two tables. This column happens to be the same value, DocID. There are few if any indexes outside the clustered index on the "PK" that all tables have. I am finding 2nd and even 1st normalized tables in several locations. There is no data dictionary that anyone knows of and the original builders of the system are now long gone, the company that built this system has been turned over 3-4 times and does not support this system anymore. There is only the one box, and a "test" environment is another database structure on the same server, and I do not have the ability to create a secondary server to test/build a solution. The last little wrinkle is that my company has decided to replace the system, and if nothing else, I will need to extract a great deal of baseline data from this system for import into the new system within the next 6-8 months, if nothing else, to leave the old system.

Currently, there are issues with severe slowdowns and from I/O requests and long lock conditions due to connection conditions and several massive stored procedures that touch most if not all the tables in rapid succession. That is what I am tasked to eliminate. I have already begun a manual mapping of the system's architecture from within SQL Server.

I have not experienced a database with this degree of brokenness. I come from the developer's side of the room, so I know how to write/refactor/update queries in set notation, but I am struggling with why the system was built without using any kind of relationship outside of query WHERE logic and such a huge number of cursor based update and inserts. (I suppose the why is moot at this point, but it would be nice to attribute the pain of this process to some “good” reason.)

What can I do to establish the relationships so that the DBMS can actually use its power to handle the requests that currently bring the system to a crawl 3-4 times a day? Would using the SQL Server 2005 Relational Diagram tool be useful for building the relationships and examining the index potential for the columns in each table? If I use the tool, will the relationship changes be done without "breaking" the legacy processes? For example, will the stored procedures with cursors still function correctly, if I re-key and index the table they are working on without adapting the Stored Procedure?

  • You may want to keep in mind that the application itself may be enforcing referential integrity, allowing itself to update parent and child tables out of order, eventually arriving at a consistent state. If you introduce database-side referential integrity constraints it might break the application. – mustaccio Oct 20 '14 at 19:41
  • Very true, I did some testing on it yesterday, it breaks all over the place because nulls and duplicate values exist in the current "Key" since the unique constraints are off, and Nulls are allowed. I would have to rebuild every table from scratch, and that would mean making the application behave differently. In this case it is not worth it. – Vanzanz Oct 21 '14 at 12:47

I would not make modifications like adding primary/unique or foreign key constraints to a legacy database that you haven't built yourself. Chances are that the original developer, given the problems and design issues you mention, may have built logic that breaks with such constraints.

For instance, if the app uses a 0 in a key column instead of NULL to represent "nothing", a foreign key constraint would fail that UPDATE/INSERT, even if the value is just used temporarily in a procedure.

The best place to start is probably to identify specific queries that show performance issues or cause locking, and deal with those. Just adding proper indexes on strategic tables may prove a huge improvement for you.

As for cursors.. some devs just don't know better. :)

| improve this answer | |
  • Upon further examination and a couple trial attempts at altering minor functionality in the table structures, you sir, are dead on. Ugly is as ugly does, and I will work on optimizing the queries without the database side relationships. I am sad, it looked like it would be a "fun" challenge. – Vanzanz Oct 21 '14 at 12:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.