从 Excel 导入到 SQL 的大数据效率问题

Question

导入数据量：10W。
环境：Sql server 2008 R2
导入用时 29分钟。
每条执行语句：IF EXISTS(SELECT 1 FROM [CustomerSchedule_Temp] WHERE [ContractNO] = 'F8N37643' AND [BaseData] = '2013-01-21' AND [CompID] = 'KH0001')
BEGIN
    DELETE FROM [CustomerSchedule_Temp] WHERE [ContractNO] = 'F8N37643' AND [BaseData] = '2013-01-21' AND [CompID] = 'KH0001'
END
INSERT INTO [CustomerSchedule_Temp]([OTISWeeks], [Re1], [Re2], [Dstation], [OrderNumber], [ShippMethod], [ContractNO], [ContractType], [BaseData], [IsWorry], [PlanType], [PlaceOfDelivery], [ReceiveData], [UnderCust], [DeliveryDate], [Batch], [Area], [OWeight], [Ladder], [LadderRe], [CompID], [RequestFinishDate], [LeaveFacDate], [ReciveDate], [PartsBuyDate], [DrawDownData], [AssemFinishDate], [InventoryFinishDate], [ProcDownData], [WholeBankDate], [SelfFinishDate], [CancelCause], [CompNumber], [ContactVersionsNo], [contract_stat], [DrawFinishData], [HzFSendData], [ID], [IN_Time], [Inbatches], [Input_ImportInfo], [KeyPartyDesc], [ModifyData], [ModifyPrsn], [PickUpType], [RequestData], [UpInventoryDate], [PType], [SendData], [Attachment], [CompType], [PcData], [FirstDeliDate], [BoxNum], [Series], [TrsptModeID]) VALUES(NULL, NULL, NULL, NULL, NULL, NULL, 'F8N37643', '客梯', '2013-01-21', NULL, '410-1', NULL, ISNULL('2013-01-11', CONVERT(VARCHAR(10),GETDATE(),120)), '泉州市XXXX机械有限公司', '2013-01-21', '1', '福州', '1', 'GeN2-MR', 'CMD', 'KH0001', '2013-01-21', '2013-01-21', NULL, '2013-01-16', '2013-01-14', NULL, '2013-01-15', '2013-01-14', '2013-01-17', '2013-01-16', NULL, NULL, NULL, ISNULL(NULL, ('正常')), NULL, NULL, '0000000000000076', ISNULL(NULL, (CONVERT([varchar](20),getdate(),(120)))), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 76, NULL)客户一定需要DEL后重复的再插入，不能用UPDATE。在这种情况下，这个速度还有提升的空间吗，这个速度合理吗？用参数导入得多花10分钟。

Answer 1

Sql server 2008 R2这个环境可以使用merge语句.就是处理两个集合了.不是单行处理了.

Answer 2

merge 是做2表之间同步的吧？

Answer 3

那可以先插入到一个临时表（可以是实体临时表），然后再做sql层面的匹配吗？

Answer 4

merge不是同步，是融合一些判断功能。但是效率上有无提升就没试过了。

Answer 5

USE test
GO
-->生成表tbif object_id('tb') is not null 
drop table tb
Go
Create table tb([Type] nvarchar(1))
Insert into tb
Select N'A'
Union all Select N'B'
INSERT INTO TB 
SELECT * FROM OPENROWSET('MICROSOFT.ACE.OLEDB.12.0','EXCEL 12.0;HDR=YES;IMEX=2;DATABASE=H:\test.xls',[Sheet1$]) AS t
WHERE NOT EXISTS(SELECT 1 FROM tb AS x
WHERE x.Type=t.TYPE
)

/*
(4 個資料列受到影響)
*/       SELECT * FROM tb
/*
Type
----
A
B
C
D
E
F*/

Answer 6

用法有问题，别怪SQL不好
先全部导入临时表，再连接删除，插入新数据即可

Answer 7

同意大版这种做法,先全部搞到临时表中,然后在SQL中进行其它的操作.

Answer 8

最起码你插入时的速度会大大增快，而匹配的话那都是SQLServer的事情，导入时会涉及SQLServer以外的组件。开销不小

Answer 9

不要一条条搞，1000-10000条先取出ID集中delete，然后用bulkcopy插入，如果用.net，sql2008支持datatable传入表变量，可以通过exists来删除一批数据。io无压力的话，预计每10000数据插入需1s，删除看目标表数据量，但也就几秒能够搞定。1分钟处理几十万元没问题

Answer 10

用了bulkcopy的方法，搞定了，现在1分30秒左右搞定10W数据