SQL Server 2005 – How To Move 10 Millions Rows In 1 Millisecond

This blog post is more a tip that I picked up on while at PASS 2009.  Have you ever had the need to copy the contents of an entire table into another table?  Traditionally speaking, we as developers will use SELECT INTO or a INSERT INTO statement to load a destination table.  This is a still a great way of accomplishing the task at hand, but it is not nearly fast as what I am about to show you.  The method I am about to show you is not for all scenarios, but it can be very handy.  I do not know how many Oracle guys are reading this, but I have one question for you, “can your RDMS move 10 millions rows of data in <= 1 millisecond?”  I would be willing to bet that most will answer no, but I have to admit that this is not really a fair fight.  Why is this a little unfair…. let’s have a look at how this works under the hood. 

This method derives it power based on new partitioning functionality, in SQL Server 2005.  If you have used partitioning in SQL Server 2005, you probably have a good idea where I am going with this.  If not, SQL Server 2005 has built-in functionality that allows tables to be split or divided into what I will call virtual tables, whose values are dependent on predefined boundaries. When the partitioning function column is used in a query predicate the optimizer knows which partition the data resides in, which makes queries more IO efficient.  This is amazing functionality because it does not require application changes and significantly reduces the amount of data SQL Server has to sift through. The partitioning feature I will be focusing on is the feature that allows SQL Server to switch or trade partitions out, ironically named SWTICH.  This is commonly used for situations were you want to move data to a different partition either because the boundaries have changed or you need to phase data out.  The real benefit in using the SWITCH function is SQL Server does not actually move the data, it updates the meta data pointers to the data.  Because I am not actually moving data, I am able to move data around nearly instantaneously, regardless of the number of rows. This is why I said it is not fair, but hey what in life is fair :^)

Okay let’s see an example.  I will start by creating a sample table.

USE [tempdb]
GO

IF EXISTS(SELECT 1 FROM sys.tables WHERE NAME = 'TestData')
BEGIN
    DROP TABLE [dbo].[TestData];
END
GO

CREATE TABLE [dbo].[TestData](
RowNum INT PRIMARY KEY,
SomeId INT,
SomeCode CHAR(2)
);
GO

INSERT INTO [dbo].[TestData]
SELECT TOP 10000000 
    ROW_NUMBER() OVER (ORDER BY t1.NAME) AS RowNumber,
    ABS(CHECKSUM(NEWID()))%2500+1 AS SomeId, 
    CHAR(ABS(CHECKSUM(NEWID()))%26+65)
    + CHAR(ABS(CHECKSUM(NEWID()))%26+65) AS SomeCode
FROM 
    Master.dbo.SysColumns t1,
    Master.dbo.SysColumns t2
GO

IF EXISTS(SELECT 1 FROM sys.tables WHERE NAME = 'NewTestData')
BEGIN
    DROP TABLE [dbo].[NewTestData];
END
GO

--Create New Table To Move Data To
CREATE TABLE [dbo].[NewTestData](
RowNum INT PRIMARY KEY,
SomeId INT,
SomeCode CHAR(2)
);
GO

Now the fun part……. behold the power of SQL Server!!!!!!!!!!!!!!!!

--Move data to the new table
SET STATISTICS TIME ON;
SET STATISTICS IO ON;

ALTER TABLE [dbo].[TestData] SWITCH to [dbo].[NewTestData];

SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
GO

/*
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.
*/

Next, I will verify the results.

SELECT COUNT(*) FROM [dbo].[TestData]; --0
SELECT COUNT(*) FROM [dbo].[NewTestData]; --10,000,000

/*
-----------
0

(1 row(s) affected)


-----------
10000000

(1 row(s) affected)
*/

There you have.  I have successfully moved 10 million rows into a new table in 1 MS and incurred no IO through IO stats; however, IO has to be incurred to update meta data, although it should be minimal.  This method has limited use, but can be extremely advantageous.  There are stipulations that have to be met for SWITCH, such as the table you are switching to must be empty and the table must have the same schema.  For a comprehensive list of requirements please refer to this link, http://technet.microsoft.com/en-us/library/ms191160.aspx . 

Until next time happy coding.

Sequencing Data Using SQL Server 2000+ (Part 2)

This is part two of a two part series where I will be demonstrating partitioned ranking or what I call partitioned sequences.  In part one of this series, we looked at sequencing data, http://jahaines.blogspot.com/2009/09/sequencing-data-in-sql-server-part-1.html.  A sequence number is often attributed as a row number given to each row of a result set.  A partitioned sequence is a sequence of numbers that is distinct across a grouping of data.  In my opinion, I believe it to be easier to partition sequence data, if you have a somewhat sequential surrogate key, like a date column or even better an identity column.  It is a lot easier to handle sequenced partitioning when you have a unique sequential  key, but not required. Let’s get started by creating our same sample table.

