Many of you probably know Martin Bach. He’s a colleague here at Oracle although we’ve both been good mates in the Oracle community long before either of us joined Oracle.
But here’s the issue. Martin is just a genuinely nice guy and always takes an optimistic glass-half-full view to the world.
So recently when he posted a cautionary tale about bind variables and their impact on performance, he concluded up the post with some results from a simple benchmark showing that not using bind variables could incur a 5x performance hit on your system (let alone the other memory and concurrency implications).
Here’s my theory: There’s the SQL injection risk and there’s the performance impact…but because Martin’s a good guy, he didn’t want to terrify you as a developer if you were reading blog and suddenly realised you were not using bind variables in your code.
But I’m not Martin 🙂 I’m a bitter twisted dude that comes from an island where the world used to send its convicts. No rose coloured glasses from this neck of the woods. We call it like it is!
Let me show just how bad things can get if you do not listen to Martin’s advice.
I’ll start with a simple demo where I’ll do 10,000 individual primary key lookups to a simple table. This little Java program creates and populates a table, and then builds its SQL query with concatenation of literals (and hence no bind variable use).
I run the program against my local database and got this:
$ java ParseDemo
Creating test table
Done
Querying table with individual values
10,000 iterations, 4665 ms
Throughput 2143/sec
and with thanks to the magic of modern computer hardware, we get over 2000 executions per second, which will make many a developer feel: “Well, that’s probably good enough”
Just like Martin did, I now swap in some code to replace the literal SQL queries with a bind variable version
and in my case, I got a 10x improvement
$ java ParseDemoBind
Creating test table
Done
Querying table with bind values
10,000 iterations, 407 ms
Throughput 24570/sec
At which point you’re probably thinking: “You have just replicated Martin’s findings. What is the big deal?”
But database applications are not simple things. We often do simple demos to illustrate a point, but when was the last time you saw a modern application just doing primary key lookups? They have views, and security predicates, and complex joins and all sorts of things that are needed for genuine enterprise level applications.
So let me swap in this new version of the code
What happens when the SQL statement you need to parse is a “serious” one? Lets take a look at the results
$ java ParseDemo2 20
Querying table with individual values
2138 ms
Throughput 9/sec
Welcome to parsing purgatory. And if you’re thinking that maybe we just need more “ramp up”, you can see that this does not help at all
$ java ParseDemo2 400
Querying table with individual values
39531 ms
Throughput 10/sec
10 queries per second! Can you imagine fronting up to your CEO and saying “Yeah, we only get 10 queries per second because I didn’t want to add a couple of extra lines of code to my Java app“
Let’s compare that to the bind variable equivalent
$ java ParseDemo2Bind 40000
Querying table with bind values
1940 ms
Throughput 20618/sec
You’re seeing that correctly – this is a 2000x performance jump!
I cannot stress this highly enough – All of that (excellent!) demo code out there on the interwebs proving that bind variables are 5x or 10x better than not using them were dramatically underselling the impact. I’m not criticising them for that – it is important to post simple, easy to reproduce examples in demos in order to keep things as simple as possible for readers to digest. That is how efficient learning is done. However, once people started thinking: “Hey, I’ll still get 2000 queries per second even without binds” then I decided it was time to set the record straight.
Do you want 10 queries per second or 20,000 per second?
Leave a Reply