Choosing DB Edition (Std vs Ent)

I need to decided between Standard and Enterprise Edition (Cost is a
criteria - but its secondary to performance - <!--and I am not paying for
it myself-->)

The server spec under consideration: Dual Xeon, 1GB RAM, 36GB - RAID 1
(Dell PowerEdge 1850).

Application: Windows 2003 Std Server, ASP.NET, MS SQL Server 2000 based
data driven web application.

Approximately 25 simultaneous clients. Peak activity would probably be 50
transactions/activities per second (2 per second per client). I expect
the database size to grow up to 4GB in 1 year.

The application would use only basic OLAP features (if at all)...so
feature set wise I believe that standard edition is good enough.

What I am concerned about is when MS documentation says that Standard
Edition is for "organization that do not require the advanced scalability,
availability, performance, or analysis features of the SQL Server 2000
Enterprise Edition"

Is there a difference in performance between Std and Ent editions? In
terms of number of transactions per second that can be serviced?

What other criteria should I be aware of before deciding to go one way or
the other?

Any ideas?"Jonas Hei" <maps_263@.hotmail.com> wrote in message
news:opsehfbjyzr0m89z@.fx1025...
>I need to decided between Standard and Enterprise Edition (Cost is a
>criteria - but its secondary to performance - <!--and I am not paying for
>it myself-->)
> The server spec under consideration: Dual Xeon, 1GB RAM, 36GB - RAID 1
> (Dell PowerEdge 1850).
> Application: Windows 2003 Std Server, ASP.NET, MS SQL Server 2000 based
> data driven web application.
> Approximately 25 simultaneous clients. Peak activity would probably be 50
> transactions/activities per second (2 per second per client). I expect
> the database size to grow up to 4GB in 1 year.
> The application would use only basic OLAP features (if at all)...so
> feature set wise I believe that standard edition is good enough.
> What I am concerned about is when MS documentation says that Standard
> Edition is for "organization that do not require the advanced scalability,
> availability, performance, or analysis features of the SQL Server 2000
> Enterprise Edition"
> Is there a difference in performance between Std and Ent editions? In
> terms of number of transactions per second that can be serviced?
> What other criteria should I be aware of before deciding to go one way or
> the other?
> Any ideas?

I'd guess that we're referring to features in the section you quoted even
though it makes it sound like the Enterprise Edition is inherently faster
than the Standard Edition. That's simply not the case. For example,
Clustering is a high availability option that is only available in the
Enterprise Edition. You can get more information about features by Edition
and choosing a particular Edition here:
http://www.microsoft.com/sql/evalua...es/choosing.asp.

There is nothing in either the Standard or Enterprise Edition engine that
I'm aware of that throttles performance based on the Edition that you're
using. The only Edition that has a performance throttle based on the Edition
is MSDE.

If the Standard Edition contains the features your application needs, my
guess is that it will run it just fine. Of course, without testing that's
impossible to know for sure.

--
Sincerely,
Stephen Dybing

This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi

The engine is the same for both editions, the Enterprise edition has
additional features, such as failover clustering, built in log shipping and
automatic use of indexed views.

See
http://msdn.microsoft.com/library/d..._ar_ts_1cdv.asp

If you don't want to use these features or if you are happy to "manually"
implement the features or can provide your own solutions, then standard
edition should be ok. All editions should be supported on your hardware.

John

"Jonas Hei" <maps_263@.hotmail.com> wrote in message
news:opsehfbjyzr0m89z@.fx1025...
> I need to decided between Standard and Enterprise Edition (Cost is a
> criteria - but its secondary to performance - <!--and I am not paying for
> it myself-->)
> The server spec under consideration: Dual Xeon, 1GB RAM, 36GB - RAID 1
> (Dell PowerEdge 1850).
> Application: Windows 2003 Std Server, ASP.NET, MS SQL Server 2000 based
> data driven web application.
> Approximately 25 simultaneous clients. Peak activity would probably be 50
> transactions/activities per second (2 per second per client). I expect
> the database size to grow up to 4GB in 1 year.
> The application would use only basic OLAP features (if at all)...so
> feature set wise I believe that standard edition is good enough.
> What I am concerned about is when MS documentation says that Standard
> Edition is for "organization that do not require the advanced scalability,
> availability, performance, or analysis features of the SQL Server 2000
> Enterprise Edition"
> Is there a difference in performance between Std and Ent editions? In
> terms of number of transactions per second that can be serviced?
> What other criteria should I be aware of before deciding to go one way or
> the other?
> Any ideas?|||Stephen Dybing [MSFT] (stephd@.online.microsoft.com) writes:
> There is nothing in either the Standard or Enterprise Edition engine
> that I'm aware of that throttles performance based on the Edition that
> you're using. The only Edition that has a performance throttle based on
> the Edition is MSDE.

There are however features in Enterprise Edition that may help to
improve performance. One such features in indexed views. You can use
indexed views in Std Edition too, but there situations where the optimizer
will not consider the view.

Then again, if you are not using indexed views, this will not make a
difference.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||True, but in my defense, it's listed on the Features page I pointed
everybody at. :-)

--
Sincerely,
Stephen Dybing

This posting is provided "AS IS" with no warranties, and confers no rights.

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns956828A66F7CYazorman@.127.0.0.1...
> Stephen Dybing [MSFT] (stephd@.online.microsoft.com) writes:
>> There is nothing in either the Standard or Enterprise Edition engine
>> that I'm aware of that throttles performance based on the Edition that
>> you're using. The only Edition that has a performance throttle based on
>> the Edition is MSDE.
> There are however features in Enterprise Edition that may help to
> improve performance. One such features in indexed views. You can use
> indexed views in Std Edition too, but there situations where the optimizer
> will not consider the view.
> Then again, if you are not using indexed views, this will not make a
> difference.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Chinese chars through stored procedures...

