9

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)?

halfer
  • 19,824
  • 17
  • 99
  • 186
thelolcat
  • 10,995
  • 21
  • 60
  • 102

1 Answers1

16

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.

GolezTrol
  • 114,394
  • 18
  • 182
  • 210
  • which is a fantastic reason to to use [N-Tiered](http://stackoverflow.com/a/312197/124069) architecture. Being able to swap one query with another as needs change is invaluable to a project. Don't do [premature optimization](https://en.wikipedia.org/wiki/Program_optimization), but do what works for you now in a way that can be altered later... AND DON'T FORGET YOUR TESTS! – Chase Florell Feb 27 '14 at 18:17