Python Forum

Full Version: SQL Query very slow
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
Pages: 1 2
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()
Minor fixup's
...
def db_connect(self):

    #print(f'\nConnecting to: {self.db}') ## Py >= 3.6
    print('\nConnecting to: {}'.format(self.db))

    #self.dbcon = sqlite3.connect(self.db) ## TypeError: argument 1 must be str, not PosixPath
    self.dbcon = sqlite3.connect(str(self.db))
...
Maybe it's the like operator? Can you clean up your data so that you can use an equality instead?
Quote:Maybe it's the like operator? Can you clean up your data so that you can use an equality instead?
Actually this query is the beginnings of an attempt to clean up the data.
I'm dealing with legacy information, and thought I might be able to clean it up in place, but might be better off dumping the data and cleaning up that instead.
You are combining two tables here: FROM ElectionResults2012, Places
but you only give the length of one table. What is the length of each table? The join then multiples those lengths together, and that could be millions of records of course.
Quote:You are combining two tables here: FROM ElectionResults2012, Places
but you only give the length of one table. What is the length of each table? The join then multiples those lengths together, and that could be millions of records of course.
This makes sense, so how do I speed up the query.
I was once an SQL whiz kid, but that goes back to the 1990's It's coming back, but slowly.
I would pick and add an appropriate JOIN to see what effect that has.
https://www.techonthenet.com/sqlite/joins.php
Quote:Places.Name like ElectionResults2012.Town + '%'

Is the name the only way the two tables are related? There's no ids or anything? I think that, by default, sqlite can't use indices for 'like' queries due to case sensitivity. You can check what the sql engine is doing with your query by asking it to explain query: https://www.sqlite.org/eqp.html

Depending on how much control you have, rebuilding the table to use a fulltext search engine would make it work as expected, if you use the keyword 'match' instead of 'like': https://www.sqlite.org/fts3.html

Or, you can use pragma declarations to let sql know you don't care about case sensitivity, then it might decide to use the index: https://www.sqlite.org/optoverview.html#...timization
https://stackoverflow.com/a/8586390

sqlite apparently doesn't let you alter tables that already exist, so you can't modify the collation of a preexisting table. But you can create a temporary table to hold the contents, drop the table, recreate it using the proper collation, then put the data back in:https://stackoverflow.com/a/47469890

CREATE TABLE table01 (id, name countrycode, comment );
INSERT INTO table01 SELECT id, name, countrycode, comment FROM table;
DROP TABLE table;
CREATE TABLE table (
   id       integer PRIMARY KEY AUTOINCREMENT,
   name     text COLLATE NOCASE
   country  integer,
   comment  text COLLATE NOCASE
);
INSERT INTO table (id, name, countrycode, comment)
    SELECT id, name, countrycode, comment FROM table01;
DROP TABLE table01;
Quote:Is the name the only way the two tables are related? There's no ids or anything?
Unfortunately yes, however new tables will have PlaceId code

Quote:Depending on how much control you have, rebuilding the table to use a fulltext search engine would make it work as expected, if you use the keyword 'match' instead of 'like'
This is a possibility, however once Id's have been added, this query will never have to be performed again.

Quote:Or, you can use pragma declarations to let sql know you don't care about case sensitivity, then it might decide to use the index:
probably a good idea

Quote:sqlite apparently doesn't let you alter tables that already exist, so you can't modify the collation of a preexisting table. But you can create a temporary table to hold the contents, drop the table, recreate it using the proper collation, then put the data back in:https://stackoverflow.com/a/47469890

sqlite is a temporary solution. I plan on converting all to PostGreSQL ASAP.
legacy prevents me from doing that until I put out some fires that I didn't start.

I will try your code suggestion, it looks like a good solution.

It's coming back to me slowly, it's been quite a while since I had to so any SQL other than simple stuff, But had many years experience with ORACLE, Sybase and Informix (20+ years ago)
I know several ways to speed up SQL query. I would advise you to try the following:
1. Review the execution elan
2. Ensure that all joins are leveraging indexes
3. Try to join on data types such as integers rather than text-based data types such as varchar or nvarchar
Pages: 1 2