SQL Server cannot create table as select directly

SQL Server cannot create table as select directly

To copy a table under SQL Server, think of Oracle under direct CREATE TABLE XXX as SELECT * from .... Can.
But the result is wrong, Baidu found.
SQL Server syntax is: SELECT * into Tablenew from Tableold

Insert into select and CREATE table as performance test and create TABLE 2013-10-05 09:55:00

Category: Linux

1. Document Description

The INSERT into Select and CREATE table as statements are often used in the work to copy the form data, and when the data volume of the replicated table is small, the efficiency of the two statements is very different, but when the number level of the table reaches above millions, there will be obvious efficiency difference;

This document is also divided into two parts of the report several tests;

One, insert into select and CREATE table as the efficiency test;

Second, the use of the CREATE table as method of performance tuning;

2. Insert into Select performance test with CREATE table asTest environment: Oracle version: 10.2.0.2 pga:1g sga:1g Database: Non-archive mode

2.1 Create tables as test, statement: Create Table Zz_j_3abssi as Select * from J_3abssi

Test environment: Table J_3abssi data 740,000, the table is not indexed;

Execution Time: 280S

2.2 Insert into Select test, statement:

Truncate table Zz_j_3abssi; (Truncate table preserving the structure of tables)

Insert into Zz_j_3abssi ("J_3absnr", "j_3ahbsp", "J_3abskz", "j_3aebsp", "Werks", "Lifnr", "J_4kscat", "Matnr", "j_3asize "," Menge "," DABMG "," iamng ") Select" J_3absnr "," j_3ahbsp "," J_3abskz "," j_3aebsp "," Werks "," Lifnr "," J_4kscat "," Matnr " , "J_3asize", "Menge", "DABMG", "Iamng" FROMSAPSR3. " J_3abssi "

Execution Time: 1255S

2.2 Summary: Create TABLE as is more than 4 times times better performance than insert into select;

Principle Analysis: Create table as is a DDL statement, insert INTO SELECT is a DML statement, insert into select each record will produce undo and redo, the whole process compared to create table As the resulting redo and undo are quite large, the whole process is slow and normal, but the CREATE table as is used only if the structure of the target table does not exist;

Insert into as is not recommended when there is a large amount of data, because the insertion efficiency of the statement is slow;

2.3 Extension: Online friends in the test when the nologging and parallel parameters, can improve the corresponding execution efficiency, but in the actual test, found that there is no real efficiency improvement;

Added nologging but the database will still write the log, this parameter only in the direct path to write the time only valid, about what case the database will run direct path to write, please the big God online inquiry, should know the answer;

Parallel improve efficiency, younger brother still did not want to understand, please aunt help reply;

3. CREATE TABLE as Performance Improvements

When the method of CREATE table as is used to replicate the data, the efficiency of the statement is improved as the method of further study.

3.1 Analysis Optimization Method:

Create a table as statement, which has two main steps:

Step one, read the process: The data disk to the SGA, and then from the SGA to the PGA (if this time the PGA is not large enough, then it will be written to the temporary table space, this step has a considerable impact on efficiency);

Step two, write the process: write redo, undo;

3.2 Tuning Methods

A) Increase the size of the SGA and PGA, the PGA increased from 1g to 3G;SGA from 1g to 3g

b) Increase the size of the redo log from the current 50M each to 1G each;

3.3 Experimental verification

A) The experimental results show that increasing the size of the redo log has little effect, but it still has a slight increase (an unbelief friend can test)

b) Increase the size of the SGA and PGA, it is obvious to improve the speed of the entire replication, the effect of the powder scary, a full increase of 10 times times; (here no longer put the test, you crossing to do their own test, note that the size of the table is larger than the PGA effect is more significant)

http://blog.itpub.net/12679300/viewspace-773739/

SQL Server cannot create table as select directly