What is the performance of subqueries vs two separate select queries? [closed]

Question

Is one generally faster than the other with SQLite file databases?

Do subqueries benefit of some kind of internal optimization or are they handled internally as if you did two separate queries?

I did some testing but I don't see much difference, probably because my table is too small now (less than 100 records)?

Answer 1

It depends on many factors. Two separate queries means two requests. A request has a little overhead, but this weighs more heavily if the database is on a different server. For subqueries and joins, the data needs to be combined. Small amounts can easily be combined in memory, but if the data gets bigger, then it might not fit, causing the need to swap temporary data to disk, degrading performance.

So, there is no general rule to say which one is faster. It's good to do some testing and find out about these factors. Do some tests with 'real' data, and with the amount of data you are expecting to have in a year from now.

And keep testing in the future. A query that performs well, might suddenly become slow when the environment or the amount of data changes.