Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: 27317 | Views in the last 30 days: 3
 
Related Articles
BLOG

Sales People & Commission

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

ARTICLE

Leave Developers Alone

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

ARTICLE

Personal Development

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

FORUM

Parameter selection causing screen refresh

Parameter selection causing screen refresh

BLOG

Linchpin People Partner

In April 0f 2012 I was fortunate enough to be asked to become Linchpin People LLC’s first ever TeamM...

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones