Alberto Ferrari

Bulk Insert of sorted data

AlbertoFerrari — Mon, 02 Jul 2007 19:53:00 GMT

In a previous post I showed how to make bulk insert faster using SSIS. During Sql Server Conference 2007 I had the great opportunity to double check it with Stefano Stefani that analyzed the problem in greater detail and finally came up with both an explanation of the problem and a simple workaround when facing the same problem with T-SQL (my solution is still good with SSIS but Stefano’s one is very effective with T-SQL).

The problem is related to bad performances when inserting rows into a table that has a clustered index (typically a primary key) based on an INT IDENTITY field and is easily shown in this example:

Create Table Destination (
	ID_Test Int Identity (1,1) Primary Key,
	Cod_Test VarChar (20),
	Test VarChar (100))
Go

Create Table RowsToInsert (
	Cod_Test VarChar (20),
	Test VarChar (100))
Go

Now, suppose you have 1.000.000 of rows in RowsToInsert, if you analyze the execution plan of this simple insert:

INSERT 
    Destination
SELECT 
    Cod_Test, 
    Test
FROM
    RowsToInsert
OPTION (RECOMPILE)

You will get this:

As you can see, the data is sorted before being inserted into the table even if the IDENTITY specification of the primary key guarantees to SQL Server that the data is already sorted. Of course, sorting one million rows takes a long time.

The problem is that SQL Server can insert rows in parallel mode into the table, thus there is no guarantee that the IDENTITY fields will be generated sequentially. This leads to very poor performances when inserting a huge amount of data, as is normally the case in DWH tasks.

A very effective workaround, when facing the problem in a T-SQL task, is that of sorting the data based on a dummy column, as in this example:

INSERT 
    Destination
SELECT 
    Cod_Test, 
    Test
FROM
    (SELECT 1 A, * FROM RowsToInsert) Q
ORDER BY A
OPTION (RECOMPILE)

The execution plan is now:

The sort task disappeared, as now SQL Server knows that the IDENTITY fields will be generated sequentially because the input is sorted.

The final consideration is that you can have a dramatic speed gain in huge INSERT operations with SQL Server into clustered indexed tables if you provide SORTED SELECTS to the INSERT operation. When the clustered index is based on an INT IDENTITY field you can get the same speed by sorting on a dummy field, instructing SQL Server that the IDENTITY columns will be computed sequentially and there is no need to sort the data.

Generate huge test tables using CTE

AlbertoFerrari — Mon, 02 Jul 2007 19:07:00 GMT

Sometimes I need to have a huge table to make performance tests and I want to create the table easily. I found a simple way of producing these test data using CTE in a creative way and decided to post it so I can have it at hand when needed and maybe somebody will find it useful.

The trick is to have a CTE Select that generates numbers from 0 to 9 and then cross join it with itself several times composing the numbers you want. The sample generates a one million row but can be easily changed to fit your needs

With 
    ZeroToNine (Digit) As (
        Select 0 As Digit
        Union All
        Select Digit + 1 From ZeroToNine Where Digit < 9),
    OneMillionRows (Number) As (
        Select 
          Number = SixthDigit.Digit  * 100000 
                 + FifthDigit.Digit  *  10000 
                 + FourthDigit.Digit *   1000 
                 + ThirdDigit.Digit  *    100 
                 + SecondDigit.Digit *     10 
                 + FirstDigit.Digit  *      1 
        From
            ZeroToNine As FirstDigit  Cross Join
            ZeroToNine As SecondDigit Cross Join
            ZeroToNine As ThirdDigit  Cross Join
            ZeroToNine As FourthDigit Cross Join
            ZeroToNine As FifthDigit  Cross Join
            ZeroToNine As SixthDigit)
Select 
    COD_Test = Number,
    Test     = Replicate ('A', 100) 
From OneMillionRows 
Order By Number

Removing the ORDER BY clause to the final SELECT you will have unordered rows, this can be useful if you need – for example – to test a SORT component.

Mantaining order in SSIS flow, problems with Merge

AlbertoFerrari — Tue, 10 Apr 2007 08:29:00 GMT

In a previous post I spoke about the advantages of having sorted flows in SSIS to greatly speed up data insertion using fastload. The need to have a sorted flow brings some severe problems to the SSIS programmer that he need to be aware of and that IMHO Microsoft should address with a future implementation of SQL Server.

Let’s have a look at the problem.

In the image you can see a very typical SSIS data flow where you need to manage the error flow of a lookup component and go on with the processing. Even if you can’t see it from the picture, think that Sample Source will produce a sorted output of several millions rows and we want to insert into TestTable with the same sorting.

This task works fine but it has a big problem: the Union All component will lose the sorting of the flow as it will handle data from both its input in an unordered way. This is not a bug, Union All has its behaviour by design. Still we have a problem and we know that sorting several millions rows after the Union All component is not an option for memory consumption.

In SSIS you have another component, Merge, that will kindly maintain the order of its inputs, so you can change your package this way:

Everything will work fine until you have a package that should handle several millions rows where only a very few of them (say 1.000) will enter the lookup error output flow.

In such a situation Merge will start caching ALL the rows from its first input (the row that correctly matched lookup) until something will come from its second input (rows coming from the Derived Column task). The problem is that SSIS will NOT call the ProcessInput method of the Derived Column task until its buffer reaches a certain amount of rows (normally 10.000 or a number like it) and this will not happen because only 1.000 rows will enter the Derived Column path. In such a situation Merge will start consuming memory and will fill up all the available memory really fast leading to crashes and/or very poor performances.

Even this behavior of both Merge and SSIS is “by design”, so we cannot complain it. But it could be easily solved setting the max number of rows of the buffer in the Derived column to 1 in order to call ProcessInput immediately (if I know what kind of data I will read I can easily set up values that make my SSIS package run faster). Unfortunately there is no such kind of option in SSIS so, at present, the problem cannot be solved.

Needless to say, even if this is a problem, you can try to make your sorted package run without any merge component and you will be able to maintain the sort of the flow but this is a really big limitation in expressivity of your ETL algorithm so awareness of the problem is mandatory to successfully complete your ETL process.

Making Fast Load really fast on clustered indexed tables with SSIS

AlbertoFerrari — Sun, 08 Apr 2007 16:12:00 GMT

I have been so used to use int identity fields as primary key for any table and to believe it’s the fastest way to define a clustered primary key that I never thought this can cause serious performance problems to SSIS. Until I tested it and discovered that int identity primary keys are among the slowest way to insert huge amount of data with SSIS. In the post I’ll describe the technique that – from my tests – is the fastest way to insert data into tables with clustered index using SSIS.

As a test case I used a very simple table with only two fields:

CREATE TABLE [dbo].[FastLoadTests](

[Id] [int] Identity NOT NULL,

[TestString] [varchar](2048) NOT NULL

) ON [PRIMARY]

I filled it with a very simple task that generates one million of monotonically increasing Id and TestStrings of exactly 512 bytes each, throwing them into an OleDb destination adapter that uses FastLoad to fill the table.

If the table has no index at all the package runs in 18.5 seconds, pretty nice. Now, I created an index on the table like this:

CREATE UNIQUE CLUSTERED INDEX [FastLoadTests_ClusteredIndex] ON [dbo].[FastLoadTests] ([Id] ASC) ON [PRIMARY]

And run the package once more. The execution time is now 1.08.5 (one minute and eight seconds). It is more or less 4 times slower. Clearly, as SQL has to sort one million rows, I was expecting poor performances. The first trial has been that of reducing the number of rows to sort.

Looking at the various options in the OleDb destination adapter it is easy to find that it has a parameter (Maximun Insert Commit Size) that defaults to 0.

Setting it to 10.000 reduced execution time to 44.5 seconds, a better result but still more than two times slower than the table with no clustered index. Is this the best result? Not at all! Let’s try something different.

