question

PadmanabhanVenkatesh-6789 avatar image
0 Votes"
PadmanabhanVenkatesh-6789 asked CarrinWu-MSFT commented

Inserting data to Global Temporary table taking longer time

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?

sql-server-general
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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)
    )
0 Votes 0 ·

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.

0 Votes 0 ·

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.

0 Votes 0 ·

Hi @PadmanabhanVenkatesh-6789, 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!

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi. Thanks for the reply.
How can the insert be better performed ?

0 Votes 0 ·

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.

0 Votes 0 ·
CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered

Hi @PadmanabhanVenkatesh-6789,

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:
98498-1.png
98499-2.png


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.



1.png (24.8 KiB)
2.png (24.9 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 commented

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.


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

HI. Creation and Recondt are of type datetime2(6) not null

0 Votes 0 ·
Viorel-1 avatar image Viorel-1 PadmanabhanVenkatesh-6789 ·

Then use datetime2 instead of datetime in above script.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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".

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.