July 14, 2010 at 7:08 am
I wouldn't be surprised if this came up as the user saying, "Hey, on this transitional screen would it be possible to display summary data," it got implemented, and then halted testing so nothing could be done until it was resolved. In those cases, an immediate quick fix to allow testing to continue is perfectly appropriate as long as there's follow up later. I recently dealt with a case like this where we had to hotfix several versions of our application because there was a field that could return two different values, one that was what the column header said and another one if the original value was 0 but that didn't always happen properly. Then there was a flag to say which was returned. It was a poor choice of how to handle it but since this was a critical hotfix and splitting the two values into different fields would have taken more time we just fixed the bug so it always returned corrected and then split the dual purpose field in an upcoming version.
The danger of things like this is that they never get revisited. Once the fire drill is over people frequently get pulled to other issues and the hack is left in because it works and something else is "more important." However, don't underestimate the importance of revisiting those hacks and trying to improve them.
July 14, 2010 at 7:33 am
Are you referring to the Liberace quote: "Too many young performers have forgotten that the most important part of show business is not the second word, it`s the first. Without the show there`s no business." ??
Nice article. Good points. Keep your head when under pressure, question what's currently being done, and consider all your options.
Thanks
July 14, 2010 at 7:47 am
Yes Mike, I was. Such a profound concept, a whole school of philosophy, from those magical six words, from a man you would never think of.
Funny that someone mentioned typewriters, I worked for Smith Corona towards the end of its run. If the owners could have thought outside of their box (they were a cement company), the onset of the PC revolution would have been delayed. The word processors we produced were little PC applicances, with a printer, spreadsheet, word processor and relational database all embedded (using an 8 bit microcontroller as the engine). We had Palm before Palm. Ah well.
And yes, we had form letter capabilities.
Oracle snapshots (now called materialized views) didn't have indexes built into it at the time (1998) when the base query included a join.
And as far as the title goes, je ne regrette rien...:-P
July 14, 2010 at 7:51 am
James Goodwin (7/14/2010)
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.JimFive
I find that they are almost never the same. I just finished up on some projects where they were all in sync, and they were among the most satisfying projects I have worked on in my 27 years as a professional.
July 14, 2010 at 8:03 am
While I like the out-of-the-box solution, I don't think the article lives up to the title, which includes, "How to Increase Query Speed". This article does not discuss that statement.
July 14, 2010 at 8:03 am
While I like the out-of-the-box solution, I don't think the article lives up to the title, which includes, "How to Increase Query Speed". This article does not discuss that statement.
How would this story have been different if the end users had answered the other way, that they wanted instant data?
July 14, 2010 at 8:15 am
nsmith2 (7/14/2010)
While I like the out-of-the-box solution, I don't think the article lives up to the title, which includes, "How to Increase Query Speed". This article does not discuss that statement.How would this story have been different if the end users had answered the other way, that they wanted instant data?
It absolutely does. The statement [font="Courier New"]SELECT * FROM view [/font]went from taking hours to taking seconds.
If the end users really needed "real-time" data, then the story wouldn't have been written.
July 14, 2010 at 8:26 am
Why go to all of the work of talking to end users when you could have simply used OPTION (GOFAST) ?!? Much simpler and, I daresay, more effective.
--
Adam Machanic
whoisactive
July 14, 2010 at 8:49 am
Stephen Hirsch (7/14/2010)
nsmith2 (7/14/2010)
While I like the out-of-the-box solution, I don't think the article lives up to the title, which includes, "How to Increase Query Speed". This article does not discuss that statement.How would this story have been different if the end users had answered the other way, that they wanted instant data?
It absolutely does. The statement [font="Courier New"]SELECT * FROM view [/font]went from taking hours to taking seconds.
If the end users really needed "real-time" data, then the story wouldn't have been written.
I'll argue that you didn't increase the speed of your original query, you changed the query to get it's results from a different source, the snapshot view. Without details, I can only assume that the heavy lifting from your original query was transferred to the creation of the view at midnight. How long does that run?
Further, by using the snapshot, you have not addressed the impact of updates to the data in the range of the snapshot. Those updates will also trigger copying original data states to the sparse file involved in the snapshot. Thus, I believe you missed the opportunity to discuss the snapshot in more detail and how it solved the problem. Nonetehless, I still like the fact that your article looks at solving the problem of long running queries from a different angle.
July 14, 2010 at 8:58 am
nsmith2 (7/14/2010)
Stephen Hirsch (7/14/2010)
nsmith2 (7/14/2010)
While I like the out-of-the-box solution, I don't think the article lives up to the title, which includes, "How to Increase Query Speed". This article does not discuss that statement.How would this story have been different if the end users had answered the other way, that they wanted instant data?
It absolutely does. The statement [font="Courier New"]SELECT * FROM view [/font]went from taking hours to taking seconds.
If the end users really needed "real-time" data, then the story wouldn't have been written.
I'll argue that you didn't increase the speed of your original query, you changed the query to get it's results from a different source, the snapshot view. Without details, I can only assume that the heavy lifting from your original query was transferred to the creation of the view at midnight. How long does that run?
Yes, although the "heavy lifting" is also carried out just once a day rather than every time the form is accessed. I'd expect that to be quite an overall saving.
Further, by using the snapshot, you have not addressed the impact of updates to the data in the range of the snapshot. Those updates will also trigger copying original data states to the sparse file involved in the snapshot. Thus, I believe you missed the opportunity to discuss the snapshot in more detail and how it solved the problem.
Could we be looking forward to a follow-up article? I'd put up my hand as an interested party....
Semper in excretia, suus solum profundum variat
July 14, 2010 at 9:09 am
I liked the article. The title reminds me of stage magic. I once saw an elephant disappear before my eyes. The magician brought out an elephant and a huge box (2 times the size of the elephant but it doesn't look that big because of the stage lighting). The elephant was guided into the box, the box closed. 8 assistants turned the box around and the box was opened revealing the elephant was no longer there. The box was closed again, The same 8 assistants turned the box around and opened it to show the elephant had returned. The audience was amazed and got the results they wanted to see...an elephant disappear.
The audience in your story was the users. Moving the query to a nightly process was like the hidden compartment in the box. They users like the audience got what they needed. It still took 8 assitants to move the box around. The only difference I can see in my analogy is that real magic could have been done in improving the query given enough time and resources which having been in similar crunches might not have been there.
Reading the comments from disappointed readers also reminded me that most people feel disappointed when the realize how the magic tricks work.
*edited for spelling and grammer errors.
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
July 14, 2010 at 9:09 am
I fall on the side that the content of the article is only loosely related to the title. (Is this that whole "loosely coupled" thing the SOA guys are always talking about. 😉 ) This was more of an anecdote with a moral: "...and remember boys and girls, the moral of the story is always listen to your users...".
I wonder if his boss recognized that he was essentially creating an operational data store? This is a great way to increase query speed but it's also a fairly significant architectural change and involves using a completely different set of data patterns. Re-architecture isn't usually in your standard query tuning toolbox.
I suppose the author could always repackage this story as an example of the power of operational data stores.
"Beliefs" get in the way of learning.
July 14, 2010 at 9:16 am
Stephen Hirsch (7/14/2010)
James Goodwin (7/14/2010)
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.JimFive
I find that they are almost never the same. I just finished up on some projects where they were all in sync, and they were among the most satisfying projects I have worked on in my 27 years as a professional.
I found your reply interesting. I also have found them pretty much never to be the same. Where we differ is that I love the challenge/puzzle of figuring out what the user really needs. I no longer let users distract me with details/requirements. I don't see it as their job even if they are capable of figuring it out. Instead, I find out what they really need (a special process that could be an article in itself) and then I design a system for them that fully meets their needs. I find that greatly fun and satisfying.
Judging by these comments, I'm sure people can guess that I think the article was great. From what I've seen time and again, there are plenty?/many?/most? of developers who do not have a clue what their users really need and end up creating sub-optimal systems that disappoint and waste people's time. Even if you do not like the particular solution to this problem, the message at the end of the article is a keeper.
July 14, 2010 at 9:36 am
"Could we be looking forward to a follow-up article? I'd put up my hand as an interested party...."
Thanks. Maybe (from my perspective), some rules of thumb as to when to go with the more normal technical solution (i.e., reorganizing the database storage, creating indexes, etc.) as opposed to going back to the users to question some assumptions.
The solution worked as well as it did because a) space was available for storing the query results b) there was no DML at all from 6PM to 7AM every day. We didn't worry about the data changing during the query execution.
Please don't misunderstand the article. This was a one-time fix for a one-time problem. It was technically beautiful because it solved human beings' problems so quickly and so easily by dumping the problem out of sight and out of mind.
What I find interesting here is how much it bothers many of you out there that the query was never optimized in the normal sense.
July 14, 2010 at 9:43 am
Nice article with advice on working patiently with customers. That is definitely one way to increase query speed; make sure that you have understanding of how the users want the data and write the query accordingly.
Viewing 15 posts - 31 through 45 (of 87 total)
You must be logged in to reply to this topic. Login to reply