I have developed this script to find the counts of attachment records stored in a table in PostgreSQL for different other tables. I check if the
res_id field of
ir_attachment table is existed in it's corresponding table
(res_model) and if it's not existed, I increase the counter. This script works fine and I get the desired result but it is slow with big number of records (9000000 -10000000). How can I improve this script's performance to optimize it? Should I use any SQL technic or bring changes in python code?
def delete_attachment(self): self.apps_cursor.execute(""" SELECT id,res_id,res_model FROM ir_attachment WHERE res_model IS NOT NULL AND res_id IS NOT NULL limit 5; """) counter = 0 attachment_records = self.apps_cursor.fetchall() print '=========================================Checking attachment records started=========================================' self.apps_cursor.execute(""" SELECT model FROM ir_model WHERE model IS NOT NULL; """) apps_models = self.apps_cursor.fetchall() apps_models = [model.get('model') for model in apps_models] for record in attachment_records: if record['res_model'] in apps_models: model_name = str(record['res_model'].replace('.', '_')) self.apps_cursor.execute(""" SELECT id FROM %s WHERE id=%s """ % (model_name, record['res_id'])) model_record = self.apps_cursor.fetchall() if not model_record: counter += 1 print 'Total attachment records with out res_id field existed in corresponding model:', counter
python-3.x, as python2.x has reached it's end of life. \$\endgroup\$