sqlserver不能直接create table as select

標籤：

 sqlserver不能直接create table as select 

在sqlserver 下想複製一張表的，想到oracle下直接create table xxx as select * from ....即可。
但是結果卻是錯誤的，baidu一下發現。
sqlserver的文法是 : select * into tablenew from tableold

 Insert into select 與create table as的效能測試及create table 2013-10-05 09:55:00

分類： Linux

 

1.  文檔說明

在工作中經常用insert into select 與 create table as語句來進行表單資料的複製；當複製表的資料量很小的時候兩個語句的效率區別不大，但當表的數量層級達百萬級以上的時候，就會有很明顯的效率區別；

本文檔也也分成兩個部分進行報表數度的測試；

一、 insert into select 與 create table as的效率測試；

二、 用create table as方法時效能的調優；

2.  Insert into select  與create table as的效能測試測試環境：ORACLE版本：10.2.0.2 PGA:1G SGA:1G 資料庫:非歸檔模式

2.1 create tables as的測試，語句：Create Table zz_J_3ABSSI as Select * From J_3ABSSI

測試環境：表J_3ABSSI資料74萬，該表沒有索引；

執行時間：280S

 

2.2 Insert into select的測試，語句：

Truncate Table zz_J_3abssi;（先truncate表保留表的結構）

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"

執行時間：1255S

 

2.2 總結：create table as比insert into select具有更好的效能越提升4倍以上；

原理分析：create table as是ddl語句，insert into select是dml語句，insert into select每一條記錄的時候都會產生undo和redo，整個過程相比create table as產生的redo和undo相當多，因此整個過程會慢也是正常的；但是create table as使用的前提是目標表的結構不存在才能使用；

當有大量資料的時候不推薦使用Insert into as，因為該語句的插入的效率很慢；

 

2.3 延伸：網上有朋友在測試的時候增加了nologging和parallel參數，能夠提升相應的執行效率；但是在實際中測試中，發現並沒有實際效率的提高；

增加了nologging但是資料庫還是會寫日誌，該參數只有在進行直接路徑寫的時候才有效，關於什麼情況下資料庫會運行直接路徑寫，請各位大神網上查詢，應該會知道答案；

Parallel提高效率，小弟還沒有想明白，請各位大嬸幫忙回複；

 

3.  create table as 效能的提升

在確定了用create table as的方法進行複製資料的時候，提升該語句的效率成了繼續研究的方法；

3.1 分析最佳化方法：

create table as的語句，該語句主要有兩個步驟：

步驟一、讀的過程：資料磁碟到sga，然後從sga到pga（如果這個時候pga不夠大，那麼就會寫到暫存資料表空間，該步驟相當影響效率）；

步驟二、寫的過程：寫redo、undo；

3.2 調優方法

a) 增加sga和pga的大小，pga從1g增大到3g；sga從1g增加到3g

b) 增加redo log的大小，從當前的50M每個增加到1G每個；

3.3 實驗驗證

a) 實驗結果表明，增加redo log的大小影響速度不大，但還是有稍微的提升（不信的朋友可以測試）

b) 增加sga和pga的大小，很明顯的提升了整個複製的速度，效果粉嚇人，整整提升了10倍；（這裡不再貼測試，各位看官自己動手測試下，注意表的大小要大於PGA時效果比較顯著）

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

sqlserver不能直接create table as select