Sqlite subqueries : in one big query or in a for loop ?

Question

I was planning to benchmark that but since it's a lot of work, I'd like to check if I didn't miss any obvious answer before.

I have a huge query that gets some more details for each row with a subquery.

Each row is then used in a ListAdapter that is plugged in a ListView, so another loop take each row one by one to make it a ListItem.

What do you think is more efficient :

• Keeping the subqueries in the SQL mess, counting on the SQL engine to make optimizations .
• Taking out the subqueries in the ListAdapter loop, so we lazy load the details on display : much more readable but I'm afraid too many hit would slow down the process.

Two important things :

• I can't rewrite the big SQL chunk to get rid of the subqueries. I know it would be better, but I failed to do so.
• As far as I can tell, a list won't contain more than 1000 items, and it's a desktop app so there is no concurrency. Is this even relevant to care about perf in that case ? If not, I'd still be interested in the answser for a hight traffic web site anyway. It's good to know...

Answer 1

What about using cursors?

I would prefer using a big query and let my SQL engine optimize my query. Also I can't think of an example where it's better to do a loop outside SQL instead of using a "big" query or using cursors.

But the best way to know what's better is to benchmark it.

Good luck!

Answer 2

SQlite is a surprisingly good little engine, but it's not really about extra clever optimizations, and I wouldn't really consider it for a "high traffic web site". One big plus (for uses within its limitations) is that it can run in-process, so that the overhead of multiple queries is really small compared to one big query; if that's easiest to code, for your specific use case, I would really consider it (and doing it in a "lazy load" way, as you hint, might actually make the first screen of data appear faster!). As you suspect, it's unlikely that this will be a performance bottleneck, in your use case, so going for simpler and thus more reliable coding is an important plus.

If I was doing a high-traffic site, and using a richer, "heavier" engine such as PosgtreSQL, Oracle, SQL Server, or DB2, I would trust the optimizer much more. One thing I've noticed, however, is that I can often (alas, not always) change sub-queries into joins, and that often tends to improve performance (joins make it easier for the optimizer to use good indices, I think -- I have never coded a SQL optimizer myself, but that's my impression from staring at query execution plans from many engines for alternative forms of queries... that, of course, DOES assume you have good indices!-) -- this would have to be confirmed with a benchmark of the specific case in question, of course, but it would be my initial working assumption.