# SQL Without LIMIT unoptimized ? doc Schema performance different compared to other schemas? **URL:** \ **Category:** CrateDB\ **Created:** [March 28, 2021, 7:56pm UTC](https://community.cratedb.com/t/sql-without-limit-unoptimized-doc-schema-performance-different-compared-to-other-schemas/601 "2021-03-28T19:56:19Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![MikeMax](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/mikemax/32/249_2.png) [@MikeMax](https://community.cratedb.com/u/MikeMax)\ **Post date:** [March 28, 2021, 7:56pm UTC](https://community.cratedb.com/t/sql-without-limit-unoptimized-doc-schema-performance-different-compared-to-other-schemas/601/1 "2021-03-28T19:56:19Z")
Hi everyone 🙂 I have two strange behaviours. (i’m testing in nightly Build 4.5, and i will test on previous version) On the same CrateDB Server, i have two schemas : - Doc schema - Bonjour schema Both have the same tables and datas (table parameters are the same). ![image](https://us1.discourse-cdn.com/flex020/uploads/crate/original/1X/59cf7c4b6ba7f2d7984ff9966483f87542723000.png) But performances are strangely different depending on schema and also if LIMIT clause is present or not : in Red : On schema “Bonjour” In Blue : On schema “Doc” ![image](https://us1.discourse-cdn.com/flex020/uploads/crate/original/1X/bbbe3991d74658629d4ab9e1f3a25b1691f81a27.png) Any idea ? ---
**Author:** ![amotl](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/amotl/32/617_2.png) [@amotl](https://community.cratedb.com/u/amotl)\ **Post date:** [March 29, 2021, 10:28am UTC](https://community.cratedb.com/t/sql-without-limit-unoptimized-doc-schema-performance-different-compared-to-other-schemas/601/2 "2021-03-29T10:28:56Z")
Dear Mike, thanks for your report. Is the behavior reproducible in a way that reissuing both statements without `LIMIT` clauses once more will also yield a long query duration? The reason for the initial (longer) duration might be some initialization overhead. May I ask what hardware and environment you are running CrateDB on and how your configuration looks like? Having enough memory available for CrateDB is crucial for reasonable operation. That the first query took 30 seconds for answering might indicate something into the direction that the environment CrateDB is running on needs some time to get ready. It might also indicate that the number of configured shards is too high for the fair amount of (~100000?) records as this would definitively induce a significant overhead. With kind regards, Andreas. ---
**Author:** ![MikeMax](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/mikemax/32/249_2.png) [@MikeMax](https://community.cratedb.com/u/MikeMax)\ **Post date:** [March 30, 2021, 4:37am UTC](https://community.cratedb.com/t/sql-without-limit-unoptimized-doc-schema-performance-different-compared-to-other-schemas/601/3 "2021-03-30T04:37:35Z")
Hi Andreas, it seems after re-importing my 100 000 Rows into my two different schemas, performances are now the same for both. However, even after re-importing, the **SELECT \* from users** (containing 100 001 rows) and **SELECT \* from users LIMIT 1000000** (which returns the same result because i have only 100 001 rows) still does a different thing (done multiple times) : ![image](https://us1.discourse-cdn.com/flex020/uploads/crate/original/1X/ca2f95984588895725095ab5d53dcb45e30b18ca.png) In CrateDB WebAdmin UI Console, i can’t reproduce due to LIMIT security 🙂 (but with a LIMIT, i have the same duration results as in my CrateDBAdmin .Net client). ---
**Author:** ![MikeMax](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/mikemax/32/249_2.png) [@MikeMax](https://community.cratedb.com/u/MikeMax)\ **Post date:** [March 31, 2021, 11:35am UTC](https://community.cratedb.com/t/sql-without-limit-unoptimized-doc-schema-performance-different-compared-to-other-schemas/601/4 "2021-03-31T11:35:11Z")
I have also tried with a Where clause (just to be sure) : ![image](https://us1.discourse-cdn.com/flex020/uploads/crate/original/1X/9ff7fb49b4e3f8c2241d34352ad217ce3adf9ac7.png) And i have tried with one result only … The limit clause at 1 performs about 10x faster than without the limit clause … ![image](https://us1.discourse-cdn.com/flex020/uploads/crate/original/1X/d1fdf1988281d6eb89943ca11743320bcf487544.png) So, in conclusion … we would count(_) results before systematically, and re-request with the Count(_) result in the LIMIT clause 😉 … Something is boosting requests with LIMIT clause, even if the limit is over the number of resulted rows. This behaviour is totally hidden in Web Admin UI Console due to LIMIT security clause 🙂 (but in custom applications … it may not be the case …) I’m’ using CrateDB 4.5 in a Windows Test Machine (Core I7, 16GB RAM) with nothing running on it … **But a good info : on the same machine, i have switched for the CrateDB 4.4.2 and 4.1.4 and i can’t reproduce the problem (with same tables and data and config… only one node)…** You can reproduce that behaviour on version 4.5 on your side ? ---
**Author:** ![amotl](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/amotl/32/617_2.png) [@amotl](https://community.cratedb.com/u/amotl)\ **Post date:** [March 31, 2021, 12:37pm UTC](https://community.cratedb.com/t/sql-without-limit-unoptimized-doc-schema-performance-different-compared-to-other-schemas/601/5 "2021-03-31T12:37:15Z")
Dear Mike, thank you for investigating further. > [@MikeMax](#): > > And i have tried with one result only … The limit clause at 1 performs about 10x faster than without the limit clause … Sure, that is obvious, right? > [@MikeMax](#): > > Something is boosting requests with LIMIT clause, even if the limit is over the number of resulted rows. This is the thing I would like to follow up on and dig deeper. > [@MikeMax](#): > > I’m’ using CrateDB 4.5 > [@MikeMax](#): > > i have switched for the CrateDB 4.4.2 and 4.1.4 and i can’t reproduce the problem So, according to your observations, you believe it happened somewhere in between 4.4.2 and 4.5.0? There is one other release before 4.5.0 happened, namely 4.4.3. Maybe you can also check that one in order to narrow down your observations further? With kind regards, Andreas. ---
**Author:** ![MikeMax](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/mikemax/32/249_2.png) [@MikeMax](https://community.cratedb.com/u/MikeMax)\ **Post date:** [March 31, 2021, 12:58pm UTC](https://community.cratedb.com/t/sql-without-limit-unoptimized-doc-schema-performance-different-compared-to-other-schemas/601/6 "2021-03-31T12:58:40Z")
You have a quick link for the 4.4.3 Windows x64 package please ? 🙂 ---
**Author:** ![proddata](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/proddata/32/1379_2.png) [@proddata](https://community.cratedb.com/u/proddata)\ **Post date:** [March 31, 2021, 1:02pm UTC](https://community.cratedb.com/t/sql-without-limit-unoptimized-doc-schema-performance-different-compared-to-other-schemas/601/7 "2021-03-31T13:02:26Z")
[Index of /downloads/releases/cratedb/x64\_windows/](https://cdn.crate.io/downloads/releases/cratedb/x64_windows/) 😉 ---
**Author:** ![MikeMax](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/mikemax/32/249_2.png) [@MikeMax](https://community.cratedb.com/u/MikeMax)\ **Post date:** [March 31, 2021, 1:02pm UTC](https://community.cratedb.com/t/sql-without-limit-unoptimized-doc-schema-performance-different-compared-to-other-schemas/601/8 "2021-03-31T13:02:51Z")
yes i found it sorry 😉 ---
**Author:** ![MikeMax](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/mikemax/32/249_2.png) [@MikeMax](https://community.cratedb.com/u/MikeMax)\ **Post date:** [March 31, 2021, 1:15pm UTC](https://community.cratedb.com/t/sql-without-limit-unoptimized-doc-schema-performance-different-compared-to-other-schemas/601/9 "2021-03-31T13:15:52Z")
I can reproduce it with the 4.4.3 version (but not for the LIMIT 1 with a where clause as the 4.5 version) : ![image](https://us1.discourse-cdn.com/flex020/uploads/crate/original/1X/f7d560b221b72008d220d94ed833913086046033.png) ---
**Author:** ![MikeMax](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/mikemax/32/249_2.png) [@MikeMax](https://community.cratedb.com/u/MikeMax)\ **Post date:** [March 31, 2021, 1:22pm UTC](https://community.cratedb.com/t/sql-without-limit-unoptimized-doc-schema-performance-different-compared-to-other-schemas/601/10 "2021-03-31T13:22:12Z")
> [@amotl](#): > > > [@MikeMax](#): > > > > And i have tried with one result only … The limit clause at 1 performs about 10x faster than without the limit clause … > > Sure, that is obvious, right? No because i had a where clause which returned only one result with or without the limit clause 🙂 but this usecase is not reproductible in 4.4.3 ---
**Author:** ![amotl](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/amotl/32/617_2.png) [@amotl](https://community.cratedb.com/u/amotl)\ **Post date:** [March 31, 2021, 1:39pm UTC](https://community.cratedb.com/t/sql-without-limit-unoptimized-doc-schema-performance-different-compared-to-other-schemas/601/11 "2021-03-31T13:39:34Z")
Hi Mike, thank you again. > [@MikeMax](#): > > I can reproduce it with the 4.4.3 version All right, so the observed performance regression on the _with or without `LIMIT` clause_ thing might have slipped in between 4.4.2 and 4.4.3, right? > [@MikeMax](#): > > But not for the `LIMIT 1` with a `WHERE` clause as the 4.5 version. This use case is not reproducible in 4.4.3. All right, so the observed performance regression on the _with a `WHERE` clause only returning one record, even adding a `LIMIT 1` will gain more performance_ thing only happened between 4.4.3 and 4.5.0, right? With kind regards, Andreas. ---
**Author:** ![MikeMax](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/mikemax/32/249_2.png) [@MikeMax](https://community.cratedb.com/u/MikeMax)\ **Post date:** [March 31, 2021, 1:49pm UTC](https://community.cratedb.com/t/sql-without-limit-unoptimized-doc-schema-performance-different-compared-to-other-schemas/601/12 "2021-03-31T13:49:52Z")
That’s right. You’ve well summarized 🙂 ---
**Author:** ![MikeMax](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/mikemax/32/249_2.png) [@MikeMax](https://community.cratedb.com/u/MikeMax)\ **Post date:** [March 31, 2021, 3:17pm UTC](https://community.cratedb.com/t/sql-without-limit-unoptimized-doc-schema-performance-different-compared-to-other-schemas/601/13 "2021-03-31T15:17:32Z")
Ok, other test : I have installed the 4.5 version on a Linux Debian Stretch on a dedicated server. I have the same behaviour on LIMIT Clauses but not on the WHERE + LIMIT 1 usecase (i think we can forget it as it does not have the same behaviour on linux (which is OK) and windows (not OK)). Another info, my table “users” have a column “id” which is a PRIMARY KEY. Table rows have been initially imported (by JSON files) on a **disordered** manner (thanks to multi-threading ! :p) ![image](https://us1.discourse-cdn.com/flex020/uploads/crate/original/1X/2d4a86284eba14ba5bb7c2e5755832d113ab63bf.png) So if i do a **SELECT \* from users ORDER BY id** , performances are now equal to the same request with a LIMIT 1000000 (but without the ORDER BY) … I can’t see the link between the ORDER BY [primary key] and the LIMIT clause (which should takes place after the query results) but optimization has certainly multiple paths (and passes :p) : ![image](https://us1.discourse-cdn.com/flex020/uploads/crate/original/1X/f16860cc0b2d72dbdae186de954be0dd4a682f43.png) Finally… i don’t know what to think about all of that 🙂