Rhino.Etl good practice

[codin...@googlemail.com]

Hi

I've just started out writing a new Rhino.Etl project for a data
migration we're doing here. The migration needs to be speedy as we
need to import > 3,000,000 user records from an old legacy db to a new
system and we only have a small timeframe on the night with which to
do this. Which is why we chose R.Etl :)

My question is - as a basic usage - we're extracting records from
table a, b and c. Specific columns from those tables are merged to
create a new row which is inserted into table x. I then need to get
the ID of that new row and use it to insert another row into another
table further down the pipeline.

What's the best way of getting that? I don't think doing a select
straight after the initial insert is best. Is there a way to use
@@IDENTITY inside the OutputCommandOperation?

Cheers

:)

w://

[Jason Meckley]

if you are using identity, then I don't think you can batch the
commands, which is where you will get high throughput. you can
disable/override identity and insert your own id, which would allow
for batch processing. when finished you can enable identity again.

if this is a one time transfer to populate a new system i would
1. disable identity
2. insert the data in batches and manage the PK yourself (id = i++)
3. enable identity and set the seed to start at Max(Id) +1 from user
table

On Aug 17, 6:51 am, "wa...@codingvista.com"

[Wayne Douglas]

Actually appending data to a newly implemented system - but your method is still v.good and should work. Thanks :)

--
You received this message because you are subscribed to the Google Groups "Rhino Tools Dev" group.
To post to this group, send email to rhino-t...@googlegroups.com.
To unsubscribe from this group, send email to rhino-tools-d...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/rhino-tools-dev?hl=en.

--
Cheers,

w://

[Wayne Douglas]

how would I set identity insert on at the start of a batch?

--
Cheers,

w://

[Jason Meckley]

there is a sql command like "Set Identity Off" or something like that.
when you issue this command you can then insert a value into an
identity column. when you are finished processing all the rows you can
issue another command to activate the identity "Set Identity On".

another option, since you are populating a new system. is to remove
the identity constraint altogether. Insert the users and add the
constraint back
alter table [Users] alter column [Id] [Id] bigint not null

public void Process(IEnumerable<Row> rows)
{
Use.Command( c => {
c.CommandText = "alter table [Users] alter column [Id]
[Id] bigint not null";
c.ExecuteNonQuery();
});

foreach(var row in rows)
{
//process
}

long seed;
Use.Command(c => {
c.CommandText = "max([Id])+1 from [Users]"
seed = (long) c.ExecuteScalar();
});
Use.Command( c => {
c.CommandText = string.Format("alter table [Users] alter
column [Id] [Id] bigint not null identity ({0}, 1)", seed);
c.ExecuteNonQuery();
});

}

On Aug 17, 10:25 am, Wayne Douglas <codingvi...@googlemail.com> wrote:
> how would I set identity insert on at the start of a batch?
>
> On Tue, Aug 17, 2010 at 3:23 PM, Wayne Douglas

> <codingvi...@googlemail.com>wrote:

>
>
>
> > Actually appending data to a newly implemented system - but your method is
> > still v.good and should work. Thanks :)
>

> >> rhino-tools-d...@googlegroups.com<rhino-tools-dev%2Bunsu...@googlegroups.com>
> >> .

[Wayne Douglas]

I dont see Use.Command in my API?

On Tue, Aug 17, 2010 at 4:02 PM, Jason Meckley <jasonm...@gmail.com> wrote:

Use.Command

--
Cheers,

w://

[Stephen Bohlen]

BTW, this is the link to the T-SQL that you need to invoke: http://msdn.microsoft.com/en-us/library/ms188059.aspx

You set IDENTITY_INSERT to ON before you begin to insert rows for which you will be providing pre-computed PK values and then set it back to OFF after you are done inserting such rows into the table.

Steve Bohlen
sbo...@gmail.com
http://blog.unhandled-exceptions.com
http://twitter.com/sbohlen

