Wow! That's quite a bit of useful information.
I'm a bit inexperienced in this arena but wanted to voice my thoughts
anyway. It seems to me that much of the design of DBI was borrowed from how
Oracle OCI handles things and thus it is probably the case that placeholders
are a very streamlined implementation when using Oracle and that perhaps
with the other DBD drivers it has not been possible to implement them quite
as efficiently.
How do you think similar tests on Oracle and/or other drivers would go?
> -----Original Message-----
> From: Michael Peppler [mailto:[EMAIL PROTECTED]]
> Sent: Friday, March 02, 2001 11:48 AM
> To: [EMAIL PROTECTED]
> Subject: DBD::Sybase, prepare, placeholders and stored procs
>
>
> I have come forward several times on this list advocating the use of
> stored procedures over straight SQL (with or without
> placeholders). I'm convinced that for a large class of applications
> using stored procedures (at least stored procs that have Sybase
> semantics) is very beneficial in terms of overall architecture and
> application design, but I hadn't done any timings to actually measure
> the difference of using procs versus straight SQL.
>
> So I did a little test.
>
> I did the same simple query with straight SQL (prepare/execute/fetch/...),
> with placeholders (prepare/execute/fetch/execute/fetch...) and with a
> stored proc (prepare/execute/fetch/...).
>
> The basic query is a three way join between a "market" table, and
> order table and a transaction table:
>
> select m.cl, o.ord_seq, o.account, o.broker, t.type, t.quantity,
> t.price, t.comms, t.fees
> from excalibur..market m,
> excalibur..orders o,
> excalibur..transactions t
> where o.commodity = m.commodity
> and o.date = m.date
> and t.ord_seq = o.ord_seq
> and m.commodity = ?
> and m.date = ?
>
> The market table has 1.4 million rows, and the others have a couple of
> 100k rows each, properly indexed. The query is guaranteed to never
> return any rows (I'm not interested in fetch times - only
> prepare/execute). The server is ASE 12.0 on a Solaris 7 box (an ultra
> 1/140) and the test script is run on a PII/450 linux system (with the
> beta 12.5 OpenClient package, though I doubt it matters).
>
> I wanted to test two things. First the overhead of running a prepare
> with ? placeholders compared with a normal select or stored proc
> execute, and second what the gains are when running multiple execute()
> calls for a single prepare(). A third test would be to test
> placeholder perpare concurrency issues on the server, but I haven't
> done that yet (the 11.0.x servers are notoriously bad in that respect
> due to contention in tempdb).
>
> The version of DBD::Sybase I used also has an experimental feature
> that lets you use ? placeholders for exec proc statements and
> transforms these to RPC calls instead of language calls, so I also
> tested that.
>
> Timings are done using Time::HiRes in the client, on an otherwise idle
> system.
>
> First a basic run (a single iteration to query one "commodity" value)
>
> SQL: 0.081944
> Place: 0.052600
> Proc: 0.012829
> Proc2: 0.009929
>
> (where proc2 is an execute with placeholders).
>
> I was actually surprised to see that the placeholder prepare is
> actually *faster* than the regular prepare - I don't really understand
> how that is possible, but, well, here it is...
>
> Next, do 30 iterations of a single prepare/execute/fetch block:
>
> SQL: 1.296186
> Place: 0.856850
> Proc: 0.201748
> Proc2: 0.162390
>
> Again placeholder prepares are faster than normal SQL, but stored
> procedure requests are *much* faster than placeholder prepares.
>
> Now, do 1 iteration with 40 different values being passed to execute():
>
> SQL: 2.652391
> Place: 0.247891
> Proc: 0.258102
> Proc2: 0.193621
>
> The parsing overhead with stored proc execution is starting to become
> somewhat significant. This is where the experimental exec with
> placeholders (proc2) makes a difference as there is no SQL parsing
> involved (just a little parsing in C in DBD::Sybase.)
>
>
> Conclusion (for now): placeholder prepares are surprisingly fast (at
> least with recent versions of Sybase), with the potential caveat of
> system resource contention if you have a situation with a large
> number of clients (typically - a web site). Using stored procedures
> is faster, significantly so if the number of executes per prepare is
> low (< 10 or so).
>
> Comments are of course welcome.
>
> Michael
> --
> Michael Peppler - Data Migrations Inc. - [EMAIL PROTECTED]
> http://www.mbay.net/~mpeppler - [EMAIL PROTECTED]
> International Sybase User Group - http://www.isug.com
> Sybase on Linux mailing list: [EMAIL PROTECTED]
>
