The Sweet Spot for Solving Data Layer Problems

Photo credit: Rakieta tenisowa - @kisspng

There is a sweet spot for solving data layer problems. Some days it feels like that is the only thing that has kept me gainfully employed for decades.  It lies somewhere in-between (and across) DBA, Business Analyst and Application Developer roles.

(The proceeding story is fictional and should not be construed as depicting actual persons or events, even though it does.)

Harried Project Manager pings me: Thingamajab X runs waaay too long and breaks half the time. I know you have never heard of this system before, but can you take a quick look?  Formal testing starts in 23 hours and we're getting nowhere fast.

DBA: adding an index on lbrslt_code will provide a 12% reduction in I/O compared to the current concatenated index range scan.

Application Developer: I reviewed the hibernate config and can change the JDBC fetch size from 10 to 100 and expect average elapsed time for this query would decrease by 20%.

Project Analyst: I noticed that the result should represent the most recent 24 hours of activity, but it looks like we are doing a little extra work and also including all the way back to midnight of the previous full day. We should be able to reduce the size of the data on average by 33% and make everything that much smaller and faster by fixing the time filter. This process just calculates the average execution time of Jabberdejibbet X by study by day, but it takes over an hour for the query to run. I don't understand why it runs so long.

Looking across, one can connect the dots:

Taking a look at the SQL Plan and table structures; yes, the query could use a more efficient index, but most of db wait is on a range scan inside a nested loop that scans half a billion rows just to throw most away and return a few rows to the client. The final result set returning to the Web App is less than 2k rows, so most of the work (and wait) is in the data layer. Any middle-tier tweaks will be "shaking the short end of the stick".

Knowing that we are concerned with only the last 24 hours of data and looking at the structure and content of these tables, I can convert the outer join to an inner join, rewrite the SQL to completely exclude the "Spamalot" table and filter much earlier in the execution path and on a completely different column.

Thinking through what the SQL specifies and knowing what is in these tables, it includes failed executions in the average execution time, which skews the result pretty badly. Knowing what the customer is trying to do, that is probably unintentional. I chat with the analyst, and then the customer and confirm that this is indeed the case. I add another predicate to the query to filter out failed executions.

The new SQL does 20 thousand instead of 589 million logical reads, is now 129 times faster, returns in 1.2 seconds, and returns a measure that no longer is skewed from failed executions.

A little voice in the back of my head (the repressed architect) pipes up: what if these tables were restructured to completely avoid this ever happening? And... it would be so much simpler to create a really clean instrumentation layer with an API all Web apps on our platform could leverage to address a whole class of queries instead of this single case. It would establish a pattern that would completely avoid all this unnecessary pain.

Well? Why not? A little voice in the back of the architect's head pipes up "Grant me the serenity to accept the things I cannot change, the courage to change the things I can, and the wisdom to know the difference."

Well.. back to the topic at hand ....  so, how to get into the data layer sweet spot?

Photo credit: Justbatreviews.com

• understand the current code
• understand what the technology is capable of
• understand the physical data model
• understand the logical data model
• understand the business need

I often get called in to troubleshoot performance or functionality snafus with unfamiliar systems, so have accumulated a hodgepodge of tools and techniques for quickly getting into the sweet spot. There is no "easy button", but there are some shortcuts. Maybe those would be good topics for a future posts.