Funny Developer Tricks – (substr(cust_id,1,length(:b1))

Ha. This one was a little surprising. I ran across a SQL statement that gets fired off hundreds at a time in rapid succession. Each execution taking several seconds – too long for thousands of executions in a row. The statement looked like this (cleaned up to protect the guilty):

b1 := '10355P034001SGL00066';
b2 := '10355P034001SGL00066';
 
select count(cust_id) 
from customers
where substr(cust_id,1,length(:b1)) = :b2;

What was the developer trying to do? How can we fix it? Your comments are welcomed.

Well it only took Jeremiah a couple of minutes. Yes – it looks like the developer had learned to use the substr function, but not the LIKE operator. Maybe the developer didn’t know you could use the same bind variable twice in the same statement as well. Or maybe the variables could have different values, but that seems unlikely. Anyway, here’s the stats for the before and after.

SYS@FYIDOCS> @fss
Enter value for sql_text: 
Enter value for sql_id: f0n7vkcdhqp3v
 
SQL_ID        PLAN_HASH_VALUE      EXECS ROWS_PROCESSED  AVG_ETIME    AVG_CPU    AVG_PIO      AVG_LIO SQL_TEXT
------------- --------------- ---------- -------------- ---------- ---------- ---------- ------------ ------------------------------------------------------------
f0n7vkcdhqp3v       182380728      9,933           9933       7.68       1.89      83.06       11,627 select count(cust_id) from customers
                                                                                                      where substr(cust_id,1,length(:b1)) = :b2
 
 
 
SYS@FYIDOCS> @fss                   
Enter value for sql_text: %test f0n7vkcdhqp3v_3.sql%
Enter value for sql_id: 
 
SQL_ID        PLAN_HASH_VALUE      EXECS ROWS_PROCESSED  AVG_ETIME    AVG_CPU    AVG_PIO      AVG_LIO SQL_TEXT
------------- --------------- ---------- -------------- ---------- ---------- ---------- ------------ ------------------------------------------------------------
4j1apzncj8dps       611149136          1              1        .00        .00        .00            8 select count(cust_id) from customers
                                                                                                      where cust_id like :b2||'%'

Leave a Reply