Databases
StackOverflow2013
Postsdbo
POJoin with Pythons sqlite module is slower than doing it manually
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POJoin with Pythons sqlite module is slower than doing it manually
    text
    copied!<p>I am using pythons built-in <code>sqlite3</code> module to access a database. My query executes a join between a table of 150000 entries and a table of 40000 entries, the result contains about 150000 entries again. If I execute the query in the <a href="http://code.google.com/p/sqlite-manager/" rel="nofollow noreferrer">SQLite Manager</a> it takes a few seconds, but if I execute the same query from python, it has not finished after a minute. Here is the code I use:</p> <pre><code>cursor = self._connection.cursor() annotationList = cursor.execute("SELECT PrimaryId, GOId " + "FROM Proteins, Annotations " + "WHERE Proteins.Id = Annotations.ProteinId") annotations = defaultdict(list) for protein, goterm in annotationList: annotations[protein].append(goterm) </code></pre> <p>I did the <code>fetchall</code> just to measure the execution time. Does anyone have an explanation for the huge difference in performance? I am using Python 2.6.1 on Mac OS X 10.6.4.</p> <p><strong>EDIT</strong></p> <p>I implemented the join manually, and this works much faster. The code looks like this:</p> <pre><code>cursor = self._connection.cursor() proteinList = cursor.execute("SELECT Id, PrimaryId FROM Proteins ").fetchall() annotationList = cursor.execute("SELECT ProteinId, GOId FROM Annotations").fetchall() proteins = dict(proteinList) annotations = defaultdict(list) for protein, goterm in annotationList: annotations[proteins[protein]].append(goterm) </code></pre> <p>So when I fetch the tables myself and then do the join in python, it takes about 2 seconds. The code above takes forever. Am I missing something here?</p> <p><strong>2nd EDIT</strong> I tried the same with <a href="http://code.google.com/p/apsw/" rel="nofollow noreferrer">apsw</a> now, and it works just fine (the code does not need to be changed at all), the performance it great. I'm still wondering why this is so slow with the <code>sqlite3</code>-module.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload