CODEWITHSUNDEEP
pythonpython-3.xpostgresqlpsycopg2
Friedrich42
Friedrich42

Reputation: 752

Is it better to make 1 query or many queries to postgresql?

I got DB class which handles all these cursors, connections etc. And I got 3 methods:

return_all_records: (SELECT * FROM TABLE)

count_of_records: (SELECT COUNT(*) FROM TABLE)

and return_record_by_pk: (SELECT * FROM TABLE WHERE pk=?)

I should take each row and make operations with data. Is it better to make 1 big query to db or many small queries?

PS. I have got really many records in db. Smth like 10^8. It's not really easy to receive them an once

for record in DB.return_all_records(): # 1 big query
     #code

for pk in range(DB.count_of_records()):
    DB.return_record_by_pk(pk) #small queries

Upvotes: 0

Views: 1163

Answers (1)

QzWXQ
QzWXQ

Reputation: 96

It very much depends on the underlying database driver how this is handled. Instead of fetching all records at once, you should/can consider limiting the amount of rows returned using a where condition on the SQL to specify you "start row" and "end row". You can then slide e.g. 2000 rows at a time over your data. Please make sure to sort the data using an order by as otherwise the returned data will/can vary. Depending on the database you are using, the "limit" has different syntax (e.g. rownum in Oracle, a combination of limit and offset when using MySQL, ...). Depending on the column you use for sorting (and if it's indexed or not), the sorting can have a huge impact on the execution times and fetching "windows" from the middle or end of the table might be getting slow

Upvotes: 1

Related Questions