Hi,
I have a problem with my Stored Procedures...
Recently we decided to change the type of our column in our databse from
varchar to nvarchar because of new customers (chinese).
Everything works fine EXCEPT the stored procedures... When i try to pass
chinese characters for a simple SP that those a basic insert in my table, it
inserts ? instead of chinese characters...
Did i miss something obvious?
Thanks a lot!
Etienne
p.s.: you can email me at etienne_stgeorges@.hotmail.com or reply on this
newsgroup.Etienne,
Did you also change the types of the variables in the stored procedures? If
not, then you will have conversions happening.
You might also find this article useful if the international and Chinese
world are new to you:
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/dnsql2k/html/intlfeaturesinsqlserver2000.asp
Russell Fields
http://www.sqlpass.org/
2004 PASS Community Summit - Orlando
- The largest user-event dedicated to SQL Server!
"Etienne M. St-Georges" <nospam.etienne@.emstg.com> wrote in message
news:hQqvb.16446$iT4.2055861@.news20.bellglobal.com...
> Hi,
> I have a problem with my Stored Procedures...
> Recently we decided to change the type of our column in our databse from
> varchar to nvarchar because of new customers (chinese).
> Everything works fine EXCEPT the stored procedures... When i try to pass
> chinese characters for a simple SP that those a basic insert in my table,
it
> inserts ? instead of chinese characters...
> Did i miss something obvious?
> Thanks a lot!
> Etienne
> p.s.: you can email me at etienne_stgeorges@.hotmail.com or reply on this
> newsgroup.
>|||Hi Russell,
Yep, changed the types of variables too...
I'm really confused, nowhere on the web i could find somebody with the same
problem as me...
What is strange is that with my access application, i can write, update and
select (of course!) any field that has chinese characters. But when i try
my SP with Query Analyzer, it inserts ? for every chinese character...
I'll keep looking, thank you anyway!
Etienne
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:OFh7i2EsDHA.1600@.TK2MSFTNGP10.phx.gbl...
> Etienne,
> Did you also change the types of the variables in the stored procedures?
If
> not, then you will have conversions happening.
> You might also find this article useful if the international and Chinese
> world are new to you:
> http://msdn.microsoft.com/library/default.asp?
> url=/library/en-us/dnsql2k/html/intlfeaturesinsqlserver2000.asp
>
> Russell Fields
> http://www.sqlpass.org/
> 2004 PASS Community Summit - Orlando
> - The largest user-event dedicated to SQL Server!
> "Etienne M. St-Georges" <nospam.etienne@.emstg.com> wrote in message
> news:hQqvb.16446$iT4.2055861@.news20.bellglobal.com...
> >
> > Hi,
> > I have a problem with my Stored Procedures...
> > Recently we decided to change the type of our column in our databse from
> > varchar to nvarchar because of new customers (chinese).
> > Everything works fine EXCEPT the stored procedures... When i try to
pass
> > chinese characters for a simple SP that those a basic insert in my
table,
> it
> > inserts ? instead of chinese characters...
> > Did i miss something obvious?
> > Thanks a lot!
> > Etienne
> >
> > p.s.: you can email me at etienne_stgeorges@.hotmail.com or reply on this
> > newsgroup.
> >
> >
>|||Damn! Finally found it!
I had to put the letter N (capital) in front of each of my Unicode value...
example:
exec sp_SimpleInsert N'(chinesetext1)',N'(chineseText2)',N'(chineseText3)'
Thanks a lot!
"Etienne M. St-Georges" <nospam.etienne@.emstg.com> wrote in message
news:1Xrvb.16528$iT4.2069449@.news20.bellglobal.com...
> Hi Russell,
> Yep, changed the types of variables too...
> I'm really confused, nowhere on the web i could find somebody with the
same
> problem as me...
> What is strange is that with my access application, i can write, update
and
> select (of course!) any field that has chinese characters. But when i try
> my SP with Query Analyzer, it inserts ? for every chinese character...
> I'll keep looking, thank you anyway!
> Etienne
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:OFh7i2EsDHA.1600@.TK2MSFTNGP10.phx.gbl...
> > Etienne,
> >
> > Did you also change the types of the variables in the stored procedures?
> If
> > not, then you will have conversions happening.
> >
> > You might also find this article useful if the international and Chinese
> > world are new to you:
> > http://msdn.microsoft.com/library/default.asp?
> > url=/library/en-us/dnsql2k/html/intlfeaturesinsqlserver2000.asp
> >
> >
> > Russell Fields
> > http://www.sqlpass.org/
> > 2004 PASS Community Summit - Orlando
> > - The largest user-event dedicated to SQL Server!
> >
> > "Etienne M. St-Georges" <nospam.etienne@.emstg.com> wrote in message
> > news:hQqvb.16446$iT4.2055861@.news20.bellglobal.com...
> > >
> > > Hi,
> > > I have a problem with my Stored Procedures...
> > > Recently we decided to change the type of our column in our databse
from
> > > varchar to nvarchar because of new customers (chinese).
> > > Everything works fine EXCEPT the stored procedures... When i try to
> pass
> > > chinese characters for a simple SP that those a basic insert in my
> table,
> > it
> > > inserts ? instead of chinese characters...
> > > Did i miss something obvious?
> > > Thanks a lot!
> > > Etienne
> > >
> > > p.s.: you can email me at etienne_stgeorges@.hotmail.com or reply on
this
> > > newsgroup.
> > >
> > >
> >
> >
>|||Congratulations
Russell Fields
"Etienne M. St-Georges" <nospam.etienne@.emstg.com> wrote in message
news:B5svb.16541$iT4.2072482@.news20.bellglobal.com...
> Damn! Finally found it!
> I had to put the letter N (capital) in front of each of my Unicode
value...
> example:
> exec sp_SimpleInsert N'(chinesetext1)',N'(chineseText2)',N'(chineseText3)'
> Thanks a lot!
> "Etienne M. St-Georges" <nospam.etienne@.emstg.com> wrote in message
> news:1Xrvb.16528$iT4.2069449@.news20.bellglobal.com...
> > Hi Russell,
> > Yep, changed the types of variables too...
> > I'm really confused, nowhere on the web i could find somebody with the
> same
> > problem as me...
> > What is strange is that with my access application, i can write, update
> and
> > select (of course!) any field that has chinese characters. But when i
try
> > my SP with Query Analyzer, it inserts ? for every chinese character...
> > I'll keep looking, thank you anyway!
> > Etienne
> >
> > "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> > news:OFh7i2EsDHA.1600@.TK2MSFTNGP10.phx.gbl...
> > > Etienne,
> > >
> > > Did you also change the types of the variables in the stored
procedures?
> > If
> > > not, then you will have conversions happening.
> > >
> > > You might also find this article useful if the international and
Chinese
> > > world are new to you:
> > > http://msdn.microsoft.com/library/default.asp?
> > > url=/library/en-us/dnsql2k/html/intlfeaturesinsqlserver2000.asp
> > >
> > >
> > > Russell Fields
> > > http://www.sqlpass.org/
> > > 2004 PASS Community Summit - Orlando
> > > - The largest user-event dedicated to SQL Server!
> > >
> > > "Etienne M. St-Georges" <nospam.etienne@.emstg.com> wrote in message
> > > news:hQqvb.16446$iT4.2055861@.news20.bellglobal.com...
> > > >
> > > > Hi,
> > > > I have a problem with my Stored Procedures...
> > > > Recently we decided to change the type of our column in our databse
> from
> > > > varchar to nvarchar because of new customers (chinese).
> > > > Everything works fine EXCEPT the stored procedures... When i try to
> > pass
> > > > chinese characters for a simple SP that those a basic insert in my
> > table,
> > > it
> > > > inserts ? instead of chinese characters...
> > > > Did i miss something obvious?
> > > > Thanks a lot!
> > > > Etienne
> > > >
> > > > p.s.: you can email me at etienne_stgeorges@.hotmail.com or reply on
> this
> > > > newsgroup.
> > > >
> > > >
> > >
> > >
> >
> >
>|||On Fri, 21 Nov 2003 11:12:01 -0500, "Etienne M. St-Georges"
<nospam.etienne@.emstg.com> wrote:
>Hi,
>I have a problem with my Stored Procedures...
>Recently we decided to change the type of our column in our databse from
>varchar to nvarchar because of new customers (chinese).
>Everything works fine EXCEPT the stored procedures... When i try to pass
>chinese characters for a simple SP that those a basic insert in my table, it
>inserts ? instead of chinese characters...
>Did i miss something obvious?
>Thanks a lot!
G'day Etienne,
You don't mention how you are calling your stored procedures, but if you
are using ADO, note that you should use the Command object and
Parameters, and each nchar or nvarchar parameter should be created of
type adWChar or adWVarchar as appropriate.
cheers,
Ross.
--
Ross McKay, WebAware Pty Ltd
"Words can only hurt if you try to read them. Don't play their game" - Zoolander

Chinese chars through stored procedures...

Hi,
I have a problem with my Stored Procedures...
Recently we decided to change the type of our column in our databse from
varchar to nvarchar because of new customers (chinese).
Everything works fine EXCEPT the stored procedures... When i try to pass
chinese characters for a simple SP that those a basic insert in my table, it
inserts ? instead of chinese characters...
Did i miss something obvious?
Thanks a lot!
Etienne

p.s.: you can email me at etienne_stgeorges@.hotmail.com or reply on this
newsgroup.Check the source code of your stored procedure to make sure the parameter is
declared as nvarchar and not varchar.

HTH,
Dave

"Etienne M. St-Georges" <nospam.etienne@.emstg.com> wrote in message
news:MOqvb.16440$iT4.2055717@.news20.bellglobal.com ...
> Hi,
> I have a problem with my Stored Procedures...
> Recently we decided to change the type of our column in our databse from
> varchar to nvarchar because of new customers (chinese).
> Everything works fine EXCEPT the stored procedures... When i try to pass
> chinese characters for a simple SP that those a basic insert in my table,
it
> inserts ? instead of chinese characters...
> Did i miss something obvious?
> Thanks a lot!
> Etienne
> p.s.: you can email me at etienne_stgeorges@.hotmail.com or reply on this
> newsgroup.

Checking to see if a record exists and if so update else insert

I've decided to post this as a sticky given the frequency this question is asked.

For those of you wishing to build a package that determines if a source row exists in the destination and if so update it else insert it, this link is for you.

http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

Thanks Jamie!

If you want to do a similar concept to Jamie's blog post above, but with the Konesan's Checksum Transformation to quickly compare MANY fields, you can visit here:
http://www.ssistalk.com/2007/03/09/ssis-using-a-checksum-to-determine-if-a-row-has-changed/

PhilThis is exactly what I was looking for.

I appreciate that.
Fahad|||It's an awesome article - and I used this method in my 1st SSIS package.

However, if number of records is high (over 1000) the OLE Update Command performs slowly. So in that case you might better off loading source data to staging table and creating Stored Procedure which Updates then Inserts (and Deletes) data in the destination table.

Using Lookup transform in the way described in article is quite efficient (compared to other methods like merge join) - and can be useful in a variety of situations|||TheViewMaster,
Yes, very valid points. Each developer will have to decide based on the pros and cons of each method.

Personally, I wouldn't go to the extreme that you did, but would rather dump just the updates to a staging table and then outside of the data flow use an Execute SQL task to perform the batch update. I don't want to spend the cost to insert the data twice (once into staging table, and then again into the destination table using your idea).|||Can somebody point me to a simple example of this.

I have a flat file and I want to check if the record exist in the file exist in the table, if it does I want to update and if it does not I want to insert.
|||

This is very helpful. Thanks!

I am completely new to the SQL Server Integration Services. I went through your link and understand how you filter out diffrent records. Could you pointting out how you actually update you existing records ?

Thanks!

|||I have cloned the example above and it does not seem to be picking up the new or changed records. Is there any way to troubleshoot this.
|||I use OLE DB Command update the records. Thanks!|||

Hi phill,

Great article, I loved it.

This is what i had been looking for since long time.

One doubt is there in my mind, my current assignment i have to transform about 40K rows from a database.

Will this method work for me or there is any other better way.. ?

regards

Sudhir Kesharwani

|||

Sudhir Kesharwani wrote:

Hi phill,

Great article, I loved it.

