# Slow insert into table **URL:** \ **Category:** Uncategorized\ **Created:** [May 27, 2024, 4:22pm UTC](https://community.dremio.com/t/slow-insert-into-table/11880 "2024-05-27T16:22:45Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![Ken](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/ken/32/5784_2.png) [@Ken](https://community.dremio.com/u/Ken)\ **Post date:** [May 27, 2024, 4:22pm UTC](https://community.dremio.com/t/slow-insert-into-table/11880/1 "2024-05-27T16:22:45Z")
I am using dbt-dremio to run an incremental model. In the first phase, it creates a \_\_dbt\_tmp table. It runs pretty quickly. ![image](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/9/9e2470bf9a3a9f7ac210a95b42d74e916ce7fed8.png) (Screenshot: Fast creation of \_\_dbt\_tmp) After that, in the second phase, it selects \* from the \_\_dbt\_tmp, and then insert into the target table. The second phase, however, runs pretty slowly. ![image](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/7/762a7cc331ee0a6f6cdf9a349c2cee0ff59a4e72.png) (Screenshot: Very slow insert from\_\_dbt\_tmp into target table) Attached below is the job profile of the long-running insert. [e0ae6a1c-8dc2-4565-9881-e460d2e5c7ca.zip](https://community.dremio.com/uploads/short-url/egSWK0xFkNxwMYfG8NZwGFBwpJG.zip) (20.0 KB) Not too sure what the cause is. ---
**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:** [May 28, 2024, 3:52am UTC](https://community.dremio.com/t/slow-insert-into-table/11880/2 "2024-05-28T03:52:33Z")
@Ken Where is the data on table `Minio.finance.l1.rollup_daily_hk_stock_shareholding_change__dbt_tmp` stored? From the name it look like MinIO. All the time is spent on wait time for table\_function 03-xx-02. Expand Operator Metrics and you will see NUM\_CACHE\_MISSES are matching with NUM\_CACHE\_HITS which also match with the total number of readers. Looks like a reporting issue that I will follow up. Total datafiles to be read are 30,162. Can you run select \* from this table a few times (from JDBC as UI will truncate) and then try the CTAS? ---
**Author:** ![Ken](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/ken/32/5784_2.png) [@Ken](https://community.dremio.com/u/Ken)\ **Post date:** [May 29, 2024, 1:54pm UTC](https://community.dremio.com/t/slow-insert-into-table/11880/3 "2024-05-29T13:54:31Z")
Hi @balaji.ramaswamy Thanks for following up. I have this job running once every day at night. Attached below is the log file in yesterday’s run. ![image](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/0/006898336cdd5ec27d80fe5c93ea557b9f8130d2.png) [c1e8234f-2eb5-4344-8a7f-a182f1705074.zip](https://community.dremio.com/uploads/short-url/aWRFA5ugWJHs4zXaigbk3VpwxRB.zip) (22.9 KB) It is still running 33 minutes for the simple insert. ========= In fact, I see that the \_\_dbt\_tmp table (i.e. the **source table** ) is created with a partition on sehk\_code. Actually, I just want the **destination table** with the partition. I feel like the insert is slow because there’s too many partition in the source table. Let me try to change/remove the partition and see how long it runs tonight ---
**Author:** ![Ken](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/ken/32/5784_2.png) [@Ken](https://community.dremio.com/u/Ken)\ **Post date:** [May 31, 2024, 12:47pm UTC](https://community.dremio.com/t/slow-insert-into-table/11880/4 "2024-05-31T12:47:53Z")
I changed the partition\_by to another field called `as_of_date` and the (INSERT INTO xxxx select \* from yyy) is now much faster. I believe the problem was the partition I chose. ---
**Author:** ![Benny\_Chow](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/benny_chow/32/5527_2.png) [@Benny\_Chow](https://community.dremio.com/u/Benny_Chow)\ **Post date:** [June 3, 2024, 7:12pm UTC](https://community.dremio.com/t/slow-insert-into-table/11880/5 "2024-06-03T19:12:56Z")
Yes, the problem was with over partitioning the source table. You had 30K parquet files with about 15 records per file. There’s too much overhead with opening and closing so many files for such a small dataset. Were you able to get the incremental model to work successfully? I.e. was there an incremental filter present in the query that was used to materialize that dbt\_tmp table? ---
**Author:** ![Ken](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/ken/32/5784_2.png) [@Ken](https://community.dremio.com/u/Ken)\ **Post date:** [June 4, 2024, 1:02am UTC](https://community.dremio.com/t/slow-insert-into-table/11880/6 "2024-06-04T01:02:57Z")
Hi Benny, Thanks for following up. The issue was not with \_\_dbt\_tmp. \_\_dbt\_tmp was created with lots of small partition and it caused problem in the second stage of selecting from \_\_dbt\_tmp into the final table. But the issue was already resolved after I changed to another partition key for the incremental model. I used date as the key, which is more appropriate, as I got new rows every day (and they all fall into the new day’s partition). Thank you! ---
**Author:** ![starlight](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/starlight/32/5725_2.png) [@starlight](https://community.dremio.com/u/starlight)\ **Post date:** [June 6, 2024, 11:58am UTC](https://community.dremio.com/t/slow-insert-into-table/11880/7 "2024-06-06T11:58:36Z")
Hey there, I totally got your query,the insertion process from the \_\_dbt\_tmp table to the target table is slower than expected. Analyzing the job profile provided can help identify any bottlenecks or inefficiencies. Optimizing SQL queries and ensuring proper Dremio environment configuration may improve performance. If needed, seek assistance from the dbt-dremio community or support team. ---
**Author:** ![dacopan](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/dacopan/32/4289_2.png) [@dacopan](https://community.dremio.com/u/dacopan)\ **Post date:** [June 19, 2024, 10:55pm UTC](https://community.dremio.com/t/slow-insert-into-table/11880/8 "2024-06-19T22:55:13Z")
> [@Benny\_Chow](#): > > the problem was with over partitioning the source table. You had 30K parquet files with about 15 records per file Hello Benny, please can you tech me how can you identify this in Query profile? this could be very helpful for other analyze jobs ---
**Author:** ![Benny\_Chow](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/benny_chow/32/5527_2.png) [@Benny\_Chow](https://community.dremio.com/u/Benny_Chow)\ **Post date:** [June 20, 2024, 5:56am UTC](https://community.dremio.com/t/slow-insert-into-table/11880/9 "2024-06-20T05:56:34Z")
You can use either the Visual Profile or the Query Profile’s Operator metrics to get partitioning and row count information. Basically, every Iceberg table scan is going to have a pattern that looks like this: ICEBERG\_SUB\_SCAN → TABLE\_FUNCTION → OTHER OPERATORS → TABLE\_FUNCTION ICEBERG\_SUB\_SCAN is a scan against the Iceberg manifest file and outputs the number of manifest files (after partition field pruning). TABLE\_FUNCTION is a scan against the manifest files and outputs the number of data files (after partition and non-partition field pruning). The second TABLE\_FUNCTION is the distributed scan against the Parquet data files which could also include row group pruning. (See NUM\_ROW\_GROUPS\_PRUNED) The output is the total number of records read from the Iceberg table. The NUM\_CACHE\_HITS and NUM\_CACHE\_MISSES is particularly important here for IO performance because it tells you about the state of the C3 cache. OTHER OPERATOR is a special case to handle data file pruning on filter expressions that cannot be pushed down into the Iceberg manifest file scan. The visual profile will sum the output rows across threads for each operator whereas the operator metrics will breakdown the max records per thread. This just sums back to the same total shown in the visual profile. Hope that helps!