SET NOCOUNT ON

GO

USE [tempdb]

GO

IF OBJECT_ID('tempdb.dbo.Orders') IS NOT NULL

BEGIN

    DROP TABLE [dbo].[Orders]

END;

GO

CREATE TABLE dbo.[Orders](

OrderId INT IDENTITY(1,1),

CustId INT,

OrderDate DateTIME

);

GO

INSERT INTO [dbo].[Orders] (CustId,OrderDate)

SELECT 1,'1/1/09 08:00 AM' UNION ALL 

SELECT 1,'1/1/09 10:00 AM' UNION ALL 

SELECT 1,'2/1/09 02:00 PM' UNION ALL

SELECT 1,'3/1/09 03:15 PM' UNION ALL

SELECT 1,'3/8/09 10:00 PM' UNION ALL

SELECT 1,'3/8/09 10:00 PM' UNION ALL --Show that ties do not affect the output

SELECT 2,'1/1/09 07:30 AM' UNION ALL

SELECT 2,'1/1/09 05:45 PM' UNION ALL

SELECT 2,'2/2/09 11:38 PM';

GO

Now that we have our sample table, we can begin our first partitioning sequence, using the row_number function (SQL Server 2005+). 

--Row Sequence Partitioning: 2005+

SELECT *, ROW_NUMBER() OVER(PARTITION BY o1.CustId ORDER BY o1.OrderId ASC) AS seq

FROM [dbo].[Orders]o1

ORDER BY o1.OrderId, o1.CustId

In the above example, the Row_Number, http://msdn.microsoft.com/en-us/library/ms186734.aspx ,  function is driving the results.  As you can see, I am using the partition by clause to “group” the sequence by o.CustId.  The partitioning clause creates a new sequence for each grouping.  The order by clause of the Row_Number function dictates the sequence order.  I have chosen ASC order, so the sequence will be generated on each CustId starting with the smallest OrderId. until it reaches the largest OrderId.

Results:

Now, we will move onto to solutions for SQL Server 2000.  The next example will use a correlated subquery, which can really degrade query performance.  I do not really like correlated subqueries, but I will list this as a possible method, but advise you to use another method, http://jahaines.blogspot.com/2009/06/correlated-sub-queries-for-good-or-evil.html

--Partitioning: 2000 slow

SELECT *,(SELECT COUNT(*) FROM [dbo].[Orders]o2 WHERE o2.OrderId <= o1.OrderId AND o1.CustId=o2.[CustId]) AS seq

FROM [dbo].[Orders]o1

ORDER BY o1.OrderId, o1.CustId

Results:

There really is not a whole lot of magic to the correlated subquery solution.  All we are doing is counting the number of rows less than the current OrderId, which effectively generates our partitioned sequence.  The next method is the derived table method. 

--Partitioning: 2000 much faster

SELECT o1.OrderId,o1.CustId,o1.OrderDate,COUNT(*) AS seq

FROM [dbo].[Orders]o1

INNER JOIN [dbo].[Orders]o2 ON o2.OrderId <=o1.OrderId AND o1.[CustId]=o2.[CustId]

GROUP BY o1.OrderId,o1.CustId, o1.OrderDate

ORDER BY o1.OrderId, o1.CustId, o1.OrderDate

Again, there is no magic formula here.  We are using the same logic, as the correlated subquery, yet we using a derived table.  The derived table method is a better solution than the correlated subquery method and CAN be more performant than the row_number method.  Whether or not the derived table method is better performing than the Row_Number function depends on several factors, but the biggest factor is the number of sort operators.  the derived table method will mostly use one sort operator (Order by Clause) and a stream aggregate, whereas, the Row_Number solution can use two sort operators, if the ORDER BY clause does not align with the ORDER BY clause of the Row_Number function.  In this scenario, the Row_Number solution can be more expensive than the derived table solution.  In most cases the derived table method is more costly than the row_number function because it requires another join, which translates into higher IO, while the Row_Number function reads from a single table , in this scenario.

Results:

There you have it.  We have successfully implemented sequenced partitioning, in SQL 2000 and greater versions.  The main thing to remember for solutions prior to SQL Server 2005 is that in some cases you may have to handle ties.  I find it best to handle sequence partitioning using a ranking function, such as Row_Number.  If you are using SQL 2000 and do not have a sequential key like an identity, you may have a little heartache, especially if you are trying to handle ties.  It is not impossible, but it is more tricky and you have to be more creative with your solution.  I hope that you have learned something that you can use in your environment and as always, happy coding.