Reading (carefully) the MS documentation about the OleDB destination adapter (http://msdn2.microsoft.com/en-us/library/ms141237.aspx) you’ll discover that you can gain performance if the input data is sorted accordingly to the clustered index on the table, specifying the ORDER option with the advanced editor.

After some trials I managed to set it with the advanced editor:

It has been surprising that, running the package, I got this error:

[Test Table [44]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Could not bulk load. The sorted column 'Id' is not valid. The ORDER hint is ignored.".

So, it seems that setting this option on an INT IDENTITTY field causes FastLoad to fail. Really surprising because it seems that SQL Server does not know that the keys generated by itself will be sorted! Anyway, removing the Identity setting from the table and using the package generated ID resulted in execution time of 46.23 seconds. No performance gain at all. Anyway as it seemed an interesting way to search into, I went more on trials.

The last and resolving trial test has been that of REMOVING the Maximum Insert Commit Size parameter from the destination adapter. Running the package again I got a result of 19.07 seconds execution time, more or less the same time as inserting the data with no clustered index at all. Got it! Now I have a really fast way to insert data.

The results are summarized in this table

	No index 0 MICS	Index, 10.000 MICS	Index, 0 MICS
Int Identity key	18.85	41.15	1.08.57
SSIS generated key	18.59	44.54	1.10.03
SSIS key and ORDER option	18.85	46.23	19.07

*MICS = Maximum Insert Commit Size

You can easily see that using the ORDER option gives a dramatic speed improvement when you have a clustered index but ONLY if you are able to generate the sorting key in the SSIS package and DO NOT USE the MICS parameter.

I think that the documentation for this behavior should have been made far more accessible and that ORDER parameter is so important that should have been shown in the first page of the standard editor for OleDb destination, hope it will be done in the next release of Sql Server.

Moreover, as using MICS parameter leads to a dramatic performance degradation, I think that the OleDb destination adapter should issue a warning if both ORDER and MICS parameter are set so to make the programmer think twice before using them together.

Distinct values in SSIS

AlbertoFerrari — Thu, 08 Feb 2007 09:37:00 GMT

Distinct is a partially blocking component that remove duplicates from one flow. Its main advantages against the sort component provided in SSIS are:

Memory usage: Distinct does not cache the whole flow (as Sort does) but retains in memory only the distincts, consuming less memory then Sort
Distinct is partially blocking where Sort is fully blocking
Distinct is freeware, you can easily download sources and adapt it to your needs

A full description of the component and full sources can be downloaded at www.sqlbi.eu.

Visio does not handle Schema? Let's force it to do!

AlbertoFerrari — Wed, 06 Sep 2006 12:12:00 GMT

I love VISIO to make database modeling, it gives very nice and colorful diagrams that I use for the documentation.

I hate VISIO as it is really far from perfect, among all the others one thing it lacks is the ability to generate a database using SCHEMA information.

As I like schemas I decided to find a way to bypass VISIO limitations, here it is:

I create table names in VISIO adding the schema to their name (so "Customers" is created as "Config.Customers"). Then I use all the colorful and funny tools to model the database and, after I have generated and run the script for creating the DB (which creates ALL the tables in the schema "dbo") I run this script that changes all the table names removing the schema information, creates the necessary schemas and transfer the tables to their correct one. Et voilà, I can still love VISIO, even with this (totally absurd) limitation.

--
--  This code analyzes all the tables in a database, removes the schema information
--  from the name and transfer each table to its proper schema
--
Declare @TableSchema VarChar (100);     -- Will hold the table Schema
Declare @TableFullName VarChar(100);    -- Will hold the table full name
Declare @TableName VarChar (100);       -- Will hold the table name without the schema
Declare @SqlCommand NVarChar(100);      -- Temporary for issuing SQL commands

--
--   Traverse the tables searching for those containing a dot in the name
--
Select 
    TableFullName = '[' + Name + ']',
    TableSchema = SubString (Name, 0, charindex ('.', Name)),
    TableName = SubString (Name, charindex ('.', Name) + 1, 100)
Into #TempTables
From Sys.Tables
Where charindex ('.', Name) > 0

--
--  A cursor used to traverse the table names
--
Declare Tables_Cursor CURSOR FOR 
    Select * From #TempTables;

Open Tables_Cursor;
Fetch Next From Tables_Cursor Into @TableFullName, @TableSchema, @TableName;
While @@Fetch_Status = 0 Begin
    --
    --  If the schema does not exists then create it
    --
    If Not Exists (Select * from Sys.Schemas where name = @TableSchema) Begin
        Select @SqlCommand = 'Create Schema ' + @TableSchema;
        Exec sp_executesql @SqlCommand;
    End;
    --
    --  Rename the table, removing its schema definition
    --
    Exec sp_rename @TableFullName, @TableName
    --
    --  Transfer the table to its desider schema
    --
    Select @SqlCommand = 'alter schema ' + @TableSchema + ' Transfer ' + @TableName
    Exec sp_executesql @SqlCommand;

    Fetch Next From Tables_Cursor Into @TableFullName, @TableSchema, @TableName;
End;

Deallocate Tables_Cursor;
Drop Table #TempTables

Data Flow Components, constructors and SSIS

AlbertoFerrari — Mon, 31 Jul 2006 05:35:00 GMT

Yesterday I had to fix a simple still subtle bug in TableDifference, I think sharing the experience will be useful for everybody involved in custom component creation.

The component has some fields that are initialized in the component constructor, at the end of the work the component does not clear them as I would expect the object to be destroyed by the SSIS engine. Everything works fine until you run the component in a data flow task contained in a foreach loop then... bang! the component crash.

The problem is that the component was not fresh-built but contained properties with the same data that were there at the end of its first execution. It seems to me that SSIS does not destroy components after a data flow terminates but reuses them during the subsequent runs of the same data flow task recalling their pre-execute method. I solved the problem clearing variable values in the pre-execute method, the problem is solved but I don't like the way the SSIS engine works, I would really like an object to be destroyed and then recreated when the container data flow task finishes execution.

If confirmed (is anybody from Microsoft listening?) this behaviour is - in my opinion - very interesting but wrong. You can decide to use this behaviour to implement some sort of state management in a component but in the same time you are prone to very nasty bugs if you do not clear all your properties in the pre-execute method and then initialize them to a meaningful value.

I did not found useful documentation about this behaviour, has anybody done any kind of investigation about it?

SqlBulkTool

AlbertoFerrari — Fri, 14 Jul 2006 08:32:00 GMT

SqlBulkTool is a command line utility that is used to quickly create a mirror of a database. It reads its configuration from an XML file containing source and destination command strings and a list of all the tables to mirror and then handles the work of copying the database in an automated and highly parallelized way.

The parallelism can use the partition capabilities of SQL Server 2005: to handle a huge table it is enough to partition it to make the tool load it by running each single partition in a separate thread, dramatically increasing table load time. In the case where no partitioning is defined the parallelism is handled at the table level.

You can find the full article here. Full source code is provided, the project is freeware and available at www.sqlbi.eu.

Table Difference 1.1

AlbertoFerrari — Thu, 06 Jul 2006 14:34:00 GMT

TableDifference has been updated to version 1.1.

The major improvement is the fact that now TableDifference can handle a field as

Key: a field that represent a key in the table, you must supply KeyOrder
Compare: a field that is to be compared between OLD and NEW, you must supply UpdateID
PreferNEW: the field appear in both flows but you want the value from the NEW flow
PreferOLD: the field appear in both flows but you want the value from the OLD flow.

Mostly valuable implementation is PreferOLD, in this way you can use the ID of the OLD table in the subsequent flows to make any kind of update you want in an easier way.

You can find the whole article and source code on www.sqlbi.eu.

Threads and custom components: FlowSync 1.0

AlbertoFerrari — Thu, 06 Jul 2006 09:51:00 GMT

Several people downloaded TableDifference to handle SCD faster, some of them, especially using it on huge table (more than 10 millions rows) noticed memory problems. The problem is that of a flow running too fast and making TableDifference cache data, we know of it and now we decided to solve it creating a new component called "FlowSync". You can find all the details and source code here.

In the article there is a brief discussion about how SSIS handles the ProcessInput method of a component with more than one input, here is an extract:

As you may already know ProcessInput is called once for every buffer and, in the case of a component with two or more inputs like TableDifference or Union All, this method is called once for each buffer of each input, so the inputs are mixed together and handled by the same method. A solution to the problem of syncronizing input, before deciding to develop FlowSync, has been that of using semaphores to stop the faster input inside the ProcessInput method. It would have been a nicer solution BUT ProcessInput is called in only ONE thread, even if it has two input flows. So, if ProcessInput is stopped then all the inputs of the components are stopped and the system will be in a deadlock state.

This is very strange because each flow runs in a separate thread but it seems that the two thread synchronize on a single one when they need to pass data to the component. So the solution has been that of inserting the sync technique where we still have separate threads, hence directly on the flows with a transformation component: FlowSync.

I would really like to see in the next version of SSIS the ability to decide if – when developing a component – we want ProcessInput to be called in a multithreaded environment or not, my personal opinion is that – using threads – programs become easier to write and maintain, TableDifference may be a good candidate to demonstrate this statement.

TableDifference: a solution to SCD handling

AlbertoFerrari — Thu, 08 Jun 2006 12:14:00 GMT

In SSIS the SCD component does a lot of work for you but it works so slowly that it is quite always convenient to create an “ad hoc” solution to handle the slowly changing dimensions.

As the main problem is that of determining the difference between the last snapshot of the SCD and the current data we decided to write an SSIS component that has two inputs, one for the “old” and one for the “new” flow of data. It compares all the rows and sends each one to a different output for new, deleted and user configurable updated data. You can then decide how to handle the variations executing the correct UPDATE sequences to the dimension table or whatever you need to handle variations.

The following picture illustrates the component functionality better than thousand words.

Using TableDifference two tables with 5.5 millions of records and 25 columns each were compared, using an AMD dual core processor with 4Gb of RAM and standard SATA disks running both SSIS and SQL Server in 7 minutes. The complexity of the algorithm is linear, so you can expect to take more or less 15 minutes for a 10 millions record tables with the same hardware.

You can find the full article that describes TableDifference here. Full source code is available at www.sqlbi.eu.