Bad screen design, bad performance

Recently one of my customers had loaded a new version into production. Immediately performance issues have started to rise. I have spotted a new query that became one of the top-3 heaviest SQLs running in the instance. Certainly not something that can be ignored…

The query was something like:

Select distinct ….

From T1

Where code = :1

And ID like :2

Order by …;

This query was being executed 10k-20k a day, with average execution time 1.5 seconds, and had an intensive CPU usage.  That summed up to 4-8 hours a day of cpu usage.

Next thing I went to v$sql_bind_capture, to examine the values sent to ID parameter. To my surprise I saw that most of the time (more than 95%) application was sending the complete ID without adding "%" as I would have expect it to be since query is written with LIKE operator " and ID like :2" and not written with equal operation on ID. That looked very weird. Why using LIKE without using % , right?

As a result of this use of LIKE operator in the query Oracle couldn't apply an index unique scan, and forced to use an index range scan + sort unique thus became a performance issue.

Let's examine the facts again:

1.       The combination of (CODE, ID) is unique since a unique index was built on those 2 columns.

2.       Application rarely uses wild card "%" when passing values to ID. Most of the time they only send a complete number, i.e …and ID like '12345' , and appose to and ID like '12345%' , so in fact only one unique row will be returned.

3.       Query is using distinct and order by on the returned rows. In case no wild card is sent, both distinct and order by can be omitted from the query since only one unique row will be returned anyway.

From what I have seen it was clear to me that performance became here an issue due to some bad design of something. It looked like someone was trying to generalize a use of a query.

I thought to myself: If you want to send a complete number, use equal operator. If you wish to apply a wild card, use a like operator. Certainly do not use like for both cases.

Well, the time for a little investigation had come. After a little research with the application, it appeared there was a new screen allowing the user to search for ID. The screen has a combo box which its default value was set to "Begins with" (which sent the LIKE operator), so every time a user was searching for ID the same query with LIKE was sent in. User is passing 12345%, LIKE is being used (right). User is passing 12345, again, LIKE is being used (wrong).

I have asked the application to change three things:

1.       Split this query into two different queries. One with LIKE and the other with equal operator "=".

2.       Query with equal operator should omit DISTINCT + ORDER BY.

3.       Redesign the screen, so default combo value will be set to "equal" (as this was the major use of the users), and in this case query with equal operation will be used.

Query with equal operator should look like:

Select ….

From T1

Where code = :1

And ID = :2;

Indeed, after 2 weeks application had passed a fix to production and a performance relief could be noticed. The new query average execution time went down to 0.004 second (remember, query using LIKE had average execution time of 1.5 sec). And that summed up to only 40-80 seconds a day (as opposed to 4-8 hours when using LIKE).

Of course, query with LIKE operator is still being used, but only when really needed, and that is how it should be.  

To sum it all up, what we saw here was bad screen design which lead to bad performance…

There is no doubt on my mind. As I said before and will say again, DBAs should work together with application. DBAs should definitely participate during design phases. Otherwise this type of scenario would occur over and over again…

Merav Kedem,

Founder of

DB OPTimize

Oracle Performance Tuning & DBA Consulting

http://www.dboptimize.co.il/
merav@dboptimize.co.il