Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2004
    Posts
    4

    Question Unanswered: Database Optimization

    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!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2008
    Posts
    29
    @jessn:

    Like r937 said, you might be spending resources in a project that is doomed before it starts... If you vendor's modifying the database I guess it would be better to wait for a stable database.

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    you guys forget that sometimes we must take some action so that we can state that we are taking an action.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    My, you do get around. I've seen this same question on Coding Forums, then on SQL Server Central! I'm sorry that it has taken me so long to respond, but some personal issues came up yesterday that prevented me from responding here.

    Probably the largest single pitfall would be tarea plagio which is a common problem in this situation. This can be most effectively mitigated by early detection and remidiation. If you don't deal with it reasonably promptly, the problem will expand to the pont that it consumes not only this project, but others as well.

    You need to work with your supervisor or even the vendor to understand what is changing, and why it is being changed. Ask your manager if they understand what is being changed by the vendor and what purpose those changes serve. You need more experience before you try to lay out plans like this, so now is when you need to realize your limitations and go to your supervisor or management for help.

    Talking with the vendor is probably the best way to approach this kind of problem. Communication is the key to dealing with this type of problem, there is no technical trick that will replace understanding what is happening and why it is happening. There are many things that can be done, but the vendor can help your company far more than any third party can help you with this kind of problem.

    Your best source of information is probably one of the senior folks within your organization. They should understand what the vendor has in mind, and if necessary they can ask the appropriate questions of the appropriate people.

    -PatP

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Pat Phelan
    I've seen this same question on ...
    your two, plus this one of course, and three others that i know of, makes 6
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm not quite as well travelled as you and Jessn are. Sorry, I guess I lead a sheltered life.

    -PatP

  8. #8
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313

  9. #9
    Join Date
    Sep 2004
    Posts
    4
    Quote Originally Posted by Pat Phelan
    My, you do get around. I've seen this same question on Coding Forums, then on SQL Server Central! I'm sorry that it has taken me so long to respond, but some personal issues came up yesterday that prevented me from responding here.

    Probably the largest single pitfall would be tarea plagio which is a common problem in this situation. This can be most effectively mitigated by early detection and remidiation. If you don't deal with it reasonably promptly, the problem will expand to the pont that it consumes not only this project, but others as well.

    You need to work with your supervisor or even the vendor to understand what is changing, and why it is being changed. Ask your manager if they understand what is being changed by the vendor and what purpose those changes serve. You need more experience before you try to lay out plans like this, so now is when you need to realize your limitations and go to your supervisor or management for help.

    Talking with the vendor is probably the best way to approach this kind of problem. Communication is the key to dealing with this type of problem, there is no technical trick that will replace understanding what is happening and why it is happening. There are many things that can be done, but the vendor can help your company far more than any third party can help you with this kind of problem.

    Your best source of information is probably one of the senior folks within your organization. They should understand what the vendor has in mind, and if necessary they can ask the appropriate questions of the appropriate people.

    -PatP
    Thanks PatP. Been doing all the things you just proposed and I agree with you 100%! To you and everyone else: this project was started about a year ago, then it was put on halt. Now it's resurrected and I inherited and wanted to get various people's take on it.

    The big issue right now is that once the vendor is done with this new optimized database, this new database won't be ready for production because it will be outdated. The company still wants to continue development, but it has to be on the old database. The vendor will be missing the changes that took place from the time they took a snapshot of the database to the day the consider it done.

    This brings the idea of a code freeze - we stop all new development / changes and wait for the vendor to fully catch up so that once they deliver it / tested it, then and only then, we continue developing on this new database.

    Also, in regards to my mass posting - I want to get different perspectives on it. Not all people are subscribed to the same list of forums.

  10. #10
    Join Date
    Sep 2004
    Posts
    4
    Thanks for the great advice, people. 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.

  11. #11
    Join Date
    Apr 2008
    Posts
    29
    I guess the code freeze will give you an oportunity of sit down with the vendor, and all your staff and prepare that migration.

  12. #12
    Join Date
    Apr 2008
    Posts
    2
    Assumption 1: This vendor has vast experience or you wouldn't trust them for such a mass change. This sounds like a massive replacement rather than the usual series of tweak-and-measure.

    Assumption 2: Given assumption 1 (they're experienced at this); a large part of this experience must involve implementing the changes.

    Assumption 3: Surely their experience has taught them to insist on adequate communication and implementation planning. Are they?

    I'm not a DBA, but my performance tuning has always consisted of small steps. The nature of SQL is that most properly written queries remain backward compatible after a change is implemented. Compounding more than a few changes in a single implementation seems exponentially more likely to fail. I can understand your drowning feeling.

    Everyone here assumed that you must be some programmer sticking their nose into things. Hearing that this is your project and responsibility seems contrary to your apparent lack of confidence and game-plan.

    I would stop the presses and insist on seeing a workable implementation plan from the Vendor that your department will cooperate with. Short of that, it's doomed.

    I have to wonder if an incremental implementation is feasible.
    Last edited by BL515; 04-10-08 at 13:57.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •