Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
SQL: COUNT(*) instead of EXISTS()
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
Do you go to the supermarket to count all their apples just to see if they have any apples? You don't. Likewise, you shouldn't run a COUNT(*)
query to check if the value is bigger than 0
. Use the EXISTS predicate, instead.
We've blogged about this and benchmarked the difference. It really does make a difference!
So, don't do this:
if (create.fetchValue(selectCount().from(AUTHOR)) > 0) { // ... }
But do this, instead:
if (create.fetchValue(exists(selectOne().from(AUTHOR)))) { // ... }
Of course, it's totally possible to embed this EXISTS
predicate in a more complex query and possibly avoid the unnecessary secondary roundtrip...
Feedback
Do you have any feedback about this page? We'd love to hear it!