Unirse al módulo sqlite de Pythons es más lento que hacerlo manualmente

Estoy usando sqlite3 módulo sqlite3 incorporado de pythons para acceder a una base de datos. Mi consulta ejecuta una unión entre una tabla de 150000 entradas y una tabla de 40000 entradas, el resultado contiene alrededor de 150000 entradas de nuevo. Si ejecuto la consulta en el Administrador de SQLite , toma unos segundos, pero si ejecuto la misma consulta desde Python, no ha terminado después de un minuto. Aquí está el código que utilizo:

 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) 

Hice el fetchall solo para medir el tiempo de ejecución. ¿Alguien tiene una explicación para la gran diferencia en el rendimiento? Estoy usando Python 2.6.1 en Mac OS X 10.6.4.

EDITAR

Implementé la unión manualmente, y esto funciona mucho más rápido. El código se ve así:

 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) 

Así que cuando busco las tablas yo mismo y luego hago la unión en python, toma aproximadamente 2 segundos. El código anterior lleva para siempre. ¿Me estoy perdiendo de algo?

2º EDICIÓN Intenté lo mismo con apsw ahora, y funciona bien (el código no necesita ser cambiado en absoluto), el rendimiento es excelente. Todavía me pregunto por qué esto es tan lento con el módulo sqlite3 .

Hay una discusión al respecto aquí: http://www.mail-archive.com/python-list@python.org/msg253067.html

Parece que hay un cuello de botella en el rendimiento del módulo sqlite3. Hay un consejo de cómo hacer sus consultas más rápido:

  • Asegúrese de que tiene índices en las columnas de unión
  • usar pysqlite

No ha publicado el esquema de las tablas en cuestión, pero creo que puede haber un problema con los índices, específicamente por no tener un índice en Proteins.Id o Annotations.ProteinId (o ambos).

Crea los índices SQLite como este

 CREATE INDEX IF NOT EXISTS index_Proteins_Id ON Proteins (Id) CREATE INDEX IF NOT EXISTS index_Annotations_ProteinId ON Annotations (ProteinId)