2
\$\begingroup\$

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))
\$\endgroup\$
  • 3
    \$\begingroup\$ The current question title, which states your concerns about the code, is too general to be useful here. Please edit to the site standard, which is for the title to simply state the task accomplished by the code. Please see How to get the best value out of Code Review: Asking Questions for guidance on writing good question titles. \$\endgroup\$ – Toby Speight Nov 27 '19 at 8:06
4
\$\begingroup\$

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.

| improve this answer | |
\$\endgroup\$
  • \$\begingroup\$ I added an Index for b.Assetnum (and for the heck of it a.ASSET NUMBER) -- The speed has gone up from 2 or 3 minutes per loop to about 1 or 2 seconds per loop. That fixed it...Perfect! \$\endgroup\$ – user12711 Aug 14 '13 at 20:01
2
\$\begingroup\$

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.

| improve this answer | |
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.