Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Appalling performance of CLR SqlBulkCopy


Appalling performance of CLR SqlBulkCopy

Author
Message
Tonci Korsano
Tonci Korsano
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 46
hi there,

your suggestions are all fine.
bulk copy is always fast because it doesn't log (it is like saying it doesn't use the transaction log). so, if the issue is not rolling back from a heavy amount of inserts then sqlbulkcopy, bcp, etc is always an answer.

datareaders are always faster than dataadapters with datasets, etc. so, if your issue is speed then datareaders are a solution, if they can be used in your solution.

everything looks great, but i wanted to add that in the same way you send an update or "insert into" statements to a dataadapter with a command object, you can also send a stored procedure call, you attach the connection to the command object, plus the procedure and its parameters. later, you use this command object in your (maybe) fill method call of the dataadapter.

i tested this procedure call with command and dataadapter objects for sql server and oracle, and i can guarantee it will give performance boost over a sql batch or dynamic sql (at least most of the time).

best regards,

tonko.
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8239 Visits: 14368
Tonci Korsano (5/11/2012)
hi there,

your suggestions are all fine.
bulk copy is always fast because it doesn't log (it is like saying it doesn't use the transaction log). so, if the issue is not rolling back from a heavy amount of inserts then sqlbulkcopy, bcp, etc is always an answer.

That is completely untrue. All DML operations are logged. Data added using bulk-load tools are no exception.

datareaders are always faster than dataadapters with datasets, etc. so, if your issue is speed then datareaders are a solution, if they can be used in your solution.

everything looks great, but i wanted to add that in the same way you send an update or "insert into" statements to a dataadapter with a command object, you can also send a stored procedure call, you attach the connection to the command object, plus the procedure and its parameters. later, you use this command object in your (maybe) fill method call of the dataadapter.

i tested this procedure call with command and dataadapter objects for sql server and oracle, and i can guarantee it will give performance boost over a sql batch or dynamic sql (at least most of the time).

best regards,

tonko.

Wow, you threw out an "always" and even a "guarantee". I suspect the OP will need to test things on their system hardware with their data and their workload to know for sure. "It depends" usually rules the discourse here.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Tonci Korsano
Tonci Korsano
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 46
hi there,

here is the answer --> https://www.google.com/#hl=en&sa=X&psj=1&ei=70OtT53nCYKy8ASUwrWXDQ&ved=0CBgQvwUoAQ&q=(sqlbulkcopy+OR+bcp)+(%22no+logging%22+OR+%22does+not+log%22)&spell=1&bav=on.2,or.r_gc.r_pw.r_cp.r_qf.,cf.osb&fp=356e729b7a15c7f0&biw=1280&bih=891

or just put in google.com

(sqlbulkcopy OR bcp) ("no logging" OR "does not log")

this is really a web search... even though it doesn't look like one

i remember bcp or bulk copying from 1997 and sql server 6.5.
i think it was already there for sql server 6.0, but i am not sure.
i also remember this from sybase 11.02 and 11.5 and 1998 and 1999.

truncate doesn't log either.
there is truncate in sql server, sybase and oracle, as far as i can remember.
i am sure relational dbs have all a type of truncate and bulk copy, but maybe with different names.
sometimes logging is not required...
if you are going to delete a table with millions of rows (meaning the entire table), and you won't need to rollback, then truncate can do that job for you under a second.

but what is this i would really like?
being young enough do not know any of this for those versions of sybase and sql server.
it is like "do you remember this from sq server 6.0?"
and then i could really say "what are you talking about? i am not old enough to have worked with anything like that!"

well... too bad for me! Smile

have an excellent day,

tonko.
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24189 Visits: 37956
Tonci Korsano (5/11/2012)
hi there,

here is the answer --> https://www.google.com/#hl=en&sa=X&psj=1&ei=70OtT53nCYKy8ASUwrWXDQ&ved=0CBgQvwUoAQ&q=(sqlbulkcopy+OR+bcp)+(%22no+logging%22+OR+%22does+not+log%22)&spell=1&bav=on.2,or.r_gc.r_pw.r_cp.r_qf.,cf.osb&fp=356e729b7a15c7f0&biw=1280&bih=891

or just put in google.com

(sqlbulkcopy OR bcp) ("no logging" OR "does not log")

this is really a web search... even though it doesn't look like one

i remember bcp or bulk copying from 1997 and sql server 6.5.
i think it was already there for sql server 6.0, but i am not sure.
i also remember this from sybase 11.02 and 11.5 and 1998 and 1999.

