When I first picked up Spring framework, it felt like Magic. It does things which Struts does in ugly way! It made things look neat, and made an good impression for a first timer. Well it was like that and stayed for quite some time. But not all is fairytale, and we find it in hardest way possible!
When our company decided to stick with Stored Procedures even after explaining them about benefits in going with dynamic query, we had no choice but embrace the jdbcTemplate. Poor man’s ORM!! Picked up SimpleJdbcCall, added ParameterSource and execute, bam! it works. And we were all happy, we left it there and feel like done and dusted. But months after it’s over, the performance testing started. We use Oracle and they have nice and detailed report on what makes the application so slow! It wasn’t that slow, but for the kind of load we gave, it was slow. We tested with just over 100 concurrent users and run loop forever in JMeter, things started going south. Execution time, no of times, elapsed time all indicate one thing. A query is running in the background too often, which we can’t find anywhere in our code and SPs. Then after digging up SOF, we find that SimpleJdbcCall was the culprit.
Yes, SimpleJdbcCall like to query the metadata of the column details very often, so it sneaks past us and do it’s thing. So as mentioned everywhere, we had to add withoutProcedureColumnMetaDataAccess() for all SimpleJdbcCall objects. Pretty easy, right! Nope, not if you didn’t used declareParameters()! To save time, we skipped it at the initial phase of development, but it came back to haunt us. What irritated us most was, declareParameters expects the order to be same as we declare in Stored Procedures. Man, that is really fucked up. No shit! So we had to change them all to make it work and make the application perform better. After doing this up, the number of time the meta data query executes becomes less around 50%! But why does it expect the argument list to match?
So people, if you want to use SimpleJdbcCall, keep it in mind to do these things, so that you won’t regret using it later. Well, at least I didn’t have to change all the code and I pity the poor guy who did that! so beware of this ugly truth and act carefully.