This is what i had been looking for since long time.

One doubt is there in my mind, my current assignment i have to transform about 40K rows from a database.

Will this method work for me or there is any other better way.. ?

regards

Sudhir Kesharwani

You need to likely send your changed records to a temporary table and then perform a batch update using an Execute SQL Task on the control flow. Using the OLE DB Command transformation isn't very efficient for high numbers of rows.|||

Phil,
As we discussed about updating/inserting techniques a while ago(see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211321&SiteID=1) you told me SSIS can perform this operation much faster than using plain SQL... I put the emphasize on the "much faster".

I have created a performance test package comparing the 2 methods:
1) stored proc with UPDATE and INSERT statements
2) Data flow using look up/conditional split

I have a data flow task to create a data test set with a variable number of rows (I used Jamie's script component source example) with IDs (used as PK) always ranging from 1 to nbrRows. Each row is constituted of 5 int, 4 string (10, 20, 40 and 80 characters) and 1 datetime with values chosen randomly.

So what I do is calling the package with nbrRows=1, 10, 100... The first time 1 new row is added. The second time, id=1 already exists so 1 row is updated, the other 9 are added. And so on... I then perform a second round of tests with the same numbers, the only difference being that the destination table is now full (therefore performing only updates).

What I've found out so far is that SSIS is faster only when inserting a certain number of rows (approx . 1000 to 100000). SSIS performed on avg. 30% better with 50000 rows while at 1 million rows, SQL performed better by 5%.

When the data was updated (second round), SQL was always faster.

Of course, these figures are based on my tests which may not reflect actual usage. I run SQL/SSIS in parallel, the tests are done in batch, .... I thought about using DBCC DROPCLEANBUFFER but it doesn't seem to change much.

Maybe my way of comparing is totally flawed... Have you ever tried comparing the two methods in terms of performance? How did you conclude the SSIS way is much faster?

Leonce

|||

Leonce,

First, when using fast load option on the destination, there are two parameters which need to be adjusted based on the rows you are working with. Max Insert Commit Size and Rows Per Batch. What were the values for these set at?

Also note, that when possible, using a SQL Server Destination over the OLE DB Destination would be preferred.

SSIS should be no different than using BULK INSERT (it seems to be just a glorified wrapper to that). Performing updates via an OLE DB Command will always be slower than a batch update using SQL. (Only because the OLE DB Command is executed for every row in the data flow)

Phil

|||

I left the default values for Commit Size and Rows Per Batch ('' and 0). You suggest I should adjust these values depending on the number of rows transferred? What would typical values be for a table with 100000 rows? Does it dependent on the row size?

I use OLEDB Destination, I'll try with SQL Server Destination.

I forgot to mention I use your method for updates (dump into table then update). Do you think SSIS can be faster than plain SQL for updates? I would tend to think it's impossible.

Leonce

|||

fleo wrote:

I left the default values for Commit Size and Rows Per Batch ('' and 0). You suggest I should adjust these values depending on the number of rows transferred? What would typical values be for a table with 100000 rows? Does it dependent on the row size?

MICS should be set to whatever you wish the batches to be committed. So if you want to commit in 10,000 row batches, set MICS to 10,000. Rows Per Batch should be set to 100,000 in this case.

fleo wrote:

I forgot to mention I use your method for updates (dump into table then update). Do you think SSIS can be faster than plain SQL for updates? I would tend to think it's impossible.

True. Using an Execute SQL task will likely have a slight slowness to it compared to straight SQL, but in the end they do the same thing, so they should be REAL close.

|||

Let us say that a package uses merge join option to insert new rows and it uses OLE DB Destination with fast load, table lock, and batch size (1000) options. The table lock option on the destination forces us to put a NOLOCK hint in the OLE DB Source query (on the destination table). Otherwise, they run into locking contentions (one is requesting exclusive table lock when the other is requesting shared table lock). This works most of the times but occationally I see that SQL Server does not honour the hint (one scenario: when the target table does not have a clustered index on the join key column) and runs into locking contentions. Have you seen this? Do you rely on NOLOCK hint too? Thanks for your help in advance.

Checking to see if a record exists and if so update else insert

I've decided to post this as a sticky given the frequency this question is asked.

For those of you wishing to build a package that determines if a source row exists in the destination and if so update it else insert it, this link is for you.

http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

Thanks Jamie!

If you want to do a similar concept to Jamie's blog post above, but with the Konesan's Checksum Transformation to quickly compare MANY fields, you can visit here:
http://www.ssistalk.com/2007/03/09/ssis-using-a-checksum-to-determine-if-a-row-has-changed/

PhilThis is exactly what I was looking for.

I appreciate that.
Fahad|||It's an awesome article - and I used this method in my 1st SSIS package.

However, if number of records is high (over 1000) the OLE Update Command performs slowly. So in that case you might better off loading source data to staging table and creating Stored Procedure which Updates then Inserts (and Deletes) data in the destination table.

Using Lookup transform in the way described in article is quite efficient (compared to other methods like merge join) - and can be useful in a variety of situations|||TheViewMaster,
Yes, very valid points. Each developer will have to decide based on the pros and cons of each method.

Personally, I wouldn't go to the extreme that you did, but would rather dump just the updates to a staging table and then outside of the data flow use an Execute SQL task to perform the batch update. I don't want to spend the cost to insert the data twice (once into staging table, and then again into the destination table using your idea).|||Can somebody point me to a simple example of this.

I have a flat file and I want to check if the record exist in the file exist in the table, if it does I want to update and if it does not I want to insert.
|||

This is very helpful. Thanks!

I am completely new to the SQL Server Integration Services. I went through your link and understand how you filter out diffrent records. Could you pointting out how you actually update you existing records ?

Thanks!

|||I have cloned the example above and it does not seem to be picking up the new or changed records. Is there any way to troubleshoot this.
|||I use OLE DB Command update the records. Thanks!|||

Hi phill,

Great article, I loved it.

This is what i had been looking for since long time.

One doubt is there in my mind, my current assignment i have to transform about 40K rows from a database.

Will this method work for me or there is any other better way.. ?

regards

Sudhir Kesharwani

|||

Sudhir Kesharwani wrote:

Hi phill,

Great article, I loved it.

This is what i had been looking for since long time.

One doubt is there in my mind, my current assignment i have to transform about 40K rows from a database.

Will this method work for me or there is any other better way.. ?

regards

Sudhir Kesharwani

You need to likely send your changed records to a temporary table and then perform a batch update using an Execute SQL Task on the control flow. Using the OLE DB Command transformation isn't very efficient for high numbers of rows.|||

Phil,
As we discussed about updating/inserting techniques a while ago(see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211321&SiteID=1) you told me SSIS can perform this operation much faster than using plain SQL... I put the emphasize on the "much faster".

I have created a performance test package comparing the 2 methods:
1) stored proc with UPDATE and INSERT statements
2) Data flow using look up/conditional split

