SQLAlchemy 0.9 dialects speed tests
This fourth SQLAlchemy's dialects speed tests aims to test this ORM's newest version, 0.9.x, which is available for about a month already. This version, amongst other improvements, contains C extension for python3.
There are six dialects tested like previously: 3 for postgres: psycopg2, pypostgresql, pg8000 and 3 for mysql: mysqldb (mysql-python), oursql and mysqlconnector (mysql-connector-python).
My basic setup:
System: Ubuntu 13.10 64bit
Python: Python 2.7.5+/Python 3.3.2+
SQLAlchemy: 0.9.1
Connectors versions used were:
oursql: 0.9.3.1
mysql-connector-python: 1.1.4 (new)
mysqldb: 1.2.5 (new)
psycopg2: 2.5.2 (new)
pg8000 1.9.4 (new)
py-postgresql: 1.1.0
Most major packages had seen patch updates since the last test, with mysql-connector-python seeing a minor update.
Tests run, were the five tests as previously:
Insert with rollback
Insert with commit
Select
Delete with rollback
Delete with commit
I had to extend run.sh of my test suite script to facilitate the fact, that the mysql-connector-python is not hosted on pypi.
Test results
s/run 1 |
s/run 10 |
s/run 100 |
|
|---|---|---|---|
psycopg2 |
0.049 |
0.199 |
1.746 |
psycopg2 (py3) |
0.048 |
0.202 |
1.762 |
pg8000 |
0.194 |
0.746 |
6.450 |
pg8000 (py3) |
0.196 |
0.760 |
6.397 |
py-postgresql (py3) |
0.127 |
0.532 |
4.827 |
MySQLdb |
0.069 |
0.368 |
2.850 |
Connector |
0.066 |
0.438 |
3.489 |
Connector (py3) |
0.070 |
0.449 |
3.846 |
Oursql |
0.102 |
0.422 |
3.539 |
Table: Insert with rollback
Like before, no real difference between python2 and python3 able connectors. With psycopg2 still the fastest of all (even mysql) and pg800 being about 50% slower than py-postgresql. MySQLdb as usual leads within MySQL connectors, with Mysql-connector-python close by.
s/run 1 |
s/run 10 |
s/run 100 |
|
|---|---|---|---|
psycopg2 |
0.063 |
0.226 |
2.187 |
psycopg2 (py3) |
0.113 |
0.392 |
1.979 |
pg8000 |
0.227 |
1.119 |
6.682 |
pg8000 (py3) |
0.331 |
0.815 |
6.632 |
py-postgresql (py3) |
0.151 |
0.684 |
5.079 |
MySQLdb |
0.055 |
0.325 |
2.521 |
Connector |
0.061 |
0.353 |
3.452 |
Connector (py3) |
0.068 |
0.420 |
3.700 |
Oursql |
0.083 |
0.394 |
3.741 |
Table: Insert with commit
Not much different for insert with commit, although this operation is taking longer for all connectors. Oursql passed all tests this time.
s/run 1 |
s/run 10 |
s/run 100 |
|
|---|---|---|---|
psycopg2 |
0.019 |
0.029 |
0.118 |
psycopg2 (py3) |
0.018 |
0.027 |
0.116 |
pg8000 |
0.079 |
0.109 |
0.198 |
pg8000 (py3) |
0.078 |
0.088 |
0.198 |
py-postgresql (py3) |
0.046 |
0.059 |
0.139 |
MySQLdb |
0.026 |
0.035 |
0.119 |
Connector |
0.036 |
0.043 |
0.133 |
Connector (py3) |
0.036 |
0.045 |
0.140 |
Oursql |
ERROR |
ERROR |
ERROR |
Table: Select test
Not much change since last time I presented results for this test, except mysql-connector-python gets on-par between different python versions.
s/run 1 |
s/run 10 |
s/run 100 |
|
|---|---|---|---|
psycopg2 |
0.033 |
0.033 |
0.043 |
psycopg2 (py3) |
0.032 |
0.032 |
0.040 |
pg8000 |
0.163 |
0.164 |
0.180 |
pg8000 (py3) |
0.163 |
0.180 |
0.171 |
py-postgresql (py3) |
0.118 |
0.121 |
0.145 |
MySQLdb |
0.045 |
0.064 |
0.144 |
Connector |
0.067 |
0.095 |
0.306 |
Connector (py3) |
0.074 |
0.064 |
0.086 |
Oursql |
ERROR |
ERROR |
ERROR |
Table: Delete with rollback
Again, quite an improvement for mysql-connector-python on python3 here. Other differences could be attributed to network.
s/run 1 |
s/run 10 |
s/run 100 |
|
|---|---|---|---|
psycopg2 |
0.081 |
0.081 |
0.106 |
psycopg2 (py3) |
0.046 |
0.049 |
0.058 |
pg8000 |
0.480 |
0.386 |
0.388 |
pg8000 (py3) |
0.280 |
0.300 |
0.303 |
py-postgresql (py3) |
0.152 |
0.156 |
0.229 |
MySQLdb |
0.047 |
0.061 |
0.193 |
Connector |
0.074 |
0.062 |
0.120 |
Connector (py3) |
0.066 |
0.063 |
0.232 |
Oursql |
ERROR |
ERROR |
ERROR |
Table: Delete with commit
When committing a delete operation, psycopg2 seems to perform better on python3 than python2.
Summarizing
There are some overall improvements when it comes to mysql-connector-python performance, but still, MySQLdb takes the lead for MySQL connectors. Still postgres' psycopg2 seems to perform better in most cases, and works on python3 as well. Oursql made it one step further without any work on the package.
Surprisingly, there isn't any speedup I could attribute to SQLAlchemy's C extensions being enabled on python3, although, I was hoping for one. Mysql-connector-python could be rather attributed to some mjor work done to it.
Tests code
Test code can be checked here github.com/fizyk/sqlalchemy-tests. Requirement for it is to have virtualenvwrapper installed, and script itself should create clean envirtonments and install required dependeincies for tets.
Got some ideas on moving this code to py.test based tests with some logger fixture to produce pre-rendered reports.
