DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Last call! Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • How to Restore a Transaction Log Backup in SQL Server
  • How to Attach SQL Database Without a Transaction Log File
  • A Deep Dive into Apache Doris Indexes
  • Spring Boot Sample Application Part 1: Introduction and Configuration

Trending

  • A Modern Stack for Building Scalable Systems
  • How to Configure and Customize the Go SDK for Azure Cosmos DB
  • Building Enterprise-Ready Landing Zones: Beyond the Initial Setup
  • From Zero to Production: Best Practices for Scaling LLMs in the Enterprise
  1. DZone
  2. Data Engineering
  3. Databases
  4. SQL Server: How to insert million numbers to table fast?

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

By 
Gunnar Peipman user avatar
Gunnar Peipman
·
Oct. 22, 10 · News
Likes (0)
Comment
Save
Tweet
Share
34.5K Views

Join the DZone community and get the full member experience.

Join For Free

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.

Database sql

Published at DZone with permission of Gunnar Peipman, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • How to Restore a Transaction Log Backup in SQL Server
  • How to Attach SQL Database Without a Transaction Log File
  • A Deep Dive into Apache Doris Indexes
  • Spring Boot Sample Application Part 1: Introduction and Configuration

Partner Resources

×

Comments

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends: