# "limit 100" in query is not always respected **URL:** \ **Category:** Uncategorized\ **Created:** [June 10, 2024, 10:08pm UTC](https://community.dremio.com/t/limit-100-in-query-is-not-always-respected/11936 "2024-06-10T22:08:30Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![capnjosh](https://avatars.discourse-cdn.com/v4/letter/c/f475e1/32.png) [@capnjosh](https://community.dremio.com/u/capnjosh)\ **Post date:** [June 10, 2024, 10:08pm UTC](https://community.dremio.com/t/limit-100-in-query-is-not-always-respected/11936/1 "2024-06-10T22:08:30Z")
There are times when the “Preview” returns no rows. In those cases I have to use “Run”. That’s fine, but when I submit a query where I provide a “limit 1000”, sometimes Dremio submits a query to Snowflake that has no limit at all. That is, Dremio requests the entire table from Snowflake. This essentially breaks my ability to craft a query on some tables because it takes forever for the query to return. Submitting the same query to Snowflake is nearly instant because it has the “limit 100”. Why does Dremio do this? I can imagine it would have to do this in case of some tables that have, e.g. s3-stored csv files. But in the case of Snowflake, this should never happen. Is there some way to prevent Dremio from doing this? ---
**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\ **Post date:** [June 13, 2024, 7:14am UTC](https://community.dremio.com/t/limit-100-in-query-is-not-always-respected/11936/2 "2024-06-13T07:14:39Z")
@capnjosh I see you have 2 questions #1 Why does Preview sometimes does not return any result - This could happen if the filter is applied on the sample. Do you have a profile? #2 Dremio retrieves all records from Snowflake - This should not happen, what can happen though is that Dremio would have selected a batch and even though there is a limit, the batch will go through but that still not be all the records in the Snowflake table, any chance you have the profile for this too? ---
**Author:** ![capnjosh](https://avatars.discourse-cdn.com/v4/letter/c/f475e1/32.png) [@capnjosh](https://community.dremio.com/u/capnjosh)\ **Post date:** [June 14, 2024, 4:35pm UTC](https://community.dremio.com/t/limit-100-in-query-is-not-always-respected/11936/3 "2024-06-14T16:35:15Z")
I replied to the other thread about Preview sometimes not returning any results. Regarding getting a profile for this “limit 100” thing, here is a profile: [37f3dcab-46f7-40d4-876d-369b44ca3177.zip](https://community.dremio.com/uploads/short-url/1v6uwEsI0zraak0DxILFLuEohO8.zip) (22.1 KB) This is the query: ```auto SELECT cast(player_start_date as date) as player_start_date, cast(event_date as date) as eventDate, datediff(cast(event_date as date),cast(player_start_date as date)) as days_between from "Snowflake-PQ3".PQ3.RAW."EVENT_GAMETRANSACTION_RAW" limit 100 ``` It returns with the expected results, but I can see in Snowflake that Dremio submitted a query to get all the rows from the table rather than just 100. ---
**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\ **Post date:** [June 24, 2024, 6:12am UTC](https://community.dremio.com/t/limit-100-in-query-is-not-always-respected/11936/4 "2024-06-24T06:12:30Z")
@capnjosh As you can see from the plan, the LIMIT is applied above the scan so 1 batch is returned (see number of batched in JDBC\_SUB\_SCAN) which is 3,968 records and then the LIMIT is applied. Can you please send profile for below query? ```auto SELECT player_start_date from "Snowflake-PQ3".PQ3.RAW."EVENT_GAMETRANSACTION_RAW" limit 100 ``` ---
**Author:** ![capnjosh](https://avatars.discourse-cdn.com/v4/letter/c/f475e1/32.png) [@capnjosh](https://community.dremio.com/u/capnjosh)\ **Post date:** [June 24, 2024, 6:44pm UTC](https://community.dremio.com/t/limit-100-in-query-is-not-always-respected/11936/5 "2024-06-24T18:44:19Z")
Attached is the profile for this query: ```auto [53ad29c5-a5e7-46b6-828e-42087962f13f.zip|attachment](upload://7vCtbBDcJn9ebJZ9S6396iUANyv.zip) (21.3 KB) SELECT player_start_date from "Snowflake-PQ3".PQ3.RAW."EVENT_GAMETRANSACTION_RAW" limit 100 ``` Of note, it executed and completed very quickly. Snowflake saw a query come through that included the “limit 100”: ```auto -- this comes from the "query history" view in Snowflake SELECT "EVENT_GAMETRANSACTION_RAW"."PLAYER_START_DATE" FROM "PQ3"."RAW"."EVENT_GAMETRANSACTION_RAW" LIMIT 100 ``` Whereas with this query: ```auto SELECT cast(player_start_date as date) as player_start_date, cast(event_date as date) as eventDate, datediff(cast(event_date as date),cast(player_start_date as date)) as days_between from "Snowflake-PQ3".PQ3.RAW."EVENT_GAMETRANSACTION_RAW" limit 100 ``` Snowflake sees only this come through (no “limit”): ```auto -- this comes from the "query history" view in Snowflake SELECT "EVENT_GAMETRANSACTION_RAW"."PLAYER_START_DATE", "EVENT_GAMETRANSACTION_RAW"."EVENT_DATE" FROM "PQ3"."RAW"."EVENT_GAMETRANSACTION_RAW" ``` It appears that Dremio decided to get all the raw data from Snowflake and then use its own cast() and datediff() functions before it applied the “limit”. ---
**Author:** ![capnjosh](https://avatars.discourse-cdn.com/v4/letter/c/f475e1/32.png) [@capnjosh](https://community.dremio.com/u/capnjosh)\ **Post date:** [June 25, 2024, 5:45pm UTC](https://community.dremio.com/t/limit-100-in-query-is-not-always-respected/11936/6 "2024-06-25T17:45:38Z")
I’m seeing this same problem with other queries like this, where it’s a select query with some transformations on the columns and a “group by” clause, e.g. remove double-quotes, cast to a different data type and then group by the resulting values, etc. Dremio sends a query to Snowflake that returns the relevant columns, but all the rows; and then Dremio applies the transformations and grouping. This results in very slow query times when Dremio is geographically far away from Snowflake; it downloads all the raw data, when it would be dramatically faster to just have Snowflake do the transformations and grouping. The scary part is that over time a query via that had been performant could suddenly become intensely slow; or a reflection could suddenly start taking hours to complete, when previously it was fast. ---
**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\ **Post date:** [June 26, 2024, 7:06pm UTC](https://community.dremio.com/t/limit-100-in-query-is-not-always-respected/11936/7 "2024-06-26T19:06:51Z")
@capnjosh Should not do a FTS (Full Table Scan), as I had said, atleast 1 batch needs to complete which in your case ws 4K records. Have you tried a similar query against Postgres or SQL server or Oracle or MySQL? What happens? The LIMIT is expected to be pushed down ---
**Author:** ![capnjosh](https://avatars.discourse-cdn.com/v4/letter/c/f475e1/32.png) [@capnjosh](https://community.dremio.com/u/capnjosh)\ **Post date:** [June 26, 2024, 7:20pm UTC](https://community.dremio.com/t/limit-100-in-query-is-not-always-respected/11936/8 "2024-06-26T19:20:42Z")
I haven’t tried this with any other database engines - Snowflake is where the vast majority of our data resides. I’ll see if I can get something set up and try it out.