Object-relational mappers (ORMs) are often used in Python programming when one needs to develop an application that works with databases. Examples of Python ORMs are SQLAlchemy, Peewee, Pony-ORM and Django. When choosing an ORM, performance plays a crucial role. But how are these toolsets compared? ORM performance benchmarks offer a measure of clarity but leave considerable room for improvement. I examine and extend the qualitative ORM benchmark to develop a stronger metric. The qualitative Python ORM benchmark, Tortoise ORM, ( ) analyzes the speed of six ORMs for eleven types of SQL queries. In general, the Tortoise benchmark makes it possible to evaluate the speed of query execution for the various ORMs. However there is a flaw with this approach to testing: most ORMs are selected for use in web applications. In such contexts multiple users send all manner of queries to a database often at the same time. Because no benchmark measurement tools evaluated could rate performance of Python ORMs in a scenario like this, I decided to write my own comparing PonyORM and SQLAlchemy. As a basis, I took the TPC-C benchmark. link to the repository Since 1988, has been developing tests in the field of data processing. They have long become an industry standard and are used by almost all vendors of equipment on various samples of hardware and software. The main feature of these tests is that they are focused on testing under enormous load in conditions as close as possible to real ones. TPC simulates a warehouse network. It includes a combination of five simultaneously executed transactions of various types and complexity.The purpose of the test is to evaluate the speed of transaction processing when several virtual users simultaneously access the database. TPC-C I decided to test two Python ORMs (SQLALchemy and PonyORM) using the TPC-C testing method adapted for this task. The purpose of the test is to evaluate the speed of transaction processing when several virtual users access the database at the same time. Test description Step one is to create and populate the database of a warehouse network. The database schema looks like : this The database consists of eight relations: Warehouse District Order OrderLine Stock Item Customer History Databases for Pony and SQLAlchemy are identical. Only primary and foreign keys are indexed. Pony made those indexes automatically. In SQLAlchemy I made it manually. During the test, different types of transactions are sent to the database from several virtual users. Each transaction consists of several requests. In total, there are five types of transactions that are submitted for processing with different probability of occurrence: Transactions: new_order - 45% payment - 43% order_status - 4% delivery - 4% stock_level - 4% The probability of occurrence of transactions is the same as in the original TPC-C test. However, bearing in mind that toriginal TPC-C test is conducted on servers with 64+ GB of RAM (requiring a large number of processors and huge disk space), due to technical limitations and and the fact that I wanted to test the performance of ORMs and not the resistance of hardware to enormous load, this test is somewhat simplified. The main differences from the TPC-C test are as follows: Main differences: The test runs with fewer virtual users than in the original test My test has less table entries. For example: the number of entries in the relation “Stock” in the original test is calculated using the formula 100,000 * W, where W is the number of warehouses. In this test it’s 100 * W. In the TPC-C, some of the transactions have several options for querying data from the database. For example, in a Payment transaction, with one probability, the client will be requested from the database by ID, with another by last name and first name. At the moment, my test only makes calls by ID. My test database is one table less than the TPC-C. In the TPC-C test, when an order is created, it is added to both the Order table and the NewOrder table. After delivery of the order, it is then deleted from the NewOrder table. This speeds up work when a huge number of transactions per minute is applied; but since I have fewer users accessing the database, this is unnecessary. Instead, in the Order table, I added the bool attribute “is_o_delivered”, which will be False, until the order is delivered. Next, I will briefly describe what each transaction does. Transactions New Order Passing two arguments to the transaction: warehouse ID and customer ID Selecting the warehouse and the customer from the database using the passed IDs Selecting one randomly chosen district of the warehouse from database Generating a random number that indicates the number of order lines. Creating an Order object Creating OrderLine objects in loop. In each iteration of the loop selecting one random item from the Item table Changing Stock for each item in the order Payment Passing two arguments to the transaction: warehouse ID and customer ID Selecting the warehouse and the customer from the database by the passed IDs Selecting one randomly chosen district of the warehouse from database Generating a random number that indicates the payment amount Increasing balances of Warehouse and District by the payment amount Decreasing balance of customer by the payment amount Incrementing customer payment counter Increasing sum of customers payment amounts by the payment amount Creating History object Order Status Passing customer ID as an argument of the transaction Selecting customer by ID and last order of this customer Taking order status and order lines from order. Delivery Passing warehouse ID as an argument of the transaction Selecting warehouse and all of its districts from the database For every district selecting the oldest not delivered order. For every order changing delivery status to True For every customer of orders incrementing order count Stock Level Passing warehouse ID as an argument of the transaction Selecting warehouse from the database by ID Selecting last 20 orders of this warehouse For every item in orders taking stock level of item Test results Two ORMs are participating in the test: SQLAlchemy (blue line on graph) PonyORM (orange line on graph) Below are the results of running the test for 10 minutes with 2 parallel processes accessing the database. Processes are launched using the “multiprocessing” module. X axis - time in minutes Y axis - number of completed transactions As DBMS I use PostgreSQL All transactions First, I ran the test with all five transactions, as expected in the TPC-C test. As a result of this test, Pony was about twice as fast. Average speed: Pony - 2543 transactions/minute SQLAlchemy - 1353.4 transactions/minute After that, I decided to evaluate the performance of ORMs on each of the five transactions separately. Below are the results for each individual transaction. New Order Average speed: Pony - 3349.2 transactions/minute SQLAlchemy - 1415.3 transactions/minute Payment Average speed: Pony - 7175.3 transactions/minute SQLAlchemy - 4110.6 transactions/minute Order Status Average speed: Pony - 16645.6 transactions/minute SQLAlchemy - 4820.8 transactions/minute Delivery Average speed: SQLAlchemy - 716.9 transactions/minute Pony - 323.5 transactions/minute Stock Level Average speed: Pony - 677.3 transactions/minute SQLAlchemy - 167.9 transactions/minute Analysis of test results After receiving the results, I analyzed why they are like this and came to the following conclusions: In 4 out of 5 transactions, PonyORM turned out to be faster, because, when generating SQL code, PonyORM remembers the result of translating Python expressions into SQL. Because of this Pony does not translate the expression again when the query is repeated, while SQLAlchemy is forced to generate SQL code every time when needed to execute a query. Example of this kind of query in Pony: stocks = select(stock stock Stock stock.warehouse == whouse stock.item items).order_by(Stock.id).for_update() for in if and in Generated SQL: . , . , . , . , . , . , . , . . = %(p1)s . (%(p2)s, %(p3)s) . { : , : , : } SELECT "stock" "id" "stock" "warehouse" "stock" "item" "stock" "quantity" "stock" "ytd" "stock" "order_cnt" "stock" "remote_cnt" "stock" "data" FROM "stock" "stock" WHERE "stock" "warehouse" AND "stock" "item" IN ORDER BY "stock" "id" FOR UPDATE 'p1' 7 'p2' 7 'p3' 37 SQLAlchemy: stocks = session.query(Stock).filter( Stock.warehouse == whouse, Stock.item.in_( items)).order_by(text( )).with_for_update() "id" Generated SQL: stock.id stock_id, stock.warehouse_id stock_warehouse_id, stock.item_id stock_item_id, stock.quantity stock_quantity, stock.ytd stock_ytd, stock.order_cnt stock_order_cnt, stock.remote_cnt stock_remote_cnt, stock.data stock_data stock stock.warehouse_id = %(warehouse_id_1)s stock.item_id (%(item_id_1)s, %(item_id_2)s) { : , : , : } SELECT AS AS AS AS AS AS AS AS FROM WHERE AND IN ORDER BY id FOR UPDATE 'warehouse_id_1' 7 'item_id_1' 53 'item_id_2' 54 But, Apparently, SQLAlchemy performs delivery type transactions faster because it can combine several UPDATE operations applied to different objects into a single command. Example: INFO:sqlalchemy.engine.base.Engine: order_line delivery_d=% (delivery_d)s order_line.id = %(order_line_id)s INFO:sqlalchemy.engine.base.Engine:( { : datetime.datetime( , , , , , , ), : }, { : datetime.datetime( , , , , , , ), : }, { : datetime.datetime( , , , , , , )) UPDATE SET WHERE 'delivery_d' 2020 4 6 14 33 6 922281 'order_line_id' 316 'delivery_d' 2020 4 6 14 33 6 922272 'order_line_id' 317 'delivery_d' 2020 4 6 14 33 6 922261 Pony in situations like this sends separate query for each update. Conclusion Based on the results of this test, I can say that Pony selects from the database much faster. On the other hand SQLAlchemy in some cases can produce queries of Update type with significantly higher speed. In the future, I plan to test other ORMs (Peewee, Django) this way. Links Code of the test: repository SQLAlchemy: , documentation community Pony: , documentation community
