Yesterday I attended at local community evening where one of the most famous Estonian MVPs – Henn Sarv – spoke about SQL Server queries and performance. During this session we saw very cool demos and in this posting I will introduce you my favorite one – how to insert million numbers to table.
The problem is: how to get one million numbers to table with less time? We can solve this problem using different approaches but not all of them are quick. Let’s go now step by step and see how different approaches perform.
NB! The code samples here are not original ones but written by me as I wrote this posting.
Using WHILE
First idea for many guys is using WHILE. It is robust and primitive approach but it works if you don’t think about better solutions. Solution with WHILE is here.
declare @i as int
set @i = 0
while(@i < 1000000)
begin
insert into numbers values(@i)
set @i += 1
end
When we run this code we have to wait. Well… we have to wait couple of minutes before SQL Server gets done. On my heavily loaded development machine it took 6 minutes to run. Well, maybe we can do something.
Using inline table
As a next thing we may think that inline table that is kept in memory will boost up performance. Okay, let’s try out the following code.
declare @t TABLE (number int)
declare @i as int
set @i = 0
while(@i < 1000000)
begin
insert into @t values(@i)
set @i += 1
end
insert into numbers select * from @t
Okay, it is better – it took “only” 01:30 to run. It is better than six minutes but it is not good yet. Maybe we can do something more?
Optimizing WHILE
If we investigate the code in first example we can find one hidden resource eater. All these million inserts are run in separate transaction. Let’s try to run inserts in one transaction.
declare @i as int
set @i = 0
begin transaction
while(@i < 1000000)
begin
insert into numbers values(@i)
set @i += 1
end
commit transaction
Okay, it’s a lot better – 18 seconds only!
Using only set operations
Now let’s write some SQL that doesn’t use any sequential constructs like WHILE or other loops. We will write SQL that uses only set operations and no long running stuff like before.
declare @t table (number int)
insert into @t
select 0
union all
select 1
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9
insert into numbers
select
t1.number + t2.number*10 + t3.number*100 +
t4.number*1000 + t5.number*10000 + t6.number*100000
from
@t as t1,
@t as t2,
@t as t3,
@t as t4,
@t as t5,
@t as t6
Bad side of this SQL is that it is not as intuitive for application programmers as previous examples. But when you are working with databases you have to know how some set calculus as well. The result is now seven seconds!
Results
As last thing, let’s see the results as bar chart to illustrate difference between approaches.
Results: How to get million numbers to table?
I think this example shows very well how usual optimization can give you better results but when you are moving to sets – this is something that SQL Server and other databases understand better – you can get very good results in performance.
分享到:
相关推荐
SQL SERVER:把表里的数据导出成为INSERT INTO脚本的存储过程
In this book, Chris Date explains relational theory in depth, and demonstrates through numerous examples and exercises how you can apply it to your use of SQL. This third edition has been revised, ...
sqlserver表数据生成insertsql脚本(导出成insertsql脚本).pdf
com.microsoft.sqlserver.jdbc.SQLServerException: 只进结果集不支持请求的操作 解决方案
可以将数据库中的数据导出insert into脚本, sql server2008以上版本已经自带导出数据库数据成insert脚本功能,该工具针对2005以下版本.使用时先选择数据库服务器地址,在右侧选择Sql Server login,输入数据库账号...
此工具可以将SQL Server表数据导出成Insert语句。SQL Server的导入导出功能可以导出创建数据库各对象的脚本,却不提供导出目标表的现有数据为Insert语句的功能,此工具可以将SQL Server表数据导出成Insert语句。
From there, you will learn how to write Transact-SQL statements, execute simple and complex database queries, handle system administration and security, and use the powerful analysis and BI tools....
SQL Server 之 SET IDENTITY_INSERT SET IDENTITY_INSERT 想要将值插入到自动编号(或者说是标识列,IDENTITY)中去,需要设定 SET IDENTITY_INSERT
SQL Server表数据导出成Insert语句的工具
MFC 源码 VC++ sqlserver数据库访问 insert update 带界面的数据库访问 插入 更新 源码. 属于我给一个系统的升级包,只负责数据库内容的更新。
从SQLSERVER数据库中导出记录生成insert into 语句
数据同步小工具,将数据库中的表的数据生成insert语句
sqlserver 触发器 insert阿 一个关于触发器的小例子
Dynamic SQL: Applications, Performance, and Security in Microsoft SQL Server helps you bring the productivity and user-satisfaction of flexible and responsive applications to your organization safely...
SqlServer数据库表生成C# Model实体类 小工具 TableToModel源码 具体讲解:https://blog.csdn.net/weixin_38211198/article/details/91127595
主要适用 SQL server 数据库 可以根据表内容自动生成insert 脚本,select 脚本,update脚本; blob和text数据类型的字段不支持。 可以方便定制需要的字段和条件。 维护SQL server 十分方便的小工具。 在不同...
在连接到Sql Server 2005时,在默认的设置下Sql Server不允许进行远程连接可能会导致此失败。 (provider:命名管道提供程序,error:40-无法打开到SQL server的连接) 处理:1、在配置工具->Sql server 外围应用...
It allows us to work with memory-optimized tables and indexes, and natively compiled stored procedures, in addition to the disk-based tables and indexes, and T-SQL stored procedures, that SQL Server ...
java.sql.SQLException: null, message from server: “Host ‘223.72.41.7’ is not allowed to connect to this MySQL server” 客户端访问时报错: 解决方法: 1,登陆服务器 mysql> use mysql; //用mysql ...
Explore the core engine of Microsoft SQL Server 2012—and put that practical knowledge to work. Led by a team of SQL Server experts, you’ll learn the skills you need to exploit key architectural ...