Tuesday, July 29, 2014

A Little Bit Design Will Make a Huge Difference

This seems too simple to happen, but it happened in my workplace.

I was reported/complained the database is slow in a load testing. After checking the top queries in database, one statement was identified out easily simply because it was resource heavy it was executed tons of times. The purpose of the statement is to randomly pick a student from a huge student table. It used order by dbms_random.value as a way to randomly pick a record.

The statement is like  this:
--this will sort almost entire table in order to pick one record
select xxx from
(SELECT xxx FROM xxx WHERE xxx ORDER BY dbms_random.value)
where rownum=1;

--rownum=1 is equivelant to rownum<=1. for other values than 1, < and <= are valid operators but not =

In that load testing, it needed to perform testing for 5000 students, and it was designed as a way like for each student, it retrieved a student randomly by executing the statement and then performed corresponding operations on it, thus, 5000 execution of this statement was a result.

One important thing to point out is retrieving student is not part of the testing. But this retrieving represents most of the database load during the testing, this fact itself made the loading testing very inaccurately to reflect the real resource usage of the normal operations.

Without trying to tune the query, I suggested the developer to either prepare a list of student randomly before doing the load testing, or keep a local list of student by invoking that statement just once, and then loop through the list for rest of testing.

The second proposal was adopted because they like to retrieve student as part of the testing, even this would make extra load to the real testing. Retrieving 5000 students is actually the similar cost as to retrieving one student by converting the statement to like where rownum<=5000, but it avoided 4999 times of extra invoking so that the testing would much better reflect the real stress to the systems.

One little  extra thought made the application more reasonable and yet another example of tuning application should happen first before tuning database.


No comments: