1、大数据量存储方式性能评测
(1)背景
空表插入;
表有4个字段。
(2)性能评测
序号 | 存储方式 | 插入条数 | 所用时间 |
1 | 纯JDBC | 3,000 | 3m以上 |
2 | 连接池 | 3,000 | 16s |
3 | 连接池 + 分段提交 | 3,000 | 12s |
4 | 存储过程 | 20,000 | 4s |
5 | 存储过程 + 分段提交 | 20,000 | 1s(有时还不到) |
2、“存储过程 + 分段提交”大数据量存储源码示例
ALTER PROCEDURE [dbo].[sp_save_realtimeData]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @size int -- 数据条数
declare @unit int -- 分段提交数据条数
declare @count int -- 分段提交次数
declare @commitSum int -- 分段提交实际数据条数
declare @i int
declare @j int
declare @k int
declare @curDate1 datetime
declare @curDate2 datetime
set @curDate1 = GETDATE()
-- 计算分段提交次数
set @size = 20002
set @unit = 100
if (@size % 100 = 0)
set @count = @size / @unit
else
set @count = @size / @unit + 1
--print 'count:'
--print @count
set @k = 0
set @i = 1
while @i <= @count
BEGIN
BEGIN TRY
BEGIN TRAN
-- 计算每次分段提交实际数据条数
if ((@size - @unit * @i) > 0)
set @commitSum = @unit
else
set @commitSum = @size - @unit * (@i - 1)
--print 'commitSum:'
--print @commitSum
set @j = 1
while @j <= @commitSum
BEGIN
insert into Datalog1(
ID
,Flag
,Name
,Value)
values(
@k + 1
,1
,'名称'
,'值'
)
set @j = @j + 1
set @k = @k + 1
END
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH
set @i = @i + 1
END
set @curDate2 = GETDATE()
print 'DateDiff:'
print DateDiff("s",@curDate1,@curDate2)
END