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


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!

Recent Stories
From Mainframes to Data Lakes

Scenarios for IBM z Systems-centric Data Lake Implementations

5 Things to Know about Avoiding a Data Swamp with a Data Lake