Peewee: Slow on iterating over simple query

[GMaxera]

I have this code:

tweetInsights = Gnip_Tweet_Insights \
           .select() \
           .where(
               (Gnip_Tweet_Insights.client_account_id==clientAccountId) &
               (Gnip_Tweet_Insights.occurred_at==insight.occurred_at) &
               (Gnip_Tweet_Insights.by_echomany==insight.by_echomany))
print( "After Query ", time.perf_counter() - startTime )
for singleInsight in tweetInsights:
    pass
print( "After Dict ", time.perf_counter() - startTime )

The query itself is very simple but contains 5000 rows.
It takes less than 200 milliseconds if I do it with "Sequel Pro".

But iterating over these 5000 rows using the "for singleInsight in tweetInsights:" takes around 1 seconds. And for my user case is too much.
I don't understand why so long time for iterating over a 5000 rows. It's definitely not a large set of data.

I tried using .naive(), .dicts() and .iterator() but none of these speed up the cycle.

Is there something else I can use to speed up iterating ?

[coleifer]

It's Python...

[ketozhang]

@coleifer, Please elaborate.

I'm using peewee in cases where I am required to query 1 million or more rows.

query = table.select()
data = list(query.dicts())

This process takes over 30s and when there are where clauses, it may take up to 1~2min.

Are you saying there are no way to increase the performance of peewee or do you believe other libraries say sqlalchemy will also take the same amount of time?

[coleifer]

That's a shitcomment I made, must have been in a hurry or something, so apologies.

There's a short section in the docs about iterating over lots of rows: http://docs.peewee-orm.com/en/latest/peewee/querying.html#iterating-over-lots-of-rows

The key points are:

• If you are doing a single-shot iteration and do not need to cache rows in memory, tack on a .iterator() to your query. This helps keep memory usage down for large result sets.
• The fastest way to get lots of rows is to avoid creating expensive Python objects. Requesting that rows be returned as dicts, tuples or namedtuples is going to be fastest. If you have joins and are selecting from multiple models, you can also try using ".objects()", which skips traversing the model graph. You can find examples here: http://docs.peewee-orm.com/en/latest/peewee/relationships.html#selecting-from-multiple-sources

You can compare the runtime of the query by itself versus the overhead of Peewee by using "database.execute_sql(...)", passing it a SQL string and a list of params. Alternatively, if you have a query object, you can use "database.execute(query)". These methods return a DB-API cursor which you can iterate over. This should give you a good baseline for how the query performs in Python.

[GMaxera]

@coleifer thank you that you finally admitted that was shit comment. I was quite frustrated that you closed and dismissed my question as like I was stupid. Unfortunately, it's too late for me to use peewee again ... and at that time we decided to use PHP and now I'm working for another company :-)