Cheers,

w://

--

You received this message because you are subscribed to the Google Groups "Rhino Tools Dev" group.
To post to this group, send email to rhino-t...@googlegroups.com.

To unsubscribe from this group, send email to rhino-tools-d...@googlegroups.com.

[Wayne Douglas]

i got that :)

is there a way to run a single sql command against a table before I start processing? i did try to create a SetIdentityInsert OutputCommandOperation:

[code]

public class SetIdentityInsert : OutputCommandOperation
    {
        public enum Switch
        {
            On, Off
        }

        public string TableName { get; set; }
        public Switch OnOff { get; set; }

        public SetIdentityInsert(string connectionStringName) : base(connectionStringName)
        {
        }

        protected override void PrepareCommand(System.Data.IDbCommand cmd, Rhino.Etl.Core.Row row)
        {
            cmd.CommandText = string.Format("SET IDENTITY_INSERT {0} {1}", TableName, OnOff == Switch.On ? "On" : "Off");
        }
    }

[/code]

But that gets called for every row! Jason hinted at Use.Command()

BUt I can't find that anywhere...

--
Cheers,

w://

[Wayne Douglas]

ffr i'm using this:

[code]

 Seed = Use.Transaction("Customer", delegate(IDbCommand cmd)
            {
                cmd.CommandText = "select max([customerId])+1 from dbo.tblCustomer";
                return (int)cmd.ExecuteScalar();
            });

[/code]

--
Cheers,

w://

[Jason Meckley]

try use.transaction then. it's in there somewhere :)

> > sboh...@gmail.com

> >http://blog.unhandled-exceptions.com
> >http://twitter.com/sbohlen
>
> > On Tue, Aug 17, 2010 at 11:06 AM, Wayne Douglas <
> > codingvi...@googlemail.com> wrote:
>
> >> I dont see Use.Command in my API?
>

> >> On Tue, Aug 17, 2010 at 4:02 PM, Jason Meckley <jasonmeck...@gmail.com>wrote:
>
> >>> Use.Command
>
> >> --
> >>  Cheers,
>
> >> w://
>
> >> --
> >> You received this message because you are subscribed to the Google Groups
> >> "Rhino Tools Dev" group.
> >> To post to this group, send email to rhino-t...@googlegroups.com.
> >> To unsubscribe from this group, send email to

> >> rhino-tools-d...@googlegroups.com<rhino-tools-dev%2Bunsu...@googlegroups.com>
> >> .

> >> For more options, visit this group at
> >>http://groups.google.com/group/rhino-tools-dev?hl=en.
>
> >  --
> > You received this message because you are subscribed to the Google Groups
> > "Rhino Tools Dev" group.
> > To post to this group, send email to rhino-t...@googlegroups.com.
> > To unsubscribe from this group, send email to

> > rhino-tools-d...@googlegroups.com<rhino-tools-dev%2Bunsu...@googlegroups.com>
> > .

[Wayne Douglas]

you must have sent that just after i sent mine:)

To unsubscribe from this group, send email to rhino-tools-d...@googlegroups.com.

For more options, visit this group at http://groups.google.com/group/rhino-tools-dev?hl=en.

--
Cheers,

w://

[Wayne Douglas]

Moving on from yesterdays questions.

If I need to ETL 3,000,000 users, in the first process I am obv going to want to grab those users.

I'm not going to want to select * as that would be crazy. what's the concensus on avoiding this?

--
Cheers,

w://

[Wayne Douglas]

Also, is it OK to thread these EtlProcess?

--
Cheers,

w://

[Louis Haußknecht]

Wayne,

It's ok to select all rows. The results are yielded.

There is a facility to multi-thread processes.

Louis

Am 18.08.2010 10:55 schrieb "Wayne Douglas" <codin...@googlemail.com>:

Also, is it OK to thread these EtlProcess?