I have a data flow task to create a data test set with a variable number of rows (I used Jamie's script component source example) with IDs (used as PK) always ranging from 1 to nbrRows. Each row is constituted of 5 int, 4 string (10, 20, 40 and 80 characters) and 1 datetime with values chosen randomly.

So what I do is calling the package with nbrRows=1, 10, 100... The first time 1 new row is added. The second time, id=1 already exists so 1 row is updated, the other 9 are added. And so on... I then perform a second round of tests with the same numbers, the only difference being that the destination table is now full (therefore performing only updates).

What I've found out so far is that SSIS is faster only when inserting a certain number of rows (approx . 1000 to 100000). SSIS performed on avg. 30% better with 50000 rows while at 1 million rows, SQL performed better by 5%.

When the data was updated (second round), SQL was always faster.

Of course, these figures are based on my tests which may not reflect actual usage. I run SQL/SSIS in parallel, the tests are done in batch, .... I thought about using DBCC DROPCLEANBUFFER but it doesn't seem to change much.

Maybe my way of comparing is totally flawed... Have you ever tried comparing the two methods in terms of performance? How did you conclude the SSIS way is much faster?

Leonce

|||

Leonce,

First, when using fast load option on the destination, there are two parameters which need to be adjusted based on the rows you are working with. Max Insert Commit Size and Rows Per Batch. What were the values for these set at?

Also note, that when possible, using a SQL Server Destination over the OLE DB Destination would be preferred.

SSIS should be no different than using BULK INSERT (it seems to be just a glorified wrapper to that). Performing updates via an OLE DB Command will always be slower than a batch update using SQL. (Only because the OLE DB Command is executed for every row in the data flow)

Phil

|||

I left the default values for Commit Size and Rows Per Batch ('' and 0). You suggest I should adjust these values depending on the number of rows transferred? What would typical values be for a table with 100000 rows? Does it dependent on the row size?

I use OLEDB Destination, I'll try with SQL Server Destination.

I forgot to mention I use your method for updates (dump into table then update). Do you think SSIS can be faster than plain SQL for updates? I would tend to think it's impossible.

Leonce

|||

fleo wrote:

I left the default values for Commit Size and Rows Per Batch ('' and 0). You suggest I should adjust these values depending on the number of rows transferred? What would typical values be for a table with 100000 rows? Does it dependent on the row size?

MICS should be set to whatever you wish the batches to be committed. So if you want to commit in 10,000 row batches, set MICS to 10,000. Rows Per Batch should be set to 100,000 in this case.

fleo wrote:

I forgot to mention I use your method for updates (dump into table then update). Do you think SSIS can be faster than plain SQL for updates? I would tend to think it's impossible.

True. Using an Execute SQL task will likely have a slight slowness to it compared to straight SQL, but in the end they do the same thing, so they should be REAL close.

|||

Let us say that a package uses merge join option to insert new rows and it uses OLE DB Destination with fast load, table lock, and batch size (1000) options. The table lock option on the destination forces us to put a NOLOCK hint in the OLE DB Source query (on the destination table). Otherwise, they run into locking contentions (one is requesting exclusive table lock when the other is requesting shared table lock). This works most of the times but occationally I see that SQL Server does not honour the hint (one scenario: when the target table does not have a clustered index on the join key column) and runs into locking contentions. Have you seen this? Do you rely on NOLOCK hint too? Thanks for your help in advance.

Checking to see if a record exists and if so update else insert

I've decided to post this as a sticky given the frequency this question is asked.

For those of you wishing to build a package that determines if a source row exists in the destination and if so update it else insert it, this link is for you.

http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

Thanks Jamie!

If you want to do a similar concept to Jamie's blog post above, but with the Konesan's Checksum Transformation to quickly compare MANY fields, you can visit here:
http://www.ssistalk.com/2007/03/09/ssis-using-a-checksum-to-determine-if-a-row-has-changed/

PhilThis is exactly what I was looking for.

I appreciate that.
Fahad|||It's an awesome article - and I used this method in my 1st SSIS package.

However, if number of records is high (over 1000) the OLE Update Command performs slowly. So in that case you might better off loading source data to staging table and creating Stored Procedure which Updates then Inserts (and Deletes) data in the destination table.

Using Lookup transform in the way described in article is quite efficient (compared to other methods like merge join) - and can be useful in a variety of situations|||TheViewMaster,
Yes, very valid points. Each developer will have to decide based on the pros and cons of each method.

Personally, I wouldn't go to the extreme that you did, but would rather dump just the updates to a staging table and then outside of the data flow use an Execute SQL task to perform the batch update. I don't want to spend the cost to insert the data twice (once into staging table, and then again into the destination table using your idea).|||Can somebody point me to a simple example of this.

I have a flat file and I want to check if the record exist in the file exist in the table, if it does I want to update and if it does not I want to insert.
|||

This is very helpful. Thanks!

I am completely new to the SQL Server Integration Services. I went through your link and understand how you filter out diffrent records. Could you pointting out how you actually update you existing records ?

Thanks!

|||I have cloned the example above and it does not seem to be picking up the new or changed records. Is there any way to troubleshoot this.
|||I use OLE DB Command update the records. Thanks!|||

Hi phill,

Great article, I loved it.

This is what i had been looking for since long time.

One doubt is there in my mind, my current assignment i have to transform about 40K rows from a database.

Will this method work for me or there is any other better way.. ?

regards

Sudhir Kesharwani

|||

Sudhir Kesharwani wrote:

Hi phill,

Great article, I loved it.

This is what i had been looking for since long time.

One doubt is there in my mind, my current assignment i have to transform about 40K rows from a database.

Will this method work for me or there is any other better way.. ?

regards

Sudhir Kesharwani

You need to likely send your changed records to a temporary table and then perform a batch update using an Execute SQL Task on the control flow. Using the OLE DB Command transformation isn't very efficient for high numbers of rows.|||

Phil,
As we discussed about updating/inserting techniques a while ago(see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211321&SiteID=1) you told me SSIS can perform this operation much faster than using plain SQL... I put the emphasize on the "much faster".

I have created a performance test package comparing the 2 methods:
1) stored proc with UPDATE and INSERT statements
2) Data flow using look up/conditional split

I have a data flow task to create a data test set with a variable number of rows (I used Jamie's script component source example) with IDs (used as PK) always ranging from 1 to nbrRows. Each row is constituted of 5 int, 4 string (10, 20, 40 and 80 characters) and 1 datetime with values chosen randomly.

So what I do is calling the package with nbrRows=1, 10, 100... The first time 1 new row is added. The second time, id=1 already exists so 1 row is updated, the other 9 are added. And so on... I then perform a second round of tests with the same numbers, the only difference being that the destination table is now full (therefore performing only updates).

What I've found out so far is that SSIS is faster only when inserting a certain number of rows (approx . 1000 to 100000). SSIS performed on avg. 30% better with 50000 rows while at 1 million rows, SQL performed better by 5%.

When the data was updated (second round), SQL was always faster.

Of course, these figures are based on my tests which may not reflect actual usage. I run SQL/SSIS in parallel, the tests are done in batch, .... I thought about using DBCC DROPCLEANBUFFER but it doesn't seem to change much.

Maybe my way of comparing is totally flawed... Have you ever tried comparing the two methods in terms of performance? How did you conclude the SSIS way is much faster?

Leonce

|||

Leonce,

First, when using fast load option on the destination, there are two parameters which need to be adjusted based on the rows you are working with. Max Insert Commit Size and Rows Per Batch. What were the values for these set at?

Also note, that when possible, using a SQL Server Destination over the OLE DB Destination would be preferred.

SSIS should be no different than using BULK INSERT (it seems to be just a glorified wrapper to that). Performing updates via an OLE DB Command will always be slower than a batch update using SQL. (Only because the OLE DB Command is executed for every row in the data flow)

Phil

|||

I left the default values for Commit Size and Rows Per Batch ('' and 0). You suggest I should adjust these values depending on the number of rows transferred? What would typical values be for a table with 100000 rows? Does it dependent on the row size?

I use OLEDB Destination, I'll try with SQL Server Destination.

I forgot to mention I use your method for updates (dump into table then update). Do you think SSIS can be faster than plain SQL for updates? I would tend to think it's impossible.

Leonce

|||

fleo wrote:

I left the default values for Commit Size and Rows Per Batch ('' and 0). You suggest I should adjust these values depending on the number of rows transferred? What would typical values be for a table with 100000 rows? Does it dependent on the row size?

MICS should be set to whatever you wish the batches to be committed. So if you want to commit in 10,000 row batches, set MICS to 10,000. Rows Per Batch should be set to 100,000 in this case.

fleo wrote:

I forgot to mention I use your method for updates (dump into table then update). Do you think SSIS can be faster than plain SQL for updates? I would tend to think it's impossible.

True. Using an Execute SQL task will likely have a slight slowness to it compared to straight SQL, but in the end they do the same thing, so they should be REAL close.

|||

Let us say that a package uses merge join option to insert new rows and it uses OLE DB Destination with fast load, table lock, and batch size (1000) options. The table lock option on the destination forces us to put a NOLOCK hint in the OLE DB Source query (on the destination table). Otherwise, they run into locking contentions (one is requesting exclusive table lock when the other is requesting shared table lock). This works most of the times but occationally I see that SQL Server does not honour the hint (one scenario: when the target table does not have a clustered index on the join key column) and runs into locking contentions. Have you seen this? Do you rely on NOLOCK hint too? Thanks for your help in advance.

Checking to see if a record exists and if so update else insert

I've decided to post this as a sticky given the frequency this question is asked.

For those of you wishing to build a package that determines if a source row exists in the destination and if so update it else insert it, this link is for you.

http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

Thanks Jamie!

If you want to do a similar concept to Jamie's blog post above, but with the Konesan's Checksum Transformation to quickly compare MANY fields, you can visit here:
http://www.ssistalk.com/2007/03/09/ssis-using-a-checksum-to-determine-if-a-row-has-changed/

PhilThis is exactly what I was looking for.

I appreciate that.
Fahad|||It's an awesome article - and I used this method in my 1st SSIS package.

However, if number of records is high (over 1000) the OLE Update Command performs slowly. So in that case you might better off loading source data to staging table and creating Stored Procedure which Updates then Inserts (and Deletes) data in the destination table.

Using Lookup transform in the way described in article is quite efficient (compared to other methods like merge join) - and can be useful in a variety of situations|||TheViewMaster,
Yes, very valid points. Each developer will have to decide based on the pros and cons of each method.

Personally, I wouldn't go to the extreme that you did, but would rather dump just the updates to a staging table and then outside of the data flow use an Execute SQL task to perform the batch update. I don't want to spend the cost to insert the data twice (once into staging table, and then again into the destination table using your idea).|||Can somebody point me to a simple example of this.

I have a flat file and I want to check if the record exist in the file exist in the table, if it does I want to update and if it does not I want to insert.|||

This is very helpful. Thanks!

I am completely new to the SQL Server Integration Services. I went through your link and understand how you filter out diffrent records. Could you pointting out how you actually update you existing records ?

Thanks!

|||I have cloned the example above and it does not seem to be picking up the new or changed records. Is there any way to troubleshoot this.|||I use OLE DB Command update the records. Thanks!|||

Hi phill,

Great article, I loved it.

This is what i had been looking for since long time.

One doubt is there in my mind, my current assignment i have to transform about 40K rows from a database.

Will this method work for me or there is any other better way.. ?

regards

Sudhir Kesharwani

|||

Sudhir Kesharwani wrote:

Hi phill,

Great article, I loved it.

This is what i had been looking for since long time.

One doubt is there in my mind, my current assignment i have to transform about 40K rows from a database.

Will this method work for me or there is any other better way.. ?

regards

Sudhir Kesharwani

You need to likely send your changed records to a temporary table and then perform a batch update using an Execute SQL Task on the control flow. Using the OLE DB Command transformation isn't very efficient for high numbers of rows.|||

Phil,
As we discussed about updating/inserting techniques a while ago(see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211321&SiteID=1) you told me SSIS can perform this operation much faster than using plain SQL... I put the emphasize on the "much faster".

I have created a performance test package comparing the 2 methods:
1) stored proc with UPDATE and INSERT statements
2) Data flow using look up/conditional split

