Insert into temporary table taking time

Question

Trying to insert around 4500 inserts into a temp table. its taking 45 min to complete the insert. Cant insert the data into a physical table. select has constant but different values. Checked by running only the select which is itself taking time.

insert into #tablename select '2021-11-30',15,295865129,189627539,295865129,0,0,680892,'abc'

insert into #tablename select '2021-12-31',15,303838069,191276820,303838069,0,0,700544,'abc'

Answer 1

Hello @Vidya Sankar

Thanks for the question and using MS Q&A platform.

There could be several reasons why inserting data into a temporary table in Azure Synapse Analytics is taking a long time. Here are some possible causes and solutions:

1. Table size: The size of the temporary table could be a factor. If the table has a large number of rows or columns, it could take longer to insert the data. In this case, you could try to reduce the size of the table by removing unnecessary columns or filtering the data before inserting it.
2. Indexes: If the temporary table has indexes, they could slow down the insert operation. You could try dropping the indexes before inserting the data and re-creating them after the insert is complete.
3. Resource contention: If other queries are running on the same database or server, they could be competing for resources and slowing down the insert operation. You could try running the insert operation during off-peak hours or increasing the resources available to the database.
4. Network latency: If the data is being inserted from a remote location or over a slow network connection, it could take longer to complete the insert operation. You could try optimizing the network connection or copying the data to a local location before inserting it.
5. Query optimization: The SELECT statement that is being used to populate the temporary table could also be a factor. You could try optimizing the SELECT statement by using appropriate indexes or rewriting the query to be more efficient.

It's worth noting that inserting 4500 rows into a temporary table should not take 45 minutes, so it's possible that there is an underlying issue that needs to be addressed. If none of the above solutions help, you may want to consider opening a support ticket.

For more details, refer to below articles:

Monitor tempdb
Temporary tables in dedicated SQL pool in Azure Synapse Analytics
Troubleshoot dedicated SQL pool (formerly SQL DW) in Azure Synapse Analytics

Hope this helps. Do let us know if you any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.