Here is an example of some tables which I am having very poor performance querying. I was wondering if someone could give me some suggestions as to how I can optimize this.
I am using the adobe Air framework (AS3) to access the database. I make the connection to the file like this: sqlConnection = new SQLConnection(); sqlConnection.openAsync(dbfile); In my constructor I setup a parametrized sql statement that I will use to be running the query: stmtGetQuestionsNewSystemType = new SQLStatement(); stmtGetQuestionsNewSystemType.sqlConnection = sqlConnection; stmtGetQuestionsNewSystemType.text = 'SELECT DISTINCT Question.id AS id, Question.name AS name, Question.label AS label, Question.datatypeid AS datatypeid, Question.advanced AS advanced, Question.multivalue AS multivalue, Question.measurementtypeid AS measurementtypeid, Question.hotlist AS hotlist FROM main.Question, main.CategoryQuestions, main.ApplicationQuestions, main.TechnologyQuestions WHERE (Question.id = CategoryQuestions.questionid AND CategoryQuestions.categoryid = @categoryid ) OR (Question.id = ApplicationQuestions.questionid AND ApplicationQuestions.applicationid = @applicationid ) OR (Question.id = TechnologyQuestions.questionid AND TechnologyQuestions.technologyid = @technologyid ) ;'; stmtGetQuestionsNewSystemType.itemClass = QuestionVO; stmtGetQuestionsNewSystemType.addEventListener( SQLEvent.RESULT, function ( event:SQLEvent ):void { resultHandlerQuestionsNewSystemType.call( this, new ArrayCollection( stmtGetQuestionsNewSystemType.getResult().data ), _rowItem ); }); then whenever I run the query i merely do this: setParameters( stmtGetQuestionsNewSystemType, [ {name:"categoryid", value:rowItem.systemcategoryid}, {name:"applicationid", value:rowItem.systemapplicationid}, {name:"technologyid", value:rowItem.systemtechnologyid} ] ); stmtGetQuestionsNewSystemType.execute(); Things I did to improve efficiency: reused parametrized statements without modifying text property declare explicitly that the table is coming from database main declare are columns I want to retrieve in my select statement This query is still taking about 40 seconds to execute on a relatively fast computer. I am almost at my wits end because I can't figure out how to make this query execute quickly. I know that you have to be somewhat crafty to be efficient with sqlite so I was hoping someone could point me in the right direction. thanks, Felipe Aramburu
_______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users