| Author |
Topic |
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
 Posted - 2003-12-09 : 08:47:36
|
Anyone have a good link/article for how to approach a horrendous sphagetti mess of a system, when trying to find somewhere to start, to try and improve performance, standards and design ?Some more info : I have a 740 table database (with 3 RI links in total), several thousands of SQL statements in dynamic SQL, stored procedures, triggers. Some of these stored procedures, like the current one I am looking at (ask for the SQL if you dare) has 33 table join (15 of them left outer) UNIONED with another 33 table join. It is a 180 gb database on SQL2K SP2 + slammer, btw. Documentation, especially high level "system wide" doesn't really exist - different people know "their parts" of the system.nayhting other than sympathy/recommendations to run away ? *#&#* *#&#* *#&#* *#&#* Chaos, Disorder and Panic ... my work is done here! |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-12-09 : 09:16:08
|
I'd say you should start at at least $60/hour ....  Jay White{0} |
 |
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2003-12-09 : 09:31:31
|
| Thanx Page :-) $60/hour = R384/hour ... hmmm - thats is quite a bit more than I get charged out at by my company, and a WHOLE lot more than I get ! *sighs**#&#* *#&#* *#&#* *#&#* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-12-09 : 10:31:58
|
| I think (only rarley) that I would set up a trace using profiler for an extended period.Then load the file in to a table and do some analysis to see which statements take the longest to improve performance...However, is it not effecient, is anyone complaining?If not, leave it alone.If somone complains about x, investigate x...And remeber, you're getting paid by the hour....Brett8-) |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-12-09 : 12:36:02
|
| I think Brett is bang on target. If you want to figure out what's going on, you might want to load the application and database on a test box. Then run profiler and walk through the application. That way you can see "when I click this, this big ugly SQL gets executed" so that you can optimize and learn the application.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-12-09 : 15:03:56
|
quote:
Originally posted by MichaelP
I think Brett is bang on target.
What, about the "me not thinking too much" part?Yeah well....it hurts too much...And...7 MORE TO GO MichaelBrett8-) |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2003-12-17 : 05:26:09
|
Thanx guys ...That is roughly the approach I am following now. Sadly (though not unexpectedly, given the description I gave you  ), transactional analysis doesn't exist. OS I am having to review something, make a whole host of recommendations - SQL and structure, and then wait to see (a) if it gets implemented (although the more success stories I get, the more attention I get when I make a suggestion  ), and (b) wait to see whether the indexes I add here impact anything else. Without proper TA, and at least a CRUD matrix, it is currently impossible to know what else uses a table, and which inserts and updates and deletes might be negatively affected. There transactions can be scary - the last 2 I reviewed had 137 and 139 seperate SQL statements in them.Still, when I consistently get 90 to 99% improvements, I guess it will continue to get better and better.Oh - and there are tons of performance complaints :-)*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2003-12-18 : 10:30:21
|
| Where to start?.....with the smallest and easiest to fix procedure which the most important user in the company is complaining about! 1 good success will buy you the respect to be able to dictate your own rate of progress afterwards.Start eliminating with the cursors, table scans, etc....the simplest approach may be to get the existing system going faster....medium/long term is the database restructure that you may require. |
|
|
|
|
where to start ....