I have a data flow task to create a data test set with a variable number of rows (I used Jamie's script component source example) with IDs (used as PK) always ranging from 1 to nbrRows. Each row is constituted of 5 int, 4 string (10, 20, 40 and 80 characters) and 1 datetime with values chosen randomly.

So what I do is calling the package with nbrRows=1, 10, 100... The first time 1 new row is added. The second time, id=1 already exists so 1 row is updated, the other 9 are added. And so on... I then perform a second round of tests with the same numbers, the only difference being that the destination table is now full (therefore performing only updates).

What I've found out so far is that SSIS is faster only when inserting a certain number of rows (approx . 1000 to 100000). SSIS performed on avg. 30% better with 50000 rows while at 1 million rows, SQL performed better by 5%.

When the data was updated (second round), SQL was always faster.

Of course, these figures are based on my tests which may not reflect actual usage. I run SQL/SSIS in parallel, the tests are done in batch, .... I thought about using DBCC DROPCLEANBUFFER but it doesn't seem to change much.

Maybe my way of comparing is totally flawed... Have you ever tried comparing the two methods in terms of performance? How did you conclude the SSIS way is much faster?

Leonce

|||

Leonce,

First, when using fast load option on the destination, there are two parameters which need to be adjusted based on the rows you are working with. Max Insert Commit Size and Rows Per Batch. What were the values for these set at?

Also note, that when possible, using a SQL Server Destination over the OLE DB Destination would be preferred.

SSIS should be no different than using BULK INSERT (it seems to be just a glorified wrapper to that). Performing updates via an OLE DB Command will always be slower than a batch update using SQL. (Only because the OLE DB Command is executed for every row in the data flow)

Phil

|||

I left the default values for Commit Size and Rows Per Batch ('' and 0). You suggest I should adjust these values depending on the number of rows transferred? What would typical values be for a table with 100000 rows? Does it dependent on the row size?

I use OLEDB Destination, I'll try with SQL Server Destination.

I forgot to mention I use your method for updates (dump into table then update). Do you think SSIS can be faster than plain SQL for updates? I would tend to think it's impossible.

Leonce

|||

fleo wrote:

I left the default values for Commit Size and Rows Per Batch ('' and 0). You suggest I should adjust these values depending on the number of rows transferred? What would typical values be for a table with 100000 rows? Does it dependent on the row size?

MICS should be set to whatever you wish the batches to be committed. So if you want to commit in 10,000 row batches, set MICS to 10,000. Rows Per Batch should be set to 100,000 in this case.

fleo wrote:

I forgot to mention I use your method for updates (dump into table then update). Do you think SSIS can be faster than plain SQL for updates? I would tend to think it's impossible.

True. Using an Execute SQL task will likely have a slight slowness to it compared to straight SQL, but in the end they do the same thing, so they should be REAL close.

|||

Let us say that a package uses merge join option to insert new rows and it uses OLE DB Destination with fast load, table lock, and batch size (1000) options. The table lock option on the destination forces us to put a NOLOCK hint in the OLE DB Source query (on the destination table). Otherwise, they run into locking contentions (one is requesting exclusive table lock when the other is requesting shared table lock). This works most of the times but occationally I see that SQL Server does not honour the hint (one scenario: when the target table does not have a clustered index on the join key column) and runs into locking contentions. Have you seen this? Do you rely on NOLOCK hint too? Thanks for your help in advance.

Checking to see if a record exists and if so update else insert

I've decided to post this as a sticky given the frequency this question is asked.

For those of you wishing to build a package that determines if a source row exists in the destination and if so update it else insert it, this link is for you.

http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

Thanks Jamie!

If you want to do a similar concept to Jamie's blog post above, but with the Konesan's Checksum Transformation to quickly compare MANY fields, you can visit here:
http://www.ssistalk.com/2007/03/09/ssis-using-a-checksum-to-determine-if-a-row-has-changed/

PhilThis is exactly what I was looking for.

I appreciate that.
Fahad|||It's an awesome article - and I used this method in my 1st SSIS package.

However, if number of records is high (over 1000) the OLE Update Command performs slowly. So in that case you might better off loading source data to staging table and creating Stored Procedure which Updates then Inserts (and Deletes) data in the destination table.

Using Lookup transform in the way described in article is quite efficient (compared to other methods like merge join) - and can be useful in a variety of situations|||TheViewMaster,
Yes, very valid points. Each developer will have to decide based on the pros and cons of each method.

Personally, I wouldn't go to the extreme that you did, but would rather dump just the updates to a staging table and then outside of the data flow use an Execute SQL task to perform the batch update. I don't want to spend the cost to insert the data twice (once into staging table, and then again into the destination table using your idea).|||Can somebody point me to a simple example of this.

I have a flat file and I want to check if the record exist in the file exist in the table, if it does I want to update and if it does not I want to insert.|||

This is very helpful. Thanks!

I am completely new to the SQL Server Integration Services. I went through your link and understand how you filter out diffrent records. Could you pointting out how you actually update you existing records ?

Thanks!

|||I have cloned the example above and it does not seem to be picking up the new or changed records. Is there any way to troubleshoot this.|||I use OLE DB Command update the records. Thanks!|||

Hi phill,

Great article, I loved it.

This is what i had been looking for since long time.

One doubt is there in my mind, my current assignment i have to transform about 40K rows from a database.

Will this method work for me or there is any other better way.. ?

regards

Sudhir Kesharwani

|||

Sudhir Kesharwani wrote:

Hi phill,

Great article, I loved it.

This is what i had been looking for since long time.

One doubt is there in my mind, my current assignment i have to transform about 40K rows from a database.

Will this method work for me or there is any other better way.. ?

regards

Sudhir Kesharwani

You need to likely send your changed records to a temporary table and then perform a batch update using an Execute SQL Task on the control flow. Using the OLE DB Command transformation isn't very efficient for high numbers of rows.|||

Phil,
As we discussed about updating/inserting techniques a while ago(see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211321&SiteID=1) you told me SSIS can perform this operation much faster than using plain SQL... I put the emphasize on the "much faster".

I have created a performance test package comparing the 2 methods:
1) stored proc with UPDATE and INSERT statements
2) Data flow using look up/conditional split

