SQLAlchemy, left outer join query taking a very long time (SQLite)
I have a Pylons application using SQLAlchemy to interact with a SQLite db. When one of my searches results in an OUTER LEFT JOIN query, the system takes a VERY long time to return results (i.e., several minutes). The puzzling thing is that if I run an equivalent query at the SQLite command line there is no problem. Also, when the RDBMS is MySQL the query does not take a long time. Here are the schemas of the relevant tables (form and gloss). CREATE TABLE form ( id INTEGER NOT NULL, transcription VARCHAR(255) NOT NULL, phoneticTranscription VARCHAR(255), morphemeBreak VARCHAR(255), morphemeGloss VARCHAR(255), comments TEXT, speakerComments TEXT, grammaticality VARCHAR(255), dateElicited DATE, datetimeEntered TIMESTAMP, datetimeModified TIMESTAMP, syntacticCategoryString VARCHAR(255), morphemeBreakIDs VARCHAR(1023), morphemeGlossIDs VARCHAR(1023), elicitor_id INTEGER, enterer_id INTEGER, verifier_id INTEGER, speaker_id INTEGER, elicitationmethod_id INTEGER, syntacticcategory_id INTEGER, source_id INTEGER, PRIMARY KEY (id), FOREIGN KEY(elicitationmethod_id) REFERENCES elicitationmethod (id), FOREIGN KEY(verifier_id) REFERENCES user (id), FOREIGN KEY(elicitor_id) REFERENCES user (id), FOREIGN KEY(speaker_id) REFERENCES speaker (id), FOREIGN KEY(syntacticcategory_id) REFERENCES syntacticcategory (id), FOREIGN KEY(source_id) REFERENCES source (id), FOREIGN KEY(enterer_id) REFERENCES user (id) ); CREATE TABLE gloss ( id INTEGER NOT NULL, gloss TEXT NOT NULL, glossGrammaticality VARCHAR(255), form_id INTEGER, datetimeModified TIMESTAMP, PRIMARY KEY (id), FOREIGN KEY(form_id) REFERENCES form (id) ); I'm using the SQLAlchemy ORM. When I print the relevant query object, I get the following. (This is the query that takes forever.) SELECT form.id AS form_id, form.transcription AS form_transcription, form.phoneticTranscription AS form_phoneticTranscription, form.morphemeBreak AS form_morphemeBreak, form.morphemeGloss AS form_morphemeGloss, form.comments AS form_comments, form.speakerComments AS form_speakerComments, form.grammaticality AS form_grammaticality, form.dateElicited AS form_dateElicited, form.datetimeEntered AS form_datetimeEntered, form.datetimeModified AS form_datetimeModified, form.syntacticCategoryString AS form_syntacticCategoryString, form.morphemeBreakIDs AS form_morphemeBreakIDs, form.morphemeGlossIDs AS form_morphemeGlossIDs, form.elicitor_id AS form_elicitor_id, form.enterer_id AS form_enterer_id, form.verifier_id AS form_verifier_id, form.speaker_id AS form_speaker_id, form.elicitationmethod_id AS form_elicitationmethod_id, form.syntacticcategory_id AS form_syntacticcategory_id, form.source_id AS form_source_id FROM form LEFT OUTER JOIN gloss ON form.id = gloss.form_id WHERE gloss.gloss LIKE :gloss_1 ORDER BY form.id DESC However, when I run the following at the SQLite prompt, the results are returned immediately. SELECT * FROM form LEFT OUTER JOIN gloss ON form.id = gloss.form_id WHERE gloss.gloss LIKE '%dog%' ORDER BY form.id DESC; Am I missing something obvious? I appreciate any help. Thanks -- You received this message because you are subscribed to the Google Groups pylons-discuss group. To post to this group, send email to pylons-discuss@googlegroups.com. To unsubscribe from this group, send email to pylons-discuss+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/pylons-discuss?hl=en.
Re: SQLAlchemy, left outer join query taking a very long time (SQLite)
On Nov 4, 2011, at 12:48 PM, Joel wrote: I have a Pylons application using SQLAlchemy to interact with a SQLite db. When one of my searches results in an OUTER LEFT JOIN query, the system takes a VERY long time to return results (i.e., several minutes). The puzzling thing is that if I run an equivalent query at the SQLite command line there is no problem. Also, when the RDBMS is MySQL the query does not take a long time. This sounds like SQLite's fault -- you can confirm that by running the *exact* query via the command line tool as well. -- Philip Jenvey -- You received this message because you are subscribed to the Google Groups pylons-discuss group. To post to this group, send email to pylons-discuss@googlegroups.com. To unsubscribe from this group, send email to pylons-discuss+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/pylons-discuss?hl=en.
Re: SQLAlchemy, left outer join query taking a very long time (SQLite)
Thanks for the suggestion Philip. However, if I run sqlite SELECT form.id AS form_id, form.transcription AS form_transcription, form.phoneticTranscription AS form_phoneticTranscription, form.morphemeBreak AS form_morphemeBreak, form.morphemeGloss AS form_morphemeGloss, form.comments AS form_comments, form.speakerComments AS form_speakerComments, form.grammaticality AS form_grammaticality, form.dateElicited AS form_dateElicited, form.datetimeEntered AS form_datetimeEntered, form.datetimeModified AS form_datetimeModified, form.syntacticCategoryString AS form_syntacticCategoryString, form.morphemeBreakIDs AS form_morphemeBreakIDs, form.morphemeGlossIDs AS form_morphemeGlossIDs, form.elicitor_id AS form_elicitor_id, form.enterer_id AS form_enterer_id, form.verifier_id AS form_verifier_id, form.speaker_id AS form_speaker_id, form.elicitationmethod_id AS form_elicitationmethod_id, form.syntacticcategory_id AS form_syntacticcategory_id, form.source_id AS form_source_id FROM form LEFT OUTER JOIN gloss ON form.id = gloss.form_id WHERE gloss.gloss LIKE '%dog%' ORDER BY form.id DESC; The results are returned instantly. The above is the *exact* query generated by SQLAlchemy, except that I've replaced :gloss_1 with '%dog%', which (I'm assuming) is what SQLAlchemy itself does. So, does this mean that it's not SQLite's fault? Or am I not really running the same query that SQLAlchemy generates for SQLite? Joel On Nov 4, 1:42 pm, Philip Jenvey pjen...@underboss.org wrote: On Nov 4, 2011, at 12:48 PM, Joel wrote: I have a Pylons application using SQLAlchemy to interact with a SQLite db. When one of my searches results in an OUTER LEFT JOIN query, the system takes a VERY long time to return results (i.e., several minutes). The puzzling thing is that if I run an equivalent query at the SQLite command line there is no problem. Also, when the RDBMS is MySQL the query does not take a long time. This sounds like SQLite's fault -- you can confirm that by running the *exact* query via the command line tool as well. -- Philip Jenvey -- You received this message because you are subscribed to the Google Groups pylons-discuss group. To post to this group, send email to pylons-discuss@googlegroups.com. To unsubscribe from this group, send email to pylons-discuss+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/pylons-discuss?hl=en.