truncate doesn't log either.
there is truncate in sql server, sybase and oracle, as far as i can remember.
i am sure relational dbs have all a type of truncate and bulk copy, but maybe with different names.
sometimes logging is not required...
if you are going to delete a table with millions of rows (meaning the entire table), and you won't need to rollback, then truncate can do that job for you under a second.

but what is this i would really like?
being young enough do not know any of this for those versions of sybase and sql server.
it is like "do you remember this from sq server 6.0?"
and then i could really say "what are you talking about? i am not old enough to have worked with anything like that!"

well... too bad for me! Smile

have an excellent day,

tonko.



Hate to tell you this, but truncate is logged.

Run this code:


CREATE TABLE dbo.Test (
id INT
);
GO

WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)
INSERT INTO dbo.Test (id)
SELECT * FROM cteTally;

GO

SELECT * FROM dbo.Test;

BEGIN TRANSACTION

TRUNCATE TABLE dbo.Test;

SELECT * FROM dbo.Test;

ROLLBACK;

SELECT * FROM dbo.Test;

GO

DROP TABLE dbo.Test;
GO



Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8239 Visits: 14368
Tonci Korsano (5/11/2012)
hi there,

here is the answer --> https://www.google.com/#hl=en&sa=X&psj=1&ei=70OtT53nCYKy8ASUwrWXDQ&ved=0CBgQvwUoAQ&q=(sqlbulkcopy+OR+bcp)+(%22no+logging%22+OR+%22does+not+log%22)&spell=1&bav=on.2,or.r_gc.r_pw.r_cp.r_qf.,cf.osb&fp=356e729b7a15c7f0&biw=1280&bih=891

or just put in google.com

(sqlbulkcopy OR bcp) ("no logging" OR "does not log")

this is really a web search... even though it doesn't look like one

The first two links returned are for Sybase SQL Server, not Microsoft SQL Server. The third link is for Microsoft BULK INSERT and if you read it, it says "The Bulk Insert task does not log error-causing rows". That is much different than not logging anything to the transaction log.

truncate doesn't log either.

Also false. Truncate is a logged command. Try it out. You can see it can be rolled back.

if you are going to delete a table with millions of rows (meaning the entire table), and you won't need to rollback, then truncate can do that job for you under a second.

Not always. Truncate deallocates entire pages rather than deelting the individual rows on those pages, making it a faster option, but and on large tables or on slow systems truncate can still take more than "under a second".

but what is this i would really like?
being young enough do not know any of this for those versions of sybase and sql server.
it is like "do you remember this from sq server 6.0?"
and then i could really say "what are you talking about? i am not old enough to have worked with anything like that!"

well... too bad for me! Smile

Please do not cite your age or the number of versions of a product you have worked with as a means to claim authority, you are only embarrassing yourself.

have an excellent day

You too :-)

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Tonci Korsano
Tonci Korsano
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 46
i am sure you are right because i saw it.
maybe if a table is about 167 millions of rows, which i saw happening, then it takes more than a second.
so, i know you are right...
if your table is one to two millions of rows then yes it took less than a second.
the issue about bcp and truncate is not only rolling back.
last time, which was many years ago, i couldnt figure a 100% right rollback, but maybe i took a wrong approach.
the issue with non-logged db operations is reflected in doing a restore besides a rollback.

however, this is all several versions back of sql server.
maybe i took wrong approachs to rollback and restore non-logged db operations, or maybe sql server has been improved since version 6.0, and now rollback and restore of non-logged operations can be accomplished far better than during the 90s.

by the end, maybe i am all wrong and there are no non-logged operations in sql server, but if you are logging then deleting a 167 millions of rows table, with a typical "delete from" statement might take you literally hours if not more than a day.

maybe, i am wrong about that too, but if you are logging and making a one million inserts with you typical "insert into" statement, instead of sqlbulkcopy or bcp or other techniques like dts or ssis, this sql task will take several times more "time" to accomplish.

thank you very much for all the info i didn't know about, and also thank you for correcting me.

really appreciated,

tonko.
Tonci Korsano
Tonci Korsano
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 46
i forgot to say something very important.
i apologize for all inaccuracies i have written in this thread, as these inaccuracies might mislead to wrong conclusions, which goes against the purpose of this forum.
these inaccuracies simply have no excuse as i should have double checked they were wrong by simply doing web searching.

i will avoid inaccuracies of all kinds as much as i can in the future.

best regards,

tonko.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search