Inserting data to Global Temporary table taking longer time

Question

Inserting data to Global Temporary table taking longer time

Padmanabhan, Venkatesh 246

Hi, I have a very large table - Table1

I am trying to have a temporary Global table , which contains the subset of the large table. But the insertion of data from Table1 to Global table is taking a longer time. I do not have access to run the sql execution plan / create index. The Table1 does not contain a IDENTITY column, but has another column as Primary key.

Below is the code which I am trying:

create table session.##change_criteria(CHANGE_DATE_FROM DATE not null, CHANGE_DATE_TO DATE not null)

 insert into session.##change_criteria values ('2020-09-21','2020-09-28')

 create TABLE session.##new_changes ( Test_ID integer not null,  TestName char(15) not null )

 insert into session.##new_changes select Test_ID, TestName from TABLE1 with (nolock)
where CAST(CREATION as date) >= (select CHANGE_DATE_FROM from session.##change_criteria) and
  CAST(CREATION as date) <= (select CHANGE_DATE_TO from session.##change_criteria) or 
  CAST(RECONDT as date)  >= (select CHANGE_DATE_FROM from session.##change_criteria) and
 CAST(RECONDT as date) <=  (select CHANGE_DATE_TO from session.##change_criteria)

The last insert is causing performance issues, to insert a weeks data alone , it is taking more than 2.5 hours. The first 3 statements do not take any time, only the last insert is having the issues.

How to fix this?

Jeffrey Williams 1,896 Reputation points

2021-05-23T16:43:56.78+00:00
Using CAST(CREATION as date) in the WHERE clause is going to cause the query to take longer because it cannot utilize any indexes. Assuming the column CREATION is a datetime data type - you can change this to use variables instead and set those variables to appropriate datetime data type:

Declare @changeFrom datetime = '2020-09-21' , @changeTo datetime = '2020-09-28'; insert into session.##new_changes select Test_ID, TestName from TABLE1 where ( CREATION >= @changeFrom and CREATION < dateadd(day, 1, @changeTo or ( RECONDT >= @changeFrom and RECONDT < dateadd(day, 1, @changeTo) )
Jeffrey Williams 1,896 Reputation points

2021-05-23T16:45:29.703+00:00

You also don't need the NOLOCK - in fact it is ignored on the INSERT statement. And I don't think you really need a global temp table - a normal temp table should be enough for this process.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2021-05-23T17:29:45.293+00:00

You also don't need the NOLOCK - in fact it is ignored on the INSERT statement.

The NOLOCK is on the SELECT part, so it is not ignored. But, true, too many people use NOLOCK with understanding the consequences.

Using CAST(CREATION as date) in the WHERE clause is going to cause the query to take longer because it cannot utilize any indexes

That depends. If CREATION and RECONDT are datetime/datetime2, SQL Server is mart enough to understand that the indexes can be used. On the other hand, the subqueries in the conditions are problematic; they could lead to that SQL Server makes a standard assumption which leads to a table scan.

The OR condition can also present a problem, and the query might be better written with UNION.

Then again, we don't know how many rows that are qualified by the WHERE clause, so the issue may be something else than seek vs. scan.
CarrinWu-MSFT 6,891 Reputation points

2021-05-24T06:29:21.237+00:00

Hi @Padmanabhan, Venkatesh , is there any anwers helped you? If yes, could you please do "Accept Answer". By doing so, it will benefit for community members who have this similar issue. Your contribution is highly appreciated. Thank you!

4 answers

Your answer

Jeffrey Williams 1,896 Reputation points

2021-05-23T16:43:56.78+00:00

Using CAST(CREATION as date) in the WHERE clause is going to cause the query to take longer because it cannot utilize any indexes. Assuming the column CREATION is a datetime data type - you can change this to use variables instead and set those variables to appropriate datetime data type:

Declare @changeFrom datetime = '2020-09-21' , @changeTo datetime = '2020-09-28'; insert into session.##new_changes select Test_ID, TestName from TABLE1 where ( CREATION >= @changeFrom and CREATION < dateadd(day, 1, @changeTo or ( RECONDT >= @changeFrom and RECONDT < dateadd(day, 1, @changeTo) )
Jeffrey Williams 1,896 Reputation points

2021-05-23T16:45:29.703+00:00

You also don't need the NOLOCK - in fact it is ignored on the INSERT statement. And I don't think you really need a global temp table - a normal temp table should be enough for this process.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2021-05-23T17:29:45.293+00:00

You also don't need the NOLOCK - in fact it is ignored on the INSERT statement.

The NOLOCK is on the SELECT part, so it is not ignored. But, true, too many people use NOLOCK with understanding the consequences.

Using CAST(CREATION as date) in the WHERE clause is going to cause the query to take longer because it cannot utilize any indexes

That depends. If CREATION and RECONDT are datetime/datetime2, SQL Server is mart enough to understand that the indexes can be used. On the other hand, the subqueries in the conditions are problematic; they could lead to that SQL Server makes a standard assumption which leads to a table scan.

The OR condition can also present a problem, and the query might be better written with UNION.

Then again, we don't know how many rows that are qualified by the WHERE clause, so the issue may be something else than seek vs. scan.
CarrinWu-MSFT 6,891 Reputation points

2021-05-24T06:29:21.237+00:00

Hi @Padmanabhan, Venkatesh , is there any anwers helped you? If yes, could you please do "Accept Answer". By doing so, it will benefit for community members who have this similar issue. Your contribution is highly appreciated. Thank you!

Answer 1

Erland Sommarskog 121.4K MVP Volunteer Moderator

I don't think that counts as a global temp table. I'm already past my bedtime, so I don't have the time to test. But since you have the table in a schema, I would think that the database is in the same database as the source table. Which I guess is fully logged, so quite some time may be spend on growing log and data files.

Padmanabhan, Venkatesh 246 Reputation points

2021-05-21T06:19:53.963+00:00

Hi. Thanks for the reply.
How can the insert be better performed ?
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2021-05-21T21:32:38.853+00:00

So I did test this now, and SQL Server ignores the schema and creates a global temp table in tempdb, in the dbo schema. I would still recommend that you take out the sessions schema from name of the temp table, because it is confusing.

Answer 2

Hi @Padmanabhan, Venkatesh ,

Welcome to Microsoft Q&A!

According to this blog, we could know SELECT...INTO was considerably faster than INSERT…INTO.

And I also made a test as below, we could see SELECT…INTO is faster:

Best regards,
Carrin

If the answer is helpful, please click "Accept Answer" and upvote it.
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.

Answer 3

Viorel 122.5K

If CREATION and RECONDT are datetime, then consider this approach too:

declare @change_date_from as datetime, @change_date_to as datetime

select @change_date_from = CHANGE_DATE_FROM, @change_date_to = dateadd(day, 1, CHANGE_DATE_TO)
from session.##change_criteria

Then use the next WHERE condition:

. . .
WHERE ( CREATION >= @change_date_from and CREATION < @change_date_to ) or
      ( RECONDT >= @change_date_from and RECONDT < @change_date_to )

Show details about these columns and maybe check if indices are required.

Padmanabhan, Venkatesh 246 Reputation points

2021-05-21T08:12:20.447+00:00

HI. Creation and Recondt are of type datetime2(6) not null
Viorel 122.5K Reputation points

2021-05-21T08:57:39.777+00:00

Then use datetime2 instead of datetime in above script.

Answer 4

I do not have access to run the sql execution plan

Then you may have to ask some to get the execution plan for you. For most of situations when you have a slow query, it is essential to get the execution plan. Else you are only fumbling around in darkness.

Then again, this is a fairly simple query, and unless those dates select a small portion of the table, a table scan is the only reasonable option. So it can be worth looking at other bottlenecks.

Open a new query window and run the script. When it has completed run this query:

SELECT * FROM sys.dm_exec_session_wait_stats 
WHERE session_id = @@spid ORDER BY wait_time_ms DESC

What are your top waits?

Note: this query will not run on SQL 2014 or earlier.

Also, please share the output of "SELECT @@version".

Share via

Inserting data to Global Temporary table taking longer time

4 answers

Your answer