I have a data flow task to create a data test set with a variable number of rows (I used Jamie's script component source example) with IDs (used as PK) always ranging from 1 to nbrRows. Each row is constituted of 5 int, 4 string (10, 20, 40 and 80 characters) and 1 datetime with values chosen randomly.

So what I do is calling the package with nbrRows=1, 10, 100... The first time 1 new row is added. The second time, id=1 already exists so 1 row is updated, the other 9 are added. And so on... I then perform a second round of tests with the same numbers, the only difference being that the destination table is now full (therefore performing only updates).

What I've found out so far is that SSIS is faster only when inserting a certain number of rows (approx . 1000 to 100000). SSIS performed on avg. 30% better with 50000 rows while at 1 million rows, SQL performed better by 5%.

When the data was updated (second round), SQL was always faster.

Of course, these figures are based on my tests which may not reflect actual usage. I run SQL/SSIS in parallel, the tests are done in batch, .... I thought about using DBCC DROPCLEANBUFFER but it doesn't seem to change much.

Maybe my way of comparing is totally flawed... Have you ever tried comparing the two methods in terms of performance? How did you conclude the SSIS way is much faster?

Leonce

|||

Leonce,

First, when using fast load option on the destination, there are two parameters which need to be adjusted based on the rows you are working with. Max Insert Commit Size and Rows Per Batch. What were the values for these set at?

Also note, that when possible, using a SQL Server Destination over the OLE DB Destination would be preferred.

SSIS should be no different than using BULK INSERT (it seems to be just a glorified wrapper to that). Performing updates via an OLE DB Command will always be slower than a batch update using SQL. (Only because the OLE DB Command is executed for every row in the data flow)

Phil

|||

I left the default values for Commit Size and Rows Per Batch ('' and 0). You suggest I should adjust these values depending on the number of rows transferred? What would typical values be for a table with 100000 rows? Does it dependent on the row size?

I use OLEDB Destination, I'll try with SQL Server Destination.

I forgot to mention I use your method for updates (dump into table then update). Do you think SSIS can be faster than plain SQL for updates? I would tend to think it's impossible.

Leonce

|||

fleo wrote:

I left the default values for Commit Size and Rows Per Batch ('' and 0). You suggest I should adjust these values depending on the number of rows transferred? What would typical values be for a table with 100000 rows? Does it dependent on the row size?

MICS should be set to whatever you wish the batches to be committed. So if you want to commit in 10,000 row batches, set MICS to 10,000. Rows Per Batch should be set to 100,000 in this case.

fleo wrote:

I forgot to mention I use your method for updates (dump into table then update). Do you think SSIS can be faster than plain SQL for updates? I would tend to think it's impossible.

True. Using an Execute SQL task will likely have a slight slowness to it compared to straight SQL, but in the end they do the same thing, so they should be REAL close.

|||

Let us say that a package uses merge join option to insert new rows and it uses OLE DB Destination with fast load, table lock, and batch size (1000) options. The table lock option on the destination forces us to put a NOLOCK hint in the OLE DB Source query (on the destination table). Otherwise, they run into locking contentions (one is requesting exclusive table lock when the other is requesting shared table lock). This works most of the times but occationally I see that SQL Server does not honour the hint (one scenario: when the target table does not have a clustered index on the join key column) and runs into locking contentions. Have you seen this? Do you rely on NOLOCK hint too? Thanks for your help in advance.

Checking to see if a record exists and if so update else insert

I've decided to post this as a sticky given the frequency this question is asked.

For those of you wishing to build a package that determines if a source row exists in the destination and if so update it else insert it, this link is for you.

http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

Thanks Jamie!

If you want to do a similar concept to Jamie's blog post above, but with the Konesan's Checksum Transformation to quickly compare MANY fields, you can visit here:
http://www.ssistalk.com/2007/03/09/ssis-using-a-checksum-to-determine-if-a-row-has-changed/

PhilThis is exactly what I was looking for.

I appreciate that.
Fahad|||It's an awesome article - and I used this method in my 1st SSIS package.

However, if number of records is high (over 1000) the OLE Update Command performs slowly. So in that case you might better off loading source data to staging table and creating Stored Procedure which Updates then Inserts (and Deletes) data in the destination table.

Using Lookup transform in the way described in article is quite efficient (compared to other methods like merge join) - and can be useful in a variety of situations|||TheViewMaster,
Yes, very valid points. Each developer will have to decide based on the pros and cons of each method.

Personally, I wouldn't go to the extreme that you did, but would rather dump just the updates to a staging table and then outside of the data flow use an Execute SQL task to perform the batch update. I don't want to spend the cost to insert the data twice (once into staging table, and then again into the destination table using your idea).|||Can somebody point me to a simple example of this.

I have a flat file and I want to check if the record exist in the file exist in the table, if it does I want to update and if it does not I want to insert.|||

This is very helpful. Thanks!

I am completely new to the SQL Server Integration Services. I went through your link and understand how you filter out diffrent records. Could you pointting out how you actually update you existing records ?

Thanks!

|||I have cloned the example above and it does not seem to be picking up the new or changed records. Is there any way to troubleshoot this.|||I use OLE DB Command update the records. Thanks!|||

Hi phill,

Great article, I loved it.

This is what i had been looking for since long time.

One doubt is there in my mind, my current assignment i have to transform about 40K rows from a database.

Will this method work for me or there is any other better way.. ?

regards

Sudhir Kesharwani

|||

Sudhir Kesharwani wrote:

Hi phill,

Great article, I loved it.

This is what i had been looking for since long time.

One doubt is there in my mind, my current assignment i have to transform about 40K rows from a database.

Will this method work for me or there is any other better way.. ?

regards

Sudhir Kesharwani

You need to likely send your changed records to a temporary table and then perform a batch update using an Execute SQL Task on the control flow. Using the OLE DB Command transformation isn't very efficient for high numbers of rows.|||

Phil,
As we discussed about updating/inserting techniques a while ago(see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211321&SiteID=1) you told me SSIS can perform this operation much faster than using plain SQL... I put the emphasize on the "much faster".

I have created a performance test package comparing the 2 methods:
1) stored proc with UPDATE and INSERT statements
2) Data flow using look up/conditional split

I have a data flow task to create a data test set with a variable number of rows (I used Jamie's script component source example) with IDs (used as PK) always ranging from 1 to nbrRows. Each row is constituted of 5 int, 4 string (10, 20, 40 and 80 characters) and 1 datetime with values chosen randomly.

So what I do is calling the package with nbrRows=1, 10, 100... The first time 1 new row is added. The second time, id=1 already exists so 1 row is updated, the other 9 are added. And so on... I then perform a second round of tests with the same numbers, the only difference being that the destination table is now full (therefore performing only updates).

What I've found out so far is that SSIS is faster only when inserting a certain number of rows (approx . 1000 to 100000). SSIS performed on avg. 30% better with 50000 rows while at 1 million rows, SQL performed better by 5%.

When the data was updated (second round), SQL was always faster.

Of course, these figures are based on my tests which may not reflect actual usage. I run SQL/SSIS in parallel, the tests are done in batch, .... I thought about using DBCC DROPCLEANBUFFER but it doesn't seem to change much.

Maybe my way of comparing is totally flawed... Have you ever tried comparing the two methods in terms of performance? How did you conclude the SSIS way is much faster?

Leonce

|||

Leonce,

First, when using fast load option on the destination, there are two parameters which need to be adjusted based on the rows you are working with. Max Insert Commit Size and Rows Per Batch. What were the values for these set at?

Also note, that when possible, using a SQL Server Destination over the OLE DB Destination would be preferred.

SSIS should be no different than using BULK INSERT (it seems to be just a glorified wrapper to that). Performing updates via an OLE DB Command will always be slower than a batch update using SQL. (Only because the OLE DB Command is executed for every row in the data flow)

Phil

|||

I left the default values for Commit Size and Rows Per Batch ('' and 0). You suggest I should adjust these values depending on the number of rows transferred? What would typical values be for a table with 100000 rows? Does it dependent on the row size?

I use OLEDB Destination, I'll try with SQL Server Destination.

I forgot to mention I use your method for updates (dump into table then update). Do you think SSIS can be faster than plain SQL for updates? I would tend to think it's impossible.

Leonce

|||

fleo wrote:

I left the default values for Commit Size and Rows Per Batch ('' and 0). You suggest I should adjust these values depending on the number of rows transferred? What would typical values be for a table with 100000 rows? Does it dependent on the row size?

MICS should be set to whatever you wish the batches to be committed. So if you want to commit in 10,000 row batches, set MICS to 10,000. Rows Per Batch should be set to 100,000 in this case.

fleo wrote:

I forgot to mention I use your method for updates (dump into table then update). Do you think SSIS can be faster than plain SQL for updates? I would tend to think it's impossible.

True. Using an Execute SQL task will likely have a slight slowness to it compared to straight SQL, but in the end they do the same thing, so they should be REAL close.

|||

Let us say that a package uses merge join option to insert new rows and it uses OLE DB Destination with fast load, table lock, and batch size (1000) options. The table lock option on the destination forces us to put a NOLOCK hint in the OLE DB Source query (on the destination table). Otherwise, they run into locking contentions (one is requesting exclusive table lock when the other is requesting shared table lock). This works most of the times but occationally I see that SQL Server does not honour the hint (one scenario: when the target table does not have a clustered index on the join key column) and runs into locking contentions. Have you seen this? Do you rely on NOLOCK hint too? Thanks for your help in advance.

Checking to see if a record exists and if so update else insert

I've decided to post this as a sticky given the frequency this question is asked.

For those of you wishing to build a package that determines if a source row exists in the destination and if so update it else insert it, this link is for you.

http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

Thanks Jamie!

If you want to do a similar concept to Jamie's blog post above, but with the Konesan's Checksum Transformation to quickly compare MANY fields, you can visit here:
http://www.ssistalk.com/2007/03/09/ssis-using-a-checksum-to-determine-if-a-row-has-changed/

PhilThis is exactly what I was looking for.

I appreciate that.
Fahad|||It's an awesome article - and I used this method in my 1st SSIS package.

However, if number of records is high (over 1000) the OLE Update Command performs slowly. So in that case you might better off loading source data to staging table and creating Stored Procedure which Updates then Inserts (and Deletes) data in the destination table.

Using Lookup transform in the way described in article is quite efficient (compared to other methods like merge join) - and can be useful in a variety of situations|||TheViewMaster,
Yes, very valid points. Each developer will have to decide based on the pros and cons of each method.

Personally, I wouldn't go to the extreme that you did, but would rather dump just the updates to a staging table and then outside of the data flow use an Execute SQL task to perform the batch update. I don't want to spend the cost to insert the data twice (once into staging table, and then again into the destination table using your idea).|||Can somebody point me to a simple example of this.

I have a flat file and I want to check if the record exist in the file exist in the table, if it does I want to update and if it does not I want to insert.|||

This is very helpful. Thanks!

I am completely new to the SQL Server Integration Services. I went through your link and understand how you filter out diffrent records. Could you pointting out how you actually update you existing records ?

Thanks!

|||I have cloned the example above and it does not seem to be picking up the new or changed records. Is there any way to troubleshoot this.|||I use OLE DB Command update the records. Thanks!|||

Hi phill,

Great article, I loved it.

This is what i had been looking for since long time.

One doubt is there in my mind, my current assignment i have to transform about 40K rows from a database.

Will this method work for me or there is any other better way.. ?

regards

Sudhir Kesharwani

|||

Sudhir Kesharwani wrote:

Hi phill,

Great article, I loved it.

This is what i had been looking for since long time.

One doubt is there in my mind, my current assignment i have to transform about 40K rows from a database.

Will this method work for me or there is any other better way.. ?

regards

Sudhir Kesharwani

You need to likely send your changed records to a temporary table and then perform a batch update using an Execute SQL Task on the control flow. Using the OLE DB Command transformation isn't very efficient for high numbers of rows.|||

Phil,
As we discussed about updating/inserting techniques a while ago(see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211321&SiteID=1) you told me SSIS can perform this operation much faster than using plain SQL... I put the emphasize on the "much faster".

I have created a performance test package comparing the 2 methods:
1) stored proc with UPDATE and INSERT statements
2) Data flow using look up/conditional split

