| Author |
Topic |
|
Lumbago
Norsk Yak Master
3271 Posts |
 Posted - 2011-05-20 : 03:53:19
|
| I'm currently working as a consultant for a customer to do some reporting stuff, but since most of my work is done before the end of the contract period I've also done some dba-stuff reviewing one of their central systems which is provided by a 3rd part vendor. This central system has a few data transfer jobs which use cursors and temp-tables to the extent that the entire system goes to a grinding halt every single time they're run. I've suggested they remove the cursor/temp-table related stuff and even rewritten some of the procedures and sent it to the vendor, but instead of reviewing my changes they instead advice the company to do "housekeeping" and delete old data. Would you as a consultant give your advice once and shut up, or would you keep trying to make the vendor change their opinion and maybe teach them a thing or two in the process so this might improve in the future?- LumbagoMy blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/ |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-05-20 : 04:00:48
|
I would stop at this point.I do not have to interfere because it is a special relationship between the customer and the vendor.You are not asked to save the world 
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2011-05-20 : 05:25:07
|
| I agree with webfred, though I am not a consultant, you might spot the sort of things you came across but it's up to the company to pressure the supplier - perhaps using some of the information you gave. Of course if you had been paid to look at performance it might be a little different, though even then the company could choose to ignore your advice.-----------If debugging is the process of removing software bugs, then programming must be the process of putting them in. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-05-20 : 07:24:42
|
Out of dozens, I've dealt with exactly one third-party vendor that appreciated advice on tuning SQL...and they wrote pretty damn good SQL already. They are the only vendor I've ever seen who had foreign keys in the database.Good coders can be helped, bad coders can't, and should be avoided. I agree wholeheartedly with webfred, you can't save the world.But you CAN profit from fixing their dumbass customer's performance problems.  |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-05-20 : 07:55:53
|
| Remember that the system has to be maintainable - which means that the people involved have to be able to understand it and consideration given to new people taken on.There may be other considerations that you don't know about - budgets, conformance with other systems maybe on other platforms, ...You've pointed out the issues and how you would fix them which you have a duty to do. It's up to the client whether or not they folow your advice (note the vendor is not a client of yours).The main question is how much of a problem is it causing. That will determine how much effort, time and cost they are willing to spend. Depends a bit on your relationship, you can keep bring it up whenever it is mentioned and ask why your advice isn't being taken but there's not a lot else you can do. At some point this will probably become important and the vendor might then produce your suggestion as their own work.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-05-20 : 07:59:16
|
| I appreciate the advice fellas and even though it's gonna be painful as he** I'll try to keep my mouth shut. And unfortunately for me, Nigel, I'm not self employed and this 3rd party vendor is in the UK so by offering my services I'm facing quite a commute :)- LumbagoMy blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/ |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-05-20 : 08:04:02
|
| Are you independent Nigel? Maybe I'll PM you the company name :)- LumbagoMy blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/ |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-05-20 : 08:10:37
|
| I am but I'm trying to retire. Currently just agreed to extend up to the end of next month.You can ask them if they want to talk to me but it sounds like they are happy with what they are doing.I tend not to consider things unless they are new or the client would consider a major revision/rewrite of the area.It's using cursors so I'm safe knowing that it's not one of my old systems :).==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2011-05-20 : 10:47:13
|
| The problem with vendors (or anyone else) not reacting well to criticism of their processes is so common that I actually expect it.I was recently asked to look at a process because it was all of a sudden running much longer that expected. What I saw was nested cursors processing two different tables containing columns with delimited lists of data that they were using a UDF to pivot into a table and then joining together to load for a system conversion.It had been taking around 35 hours to process with about 7 million rows in each input table. That was OK for them to be able to finish in a bank holiday weekend, but the run time had jumped up to about 6 days so they were concerned that they could not finish without shutting down the business for several days.They were hoping that I could point to some magical server setting that would get it back down to 35 hours.My suggestion of re-writing the process to eliminate the cursors was not well received, and they basically stopped talking to me about it, so I don’t know what happened. That was fine with me, because if they knew what they were doing, they never would have used cursors, so I was afraid they would try to dump that on me.CODO ERGO SUM |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2011-05-20 : 16:13:19
|
| Same old song...IO have NEVER Seen a thrid party vendor app that was not a total piece of garbage..reserved words as column names, cursor out the butt...advice like housekeeping to remove "old" dataPoor performace every single timeAnd YET, the Business people never stop buying this junkWe had a head of HR who reported to the chairmain bring in 3 different consultants over 4 years to provide them with a tool to do Succession planning...millions of dollars spent, many lives lost...then they called us....with the woman saying, if I can't get this in, I'll be firedWe got a whole we based app installed in 6 weeks for phase 1...and then there was rejoicing in the street....the HR Business people were completely painful...and ignorantBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxWant to help yourself?http://msdn.microsoft.com/en-us/library/ms130214.aspxhttp://weblogs.sqlteam.com/brettk/http://brettkaiser.blogspot.com/ |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2011-05-20 : 18:26:00
|
quote:
Originally posted by Lumbago
I appreciate the advice fellas and even though it's gonna be painful as he** I'll try to keep my mouth shut...
You don't have to stay completely quiet about it. Next time one of the frustrated business users complains, just casually mention that you already told the vendor about simple ways to fix it and they refused. Then let the users complain to the IT people and demand to know why it isn't getting fixed.Pressure in the right spot moves mountains.  May not help you land that next contract extention, though.CODO ERGO SUM |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2011-05-20 : 20:14:44
|
| Unfortunately this is all too common.. when you are a DBA (Fulltime/Vendor) and you notice issues and alert the management they ignore you because things are working fine.. unless it really starts to bite them in the rear they dont take it seriously enough.. and when things do hit the fan theres a scramble.. you cant even say "I told you so".. so unless you are a DBA manager and/or have the authority to make decisions to optimize the system.. most likely, as much painful it is, you are just a spectator.. the best thing in your case is you learn what NOT to do.. and your curiosity will help you grow as a DBA.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-05-20 : 22:21:06
|
| My first ever VB contract and my introduction to sql server and relational databases - someone showed me ho wto load query analyser.A c++ developer had written a module whci ran a query - for ever row returned it ran another query and for every row ran another query and iterated through tose rows. It kept track and produced a value to update the row from the first query. Had to be coded in C++ because VB couldn't possibly be fast enough.It took about 15 hours to run on a fairly small data sample.I recoded it in VB calling a single update statement which took about 20 seconds.That was the start of the end of of my conversion from assembler to high level language development - everywhere I went I seemed to be forced into database work.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2011-05-23 : 10:42:03
|
quote:
Originally posted by Michael Valentine Jones
quote:
Originally posted by Lumbago
I appreciate the advice fellas and even though it's gonna be painful as he** I'll try to keep my mouth shut...
You don't have to stay completely quiet about it. Next time one of the frustrated business users complains, just casually mention that you already told the vendor about simple ways to fix it and they refused. Then let the users complain to the IT people and demand to know why it isn't getting fixed.Pressure in the right spot moves mountains. May not help you land that next contract extention, though.CODO ERGO SUM
Totally agree with this approach, amazing how many times this works.. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-05-23 : 15:41:21
|
| It actually seems like they are coming around now...not the vendor but the client. I ran a procedure today that previously was unable to complete in more than 2 days. After a bit of tuning (spent roughly an hour removing the cursors + temp tables and making sure functionality was the same) it completed in 6 minutes. Seems like it got them thinking :)- LumbagoMy blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/ |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2011-05-23 : 16:16:35
|
quote:
Originally posted by Lumbago
It actually seems like they are coming around now...not the vendor but the client. I ran a procedure today that previously was unable to complete in more than 2 days. After a bit of tuning (spent roughly an hour removing the cursors + temp tables and making sure functionality was the same) it completed in 6 minutes. Seems like it got them thinking :)- LumbagoMy blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/
When you can re-write a procedure in 1/50th of the time that it took the old version to run and the new version runs in 1/500th of the time it took the old version to run, that's a good sign that the procedures need a little tuning.That's going to make the vendors's advice seem a bit lame. Now the vendor can take you new procs, and include them in their next release as "performance enhancements". CODO ERGO SUM |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-05-24 : 02:08:00
|
| I guess this is one of those times my paycheck should have been 10 cents for every second shaved off the average execution time. ((60 x 60 x 24 x 2) - (60 x 6)) / 10 = $17244 for an hours work - LumbagoMy blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/ |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2011-05-24 : 03:21:58
|
quote:
Originally posted by Michael Valentine Jones
Now the vendor can take you new procs, and include them in their next release as "performance enhancements".
And you can't charge them either... Been there plenty of times.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
|
Advice..