1
\$\begingroup\$

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
\$\endgroup\$
3
  • \$\begingroup\$ I would recommend writing any future python programs in python-3.x, as python2.x has reached it's end of life. \$\endgroup\$
    – Linny
    Jan 4, 2020 at 5:32
  • \$\begingroup\$ Thank you for nice suggestion, actually I did this in python 2 because of some limitation. Server not supporting python 3 and I can't run any python 3 code there. \$\endgroup\$ Jan 4, 2020 at 5:41
  • \$\begingroup\$ Then I would recommend upgrading your server to python 3. \$\endgroup\$
    – Linny
    Jan 5, 2020 at 23:09

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.