Database Optimization

[BlueBeep]

Hey guys,

My company is undertaking a database optimization project. Optimization the schema, the code, etc. I would like to ask, if you guys could help out, the following:

1. What risks are there? What are the pitfalls?

2. My company is hesitant to do a database freeze and stop all new development until our vendor (who's restructuring tables and changing database objects) has a stable database for us to obtain, then, and only then can we continue development on this newer copy. My question to this: how can we either reduce the database code freeze or work in parallel?

3. Can anyone point me to other sources of information? Another thread? A book? A URL?

Thanks a bunch!

 

[r937]

risks include spending time, money, and people on a project that might not have any benefit

pitfalls include making performance worse

but i have to ask: why would you want to optimize a database that a vendor is busy restructuring???

how to reduce the freeze or work in parallel? shouldn't you be asking the vendor for a migration plan for this?

r937.com | rudy.ca

 

[JoeAtWork]

1. What risks are there? What are the pitfalls?

Without any information about how you plan on doing optimization, it's impossible to answer.

At the least risk range, you would be using a well-tested methodology with poeple who are eperts in the subject, and understand your business requirements thoroughly.

At the other end, you are just poking around making random changes to see what will happen. In which case, disaster is almost guaranteed.

2. My company is hesitant to do a database freeze and stop all new development until our vendor (who's restructuring tables and changing database objects) has a stable database for us to obtain, then, and only then can we continue development on this newer copy. My question to this: how can we either reduce the database code freeze or work in parallel?

Use a 3-tier (or n-tier) architecture. Instead of making your database requests directly to the server, then displaying them directly on the screen, you have a middle-layer that translates all the requests to the user interface. The middle layer is usually called the Business layer (sometimes this is further divided into a Business and a Data Access layer).

For example, if you need to display a list of customers, your UI code would ask the Business layer to retrieve the list. The Business layer (or Data Access layer) would in turn ask the database for the list, and then the Business layer would pass the list back to the UI.

The benefit to this is that if the database changes, you can isolate the changes to the Business layer. Your UI code does not have to be updated, the Business layer will handle it so it continues to serve the data in the same format. Another benefit is if you ever want to move the UI to another platform (such as from Windows to the Web), the majority of your code is already written for you in the Business layer.

Here's an article on N-tier development:

http://www.15seconds.com/issue/011023.htm

 

[SQLSister]

If you do parallel development, then make sure you havea good tool like Red_gate's SQl Compare to be able to see the differnces between the database.

AS far as optimization, start collecting information on what is happening now and start by optimizing the worse perfoming queries and stored procs. Also consider those procs which run often, a minor perfomance increase in a proc that runs thousands of times a day can gain more than a big performance increase in a quarterly report.

There are several books out there on performance tuning. I suggest you buy copies and read them before starting a project of this nature.

One of the biggest risks in this type of work is that increasing performance in one area might make it worse somewhere else. For instance, adding indexes improves query time but imcreses time to update insert or delete. Now that might be OK depending on how much time it increases. But it is something to check when testing changes.

Some common areas to look for are:
bad or non-existant indexing, out of data statistics, use of cursors, triggers that can be replaced with constraints, bad datatypes causing lots of extra work to convert them to what you need to display in reports such as sotring dates in varchar fields when you need to do data math on them, use of functions especially in a join or the where clause, use of wildcards in the first character of a search field (Causes indexes to not be used), use of IN where a derived table would be faster, use of dynamic SQl.

There's lots more, that's why you need some performance tuning books.

"NOTHING is more important in a database than integrity." ESquared

 

[BlueBeep]

Thanks for the great advice, people! I will look further into performance tuning. I do agree that Red-Gate has a great toolkit - it's why use daily. SQL Compare is a part of this project. JoeAtWork, the work we've been doing so far entails a 3-tier architecture.

The aim of this project is refactoring and optimization, through the use of schema changing, stored proc, function, etc. changes. Ideally, we would look at this from the business process level perspective, but that would take too long. It is a big database and doing a rearchitecture based on optimized business processes would be a lenghty project (2-3 years).

So, why do I bring up the db freeze? Because the vendor says it will take 3 months to wrap up his job (he's been working on this for a while now). In those 3 months, we will make many changes of db objects to the current database. Meaning that when the 3 months are up, we can't use that new db right away. We have to now make 3 months of changes workable on the vendor's database. What if then, it takes another 2 months (as opposed to 3 months) to apply those changes to the new database while there is still changes being done to the old database? Those 2 months or changes are implemented to the new database, and yet again it's not synchronized with the old because changes were still taking place.