Python Forum
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
SQL Query very slow
#1
This is sort of off topic, it involves python, but only as a wrapper around an sqlite3 query.
I have created an index on Places.Name, but this still takes several minutes to run on table with 12,691 rows.
What can I do to speed up?
from pathlib import Path
import os
import sqlite3


class SimpleQuery:
    def __init__(self):
        os.chdir(os.path.abspath(os.path.dirname(__file__)))
        self.homepath = Path('.')
        self.dbpath = self.homepath / '../..' / 'data' / 'National' / 'database'
        self.db = self.dbpath / 'Elections.db'

        self.dbcon = None
        self.dbcur = None

    def try_query(self):
        self.db_connect()
        sqlstr = "SELECT CandidateLName, CandidateFName, CandidateMInit, CandidateSuffix, Town, PlaceId, Name, StateId " \
            " FROM ElectionResults2012, Places WHERE Places.Name like  ElectionResults2012.Town + '%';"
        self.dbcon.execute(sqlstr)
        self.db_close()

    def db_connect(self):
        print(f'\nConnecting to: {self.db}')
        self.dbcon = sqlite3.connect(self.db)
        self.dbcur = self.dbcon.cursor()

    def db_close(self, rollback=False):
        if rollback:
            self.dbcon.rollback()
        else:
            self.dbcon.commit()
        self.dbcon.close()

    def db_commit(self):
        self.dbcon.commit()

if __name__ == '__main__':
    sq = SimpleQuery()
    sq.try_query()
Reply


Messages In This Thread
SQL Query very slow - by Larz60+ - May-18-2019, 11:02 PM
RE: SQL Query very slow - by MvGulik - May-19-2019, 12:23 PM
RE: SQL Query very slow - by ichabod801 - May-19-2019, 02:07 PM
RE: SQL Query very slow - by Larz60+ - May-19-2019, 03:47 PM
RE: SQL Query very slow - by richalt2 - May-20-2019, 04:32 PM
RE: SQL Query very slow - by Larz60+ - May-20-2019, 05:11 PM
RE: SQL Query very slow - by MvGulik - May-21-2019, 06:50 AM
RE: SQL Query very slow - by nilamo - May-21-2019, 06:12 PM
RE: SQL Query very slow - by Larz60+ - May-21-2019, 10:34 PM
RE: SQL Query very slow - by Mark87 - Jun-23-2019, 11:43 AM
RE: SQL Query very slow - by Larz60+ - Jun-23-2019, 04:11 PM

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020