-1

Currently I'm trying with my hobby 2 years of hobby scripting in python to optimize my old codes.

For a script in the past I had a check for giving out resources to a player. The whole calculation for 100.000 did take about 25 minutes. This has been shortened to a mere 1 minute and 40 seconds with a filter that if resources are equal to maximum storage it will not get the row from the table.

There are still checks in place in case one of te 3 resource types are full (so other resources will receive their production bonus.

Production is split between 4 since it will run once in 15 minutes.

Old code was a 25 minute run time. The code displayed here beneath does run over 100.000 "villages" within 1 minute and 40 seconds. 2,3 seconds to withdraw all data 0,2 seconds to write all data to database the rest (1 minute and 37,5 seconds) is purely what runs between

for village in villages: and session.add(add_resources)

Is it possible to speed this up even further or are these the limits of Python itself?

            time_start, villages = datetime.utcnow(), new_get_villages()
            for village in villages:
                production_wood, production_stone, production_iron = int(village.wood_production/4), int(village.stone_production/4), int(village.iron_production/4)
                add_resources = session.query(VillageNew).filter(VillageNew.pk == village.pk).first()
                if add_resources.wood_stock != add_resources.max_storage:
                    add_resources.wood_stock = add_resources.wood_stock + production_wood
                if add_resources.wood_stock > add_resources.max_storage:
                    add_resources.wood_stock = add_resources.max_storage
                if add_resources.stone_stock != add_resources.max_storage:
                    add_resources.stone_stock = add_resources.stone_stock + production_stone
                    if add_resources.stone_stock > add_resources.max_storage:
                        add_resources.stone_stock = add_resources.max_storage
                if add_resources.iron_stock != add_resources.max_storage:
                    add_resources.iron_stock = add_resources.iron_stock + production_iron
                    if add_resources.iron_stock > add_resources.max_storage:
                        add_resources.iron_stock = add_resources.max_storage
                session.add(add_resources)
            session.commit
            time_end = datetime.utcnow()

session:

db_connection_string = conf.get_string('DBConf', 'db_connection_string')
engine = create_engine(db_connection_string, encoding='utf8')
Session = sessionmaker(bind=engine)

Code update after the post of @gimix

                for village in session.query(VillageNew).filter(or_(VillageNew.wood_stock != VillageNew.max_storage, VillageNew.stone_stock != VillageNew.max_storage, VillageNew.iron_stock != VillageNew.max_storage)).all():
                    village.wood_stock = (village.wood_stock + int(village.wood_production))
                    if village.wood_stock > village.max_storage:
                        village.wood_stock = village.max_storage
                    village.stone_stock = (village.stone_stock + int(village.wood_production))
                    if village.stone_stock > village.max_storage:
                        village.stone_stock = village.max_storage
                    village.iron_stock = (village.iron_stock + int(village.wood_production))
                    if village.iron_stock > village.max_storage:
                        village.iron_stock = village.max_storage
                session.commit()

Code update 02/11/2021:

session.query(VillageNew).where(VillageNew.wood_stock < VillageNew.max_storage).update({VillageNew.wood_stock: VillageNew.wood_stock + VillageNew.wood_production})
session.query(VillageNew).where(VillageNew.stone_stock < VillageNew.max_storage).update({VillageNew.stone_stock: VillageNew.stone_stock + VillageNew.stone_production})
session.query(VillageNew).where(VillageNew.iron_stock < VillageNew.max_storage).update({VillageNew.iron_stock: VillageNew.iron_stock + VillageNew.iron_production})
session.query(VillageNew).where(VillageNew.wood_stock > VillageNew.max_storage).update({VillageNew.wood_stock: VillageNew.max_storage})
session.query(VillageNew).where(VillageNew.stone_stock > VillageNew.max_storage).update({VillageNew.stone_stock: VillageNew.max_storage})
session.query(VillageNew).where(VillageNew.iron_stock > VillageNew.max_storage).update({VillageNew.iron_stock: VillageNew.max_storage})
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
TheRealShadow
  • 61
  • 4
  • What is the question? – hpchavaz Nov 01 '21 at 10:17
  • Is it possible to speed this up even further or are these the limits of Python itself? I was too busy making sure the summary was correct and forgot the question. – TheRealShadow Nov 01 '21 at 10:18
  • What is `session` (database? saas? onpremise?) ? – hpchavaz Nov 01 '21 at 10:24
  • Session is the connection to the database. The database and the running scripts are on the same machine during testing and when going to a productive environment also will be on the same server. I've added the session to the main post – TheRealShadow Nov 01 '21 at 10:37
  • You have a bunch of `if`s but if you `timeit` using simple variables you'll see that all your tests take less than 1 second on 1 million iterations - so the issue is with SqlAlchemy. Maybe [this post](https://stackoverflow.com/questions/270879/efficiently-updating-database-using-sqlalchemy-orm) helps? – gimix Nov 01 '21 at 10:40
  • I agree. It looks more like a database optimization problem than a python one. – hpchavaz Nov 01 '21 at 10:52
  • Thanks both for the reactions so far. @gimix I'll have a look into the post and see if I can increase the possible speed that way. – TheRealShadow Nov 01 '21 at 11:04
  • What's in the database? Think about using a _single_ `SELECT` to process _all_ the data in a single pass. – Rick James Nov 01 '21 at 15:33
  • I'm trying to get it all at once, for that I'm using session.query(VillageNew).filter(or_(VillageNew.wood_stock != VillageNew.max_storage, VillageNew.stone_stock != VillageNew.max_storage, VillageNew.iron_stock != VillageNew.max_storage)).all(): which will also filter if there are values it doesn't need to update to scale down the amount needed to be written back. @gimix, the post did actually help a lot, I could decrease the timer from 1 minute and 40 seconds back to 30-34 seconds. I only have an issue on implementing the second solution from the best chosen answer (which should be faster) – TheRealShadow Nov 01 '21 at 21:51
  • I've added the new code example after the update to the main post due character limit within comments. – TheRealShadow Nov 01 '21 at 21:54
  • @RickJames in the database currently only are the following data: current_wood_stock (+one for stone and one for iron). For all 3 types production per hour and maximum storage capacity. – TheRealShadow Nov 01 '21 at 22:26
  • `OR` does not optimize well. Instead, run one query for each part of the `OR`. Then let's see the table definition to discuss indexes. – Rick James Nov 02 '21 at 01:29
  • @RickJames I did try to run them separate but then the time needed for the whole loop gets bigger. Maybe I'm doing something wrong, but do you maybe have pseudo code of an example? – TheRealShadow Nov 02 '21 at 09:41
  • Nevermind I did run it wrongly did change it to an update query. I could decrease the time by this once again by half. – TheRealShadow Nov 02 '21 at 13:08

1 Answers1

0

In the end the fastest was pure SQL execution.

session.execute('UPDATE table.villagenew SET wood_stock = least(villagenew.wood_stock + villagenew.wood_production, villagenew.max_storage) WHERE villagenew.wood_stock < villagenew.max_storage')

In case feedback to the program was needed as in it is full: session.query(VillageNew).where(VillageNew.wood_stock < VillageNew.max_storage).update({VillageNew.wood_stock: VillageNew.wood_stock + VillageNew.wood_production}) session.query(VillageNew).where(VillageNew.stone_stock < VillageNew.max_storage).update({VillageNew.stone_stock: VillageNew.stone_stock + VillageNew.stone_production}) session.query(VillageNew).where(VillageNew.iron_stock < VillageNew.max_storage).update({VillageNew.iron_stock: VillageNew.iron_stock + VillageNew.iron_production}) session.query(VillageNew).where(VillageNew.wood_stock > VillageNew.max_storage).update({VillageNew.wood_stock: VillageNew.max_storage}) session.query(VillageNew).where(VillageNew.stone_stock > VillageNew.max_storage).update({VillageNew.stone_stock: VillageNew.max_storage}) session.query(VillageNew).where(VillageNew.iron_stock > VillageNew.max_storage).update({VillageNew.iron_stock: VillageNew.max_storage})

TheRealShadow
  • 61
  • 4