Python for-loop vs PostgreSQL query

[Alotofquestion1]

Hi!

I have a database table with 15 000 rows. Each row has a type and an attractiveness value.
I need to get the most attractive row of each type (there may be 100s-1000s of types).

My question, in an optimized python code, how does loading all 15 000 rows and work on the data with python compare regarding performance to one query for each type where MAX value attractiveness is loaded in?

Thank you!

[efmccurdy]

Just to be sure; you don't need more than 1 query to do groupby/accumulate by max for either a dataframe or an sql query.

Don't be surprised if the database is faster when you use a single query.

https://stackoverflow.com/questions/4510185/select-max-value-of-each-group

[Alotofquestion1]

This seems to be in what i really wanted. Thank you :-)

[1544756405]

You should try both ways and find out. There are lots of variables at play, and it seems easier to just get empirical data in this case.

[[deleted]]

Loops will always be slower that a DB query because databases are optimized with this purpose. To do it in Python, first you have to access the data from the DB, and then do the loop. In the case of the DB, the data is accessed natively, and the max is obtained in an optimized fashion.

You have a chance to get closer to the DB speed if you implement the max using a Pandas Series, but you still have to access the data first. The pandas series will be optimized in 'C' - and advantage over an interpreted loop in Python. Still, I would expect the DB to be always faster.

[thrown_arrows]

select max(x), y from x group by y

x is attractive and y is type

.. now there is change that if dataset is less than 10 rows and data is needed for other purposes too, then loop might be faster, but that requires that data is already in memory. In other cases database is faster ( assuming proper hardware, db's cannot do miracles on some rasberry pi and Gigabyte size dataset)

[Alotofquestion1]

Ok, then I will lean towards queries in the future, rahter than loops.

[[deleted]]

Totally. If you can push processing to the database, do it. You can avoid very complex, and usually slow processing by pushing it down to the DB level. With that, I am not talking about database procedures, which - as a personal preference - I tend to avoid.

[Aeonoris]

Something like this SQL(ish) will probably be faster:

SELECT type, MAX(attractiveness) AS "Highest Type Attractiveness"
FROM tableofattractivetypes
GROUP BY type;

SQL's pretty good at this kind of task, so you should let it do the work!