From “Death by Round-Trips” to “One and Done”

Odometer

An under-appreciated set of ANSI SQL functions introduced over a decade ago could dramatically reduce application chattiness and improve response times.

If tuning inefficient SQL isn’t tough enough on its own, DBAs face an even narrower set of options when the cause of user complaints is chatty behavior between an application and the database. As applications deliver increasingly diverse types of information to a single screen or web page, the overhead of running longer chains of SQL statements - even when they’re optimally tuned - can quickly add up to a noticeable delay.

The extreme case study illustrated in the article is based on real-world consulting engagements where we tuned an application’s SQL workload not by making the original statements run any faster, but by distilling them down to a surprisingly small set of super-queries (sometimes just one SELECT) that still delivered the necessary data.


The full article, including sample SQL, is available as a PDF download from the IDUG file library: From “Death by Round-Trips” to “One and Done”

1 Comment
1 Like

Great reading!

February 22, 2015 03:54 AM by Pete Suhner

Thanks Fred,

your article gives a very good overview of using XML functions providing an alternative approach for specific problems. I particularly like the fact that the topic is well explained and easy to understand.

Definitely highly recommended!
Pete

Recent Stories
XML, JSON and Schemas

DB2 for z/OS JSON Enhancements and use cases with Watson

Why pureXML Still Matters