I have a data flow task to create a data test set with a variable number of rows (I used Jamie's script component source example) with IDs (used as PK) always ranging from 1 to nbrRows. Each row is constituted of 5 int, 4 string (10, 20, 40 and 80 characters) and 1 datetime with values chosen randomly.

So what I do is calling the package with nbrRows=1, 10, 100... The first time 1 new row is added. The second time, id=1 already exists so 1 row is updated, the other 9 are added. And so on... I then perform a second round of tests with the same numbers, the only difference being that the destination table is now full (therefore performing only updates).

What I've found out so far is that SSIS is faster only when inserting a certain number of rows (approx . 1000 to 100000). SSIS performed on avg. 30% better with 50000 rows while at 1 million rows, SQL performed better by 5%.

When the data was updated (second round), SQL was always faster.

Of course, these figures are based on my tests which may not reflect actual usage. I run SQL/SSIS in parallel, the tests are done in batch, .... I thought about using DBCC DROPCLEANBUFFER but it doesn't seem to change much.

Maybe my way of comparing is totally flawed... Have you ever tried comparing the two methods in terms of performance? How did you conclude the SSIS way is much faster?

Leonce

|||

Leonce,

First, when using fast load option on the destination, there are two parameters which need to be adjusted based on the rows you are working with. Max Insert Commit Size and Rows Per Batch. What were the values for these set at?

Also note, that when possible, using a SQL Server Destination over the OLE DB Destination would be preferred.

SSIS should be no different than using BULK INSERT (it seems to be just a glorified wrapper to that). Performing updates via an OLE DB Command will always be slower than a batch update using SQL. (Only because the OLE DB Command is executed for every row in the data flow)

Phil

|||

I left the default values for Commit Size and Rows Per Batch ('' and 0). You suggest I should adjust these values depending on the number of rows transferred? What would typical values be for a table with 100000 rows? Does it dependent on the row size?

I use OLEDB Destination, I'll try with SQL Server Destination.

I forgot to mention I use your method for updates (dump into table then update). Do you think SSIS can be faster than plain SQL for updates? I would tend to think it's impossible.

Leonce

|||

fleo wrote:

I left the default values for Commit Size and Rows Per Batch ('' and 0). You suggest I should adjust these values depending on the number of rows transferred? What would typical values be for a table with 100000 rows? Does it dependent on the row size?

MICS should be set to whatever you wish the batches to be committed. So if you want to commit in 10,000 row batches, set MICS to 10,000. Rows Per Batch should be set to 100,000 in this case.

fleo wrote:

I forgot to mention I use your method for updates (dump into table then update). Do you think SSIS can be faster than plain SQL for updates? I would tend to think it's impossible.

True. Using an Execute SQL task will likely have a slight slowness to it compared to straight SQL, but in the end they do the same thing, so they should be REAL close.

|||

Let us say that a package uses merge join option to insert new rows and it uses OLE DB Destination with fast load, table lock, and batch size (1000) options. The table lock option on the destination forces us to put a NOLOCK hint in the OLE DB Source query (on the destination table). Otherwise, they run into locking contentions (one is requesting exclusive table lock when the other is requesting shared table lock). This works most of the times but occationally I see that SQL Server does not honour the hint (one scenario: when the target table does not have a clustered index on the join key column) and runs into locking contentions. Have you seen this? Do you rely on NOLOCK hint too? Thanks for your help in advance.

Checking to see if a record exists and if so update else insert

I've decided to post this as a sticky given the frequency this question is asked.

For those of you wishing to build a package that determines if a source row exists in the destination and if so update it else insert it, this link is for you.

http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

Thanks Jamie!

If you want to do a similar concept to Jamie's blog post above, but with the Konesan's Checksum Transformation to quickly compare MANY fields, you can visit here:
http://www.ssistalk.com/2007/03/09/ssis-using-a-checksum-to-determine-if-a-row-has-changed/

PhilThis is exactly what I was looking for.

I appreciate that.
Fahad|||It's an awesome article - and I used this method in my 1st SSIS package.

However, if number of records is high (over 1000) the OLE Update Command performs slowly. So in that case you might better off loading source data to staging table and creating Stored Procedure which Updates then Inserts (and Deletes) data in the destination table.

Using Lookup transform in the way described in article is quite efficient (compared to other methods like merge join) - and can be useful in a variety of situations|||TheViewMaster,
Yes, very valid points. Each developer will have to decide based on the pros and cons of each method.

Personally, I wouldn't go to the extreme that you did, but would rather dump just the updates to a staging table and then outside of the data flow use an Execute SQL task to perform the batch update. I don't want to spend the cost to insert the data twice (once into staging table, and then again into the destination table using your idea).|||Can somebody point me to a simple example of this.

I have a flat file and I want to check if the record exist in the file exist in the table, if it does I want to update and if it does not I want to insert.
|||

This is very helpful. Thanks!

I am completely new to the SQL Server Integration Services. I went through your link and understand how you filter out diffrent records. Could you pointting out how you actually update you existing records ?

Thanks!

|||I have cloned the example above and it does not seem to be picking up the new or changed records. Is there any way to troubleshoot this.
|||I use OLE DB Command update the records. Thanks!|||

Hi phill,

Great article, I loved it.

This is what i had been looking for since long time.

One doubt is there in my mind, my current assignment i have to transform about 40K rows from a database.

Will this method work for me or there is any other better way.. ?

regards

Sudhir Kesharwani

|||

Sudhir Kesharwani wrote:

Hi phill,

Great article, I loved it.

This is what i had been looking for since long time.

One doubt is there in my mind, my current assignment i have to transform about 40K rows from a database.

Will this method work for me or there is any other better way.. ?

regards

Sudhir Kesharwani

You need to likely send your changed records to a temporary table and then perform a batch update using an Execute SQL Task on the control flow. Using the OLE DB Command transformation isn't very efficient for high numbers of rows.|||

Phil,
As we discussed about updating/inserting techniques a while ago(see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211321&SiteID=1) you told me SSIS can perform this operation much faster than using plain SQL... I put the emphasize on the "much faster".

I have created a performance test package comparing the 2 methods:
1) stored proc with UPDATE and INSERT statements
2) Data flow using look up/conditional split

