Toad Turnpike: Real Stories from the Road

Friends in High Places

I’m starting a blog series about our Toad solution family, and how it’s changed lives for almost 20 years now.  Sometimes a vendor’s product does something extraordinary for its customers.  The stories you read in this series are real stories about real people doing real things. 

A few years ago DBA Bethany (not her real name) calls me on the phone.   This is unusual.  As a solutions consultant, I usually do the calling out to customers, not the other way around.   Bethany’s company had purchased Toad a few months ago, and she was just now getting into it.  Toad has been identifying for her queries that consume heavy resources on her database. 

One of those queries she knows well.  It’s a query coming from an application they inherited a year prior when her company purchased a smaller company.  The query executes twice a week for over 13 hours each time.  Not only does it consume disk and memory resources, but the query lengthens their weekend batch window to the point where weekend batch processing could start spilling over into normal business hours.  They’ve known about this query since day one, but no one pursued any tuning on it because “it always runs that long” according to the acquired owners of the app.

But Bethany’s voice right now sounds like a crackling high-voltage line.  She is excited.  Toad had done something better than merely drawing attention to this bad-boy SQL.  In two minutes’ time, Toad had discovered about 20 different alternatives for that one 13-hour query.  Of the 20 alternatives, one yielded the same data as the original 13-hour query but took only 16 minutes to complete.  Consistently took 16 minutes. 

Bethany has good reasons to be excited.  For one, the quicker query alternative shortens their batch processing window on weekends, allowing more buffer time to recuperate from occasional hiccups in weekend processing. 

Suddenly, the Support team is Bethany’s friend.

Secondly, she is saving expensive resources on the database server:  32 CPU- m I n u t e s   vs.   26 CPU- h o u r s per week.   And that’s just for that one query. 

Suddenly, the guys on the Operations team are her friends.

Thirdly, Bethany’s tuning efforts saves money for the application owner on the business unit side because the BU will be charged 25 CPU-hours LESS per week for the application’s processing time.  She is saving the BU internal chargeback dollars. 

The application owner is now her friend.

Bethany’s a hero.  But that’s not why she called me.   The REAL reason for her excitement—as if being dubbed a hero and being everyone’s friend isn’t enough already!—came when Bethany continued her SQL tuning efforts during the few weeks that followed, finding more and more consumptive SQL code, and tuning the “bad boys” as she calls them.  

So today, she tells me in a delightful squeal that she just saved her company over $40,000 hard cash.  How does a product like Toad return 20 times more money than she paid for it?  

Bethany’s IT team had known for a while that the database server platform was consistently edging toward dangerous load levels, frequently peaking at 95% CPU capacity and more.   High time to upgrade the database platform with a more powerful server, and maybe better disk, wouldn’t you say?   But Bethany’s flurry of tuning efforts made it apparent that running well-tuned, optimal SQL code was yielding improved throughput capacity on the database server.    So much so, that a decision had just been made by IT management yesterday to pull back a pending purchase order (remember that server/disk upgrade we mentioned?).   Turns out that Toad is helping Bethany’s company to leverage more of their current computing investment.   And that got the attention of the company’s CEO.  

Suddenly, Bethany has friends in all the right places.

About the Author
Gary.Jerep
As a Software Consultant within Quest, Gary Jerep has over 16 years of experience assisting DBAs, Developers and Analysts with relational database needs. Prior experience includes systems design and analysis...