July 14, 2010 at 2:38 am
Great article Stephen.
Yes it does open a can of worms with regards to poor development, bad communication etc...but i still think it needs an article like this to remind people of this. I imagine there are still lots of DBAs out there working with poorly developed code and /or users who don't really know what they want until they have some options so good effort give them a nudge in the right direction.
Spot on with a page taking 3 hours to populate....a firing squad awaits such developers.
I often think one of the main roles of the DBA is to suggest efficient and practical solutions even if they don't match up to the developers/users expectations. Of course there must always be cooperation from all sides 🙂
Thanks
Graeme
July 14, 2010 at 4:02 am
Here's another story about bosses and optimisation...
A few years ago when I was a still slightly wet behind the ears developer my boss asked me to run a report for the finance department. He already had the SQL from the previous year, he just wanted me to change the dates, run it and send the results in a nice looking spreadsheet to the finance controllor making sure that they got it by 9am the following morning.
Boss went off to meeting leaving me to get on with it.
About an hour later my development leader was going home. He paused by my desk smirking "Good luck with that report, the boss was here till 4am last year waiting for it to finish running!"
I beckoned him over to my desk.
"Have a look at this email" I said.
He looked at my screen and read the email, it began:
"Dear Financial Controller,
Please find attached the report you requested...."
The moral of the story?
Don't spend your afternoons getting bored and watching the executing circle spinning round. Try making something better.
July 14, 2010 at 4:07 am
To answer some critics: yes, we could have done a lot of tuning. It was a development database that was being played with daily, the DBA was famously lazy, there were lots of things that could have been done. The concept of time shifting was not new. To use another movie analogy, in Raiders of the Lost Ark, Harrison Ford's character just shoots the guy with the swords, because Harrison Ford the actor had diarrhea at the time. It wasn't in the script, but it worked so well, it made it into the final cut.
I know that it bothers a lot of you that the same stinky query was running, just at a different time. It bothered me to. It's just that sometimes, our normal definitions of quality aren't really valid; the trick is to know when.
July 14, 2010 at 4:21 am
Hmm, quite a misleading title there. ***SO*** what would have happened if the previous nights data had not been acceptable? The DBA would have been responsible...:w00t:
Developer, DBA, Pre-Sales consultant.
July 14, 2010 at 4:38 am
Excellent article. Call it lateral thinking, thinking-outside-the-box or pure professionalism but it is a great holistic way to work
I think Mr Vidal, above, has missed the point somehow.
Regards,
July 14, 2010 at 5:06 am
Every critical post on this thread has focussed on how well or not the manager focussed on the technical aspect of the problem. Every plaudit has admired the redefining or sidestepping of the problem overall.
This was a real-world solution. The project needed to move on, and the business needed a solution. What the manager did was to recognise what needed to be achieved, then achieve it. The business pays for results, and that's what he delivered, even if (as could easily be argued), his solution needed revisiting later to perhaps come up with a better answer.
It's easy to forget why the jobs we have exist. We're not around to be technically excellent; we're around to meet business needs. Technical excellence is often an effective means of meeting those needs, but is not an end in itself. Technical excellence for the sake of technical excellence is only important in so far as it panders to the perfectionist in each of us.
OK, dismounted from high horse now. I'll go sit down in a corner and shut up again.
Semper in excretia, suus solum profundum variat
July 14, 2010 at 5:11 am
steve.vidal (7/13/2010)
That's the most misleading title I've seen in a long time...I was personally interested in the story because I was confronted with a nearly identical situation (Laboratory System with screen taking up to 20 minutes to load). Now in my story, I did improve the query speed by 3 orders of magnitude, not just swipe the problem under the carpet... 😉
How is this article going to help anyone actually trying to troubleshoot performance issues?:(
Many of the first personal computers were used as better type-writers. Still the same old typing pool; just quicker. That was a case of isolating a performance issue and improving it.
Pretty much all the letters I now get from my bank are automated. No typing pool at all. That's a case of redefining the problem.
This article will help people with performance issues by reminding them to always start solving a problem by going back to the most important question - "what am I trying to achieve?"
Semper in excretia, suus solum profundum variat
July 14, 2010 at 5:41 am
majorbloodnock (7/14/2010)
Many of the first personal computers were used as better type-writers. Still the same old typing pool; just quicker. That was a case of isolating a performance issue and improving it.
Pretty much all the letters I now get from my bank are automated. No typing pool at all. That's a case of redefining the problem.
You'll find that automated letters pre-date personal computers. Asking a typist to produce 1000 near identicle letters is a waste of a good typist!
majorbloodnock (7/14/2010)
This article will help people with performance issues by reminding them to always start solving a problem by going back to the most important question - "what am I trying to achieve?"
There's an expression for that - "Seperating the wheat from the chaff". For me this is not one of those situations.
In this case the boss had gone back to the end users and offered a compromise which was accepted. The data was then compiled overnight. I've seen this done in a lot of companies and it's usually either a temporary fix till they get time to rewrite the queries or its to accomodate older systems (RDBMS systems older than some of the posters on here) which literally can't cope with the load being placed on them.
July 14, 2010 at 5:53 am
I have not read all of the responses so perhaps someone else has pointed this out already but the title references "with no Indexes". However you referenced an Indexed View as the solution. This is a very good solution and one which I have recommended as well to others with such issues in response times for daily aggregated data. It is good to point this option out to those whom are not database professionals, but are thrust into the role of writing reports and querying large amounts of data, especially. However, an Indexed view a.k.a. a materialized view or a persisted view do utilize indexes in addition to being prepared in advance of the demanded use. An Indexed View has a Clustered Index and is said to be “materialized” which is just another way of saying it is not expanded at runtime.
July 14, 2010 at 6:00 am
Samuel Vella (7/14/2010)
majorbloodnock (7/14/2010)
Many of the first personal computers were used as better type-writers. Still the same old typing pool; just quicker. That was a case of isolating a performance issue and improving it.
Pretty much all the letters I now get from my bank are automated. No typing pool at all. That's a case of redefining the problem.
You'll find that automated letters pre-date personal computers. Asking a typist to produce 1000 near identicle letters is a waste of a good typist!
I agree, but their prevalence has burgeoned with the realisation of the breadth of possibilities personal computing has opened up.
majorbloodnock (7/14/2010)
This article will help people with performance issues by reminding them to always start solving a problem by going back to the most important question - "what am I trying to achieve?"There's an expression for that - "Seperating the wheat from the chaff". For me this is not one of those situations.
In this case the boss had gone back to the end users and offered a compromise which was accepted. The data was then compiled overnight. I've seen this done in a lot of companies and it's usually either a temporary fix till they get time to rewrite the queries or its to accomodate older systems (RDBMS systems older than some of the posters on here) which literally can't cope with the load being placed on them.
I disagree. You're right that this isn't a "wheat from the chaff" problem, but "wheat from the chaff" isn't what I was describing. "Wheat from the chaff" describes clearing away irrelevances, but the performance problem in the scenario wasn't chaff; it was still clearly pertinent. What I was talking about in the quote above is not getting so hung up on one course of action that you forget others might also fulfil your overall goal.
Semper in excretia, suus solum profundum variat
July 14, 2010 at 6:13 am
We've been working this way for 15 years. There is only upside.
July 14, 2010 at 6:15 am
I have to agree with Steve Vidal on his comment, although the scenario described is interesting and in that specific case, the solution was good, however, the title of the article was extremely misleading. Additionally, it seems as though there was alot of processing which needed to be done with that real-time data. Instead of attempting to increase the actual performance of the query, the query is simply returning older data which had already been processed.
No offence to the author, but disappointing article based on the title.
Brian Lovejoy
July 14, 2010 at 6:44 am
If this story is telling something new to any readers, then I really don't think they should be working in IT in 2010.
It might have been acceptable 20 years ago to be unfamiliar with what your users do with your systems, but in my world anyone, at any level, who does not stay very close to the users ALL the time now is out of a job fairly quickly.
July 14, 2010 at 6:54 am
Nice story and extra stars for this...
Spend some time with your end users, if possible. Get to know them as people, not just users. Find out what they actually do, and what they actually want to use your tool to do. Be patient with their lack of technical skill; remember that they probably know things you don't, and that the more knowledge they have about what you do, the easier your job will be.
Look at production data. If you get the chance to create prototypes, use production data in your prototypes.
If you're in a position of some authority, eliminate the distinction between "analysts" and "programmers". Set it up so that everyone does both.
Finally, always remember what Liberace said: without the business, there's no show.
July 14, 2010 at 6:58 am
I think the moral of the story is: What the requirements say the customer needs, and what the customer says that they need and what the customer actually needs, are not necessarily the same thing.
The boss did what good bosses do, he cleared an obstacle out of the way so the project could move forward. Was there a technical solution to the long query? Probably. But how much would it cost to implement the technical solution as opposed to the business solution?
In the real world, a good enough solution is usually good enough.
--
JimFive
Viewing 15 posts - 16 through 30 (of 87 total)
You must be logged in to reply to this topic. Login to reply