http://www.filesavr.com/dbexample <http://www.filesavr.com/dbexample>
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