I have a data flow task to create a data test set with a variable number of rows (I used Jamie's script component source example) with IDs (used as PK) always ranging from 1 to nbrRows. Each row is constituted of 5 int, 4 string (10, 20, 40 and 80 characters) and 1 datetime with values chosen randomly.

So what I do is calling the package with nbrRows=1, 10, 100... The first time 1 new row is added. The second time, id=1 already exists so 1 row is updated, the other 9 are added. And so on... I then perform a second round of tests with the same numbers, the only difference being that the destination table is now full (therefore performing only updates).

What I've found out so far is that SSIS is faster only when inserting a certain number of rows (approx . 1000 to 100000). SSIS performed on avg. 30% better with 50000 rows while at 1 million rows, SQL performed better by 5%.

When the data was updated (second round), SQL was always faster.

Of course, these figures are based on my tests which may not reflect actual usage. I run SQL/SSIS in parallel, the tests are done in batch, .... I thought about using DBCC DROPCLEANBUFFER but it doesn't seem to change much.

Maybe my way of comparing is totally flawed... Have you ever tried comparing the two methods in terms of performance? How did you conclude the SSIS way is much faster?

Leonce

|||

Leonce,

First, when using fast load option on the destination, there are two parameters which need to be adjusted based on the rows you are working with. Max Insert Commit Size and Rows Per Batch. What were the values for these set at?

Also note, that when possible, using a SQL Server Destination over the OLE DB Destination would be preferred.

SSIS should be no different than using BULK INSERT (it seems to be just a glorified wrapper to that). Performing updates via an OLE DB Command will always be slower than a batch update using SQL. (Only because the OLE DB Command is executed for every row in the data flow)

Phil

|||

I left the default values for Commit Size and Rows Per Batch ('' and 0). You suggest I should adjust these values depending on the number of rows transferred? What would typical values be for a table with 100000 rows? Does it dependent on the row size?

I use OLEDB Destination, I'll try with SQL Server Destination.

I forgot to mention I use your method for updates (dump into table then update). Do you think SSIS can be faster than plain SQL for updates? I would tend to think it's impossible.

Leonce

|||

fleo wrote:

I left the default values for Commit Size and Rows Per Batch ('' and 0). You suggest I should adjust these values depending on the number of rows transferred? What would typical values be for a table with 100000 rows? Does it dependent on the row size?

MICS should be set to whatever you wish the batches to be committed. So if you want to commit in 10,000 row batches, set MICS to 10,000. Rows Per Batch should be set to 100,000 in this case.

fleo wrote:

I forgot to mention I use your method for updates (dump into table then update). Do you think SSIS can be faster than plain SQL for updates? I would tend to think it's impossible.

True. Using an Execute SQL task will likely have a slight slowness to it compared to straight SQL, but in the end they do the same thing, so they should be REAL close.

|||

Let us say that a package uses merge join option to insert new rows and it uses OLE DB Destination with fast load, table lock, and batch size (1000) options. The table lock option on the destination forces us to put a NOLOCK hint in the OLE DB Source query (on the destination table). Otherwise, they run into locking contentions (one is requesting exclusive table lock when the other is requesting shared table lock). This works most of the times but occationally I see that SQL Server does not honour the hint (one scenario: when the target table does not have a clustered index on the join key column) and runs into locking contentions. Have you seen this? Do you rely on NOLOCK hint too? Thanks for your help in advance.

Checking to see if a record exists and if so update else insert

I've decided to post this as a sticky given the frequency this question is asked.

For those of you wishing to build a package that determines if a source row exists in the destination and if so update it else insert it, this link is for you.

http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

Thanks Jamie!

If you want to do a similar concept to Jamie's blog post above, but with the Konesan's Checksum Transformation to quickly compare MANY fields, you can visit here:
http://www.ssistalk.com/2007/03/09/ssis-using-a-checksum-to-determine-if-a-row-has-changed/

PhilThis is exactly what I was looking for.

I appreciate that.
Fahad|||It's an awesome article - and I used this method in my 1st SSIS package.

However, if number of records is high (over 1000) the OLE Update Command performs slowly. So in that case you might better off loading source data to staging table and creating Stored Procedure which Updates then Inserts (and Deletes) data in the destination table.

Using Lookup transform in the way described in article is quite efficient (compared to other methods like merge join) - and can be useful in a variety of situations|||TheViewMaster,
Yes, very valid points. Each developer will have to decide based on the pros and cons of each method.

Personally, I wouldn't go to the extreme that you did, but would rather dump just the updates to a staging table and then outside of the data flow use an Execute SQL task to perform the batch update. I don't want to spend the cost to insert the data twice (once into staging table, and then again into the destination table using your idea).|||Can somebody point me to a simple example of this.

I have a flat file and I want to check if the record exist in the file exist in the table, if it does I want to update and if it does not I want to insert.
|||

This is very helpful. Thanks!

I am completely new to the SQL Server Integration Services. I went through your link and understand how you filter out diffrent records. Could you pointting out how you actually update you existing records ?

Thanks!

|||I have cloned the example above and it does not seem to be picking up the new or changed records. Is there any way to troubleshoot this.
|||I use OLE DB Command update the records. Thanks!|||

Hi phill,

Great article, I loved it.

This is what i had been looking for since long time.

One doubt is there in my mind, my current assignment i have to transform about 40K rows from a database.

Will this method work for me or there is any other better way.. ?

regards

Sudhir Kesharwani

|||

Sudhir Kesharwani wrote:

Hi phill,

Great article, I loved it.

This is what i had been looking for since long time.

One doubt is there in my mind, my current assignment i have to transform about 40K rows from a database.

Will this method work for me or there is any other better way.. ?

regards

Sudhir Kesharwani

You need to likely send your changed records to a temporary table and then perform a batch update using an Execute SQL Task on the control flow. Using the OLE DB Command transformation isn't very efficient for high numbers of rows.|||

Phil,
As we discussed about updating/inserting techniques a while ago(see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211321&SiteID=1) you told me SSIS can perform this operation much faster than using plain SQL... I put the emphasize on the "much faster".

I have created a performance test package comparing the 2 methods:
1) stored proc with UPDATE and INSERT statements
2) Data flow using look up/conditional split

I have a data flow task to create a data test set with a variable number of rows (I used Jamie's script component source example) with IDs (used as PK) always ranging from 1 to nbrRows. Each row is constituted of 5 int, 4 string (10, 20, 40 and 80 characters) and 1 datetime with values chosen randomly.

So what I do is calling the package with nbrRows=1, 10, 100... The first time 1 new row is added. The second time, id=1 already exists so 1 row is updated, the other 9 are added. And so on... I then perform a second round of tests with the same numbers, the only difference being that the destination table is now full (therefore performing only updates).

What I've found out so far is that SSIS is faster only when inserting a certain number of rows (approx . 1000 to 100000). SSIS performed on avg. 30% better with 50000 rows while at 1 million rows, SQL performed better by 5%.

When the data was updated (second round), SQL was always faster.

Of course, these figures are based on my tests which may not reflect actual usage. I run SQL/SSIS in parallel, the tests are done in batch, .... I thought about using DBCC DROPCLEANBUFFER but it doesn't seem to change much.

Maybe my way of comparing is totally flawed... Have you ever tried comparing the two methods in terms of performance? How did you conclude the SSIS way is much faster?

Leonce

|||

Leonce,

First, when using fast load option on the destination, there are two parameters which need to be adjusted based on the rows you are working with. Max Insert Commit Size and Rows Per Batch. What were the values for these set at?

Also note, that when possible, using a SQL Server Destination over the OLE DB Destination would be preferred.

SSIS should be no different than using BULK INSERT (it seems to be just a glorified wrapper to that). Performing updates via an OLE DB Command will always be slower than a batch update using SQL. (Only because the OLE DB Command is executed for every row in the data flow)

Phil

|||

I left the default values for Commit Size and Rows Per Batch ('' and 0). You suggest I should adjust these values depending on the number of rows transferred? What would typical values be for a table with 100000 rows? Does it dependent on the row size?

I use OLEDB Destination, I'll try with SQL Server Destination.

I forgot to mention I use your method for updates (dump into table then update). Do you think SSIS can be faster than plain SQL for updates? I would tend to think it's impossible.

Leonce

|||

fleo wrote:

I left the default values for Commit Size and Rows Per Batch ('' and 0). You suggest I should adjust these values depending on the number of rows transferred? What would typical values be for a table with 100000 rows? Does it dependent on the row size?

MICS should be set to whatever you wish the batches to be committed. So if you want to commit in 10,000 row batches, set MICS to 10,000. Rows Per Batch should be set to 100,000 in this case.

fleo wrote:

I forgot to mention I use your method for updates (dump into table then update). Do you think SSIS can be faster than plain SQL for updates? I would tend to think it's impossible.

True. Using an Execute SQL task will likely have a slight slowness to it compared to straight SQL, but in the end they do the same thing, so they should be REAL close.

|||

Let us say that a package uses merge join option to insert new rows and it uses OLE DB Destination with fast load, table lock, and batch size (1000) options. The table lock option on the destination forces us to put a NOLOCK hint in the OLE DB Source query (on the destination table). Otherwise, they run into locking contentions (one is requesting exclusive table lock when the other is requesting shared table lock). This works most of the times but occationally I see that SQL Server does not honour the hint (one scenario: when the target table does not have a clustered index on the join key column) and runs into locking contentions. Have you seen this? Do you rely on NOLOCK hint too? Thanks for your help in advance.

Checking if String is NULL or EMPTY in SQL

I need to check in my Stored procedure if the information passed is null or empty so I can decided to insert the new value or keep the old. How do I accomplish this please in T-SQL. Thanks in advance.

IFISNULL(@.param)OR @.param =''THEN doSomething...

(Note the two apostrophes ' and ', not a quote mark!)

|||

Books online (help files that comes with SQL) is an amzing little app. Below is an example and the Syntax.

USE pubsGOSELECTAVG(ISNULL(price, $10.00))FROM titlesGOISNULL ( check_expression , replacement_value )

|||

Create Procedure mySpNameHere

@.InputValueHere VARCHAR(50) = NULL

AS

IF @.InputValue IS NULL OR @.InputValue = ''

/*Keep the old value*/

ELSE

/*Run the update statement here with your new value*/

|||

cheetahtech:

ISNULL ( check_expression , replacement_value )

Sorry. I got a blackout. Of course, ISNULL syntax is to be used in a query where you want to specify an alternative value, if the expression is NULL.

The correct way to check for NULL in a condition is IF @.Param IS NULL as rich freeman points out.