Not using binds .. banging an obsolete drum ?

We’ve all seen the mantra – “you should be using binds”.  And this is a demo that’s been rolled out for decades now, showing the performance overhead of not using them:

SQL> drop table T purge;

Table dropped.

SQL> create table T (x primary key) as
  2  select rownum x from dual
  3  connect by level <= 100000;

Table created.

SQL> set timing on
SQL> declare
  2    res int;
  3  begin
  4    for i in 1 .. 100000 loop
  5      select x into res from t where x = i;
  6    end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.61

SQL> set timing on
SQL> declare
  2    res int;
  3  begin
  4    for i in 1 .. 100000 loop
  5      execute immediate 'select x from t where x = '||i into res ;
  6    end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:58.72

And the conclusion is pretty straightforward. The parsing costs added nearly a 40-fold overhead to the execution time.

But in the middle of a discussion like this recently, someone responded to me: “Yeah, but I’m still getting 1700 executions per second.  That’s plenty fast enough for me”

And this is true…even with the overhead of parsing, we can still crank out a pretty good execution rate.

Which leads me to thinking that the demo, which we’ve used for so long, is actually flawed.  Because in reality, we don’t run queries that are “select from one_table”.  The moment you have any sort of real application code in play, there are joins, there are views, there are security predicates…things are a lot more complicated.

So what happens to our parsing costs when we look at something closer to real application code.  Here’s a demo joining two of the Oracle dictionary views, both of which have complex definitions.  Let me stress two things in this case

  • we are not even running the SQL like the demo above.  All we are doing is parsing the query
  • we are only doing 10,000 calls, not 100,000.  This is only 10% of the size of the initial demo.  Why ?  Well… see for yourself below

SQL> set timing on
SQL> declare
  2    c number := dbms_sql.open_cursor;
  3  begin
  4    for i in 1 .. 10000 loop
  5      dbms_sql.parse(c,'select * from dba_objects o, dba_segments s
  6                        where object_id = '||i||'
  7                        and o.owner = s.owner
  8                        and o.object_name = s.segment_name' ,
  9                            dbms_sql.native);
 10    end loop;
 11    dbms_sql.close_cursor(c);
 12  end;
 13  /

PL/SQL procedure successfully completed.

Elapsed: 00:39:33.15

That’s nearly 40 minutes of my server being eaten alive by parsing costs.  Not using binds when you think about real application code is probably going to kill your app.

But to be fair – in a future post, I’ll talk about not using binds Smile


2 thoughts on “Not using binds .. banging an obsolete drum ?

  1. Maybe the “traditional” reason for using binds is what’s out of date ?
    Yes, using bind variables does have a major beneficial impact on performance. However, you could argue that this is, in fact, only a secondary reason for using them.
    I’d suggest that the primary reason is security.
    Using bind variables pretty much guarantees your application against SQL Injection, unless you get rather creative with your application code.
    In light of this, maybe the discussion would be better framed as “You should use bind variables because it makes your application more secure, oh and by the way, faster” ?

  2. Both performance and security are great reasons to bind. As arguments, performance may be more persuasive because its benefit is more tangible.

    Connor, you are parsing in one session. Parsing requires access to lots of shared resources. I suspect that in a multi-user environment that hard parsing would prevent scaling and might even slow things down because of serialization problems.

    I would also add the “cloud” argument. If I have a machine on my premises, I don’t care if the CPU is used at 15% or 30%. Once I move to the cloud, doubling the CPU usage might double my cost. The closer we get to paying for the resources we actually use, the more management should understand the benefit of efficient code.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s