[Dbix-class] Benchmark update: plan DBI vs. RDBMS-OO mappers
John Siracusa
siracusa at mindspring.com
Mon Aug 22 18:10:12 CEST 2005
On 8/22/05 3:10 AM, Dan Kubb wrote:
> On 8/11/05 3:13 PM, John Siracusa wrote:
>> I just created a benchmark suite that includes Class::DBI,
>> Class::DBI::Sweet, and DBIx::Class. It's part of version 0.65 of
>> Rose::DB::Object, but you can also use it as a way to compare the
>> performance of DBIx::Class to the other modules.
>
> I'd love it if you could include an optimized example using raw SQL
> queries and DBI. It would show us pretty much the upper-limit for
> what can be expected, and give a better frame of reference for the
> other stats.
>
> It would be really interesting to see how the different classes
> compare and how close they come to the ideal.
The updated benchmark suite will be uploaded as part of the next
release of Rose::DB::Object. (I can email the new bench.pl script to
anyone who wants to run it themselves in the meantime.)
The DBI tests could conceivably be slightly faster, but I tried to
approximate the amount of work done by the RDBMS-OO mappers. For
example, all of the mapper modules provide named access to columns,
so it seemed unfair to fetchrow_arrayref() and then index into the
array.
OTOH, fetchrow_hashref() is very inefficient. So what I tried to do
is what a programmer actually using DBI would probably do. When
selecting two columns, I did bind_columns() to two named lexicals.
When selecting many columns, I did bind_columns() to named hash
keys. To simulate "accessors" with DBI, I simply read values from
the hash.
Anyway, the point is that this isn't a test to see how fast DBI can
be made to run. It's a test to see how much slower each RDBMS-OO is
than the "typical" plain DBI implementation.
Below are the results on my system when I ran this command:
./bench.pl --cmp all --db mysql --iterations 3000 --cpu 10 --skip-intro
The bench.pl usage screen is included at the end of the email, if you
want
to see what those options mean.
In case it isn't obvious, the modules are abbreviated like this:
CDBI - Class::DBI
CDBS - Class::DBI::Sweet
DBIC - DBIx::Class
RDBO - Rose::DB::Object
DBI - DBI
-John
---
% ./bench.pl --cmp all --db mysql --iterations 3000 --cpu 10 --skip-
intro
##
## Benchmark against Class::DBI, Class::DBI::Sweet, DBI, DBIx::Class
using MySQL
##
# Simple: insert 1
Rate CDBS CDBI DBIC RDBO DBI
CDBS 926/s -- -6% -13% -65% -90%
CDBI 990/s 7% -- -7% -62% -90%
DBIC 1068/s 15% 8% -- -59% -89%
RDBO 2609/s 182% 163% 144% -- -73%
DBI 9677/s 945% 877% 806% 271% --
# Complex: insert 1
Rate CDBS CDBI DBIC RDBO DBI
CDBS 906/s -- -6% -15% -63% -90%
CDBI 962/s 6% -- -10% -61% -89%
DBIC 1071/s 18% 11% -- -56% -88%
RDBO 2459/s 171% 156% 130% -- -73%
DBI 9091/s 903% 845% 748% 270% --
# Simple: insert 2
Rate CDBS CDBI DBIC RDBO DBI
CDBS 481/s -- -2% -39% -77% -93%
CDBI 491/s 2% -- -38% -77% -93%
DBIC 787/s 64% 60% -- -63% -89%
RDBO 2113/s 339% 330% 168% -- -70%
DBI 7143/s 1386% 1355% 807% 238% --
# Complex: insert 2
Rate CDBS CDBI DBIC RDBO DBI
CDBS 563/s -- -2% -34% -74% -93%
CDBI 577/s 3% -- -32% -74% -93%
DBIC 852/s 51% 48% -- -61% -89%
RDBO 2206/s 292% 282% 159% -- -73%
DBI 8108/s 1341% 1305% 851% 268% --
# Simple: accessor 1
Rate CDBS CDBI DBIC RDBO DBI
CDBS 1.21/s -- -1% -78% -93% -97%
CDBI 1.22/s 1% -- -78% -93% -97%
DBIC 5.60/s 364% 358% -- -69% -86%
RDBO 18.3/s 1416% 1395% 227% -- -54%
DBI 39.7/s 3195% 3150% 610% 117% --
# Complex: accessor 1
Rate CDBI CDBS DBIC RDBO DBI
CDBI 1.21/s -- -0% -79% -93% -97%
CDBS 1.21/s 0% -- -79% -93% -97%
DBIC 5.67/s 370% 369% -- -66% -86%
RDBO 16.6/s 1274% 1272% 192% -- -59%
DBI 40.5/s 3252% 3246% 613% 144% --
# Simple: accessor 2
Rate CDBS CDBI DBIC RDBO DBI
CDBS 0.270/s -- -1% -80% -95% -98%
CDBI 0.272/s 1% -- -79% -95% -98%
DBIC 1.32/s 389% 385% -- -74% -92%
RDBO 5.09/s 1785% 1770% 285% -- -71%
DBI 17.5/s 6365% 6313% 1222% 243% --
# Complex: accessor 2
Rate CDBI CDBS DBIC RDBO DBI
CDBI 0.272/s -- -0% -79% -92% -98%
CDBS 0.273/s 0% -- -79% -92% -98%
DBIC 1.32/s 384% 383% -- -62% -92%
RDBO 3.45/s 1168% 1164% 162% -- -80%
DBI 17.4/s 6312% 6295% 1224% 406% --
# Simple: load 1
Rate CDBS DBIC CDBI RDBO DBI
CDBS 744/s -- -4% -37% -62% -87%
DBIC 779/s 5% -- -34% -60% -86%
CDBI 1181/s 59% 52% -- -39% -79%
RDBO 1935/s 160% 148% 64% -- -65%
DBI 5556/s 646% 613% 370% 187% --
# Simple: load 2
Rate CDBS DBIC CDBI RDBO DBI
CDBS 530/s -- -19% -28% -65% -85%
DBIC 655/s 24% -- -11% -57% -82%
CDBI 739/s 39% 13% -- -51% -79%
RDBO 1515/s 186% 131% 105% -- -58%
DBI 3571/s 574% 445% 383% 136% --
# Complex: load 2
Rate CDBS CDBI DBIC RDBO DBI
CDBS 375/s -- -19% -43% -74% -90%
CDBI 465/s 24% -- -29% -67% -87%
DBIC 655/s 74% 41% -- -54% -82%
RDBO 1429/s 280% 207% 118% -- -60%
DBI 3614/s 863% 677% 452% 153% --
# Simple: load 3
Rate DBIC CDBS CDBI RDBO DBI
DBIC 320/s -- -12% -30% -59% -90%
CDBS 363/s 13% -- -20% -54% -89%
CDBI 456/s 43% 26% -- -42% -86%
RDBO 785/s 146% 116% 72% -- -75%
DBI 3158/s 887% 771% 593% 302% --
# Complex: load 3
Rate CDBS DBIC CDBI RDBO DBI
CDBS 280/s -- -12% -15% -63% -91%
DBIC 316/s 13% -- -4% -58% -89%
CDBI 331/s 18% 5% -- -56% -89%
RDBO 748/s 168% 136% 126% -- -75%
DBI 3000/s 973% 848% 807% 301% --
# Simple: update 1
Rate CDBS DBIC CDBI RDBO DBI
CDBS 424/s -- -4% -23% -63% -86%
DBIC 441/s 4% -- -20% -61% -86%
CDBI 550/s 30% 25% -- -52% -82%
RDBO 1136/s 168% 158% 106% -- -63%
DBI 3093/s 630% 602% 462% 172% --
# Simple: update 2
Rate CDBS DBIC CDBI RDBO DBI
CDBS 333/s -- -15% -20% -64% -86%
DBIC 391/s 17% -- -6% -58% -84%
CDBI 414/s 24% 6% -- -55% -83%
RDBO 926/s 178% 137% 123% -- -62%
DBI 2459/s 638% 530% 493% 166% --
# Complex: update 2
Rate CDBS CDBI DBIC RDBO DBI
CDBS 200/s -- -12% -47% -57% -92%
CDBI 227/s 14% -- -40% -51% -91%
DBIC 379/s 90% 67% -- -18% -85%
RDBO 462/s 131% 103% 22% -- -81%
DBI 2459/s 1130% 982% 549% 433% --
# Simple: search 1
s/iter DBIC CDBS CDBI RDBO DBI
DBIC 4.97 -- -59% -65% -83% -97%
CDBS 2.04 143% -- -14% -60% -92%
CDBI 1.76 182% 16% -- -53% -91%
RDBO 0.823 503% 148% 114% -- -80%
DBI 0.167 2874% 1123% 954% 393% --
# Simple: search 2
s/iter DBIC CDBS CDBI RDBO DBI
DBIC 7.82 -- -27% -35% -80% -96%
CDBS 5.67 38% -- -11% -73% -95%
CDBI 5.05 55% 12% -- -70% -94%
RDBO 1.54 407% 269% 228% -- -81%
DBI 0.293 2566% 1836% 1624% 425% --
# Simple: search with limit and offset
Rate DBIC CDBS RDBO DBI
DBIC 72.8/s -- -33% -69% -95%
CDBS 109/s 49% -- -54% -93%
RDBO 236/s 225% 117% -- -84%
DBI 1499/s 1958% 1277% 534% --
# Complex: search with limit and offset
Rate CDBS DBIC RDBO DBI
CDBS 21.1/s -- -70% -90% -99%
DBIC 71.3/s 238% -- -65% -95%
RDBO 204/s 870% 187% -- -86%
DBI 1471/s 6882% 1964% 620% --
# Complex: search 2
s/iter CDBS CDBI DBIC RDBO DBI
CDBS 25.4 -- -6% -69% -93% -99%
CDBI 23.7 7% -- -67% -93% -99%
DBIC 7.81 225% 204% -- -78% -97%
RDBO 1.76 1345% 1252% 345% -- -86%
DBI 0.247 10162% 9503% 3058% 610% --
# Simple: search 3
s/iter DBIC CDBS CDBI RDBO DBI
DBIC 32.8 -- -77% -81% -91% -98%
CDBS 7.56 334% -- -15% -62% -93%
CDBI 6.40 413% 18% -- -56% -92%
RDBO 2.84 1056% 166% 125% -- -81%
DBI 0.531 6088% 1326% 1106% 435% --
# Complex: search 3
s/iter DBIC CDBS CDBI RDBO DBI
DBIC 33.3 -- -13% -21% -90% -98%
CDBS 28.9 15% -- -9% -89% -98%
CDBI 26.4 26% 10% -- -88% -98%
RDBO 3.28 915% 782% 705% -- -82%
DBI 0.588 5559% 4817% 4390% 458% --
# Simple: iterate 1
s/iter DBIC CDBS CDBI RDBO DBI
DBIC 5.29 -- -56% -60% -83% -98%
CDBS 2.33 127% -- -9% -62% -94%
CDBI 2.12 150% 10% -- -58% -94%
RDBO 0.897 490% 160% 136% -- -85%
DBI 0.132 3924% 1672% 1508% 582% --
# Complex: iterate 1
s/iter DBIC CDBS CDBI RDBO DBI
DBIC 5.27 -- -56% -60% -82% -97%
CDBS 2.30 129% -- -9% -60% -94%
CDBI 2.10 151% 10% -- -56% -94%
RDBO 0.927 468% 148% 126% -- -86%
DBI 0.132 3892% 1644% 1491% 602% --
# Simple: iterate 2
s/iter DBIC CDBS CDBI RDBO DBI
DBIC 8.19 -- -19% -25% -81% -98%
CDBS 6.64 23% -- -8% -76% -98%
CDBI 6.11 34% 9% -- -74% -98%
RDBO 1.57 422% 323% 290% -- -90%
DBI 0.152 5279% 4263% 3918% 931% --
# Complex: iterate 2
s/iter CDBS CDBI DBIC RDBO DBI
CDBS 28.8 -- -4% -71% -93% -99%
CDBI 27.6 4% -- -69% -93% -99%
DBIC 8.48 240% 225% -- -78% -98%
RDBO 1.90 1420% 1355% 347% -- -92%
DBI 0.152 18893% 18083% 5489% 1150% --
# Simple: iterate 3
s/iter DBIC CDBI CDBS RDBO DBI
DBIC 35.3 -- -40% -75% -92% -99%
CDBI 21.1 68% -- -58% -86% -99%
CDBS 8.92 296% 136% -- -67% -98%
RDBO 2.91 1115% 625% 207% -- -94%
DBI 0.177 19866% 11804% 4940% 1543% --
# Complex: iterate 3
s/iter CDBI DBIC CDBS RDBO DBI
CDBI 44.8 -- -22% -29% -93% -100%
DBIC 34.9 29% -- -9% -91% -99%
CDBS 31.9 41% 9% -- -90% -99%
RDBO 3.31 1253% 953% 862% -- -95%
DBI 0.177 25270% 19638% 17934% 1775% --
# Simple: delete
Rate DBIC CDBS CDBI RDBO DBI
DBIC 435/s -- -11% -38% -84% -95%
CDBS 489/s 13% -- -30% -82% -95%
CDBI 701/s 61% 43% -- -75% -92%
RDBO 2752/s 533% 462% 293% -- -70%
DBI 9091/s 1991% 1758% 1197% 230% --
# Complex: delete
Rate CDBS CDBI DBIC RDBO DBI
CDBS 278/s -- -17% -28% -90% -97%
CDBI 337/s 21% -- -12% -88% -97%
DBIC 385/s 38% 14% -- -86% -96%
RDBO 2752/s 890% 717% 616% -- -72%
DBI 9677/s 3381% 2774% 2416% 252% --
---
% ./bench.pl --help
Usage: bench.pl --help | [--skip-intro] [--cpu-time <num>]
[--compare-to <modules>] [--database <db>]
[--time | --compare | --time-and-compare]
[--simple | --complex | --simple-and-complex]
[--iterations <num>] [--hi-res-time]
--compare-to | --cmp <modules>
Benchmark against <modules>, which is a comma-separated list of
one or more for the following:
DBI, Class::DBI, Class::DBI::Sweet, DBIx::Class
The special value "all" can be used to specify all available
modules.
--database <db>
Use <db> to run benchmarks, where <db> is a one of the following
database types:
informix, mysql, pg
--cpu-time <num>
The minimum amount of CPU time in seconds to spend on benchmarks
that do not require a predictible number of iterations. Defaults
to 5.
--hi-res-time
Use high-resolution wall-clock time measurement, if available.
--iterations <num>
The number of iterations to use for benchmarks that must be run a
predictible number of times. The default is 1000.
--time
--compare
--time-and-compare
Select only one of these flags to specify whether to time, compare,
or both time and compare each benchmark. (perldoc Benchmark and
see the timethese() and cmpthese() functions.) "Compare" is the
default.
--simple
--complex
--simple-and-complex
Select only one of these flags to specify whether to test with
simple objects (no column inflate/deflate), complex objects,
or both. "Simple and complex" is the default.
--help Show this help screen.
--skip-intro Skip the introductory message.
More information about the Dbix-class
mailing list