query optimization - Azure SQL SELECT columns INTO #Temp table very slow - Stack Overflow

admin管理员组

文章数量:1345016

Azure SQL SELECT of columns INTO #Temp table is executing very slowly. If I remove the INTO clause, it executes in 20 seconds instead on 10 minutes.

Kindly suggest, how do I optimize this query? Tempdb already has 4 data files. I also tried OPTION MAXDOP(2) but no luck. In the SELECT's I am already using WITH (NOLOCK).

Azure SQL SELECT of columns INTO #Temp table is executing very slowly. If I remove the INTO clause, it executes in 20 seconds instead on 10 minutes.

Kindly suggest, how do I optimize this query? Tempdb already has 4 data files. I also tried OPTION MAXDOP(2) but no luck. In the SELECT's I am already using WITH (NOLOCK).

• azure-sql-database
• query-optimization

Share Improve this question Follow edited 19 hours ago jarlh 44.8k88 gold badges5050 silver badges6767 bronze badges asked 21 hours ago Pankaj RathodPankaj Rathod 1111 bronze badge 6

• 1 Why do you need a temp table anyway? Either way we can't help you without more info: the full query, the table definitions, and please share the query plan via pastetheplan By the way, NOLOCK is not a go-faster switch, it has serious data integrity implications. – Charlieface Commented 21 hours ago
• Is it any better if you use INSERT INTO #Temp SELECT ...? See sqlshack/select-into-temp-table-statement-in-sql-server it has a section on performance. – Barmar Commented 20 hours ago
• Hey @Charlieface Thanks for your quick response, I will try and provide the details – Pankaj Rathod Commented 5 hours ago
• Hey @Barmar Thanks for your quick response, I will try and provide the details – Pankaj Rathod Commented 5 hours ago
• Can you provide some details about your table size? – Pritam Commented 4 hours ago

 |  Show 1 more comment

1 Answer 1

Sorted by: Reset to default 0

Glad to see that you fixed your issue, posting this as your answer which will help other community members who will face similar type of issue.

SELECT INTO statement creates temporary table with default setting without any schema defined for Original table. It will be easier syntax wise. select_into

But INSERT_INTO #temptable , will be more performant because here we are creating the temporary table with explicitly mentioning specific schema requirements, constraints, or indexes that are required for original table. Insert_into

Below here is the sample process which involves two steps to execute:

1. Create temporary table with similar schema and constraint as main table

               CREATE TABLE #TempEmp (ID INT  PRIMARY  KEY,
               Name  VARCHAR(10),
               Age INT,
               Department VARCHAR(50),
               Salary DECIMAL(10, 2)
               );
   

2. Insert data from main table to temporary table.

    INSERT INTO #TempEmp
    SELECT * FROM [dbo].[Employees];
   

Resouces for your reference:

Temporary Table Performance

本文标签： query optimizationAzure SQL SELECT columns INTO Temp table very slowStack Overflow