# It takes longer to count tables where data is being inserted

**URL:** <https://community.cratedb.com/t/it-takes-longer-to-count-tables-where-data-is-being-inserted/1720>\
**Category:** CrateDB\
**Created:** [February 22, 2024, 9:59am UTC](https://community.cratedb.com/t/it-takes-longer-to-count-tables-where-data-is-being-inserted/1720 "2024-02-22T09:59:03Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Jun\_Zhou](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/jun_zhou/32/1079_2.png) [@Jun\_Zhou](https://community.cratedb.com/u/Jun_Zhou)\
**Post date:** [February 22, 2024, 9:59am UTC](https://community.cratedb.com/t/it-takes-longer-to-count-tables-where-data-is-being-inserted/1720/1 "2024-02-22T09:59:03Z")

</div>

I’m using kafka connect sink data to table in cratedb, while it takes longer to count it. Is this normal ？

```auto
cr> select count(*) from ebs.rwmv_xxmtl_transactions;
+----------+
| count(*) |
+----------+
| 3259573 |
+----------+
SELECT 1 row in set (16.699 sec)

```

If the table isn’t being insert or updated data, counting is fast.

```auto
cr> select count(*) from ebs.rwmv_xxmtl_transactions;
+----------+
| count(*) |
+----------+
| 3388488 |
+----------+
SELECT 1 row in set (0.344 sec)

```

---

<div class="post-metadata">

**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:** [February 22, 2024, 10:06am UTC](https://community.cratedb.com/t/it-takes-longer-to-count-tables-where-data-is-being-inserted/1720/2 "2024-02-22T10:06:28Z")

</div>

Hi Jun Zhou,

when reading and writing at the same time, the system has to manage both workloads in one way or another, where the hardware or other kinds of low system resources may become a bottleneck.

For example, when running data I/O on a spindle disk, the disk head needs to move around heavily to serve both workloads at the same time, thus it will slow down the process.

With kind regards,  
Andreas.

---

<div class="post-metadata">

**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:** [February 22, 2024, 10:07am UTC](https://community.cratedb.com/t/it-takes-longer-to-count-tables-where-data-is-being-inserted/1720/3 "2024-02-22T10:07:36Z")

</div>

Are you only running this SELECT query on the table once in a while and no other read queries?

By default CrateDB disables the automatic refresh of tables that are only written to, but not read otherwise. So the first query might take significantly longer, as CrateDB first refreshes the table and then runs the query.

Could you try running …

```sql
ALTER TABLE ebs.rwmv_xxmtl_transactions SET ("refresh_interval" = '1000');

```

… to see if that improves the query speed?

---

<div class="post-metadata">

**Author:** ![Jun\_Zhou](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/jun_zhou/32/1079_2.png) [@Jun\_Zhou](https://community.cratedb.com/u/Jun_Zhou)\
**Post date:** [February 23, 2024, 3:28am UTC](https://community.cratedb.com/t/it-takes-longer-to-count-tables-where-data-is-being-inserted/1720/4 "2024-02-23T03:28:12Z")

</div>

Thanks a lot @proddata . There are no other queries on the table. After setting `refresh_interval=1000`, The query speed is significantly improved. 😀

```auto
cr> select count(*) from ebs.rwmv_xxmtl_transactions;
+----------+
| count(*) |
+----------+
| 4177902 |
+----------+
SELECT 1 row in set (0.004 sec)
cr> select count(*) from ebs.rwmv_xxmtl_transactions;
+----------+
| count(*) |
+----------+
| 4182494 |
+----------+
SELECT 1 row in set (0.002 sec)
cr> select count(*) from ebs.rwmv_xxmtl_transactions;
+----------+
| count(*) |
+----------+
| 4221167 |
+----------+
SELECT 1 row in set (0.003 sec)

```