Query prefetch() method should load specified lazy attributes right in the main query if possible #172

Closed
andreymal opened this Issue Mar 23, 2016 · 1 comment

Projects

None yet

2 participants

@andreymal
db = Database()

class Post(db.Entity):
    title = Required(str, 128)
    text = Required(LongStr)

db.bind('sqlite', ':memory:')
db.generate_mapping(create_tables=True)
sql_debug(True)

with db_session:
    [Post(title=str(i), text='text {}'.format(i)) for i in range(5)]

with db_session:
    posts = Post.select().prefetch(Post.text)[:]
GET CONNECTION FROM THE LOCAL POOL
SWITCH TO AUTOCOMMIT MODE
SELECT "p"."id", "p"."title"
FROM "Post" "p"

SELECT "text"
FROM "Post"
WHERE "id" = ?
[1]

SELECT "text"
FROM "Post"
WHERE "id" = ?
[2]

SELECT "text"
FROM "Post"
WHERE "id" = ?
[3]

SELECT "text"
FROM "Post"
WHERE "id" = ?
[4]

SELECT "text"
FROM "Post"
WHERE "id" = ?
[5]

RELEASE CONNECTION

Why not

GET CONNECTION FROM THE LOCAL POOL
SWITCH TO AUTOCOMMIT MODE
SELECT "p"."id", "p"."title", "p"."text"
FROM "Post" "p"

RELEASE CONNECTION

?

@kozlovsky kozlovsky added this to the 0.6.5 milestone Mar 24, 2016
@kozlovsky kozlovsky self-assigned this Mar 24, 2016
@kozlovsky kozlovsky closed this in 3f47c1d Mar 24, 2016
@kozlovsky
Contributor

When prefetch method was added, the goal was not in minimizing SQL queries, but in loading all necessary data before exiting of db_session. Pony assumes that processing of any ORM objects take place inside db_session, but some users want to have the ability to read attribute values when the db_session is already over. So we added prefetch method which walks through the query result and preloads all specified attributes so they can be accessed later.

But you are right that prefetching can be optimized, at least in simple cases. So I just added an optimization which includes specified attributes into initial SELECT, if possible.

@kozlovsky kozlovsky changed the title from Prefetch lazy attribute? to Query prefetch() method should load specified lazy attributes right in the main query if possible Apr 4, 2016
@kozlovsky kozlovsky added a commit that referenced this issue Apr 4, 2016
@kozlovsky kozlovsky Pony ORM Release 0.6.5 (2016-04-04)
# Improvements

* Fixes #172: Query prefetch() method should load specified lazy attributes right in the main query if possible

# Bugfixes

* Fixes #168: Incorrect caching when slicing the same query multiple times
* Fixes #169: When py_check() returns False, Pony should truncate too large values in resulting ValueError message
* Fixes #171: AssertionError when saving changes of multiple objects
* Fixes #176: Autostripped strings are not validated correctly for Required attributes

See blog post for more detailed information: https://blog.ponyorm.com/2016/04/04/pony-orm-release-0-6-5/
e96fccb
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment