improve performance inserting into temp table

Question

improve performance inserting into temp table

Ismail Cassiem 61

Hi,

My execution plan show an high cost on a temp table insert to all the columns within the temp table
any ideas how i can improve the performance on this

DROP TABLE IF EXISTS #Working_usp_1
SELECT
col1,....col25
INTO #Working_usp_1
FROM

Results_usp_1

The sp calls other sp that creates temp tables that my session cant access to create a runtime index on the temp table
the below executes but still appears in my execution plan
DECLARE @Index1 AS nvarchar(max)
SET @Index1 = 'CREATE NONCLUSTERED INDEX IDX_Index6 on [dbo].[' + (SELECT TOP 1 name FROM tempdb.sys.tables WHERE [name] like '#Table1_%') + '] (col1]) INCLUDE (col2)'
EXEC sp_executesql @Index1

Please Help

regards

Accepted answer

9 additional answers

Your answer

Answer 1

Ronen Ariely 15,206

Hi XXXX,

Off-topic: I think you could spend some minutes to came up with a better nickname than a number

My execution plan show

You speak about the Execution Plan but you did not provided it. Please provide the Execution Plan (the full EP which mean the XML and not a screenshot), so we will have something to discuss about

high cost on a temp table insert to all the columns within the temp table

We cannot change the fact that writing to the disk cost. You can take some general actions to improve performance of INSERT like

(1) using fast SSD

(2) test the option to create the table fist and use INSERT INTO instead of SELECT INTO

(3) remember to drop temp tables as quickly: as you can

(4) Do NOT create indexes

Indexes meant to help in finding the rows and cannot help in INSERT

(5) tempdb serves all the databases and might be busy. Test the option of using regular table in the database if this fit your system

(6) Check the option of using a memory-optimized table variable

https://learn.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/faster-temp-table-and-table-variable-by-using-memory-optimization?view=sql-server-ver15

The sp calls other sp that creates temp tables instead

Are you sure that the issue in the INSERT and not the select for example? Well the Execution Plan might help in this. Try to simplifies the procedure

CREATE NONCLUSTERED INDEX I

Obviously create index not help you in insert data.

Try to provide more information and the Execution Plan

Bert Zhou-msft 3,436 Reputation points

2022-04-07T01:48:39.413+00:00
Hi，@Ismail Cassiem

Welcome to Microsoft T-SQL Q&A Forum!

1) You might try the code ，since statements provide similar functionality, the performance will be different.：

insert into # Working_usp_1(col1.....col25) select col1...Col25 from table,

2) By using memory-optimized tables, as a reminder, your version needs to be in 2014 and later, but memory-optimized tables will consume memory, and the advantage is that it eliminates I/O activity and gives it better performance.
3) Having a clustered index (any index, really) on the table will actually make inserts slower because it has to sort it (and/or build non-clustered) at the same time.
4) You can refer to the link below for suggestions on TempDB tables.

Best regards,
Bert Zhou

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Ronen Ariely 15,206 Reputation points

2022-04-07T13:52:49.787+00:00

Point 1 is exactly the same as point 2 in my answer

point 2 is exactly the same as point 6 in my answer

point 3 is exactly the same as my last statement in my answer

:-(
GeethaThatipatri-MSFT 29,542 Reputation points Microsoft Employee Moderator

2022-04-07T17:50:32.89+00:00

Thanks, @Ronen Ariely for pointing it out, we appreciate your help and support.

Regards
Geetha
Bert Zhou-msft 3,436 Reputation points

2022-04-08T06:30:16.63+00:00

Hi，@Ronen Ariely
Thank you for your reminder，yes, I'm just adding to your description.

Answer 2

Erland Sommarskog 121.4K MVP Volunteer Moderator

I don't see the relation between your SELECT INTO in the first part of your post and the index creation in your second part.

If you have the data in one temp table, why copy it to another?

Keep in mind that the cost percentages you see in the execution plans are only estimates, and your real bottleneck may be elsewhere.

Answer 3

Ismail Cassiem 61

Good day,
Thank You all for assisting.

This SP has like 2000 rows and i cant share the executionplan (125 plans) , finance company
the index name was only to hide the real name

I cant create a physical table , basically limited access on source & db to create.

I am having a look at the insert into but the table has 20cols to define and there's about 10 temp tables

Any other ideas please?

Ronen Ariely 15,206 Reputation points

2022-04-07T14:39:14.15+00:00

If you cannot do as suggested as a result of internal rules / limitation then maybe public forum is not your best solution and you can try to get paid support by database architect

It will make no sense to ask what more before you could do the previous steps :-)

You should remember that in solving issues sometime the information which we get from previous steps/attempts is not just for fun but can actually point us to other options

Beside this which is a basic in getting support,

basically limited access on source & db to create.

In this case, you are not the right person to speak with and not one who should solve it. It will make sense if the person who have the permission to solve the issue will be the one who solving / testing. Is this not making sense you? Can you find the person who have the permission and work with him at least?

I am having a look at the insert into but the table has 20cols to define and there's about 10 temp tables

So what?!? You need to do some writing work? This part of the task a DBA do :-)

You can find tricks to build queries dynamically but at the end the length of the query should never be a parameter in choosing the best query (or solution)! Performance should

Answer 4

Ismail Cassiem 61

QRY runs for 30 day range, 10min :(

i am tasked to bring it down to 3min without changing the DB, trying to upload the execution plans that has high cost

Answer 5

Ismail Cassiem 61

![191061-image.png ]1

Ronen Ariely 15,206 Reputation points

2022-04-07T18:10:51.623+00:00
How this Execution Plan has anything to do with your original question and the query which you described to us?!?

DROP TABLE IF EXISTS #Working_usp_1 SELECT col1,....col25 INTO #Working_usp_1 FROM

It's amazing how we waste time helping with an unrelated problem

Anyhow, as we understood from the discussion you are not the right person to have this discussion if you have no privilege to make the changes/test

I recommend to point your sysadmin to join the discussion and to provide the full information to reproduce the scenario

This question now is related to improving query and not just improving INSERT!

For this discussion we need DDL+DML + real Execution Plan (meaning the XML and not some screenshot)

Share via

improve performance inserting into temp table

Results_usp_1

9 additional answers

Your answer