Improving SQLITE3 Left Join Performance

Question

I occasional write simple yet long Python scripts using Sqlite3 (I'm not proficient with functional or object oriented programming... yet)

Usually my Sqlite3 is blazing fast. I'm used to looping through lists of SELECT statements with each SELECT taking a split second, and writing tables to text files in HTML markup. But, this is the first time I've done a LEFT JOIN in Sqlite3, and its taking much longer to complete the script than I had thought. I figured a couple of minutes perhaps, but it's taking several hours to run though all the queries.

I added print statements to narrow down that this LEFT JOIN, within a loop, is where the script hangs up for several minutes, instead taking a split second to perform. It's definitely between the SELECT and the .fetchall.

I have a couple of questions:

1. Is it normal for Sqlite3 to hang on LEFT JOIN (or any other JOIN)?
2. Did I code something the wrong way?

I need the code to work fast. If the code appears professional that's definitely a bonus.

print "selecting...",
cur.execute("SELECT \"b\".\"Disposals\" AS \"Disposals\", \
                    \"a\".\"ASSET NUMBER\" AS \"Record\", \
                    \"a\".\"DESCRIPTION\" AS \"DESCRIPTION\", \
                    \"a\".\"NAME_2012\" AS \"GIS_NAME_2012\", \
                    \"a\".\"Notes\" AS \"GIS_Notes\", \
                    \"a\".\"Other\" AS \"GIS_Other\", \
                    \"a\".\"QC_place\" AS \"GIS_place\", \
                    \"a\".\"QC_county\" AS \"GIS_county\", \
                    \"a\".\"lon_\" AS \"Longitude\", \
                    \"a\".\"lat_\" AS \"Latitude\" \
FROM \"resultsPoints\" AS \"a\" \
LEFT JOIN \"html_Tables\" AS \"b\" \
  ON (\"a\".\"ASSET NUMBER\" = \"b\".\"Assetnum\") \
WHERE \"a\".\"DEPARTMENT NUMBER\" LIKE ? \
  OR \"a\".\"AGENCY_BoB\" LIKE ? \
GROUP BY \"a\".\"ASSET NUMBER\" \
ORDER BY \"a\".\"ASSET NUMBER\"",(sqlHunt, sqlHuntBoB))

tableData = cur.fetchall()
print "fetching..",

for r, row in enumerate(tableData):
print " - ",
   for c, column in enumerate(row):
       print ".",
       mysheets[bobAgency].write (r+2, c, str(column))

Answer 1

LEFT JOINS are not inherently slow. It really depends on how you set it up. Are there any indexes on b.Assetnum? Having an index on that would probably speed up your joins tremendously. It probably is doing a full table scan on the join right now if you don't have any indexes.

Answer 2

You could use a different quote style to reduce the number of escaping needed, in this case: single quote ' or three quotes ''' or """.

cur.execute('SELECT "b"."Disposals" AS "Disposals", \
                    "a"."ASSET NUMBER" AS "Record", \
[...]
ORDER BY "a"."ASSET NUMBER"',(sqlHunt, sqlHuntBoB))

If you use three quotes, you do not need to escape that quote character. Also, with three quotes, you can write a multi-line string directly (except the last quote must be escaped, but if we add a space before the three quotes, the escaping is not necessary).

cur.execute("""SELECT "b"."Disposals" AS "Disposals", 
                    "a"."ASSET NUMBER" AS "Record",
[...]
ORDER BY "a"."ASSET NUMBER\"""",(sqlHunt, sqlHuntBoB))

Beware that this is not equal to the first example: the second example have newlines in the string.