3 ms·

Here's a comment on the stack overflow answer I linked to in the post: Very performant: (1) Bytecode decompiling is very fast. (2) Since each query has corresp

by Jake232 12y ago

Here's a comment on the stack overflow answer I linked to in the post: Very performant: (1) Bytecode decompiling is very fast. (2) Since each query has corresponding code object, this code object can be used as a cache key. Because of this, Pony ORM translates each query only once, whereas Django and SQLAlchemy have to translate the same query again and again. (3) As Pony ORM uses IdentityMap pattern, it caches query results within the same transaction. There is a post (in russian) where author states that Pony ORM turned out to be 1.5-3 times faster than Django and SQLAlchemy even without query result caching: http://www.google.com/translate?hl=en&ie=UTF8&sl=auto&tl=en&u=http%3A%2F%2Fhabrahabr.ru%2Fpost%2F188842%2F http://www.google.com/translate?hl=en&ie=UTF8&sl=auto&tl=en&...

zzzeek 12y ago
the performance cost is going to be mostly in the kinds of queries it produces and how well they will be interepreted by the query planner. My understanding is that Pony is very heavy on subqueries and correlated subqueries, and the user is given extremely little leverage on being able to control the structure of queries rendered. Subqueries and especially correlated subqueries have the worst performance of all, especially on less mature planners like that of MySQL.
- amalashkevich 12y ago
  Actually Pony can transform subqueries into JOINs in most of the cases. But when it translates the 'in' operator of a generator expression, it produces a subquery with 'IN', because otherwise the programmer can be confused by the fact that the resulted SQL looks too different from the Python code. Pony allows you to use the 'JOIN' hint in order to make it to use JOINs instead of subquiries. In the example below Pony produces a subquery when it translates the `in` section from the generator: >>> from pony.orm.examples.estore import * >>> select(c for c in Customer if 'iPad' in c.orders.items.product.name)[:] SELECT "c"."id", "c"."email", "c"."password", "c"."name", "c"."country", "c"."address" FROM "Customer" "c" WHERE 'iPad' IN ( SELECT "product-1"."name" FROM "Order" "order-1", "OrderItem" "orderitem-1", "Product" "product-1" WHERE "c"."id" = "order-1"."customer" AND "order-1"."id" = "orderitem-1"."order" AND "orderitem-1"."product" = "product-1"."id" ) But you can tell Pony to use JOIN instead of a subquery by wrapping the 'in' section into a 'JOIN' hint: >>> select(c for c in Customer if JOIN('iPad' in c.orders.items.product.name))[:] SELECT DISTINCT "c"."id", "c"."email", "c"."password", "c"."name", "c"."country", "c"."address" FROM "Customer" "c", "Order" "order-1", "OrderItem" "orderitem-1", "Product" "product-1" WHERE "product-1"."name" = 'iPad' AND "c"."id" = "order-1"."customer" AND "order-1"."id" = "orderitem-1"."order" AND "orderitem-1"."product" = "product-1"."id"

by Jake232 12y ago

zzzeek 12y ago

the performance cost is going to be mostly in the kinds of queries it produces and how well they will be interepreted by the query planner. My understanding is that Pony is very heavy on subqueries and correlated subqueries, and the user is given extremely little leverage on being able to control the structure of queries rendered. Subqueries and especially correlated subqueries have the worst performance of all, especially on less mature planners like that of MySQL.

amalashkevich 12y ago
Actually Pony can transform subqueries into JOINs in most of the cases. But when it translates the 'in' operator of a generator expression, it produces a subquery with 'IN', because otherwise the programmer can be confused by the fact that the resulted SQL looks too different from the Python code. Pony allows you to use the 'JOIN' hint in order to make it to use JOINs instead of subquiries. In the example below Pony produces a subquery when it translates the `in` section from the generator: >>> from pony.orm.examples.estore import * >>> select(c for c in Customer if 'iPad' in c.orders.items.product.name)[:] SELECT "c"."id", "c"."email", "c"."password", "c"."name", "c"."country", "c"."address" FROM "Customer" "c" WHERE 'iPad' IN ( SELECT "product-1"."name" FROM "Order" "order-1", "OrderItem" "orderitem-1", "Product" "product-1" WHERE "c"."id" = "order-1"."customer" AND "order-1"."id" = "orderitem-1"."order" AND "orderitem-1"."product" = "product-1"."id" ) But you can tell Pony to use JOIN instead of a subquery by wrapping the 'in' section into a 'JOIN' hint: >>> select(c for c in Customer if JOIN('iPad' in c.orders.items.product.name))[:] SELECT DISTINCT "c"."id", "c"."email", "c"."password", "c"."name", "c"."country", "c"."address" FROM "Customer" "c", "Order" "order-1", "OrderItem" "orderitem-1", "Product" "product-1" WHERE "product-1"."name" = 'iPad' AND "c"."id" = "order-1"."customer" AND "order-1"."id" = "orderitem-1"."order" AND "orderitem-1"."product" = "product-1"."id"