How to increase performace on a sqlite3 database that uses QT?

Question

I am using QSQlQuery on a sqlite3 database. To fetch a particular item , I was populating the result from 4 different tables. I thought joining the tables would increase the performance/speed and get the result faster. So I joined 2 tables initially but it takes longer time to fetch the data after joining the tables (?)

Any suggestion on how to improve the performance would be really appreciated. Also, I was looking at the http://qt-project.org/doc/qt-4.8/qsqlquery.html and it is mentioned that using setForwardOnly would increase the performance on some databases. Any idea if it would work for SQLite3?

Thanks!

Can you post your table schemas and/or SQL queries joined and not joined (you can simplify them for demonstration if you want)? — , Apr 16 '12 at 16:00
If you believe you have found the solution to your problem, please provide it as an answer. — SingleNegationElimination, Apr 16 '12 at 18:57
I added the solution to the question itself. I dint have enough permissions to provide an answer on the same day I posted my question. — user1065969, Apr 18 '12 at 20:34

score 0 · Accepted Answer · answered Apr 18 '12 at 20:36

According to this link,

http://sqlite.org/cvstrac/wiki?p=PerformanceTuning

SQLite implements JOIN USING by translating the USING clausing into some extra WHERE clause terms. It does the same with NATURAL JOIN and JOIN ON. So while those constructs might be helpful to the human reader, they don't really make any difference to SQLite's query optimizer.

-I was wrong to join two tables and expect the fetch to be faster. It does not work with SQLite database. Instead using a "where" clause and joining two results directly definitely has some positive impact on the performance.

(example :

select * from A,B where A.id = B.id where A.id = 1; instead of

select * from A left outer join B on A.id = B.id where A.id = 1)

The SQLite translates first statement to second before compiling and you could save on the small amount of CPU time by directly using the second statement

How to increase performace on a sqlite3 database that uses QT?

1 Answers1