Sql-server – Insert into table select * from table vs bulk insert

bulk-insertinsertselectsql server

I just wanted to know that SQL Statement INSERT INTO TABLE1 SELECT * FROM TABLE2, will work same like bulk insert ?

If no, Is there any way exclude index while inserting records. Process is inserting 150 Millions data in one execution.

We are planning to create stage table (Which will not have any index Table1), then transfer it from stage table to target table (Which will have index Table2)

We are not in the situation to create flat file from the process.

But we are looking something that can speed up process when we transfer data from Table1(not indexed) to Table2 (indexed).

Any way to use BulkInsert from Table1 to Table2?

Best Answer

Your best bet will be to use SSIS or BULK INSERT. There are various performance improvements that you can do when using them and they are very well documented in The Data Loading Performance Guide.

At SSIS level, you can look into below things to speed up data read and data load :

Fast Parse Option along with its limitations.
Use the SQL Server Native Client 10.x OLE DB provider for an In-Memory, high performance connection
Set the Packet Size to 32767
Select the OLE DB Destination Data Access mode “Table or View – fast load” option

Refer to Speeding Up SSIS Bulk Inserts into SQL Server for more details.

Below are some good ways to improve BULK INSERT operations :

Using TABLOCK as query hint.
Dropping Indexes during Bulk Load operation and then once it is completed then recreating them.
Changing the Recovery model of database to be BULK_LOGGED during the load operation.
If the target has Clustered Index then specifying ORDER BY clause in the bulk insert operation will increase the speed of BULK loading.
Using Trace Flag 610 at the beginning of BULK INSERT operation.

The max degree of parallelism should be configured on the server rather than the default. You can refer to my answer on how it configure it here.

If you are using SQL Server 2014 then SELECT ... INTO is parallel.

Also, you should monitor Wait Statistics on the server especially SOS_SCHEDULER_YIELD resulting in scheduler contention on Servers having multiple CPUs running concurrent Bulk load operations and competing for the same CPU Cycles.

Also refer to :

Related Solutions

Sql-server – SQL 2012 insert data into a table from another table and custom columns

The obvious answer is that those columns aren't in Table1 either. Otherwise I don't see an obvious reason why that would fail. It looks like you're adding data to the table that could easily be captured by a default value. See part B for an example of using SYSTEM_USER as a default value or just see the code shamelessly copied below:

CREATE TABLE Sales.Sales_Tracking
(
    Territory_id int IDENTITY(2000, 1) NOT NULL,
    Rep_id  int NOT NULL,
    Last_sale datetime NOT NULL DEFAULT GETDATE(), -- Your date info
    SRep_tracking_user varchar(30) NOT NULL DEFAULT SYSTEM_USER -- System user
);

By setting these as a default value on Table2 you might just be able to add those four columns to Table2 without having to bother with the insert. Alternatively you can set the date and user columns to default to GETDATE() and SYSTEM_USER respectively on Table1. That would eliminate the need to specify those values in your insert statement, and give you good defaults moving forward from the looks of things.

Sql-server – Bulk insert and fast ordered search large data 3B+ records

It looks like this question will be closed soon, but you could consider using a nonclustered columnstore index. The business case that you are describing with this question seems to match Microsoft's vision for it:

Real-time analytics uses an updateable columnstore index on a rowstore table. The columnstore index maintains a copy of the data, so the OLTP and analytics workloads run against separate copies of the data. This minimizes the performance impact of both workloads running at the same time. SQL Server automatically maintains index changes so OLTP changes are always up-to-date for analytics. With this design, it is possible and practical to run analytics in real-time on up-to-date data. This works for both disk-based and memory-optimized tables.

There's a lot of information contained in that link and it links out to a bunch of other blog posts, so it's difficult to summarize it all. You might experience the following benefits:

CCIs have a typical compression ratio of 10:1, so it might be smaller than the nonclustered index that you want to build. It'll certainly be smaller than a separate copy of the table.
You can use the compression delay option discussed here to possibly lighten the load on the server when inserting data.
Converting the table to a CCI will enable batch mode for your query. You may see performance benefits from this, especially because the window functions are eligible for batch mode as of SQL Server 2016.

However, it also might not be helpful for your workload. The only way to know is to test. If you really need to constantly run a query against a 3 billion row table without any filters perhaps a data model change is needed. The insert timeout that you saw also hints at a deeper problem, but I don't have the knowledge or additional context needed to to troubleshoot that.

Best Answer

Related Solutions

Sql-server – SQL 2012 insert data into a table from another table and custom columns

Sql-server – Bulk insert and fast ordered search large data 3B+ records

Related Question