`
qtlkw
  • 浏览: 307150 次
  • 性别: Icon_minigender_1
  • 来自: 杭州
社区版块
存档分类
最新评论

SQL Server: How to insert million numbers to table fast?

 
阅读更多
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.
分享到:
评论

相关推荐

    sqlserver表数据生成insertsql脚本(导出成insertsql脚本).pdf

    SQL Server表数据生成INSERT SQL脚本 SQL Server是一种强大的关系数据库管理系统,提供了多种方式来生成INSERT SQL脚本,以便将数据导出到其他数据库或系统中。在本文中,我们将介绍如何使用SQL Server 2008中文版...

    SQL Server表数据导出成Insert语句的工具

    在SQL Server数据库管理中,有时我们需要将表中的数据导出为Insert语句,这在数据迁移、备份或测试环境中非常常见。"SQL Server表数据导出成Insert语句的工具"是一个专门为此目的设计的应用程序,它能帮助数据库管理...

    SQLserver、Oracle、Mysql语法与用法对比

    在SQL Server、Oracle和MySQL这三种关系型数据库管理系统(RDBMS)中,它们的SQL语法在很多方面具有相似性,但在某些特定操作上存在差异。以下是对这些系统中一些关键语法和用法的对比: 1. **查看表结构**: - ...

    SQL SERVER 将select数据生成insert语句

    在SQL Server中,有时我们需要将一个数据库中的表结构和数据迁移到另一个数据库,或者备份部分数据以便于恢复或分析。在这种情况下,`SELECT INTO` 和 `INSERT INTO...SELECT` 语句是常用的工具,可以将数据从一个源...

    Convert SQL Server DB to SQLite DB

    Convert SQL Server DB to SQLite DB 压缩包里有教程,SQLServer数据库表导出带插入语句的sql方法:流程:SQLServer转行成SQLite文件,SQLite文件通过动软代码生成器转化成带插入的SQL语句。

    ORACLE和SQL Server的语法区别

    - **SQL Server:** SQL Server的INSERT语句同样允许插入单行或多行数据,并且可以使用`OUTPUT` 子句来捕获受影响的行。 ```sql INSERT INTO table_name (column1, column2, ...) OUTPUT inserted.column1, ...

    从SQLSERVER数据库中导出记录生成insert into 语句

    从SQLSERVER数据库中导出记录生成insert into 语句

    Sqlserver表数据导出成Insert语句的工具

    此工具可以将SQL Server表数据导出成Insert语句。SQL Server的导入导出功能可以导出创建数据库各对象的脚本,却不提供导出目标表的现有数据为Insert语句的功能,此工具可以将SQL Server表数据导出成Insert语句。

    将sqlserver表中指定数据转化为insert语句

    在SQL Server中,有时我们需要将现有表中的特定数据导出为INSERT语句,以便在其他数据库或备份中重建这些记录。这通常在迁移数据、创建测试数据或者进行数据备份时非常有用。以下是如何将SQL Server表中指定数据转化...

    sql server字段保存文件,blob操作

    SQL: string; Conn: TSQLConnection; Cmd: TSQLCommand; Param: TSQLParam; begin Conn := TSQLConnection.Create(nil); // 设置连接字符串和连接到数据库 // ... Cmd := TSQLCommand.Create(nil); Cmd....

    mysql和sqlserver比较.docx

    - SQL Server: `ALTER TABLE table_name ADD CONSTRAINT PK_table PRIMARY KEY (column_name);` - **撤销 PRIMARY KEY**: - MySQL: `ALTER TABLE table_name DROP PRIMARY KEY;` - SQL Server: `ALTER TABLE ...

    SQL SERVER BULK INSERT用法

    "SQL SERVER BULK INSERT用法" BULK INSERT 是 SQL Server 中的一种 BulkCopy 功能,允许用户快速将大量数据从文本文件或其他数据源导入到数据库表或视图中。下面是 BULK INSERT 的详细用法和参数解释: 基本语法 ...

    Labview 使用INSERT INTO(用于向SQL Server表格中插入新的行)

    Labview 使用INSERT INTO(用于向SQL Server表格中插入新的行),该视频用于指导初学者写数据库。

    数据库Insert语句生成工具(SQLSERVER版).zip

    数据库Insert语句生成工具是针对SQL Server数据库设计的一款实用程序,主要目的是为了简化在不同环境间迁移或初始化数据的过程。这个Python编写的工具允许用户快速、高效地为多个表生成Insert语句,使得数据导入变得...

    sqlserver自动生成sql语句工具sqlserver转oracle

    在IT行业中,数据库管理系统是核心组成部分,SQL Server和Oracle分别是微软和甲骨文公司推出的两款广泛应用的关系型数据库系统。在企业级应用中,有时需要在不同的数据库系统间进行数据迁移或兼容性处理,这就涉及到...

    使用flink-connector-sqlserver-cdc 2.3.0把数据从SQL Server实时同步到MySQL中

    "INSERT INTO mysql_table SELECT * FROM sql_server_table"); ``` 3. **启动并监控Flink作业**: - 启动Flink作业后,它将持续监听SQL Server的变化并实时将这些变化应用到MySQL中。 - 你可以通过Flink的Web ...

    kettle链接SQL server驱动 sqljdbc

    SQL Server是微软公司推出的一款关系型数据库管理系统,广泛应用于企业级数据存储与管理。在Kettle中配置SQL Server数据库连接时,我们需要依赖特定的数据库驱动,这就是SQL JDBC驱动。 SQL JDBC驱动是微软提供的...

    SQL server自动生成Insert脚本

    主要适用 SQL server 数据库 可以根据表内容自动生成insert 脚本,select 脚本,update脚本; blob和text数据类型的字段不支持。 可以方便定制需要的字段和条件。 维护SQL server 十分方便的小工具。 在不同...

    [SQL Server] Microsoft SQL Server 2012 技术内幕 (英文版)

    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 ...

Global site tag (gtag.js) - Google Analytics