CODEWITHSUNDEEP
sqlsql-servert-sqlsqlperformance
Daniel Z.
Daniel Z.

Reputation: 3348

INSERT INTO SELECT and SELECT INTO take much longer than the SELECT

I've got a SELECT statement which takes around 500-600ms to execute. If I use the same SELECT in a INSERT INTO ... SELECT ... or SELECT ... INTO it takes up to 30 seconds.

The table is more like a data copy of a view, for performance reasons which gets truncated and filled with the data from time to time. So my SQL looks like:

TRUNCATE myTable
INSERT INTO myTable (col, col, col) SELECT col, col, col FROM otherTable INNER JOIN ...

I tried multiple things like inserting the data into a temp table so no indexes etc. are on the table (well I also tried dropping the indexes from the original table) but nothing seems to help. If I'm inserting the data into the temp table first (which also takes 30 seconds) and then copy it to the real table, the copy itself is pretty fast (< 1 second).

The query results in ~3800 rows and like 30-40 columns.

The second time executing the Truncate-INSERT INTO/SELECT INTO sql takes less than a second (until I clear all caches). The execution plans look the same, except for the Table Insert which has a cost of 90%.

Also tried to get rid of any implicit conversions but that didnt help either.

Someone knows how this can be possible or how I could find the problem? The problem exists on multiple systems running Sql Server 2014/2016.

Edit: Just saw the execution plan of my SELECT shows an "Excessiv Grant" message as it estimated ~11000 rows but the result is only ~3800 rows. Could that be a reason for the slow insert?

Upvotes: 2

Views: 7650

Answers (4)

Big Jim
Big Jim

Reputation: 1

Here are some tips to optimize these operations:

  1. Use WITH (NOLOCK): As you mentioned, using the WITH (NOLOCK) hint can help reduce locking and improve performance. However, be cautious as it can lead to dirty reads.
  2. Check Indexes: Ensure that the source table has appropriate indexes to speed up the SELECT part of the operation.
  3. Batch Inserts: If you’re inserting a large number of rows, consider breaking the operation into smaller batches.
  4. Minimal Logging: If your database is in SIMPLE or BULK_LOGGED recovery model, you can take advantage of minimal logging to speed up bulk inserts.
  5. Avoid Triggers: If the target table has triggers, they can slow down the insert operation. Disable them temporarily if possible.
  6. Parallelism: Ensure that your SQL Server instance is configured to allow parallelism, which can speed up large operations.

Here’s an example of how you might use WITH (NOLOCK) in your INSERT INTO SELECT statement:

INSERT INTO TargetTable (Column1, Column2, ...)
SELECT Column1, Column2, ...
FROM SourceTable WITH (NOLOCK)
WHERE SomeCondition;

And for SELECT INTO:

SELECT Column1, Column2, ...
INTO NewTable
FROM SourceTable WITH (NOLOCK)
WHERE SomeCondition;

Example with Batching:

DECLARE @BatchSize INT = 1000;
DECLARE @RowCount INT = 1;

WHILE (@RowCount > 0)
BEGIN
    INSERT INTO TargetTable (Column1, Column2, ...)
    SELECT TOP (@BatchSize) Column1, Column2, ...
    FROM SourceTable WITH (NOLOCK)
    WHERE SomeCondition;

    SET @RowCount = @@ROWCOUNT;
END

Upvotes: -2

Joe Derham
Joe Derham

Reputation: 43

For what it's worth now, I had a similar problem just today. It turned out that the table I was inserting into had INT types, and the table I was selecting from had SMALLINT types. Thus, a type conversion was going on (several times) for each row.

Once I changed the target table to have the same types as the source table, then the insertion and selection took the same order of magnitude.

Upvotes: 1

Oskar Wielanowski
Oskar Wielanowski

Reputation: 11

I had identical problem. Select takes around 900ms to execute insert / select into took more then 2 minutes.

I have re written select to improve performance - just few ms for select but it have great improvement for insert.

Try to simplify query plan as much is possible. for example if you have multiple joins try to prepare multi - steps solution.

Upvotes: 1

SQL Simon
SQL Simon

Reputation: 31

I've just had the same problem. All the data types, sizes & allow-NULLS were the same in my SELECT and target table. I tried changing the table to a HEAP, then a cluster, but it made no difference. The SELECT took around 15 seconds but with the INSERT it took around 4 minutes. In my case, I ended up using SELECT INTO a temp table, then SELECTing from that into my real table, and it reverted back to 15 seconds or so. The OP said they tried this and it didn't work, but it may do for some people.

Upvotes: 1

Related Questions