On Wed, Aug 18, 2010 at 9:00 AM, Wayne Douglas <codin...@googlemail.com> wrote:
>

> Moving on f...

[Jason Meckley]

what's wrong with select *? if you need the fields it's the simplest
way to do so.

You can multithread the process using the multithreaded pipeline
executor, but you should be careful in doing so. the multithreaded
pipeline doesn't necessarily mean higher throughput or better
preformance. Infact for the simple process of transferring users from
one system to another you probably do not want this.

Something else to consider. You seem to be overly concerned about
preformance. If this is a one time transfer to populate the system
preformance is not nearly as important as correctness. You should also
determine what is an acceptable execution time. I would bet you can
spare a few hours for the routine to run if necessary. first get it
working. Then, if necessary, determine where the bottleneck is. Then
optimize.

On Aug 18, 6:07 am, Louis Haußknecht <l...@haussknecht.com> wrote:
> Wayne,
>
> It's ok to select all rows. The results are yielded.
>
> There is a facility to multi-thread processes.
>
> Louis
>

> Am 18.08.2010 10:55 schrieb "Wayne Douglas" <codingvi...@googlemail.com>:
>
> Also, is it OK to thread these EtlProcess?
>

> On Wed, Aug 18, 2010 at 9:00 AM, Wayne Douglas <codingvi...@googlemail.com>
> wrote:
>
>
>
> > Moving on f...

[Wayne Douglas]

Hi Jason

unfortunately this is a set of high throughput systems which are being switched over to another, new backend, to etl the data before hand and keep that in sync until the switch is flicked is not feasible as there are so many systems being merged into the new. This means that we are going to need to turn off -> migrate -> switch on as quick as possible. It's a huge project with a few hundred working on the whole re-platform, the data migration needs to be done in as quick a time as possible as the amount of transactions the client will be losing while the process happens is quite scary.

Sometimes perf is just as important as correctness :)

w://

> wrote:
>
>
>
> > Moving on f...

--
You received this message because you are subscribed to the Google Groups "Rhino Tools Dev" group.
To post to this group, send email to rhino-t...@googlegroups.com.
To unsubscribe from this group, send email to rhino-tools-d...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/rhino-tools-dev?hl=en.

--
Cheers,

w://

[Nathan Palmer]

Wayne,

Just as a note here. We're currently going through a very large ETL conversion from a Oracle data store that is in one side of the country to a SQL data store in another side of the country. We have around 871 million records over all and the data structures between the two systems are wildly different. Performance is a main consideration for us because these are real-time systems so when we "flip-the-switch" to go from one to the other we cannot have too much downtime. Here are a few pointers to hopefully help you out.

 * Select just the fields you need. This is a performance consideration when querying records directly from the datastore. If you don't need all of the fields then do not use a Select *. I always favor naming the fields explicitly   since I'm always going to list them out specifically to get into the next system.

 * I wasn't sure by your post if you were referring to SELECT * as grabbing all of the records or for grabbing all of the fields. If you are talking about filtering the data then you need to weigh the cost between filtering coming out of the server (which in some cases requires a join) or filtering after the fact. In our conversion we needed to remove some of the filtering (thus giving us more data) because the server cost to perform the filter was too high and lead to longer processes.

 * If you are dealing with a large dataset you will want to stay away from the standard SingleThreadedPipelineExecuter and the ThreadPoolPipelineExecuter because you will consume your memory much too quick. The SingleThreadedPipelineExecuter uses caching which in some cases you will not need. The ThreadPoolPipelineExecuter because of the nature of the threads is holding onto too much data as it's flowing through. If you are using the latest build from the website (http://builds.hibernatingrhinos.com/builds/Rhino-ETL) there is now a simple SingleThreadedNonCachedPipelineExecuter which will not cache and not thread.

 * If you have multiple EtlProcess's they certainly can be multithreaded. This is a manual process that you do in your code if you want it.

Nathan Palmer