Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Appalling performance of CLR SqlBulkCopy Expand / Collapse
Author
Message
Posted Friday, May 11, 2012 10:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 16, 2013 8:18 AM
Points: 15, Visits: 45
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.
Post #1298758
Posted Friday, May 11, 2012 10:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1298761
Posted Friday, May 11, 2012 11:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 16, 2013 8:18 AM
Points: 15, Visits: 45
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! :)

have an excellent day,

tonko.
Post #1298778
Posted Friday, May 11, 2012 11:15 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:33 PM
Points: 22,475, Visits: 30,157
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! :)

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




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)
Post #1298783
Posted Friday, May 11, 2012 11:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:25 AM
Points: 7,070, Visits: 12,523
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! :)

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1298784
Posted Friday, May 11, 2012 11:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 16, 2013 8:18 AM
Points: 15, Visits: 45
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.
Post #1298814
Posted Friday, May 11, 2012 2:02 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 16, 2013 8:18 AM
Points: 15, Visits: 45
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.
Post #1298953
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse