We help IT Professionals succeed at work.

redesign strategies

anushahanna
anushahanna asked
on

We want to re-design a warehouse database that has too much redundant and unused data and also excessive amount of inefficient stored procs (many using cursors).(it is causing high refresh times and also more disk space usage)

The current DB is in SQL 2000. Is it best
1)try to redesign the database in the current schema, and rewrite the procs
2)or migrate to SQL 2005/8 and design afresh and write procs from scratch, after the schema changes are done.?

which one would you recommend? which would take more time to program and test? any other options?
Comment
Watch Question

It depends on how much time and money are available for the project.  Cleanup will be the fastest, which usually means the cheapest.  But if it NEEDS a re-write, then do it before it gets worse.

Typically you denormalize in a warehouse in order to reduce joins and increase performance of reads at the cost of storage of redundant data.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
Certainly upgrade to SQL 2005/2008 as soon as you can.  You can move the existing db and still redesign later.  This will give you more tools to do whatever you end up doing with the procs and other queries.

If you need/want to redesign the database itself, do that next, before touching any code.  Do your design **ignoring existing code, stored procs, functions, reports, etc.**.  The db design should initially be completely independent of coding / reporting needs.

Author

Commented:
Brandon, by Cleanup, do you mean option #1 (working with SQL 2000).

I understand denormalize and warehouse go hand in hand. But can it get to a point where the rate of redundant data is too high (for example, if the same data is in 11 places)

Author

Commented:
Scott,
if design is a must, then you would recommend a total overhaul, right?
*Move to New version of SQL
*Design
*Write efficient code
*migrate data
*test & fix
*point app to new db

am i missing any other significant piece
There in comes the question about what your time budget is.  How much time do you have to allocate the project?

Author

Commented:
right now, time (and hence budget) will get a priority as the management sees the value in this database. so please give a liberal recommendation, assuming that the time and budget will allocated, for whatever it takes..
Then design it from the ground up on 2008R2 for how it SHOULD be and then migrate your old data to the new server once new data is being loaded.
Racim BOUDJAKDJIDatabase Architect - Dba - Data Scientist
Commented:
<<which one would you recommend? >>
Chances are yo are goiing to do both is the performance is really poor.

Author

Commented:
Brandon, thanks for that tip. will recommend R2.

Author

Commented:
Racimo,
>>Chances are you are going to do both

why both?

>>is the performance is really poor.

not for the reports. the refresh time is getting longer. weekend refresh is 20 hours for the whole DB.
You should break down the parts of the refresh and see what takes the longest.  Once that is determined, focus on those areas first.
Racim BOUDJAKDJIDatabase Architect - Dba - Data Scientist
Commented:
<<why both?>>
Because both solutions address different aspects of performance that, based on your description of the problem, usually make a combination of the two approaches more effective.  Both approaches, redesign or migration, should be done on a per needed basis...IMHO

Hope this helps...

Author

Commented:
>>
You should break down the parts of the refresh and see what takes the longest.  Once that is determined, focus on those areas first.

OK. thanks Brandon.

Author

Commented:
Racimo,
>>Both approaches, redesign or migration, should be done on a per needed basis

Can you please give a example of how you would do them both on a per needed basis. I can understand the option #1 part - just touch up on a few tables, and then the procs those tables used, and then it is done for now - as needed basis.

but for option #2, it is all or nothing, right? only from start to finish, option #2 will be useful, right?- that is why it is costliest option, compared to option #1...
*Move to New version of SQL
*Design
*Write efficient code
*migrate data
*test & fix
*point app to new db

thanks for your valuable input.

Author

Commented:
>>You should break down the parts of the refresh and see what takes the longest.  Once that is determined, focus on those areas first.

Brandon, this is a temporay solution to the refresh, till the new design is done in the new version, right? this is what Racimo was suggesting to take both approach - one to temporarily give some relief to the process, and then find a more permanent fix for the years to come through proper new design?

thanks
That's one way to look at it.

Author

Commented:
Thanks Brandon.