SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

How to Increase Query Speed With a Little Communication

By Stephen Hirsch,

Many years ago, shortly after I had first switched from embedded to relational database development, I saw my boss optimize performance so elegantly, so beautifully that it changed the way I approach the whole of development. He indeed figured out how to cut the running time of a query from about three hours to about 15 seconds without spending any time at all with the mechanics of query optimization.

First, some background. We were developing an integrated solution for managing a vaccine laboratory (using Oracle, but that is not relevant here). We were well along in the development process, basically starting the user acceptance phase. There was a very important screen that was taking about 3 hours to populate. Since this was a transactional screen, with performance like that the system was unusable.

Managers were starting to freak out. Important Meetings were being scheduled. The DBA was hauled onto the carpet, his explanations of "it's only a development instance" ignored. Diagnostics were being run, the database was in extents (in Oracle World, A Very Bad Thing (tm)), database monitors were flashing red. The project sponsor was called in.

My boss, a true genius, kept his head while others were losing theirs. He calmly went to the business people, the lab techs who were going to actually use the system. He asked a simple question: does the data populating this screen need to be current as of this instant, or would data as of the previous midnight be OK?

"As of midnight would be perfectly fine", the business people said. My boss then created a snapshot (an indexed view in MSSQL, now called a materialized view in Oracle) based on the query, to be run at midnight every day. The screen was now populated in 15 seconds, as opposed to 3 hours. The crisis was averted, the problem fixed, and everybody was happy.

Captain Kirk employed a similar strategy for the Kobayashi Maru game. For those non-Trekkies out there, Captain Kirk won a rigged game by changing the rules of the game. Unfortunately, we are programmed by school to think of that as "cheating", but I think it a shame that we don't do it more often in our lives of creating tools for people to use. All too often, we struggle with tasks that are not useful or are unimportant for the people who actually use our work because it is written somewhere, or because we learned the "proper" way in school.

In addition, all too often, we only talk directly with our users after experiencing a lot of drama. Then, once we sit together, we find out what they really need and want and then we slap our heads and say, that's easy!

That's a game no one enjoys playing. Here are some tips to avoid getting caught in that trap.

  • 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.
Total article views: 27354 | Views in the last 30 days: 1
Related Articles

Power BI Screen Reader Accessibility

I recently wrote a post on the BlueGranite blog called Improving Screen Reader Accessibility in Powe...


Sales People & Commission

I saw this post by Neil Davidson about sales people being different that discusses how sales people ...


Leave Developers Alone

Interruptions are the enemy of productivity for creative people, like software developers. Steve Jon...


Personal Development

Steve Jones thinks that a personal development plan is important and talks today a little about how ...


Did My Dev Changes Get to Production?

One of the things that people don’t think about often is whether the changes made in development act...