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 Tuesday, March 20, 2012 10:11 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, August 3, 2014 3:41 PM
Points: 1,293, Visits: 1,430
In my CLR, I first tried a DataAdapter - when writing back to the database the performance is awful. 8000 rows take over a minute to write. So I've changed to using SqlBulkCopy - and the advantage is minimal - just shaved maybe 10% off the time.

The CLR performs some very complex processing (very quickly) and writes it back to a table in the calling T-SQL. I've tried all the usual tweaks - drop all indexes before the bulkCopy, set recovery mode to Simple, send the data with an Order BY - But these 8000 rows, which only take a fraction of a second to create in the CLR still take over a minute to write back to the SQL Table.

Has anyone got any performance tips for writing data back from a CLR?

EDIT: Performance on development 2008 was great about 1 sec (still targeting .NET 3.5 libraries), but moved to production (SQL 2005) and performance is bad bad bad, though since the production server is a 16-processor monster with 32Gb Ram, the actual .NET processing is quicker its only the writing back to SQL Server thats wading-through-treacle slow.
Post #1269696
Posted Tuesday, March 20, 2012 10:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 16, 2013 8:18 AM
Points: 15, Visits: 45
hi there,

please, make this google saved search:

sqldatareader vs dataset performance

you could try sqldatareader.
it uses a server-side cursor.
you have to close your sqldatareader object once you are finished with it.
scrolling of a datareader is different from a dataset.
dataset are in-memory databases and disconnected from the data source.
sqldatareader is not disconnected from its data source.
sqldatareader are a little like recordsets with a server-side cursor used in classic ado.
you could also do these google saved searches:

oledbdatareader vs sqldatareader

oledbdatareader vs dataset

i think you have to know in advance the data types of the columns you access with a datareader object, but you could also access them by index position and then cast to the required data type.

you still have different techniques to try...

good luck,

tonci korsano
Post #1269726
Posted Tuesday, March 20, 2012 11:19 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:28 AM
Points: 7,139, Visits: 15,190
From my experience, the CLR itself is always going to be disadvantaged when trying to write to the DB (the CLr best practices advocate avoiding writebacks for this reason). The best advice I can give you is - don't do the write-back within CLR. Have the CLR generate the results, and insert them using "regular" T-SQL.

Insert mytargettable(mycolumns)
Exec MyCLRGenerateAndSelectProc





----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #1269745
Posted Tuesday, March 20, 2012 5:47 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:11 AM
Points: 36,994, Visits: 31,513
I guess my suggestion would be to dump the CLR altogether and do the data layer calculations in the data layer.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1269957
Posted Wednesday, March 21, 2012 7:21 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, August 3, 2014 3:41 PM
Points: 1,293, Visits: 1,430
Thanks for the suggestions but, ...

well I'll just have to eat crow on this one

Turns out I had my timing code in the wrong place in the CLR, leading to the mistaken conclusion that SqlBulkCopy sucks.

I was WRONG. SqlBulkCopy never registers above 0.2 milliseconds, and more frequently its 0.03 milliseconds.

The thing taking the time is sorting a DataTable in .NET the larger the table gets, the exponentially longer the sort takes. Haven't figured a workaround yet, but just letting you know SqlBulkCopy is off the hook.
Post #1270144
Posted Wednesday, March 21, 2012 10:22 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, August 3, 2014 3:41 PM
Points: 1,293, Visits: 1,430
Just for completeness here are a few things to try if your CLR performance is unacceptable.

1) Despite the title of this thread, DO use SqlBulkCopy - its much faster than the DataAdapter.Update() method - which does an update for every changed datarow.

2) Do sorting in T-SQL - I used BulkCopy to write out the datatable to a pre-created temp table, which had a clustered primary key on my sort field

3) Read in a DataReader if you don't need to change the data - so now I read the temp table ordered by its primary key, and process the contents I do all my processing into a new dataTable which uses appropriate PrimaryKey settings

4) Do Joins in T-SQL - again bulkcopy back to the T-SQL, and to get the join results, use a DataAdapter with SqlCommand that does the appropriate JOIN

5) You can do most things T-SQL with a DataAdapter's SqlCommand - e.g. DELETE, TRUNCATE, INSERT INTO, call other stored procedures ... anything set-based, get Sql Server to do it

My 60-second proc is now around 20 sec .. and I still have a couple of things to try ..
Post #1270322
Posted Thursday, March 22, 2012 1:02 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:04 PM
Points: 7,097, Visits: 12,603
Nice tips, thanks for posting back!

Just curious, can you describe (without disclosing anything confidential of course) the "complex processing" you are doing in your CLR object that made you decide to use the CLR instead of native T-SQL code?


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1271154
Posted Friday, March 23, 2012 2:47 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, August 3, 2014 3:41 PM
Points: 1,293, Visits: 1,430
We use the CLR because of the complexity of the processing. It probably could be written in a cursor - but it would be a maintenance headache, especially as there are more .NET skills here than T-SQL. Also it has to run in a 2-minute slot, and cursor performance may not cut it.

The process is designed to generate a sequence of records using several user-definable criteria. Each rule is given a priority and can force a grouping-together (resulting in clumps of similar records) or a spreading apart (to get ratios of 1:N patterns). On top of this there are priority 'queue-jumping' rules to be applied after.

It actually piggy-backs on antoher SQL Server Agent Job, which already gets most of the data in temporary tables, so it seemed the logical place to call the CLR process, as the context connection can access these temporary tables. (instead of creating a new .NET process that calls SQL)

And I now have performance down to 1.2 seconds.
Post #1271461
Posted Friday, March 23, 2012 9:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:04 PM
Points: 7,097, Visits: 12,603
Thanks, Tom. Granted, I have little to none of the nitty-gritty details but from what you have told us it sounds like it could have been done using T-SQL. I know one person on this thread salivating over the possibility of besting 1.2 seconds in terms of performance while using less CPU, memory and I/O than the SQLCLR object. Either way however, it appears you would have a code-maintenance challenge on your hands given the complexity.

I regularly see the choice of technology influenced by the technical strengths of existing team members, which is obviously a real concern to the success of any project. I am happy you have something maintainable that has reached an acceptable level of performance. Thanks again for posting those tips.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1271745
Posted Friday, May 11, 2012 10:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:04 PM
Points: 7,097, Visits: 12,603
curtis2325 (5/11/2012)
Thanks for shearing nice post are rally useful thanks


<span id="_ctl5_ctlTopic_ctlPanelBar_ctlTopicsRepeater__ctl22_lblPostSignature"><b><a href="http://www.textmessagespysoftware.com" target="_blank" class="SmlLinks">text message spy software</a></b>
<b><a href="http://www.textmessagespysoftware.com/spy-software/spy-girlfriends-phone-spy-software/" target="_blank" class="SmlLinks">spy on girlfriends phone</a></b>
<b><a href="http://www.textmessagespysoftware.com/cell-phone-spy-2/spy-text-messages-installing-software/" target="_blank" class="SmlLinks">spy on text messages without installing software</a></b>
<b><a href="http://www.textmessagespysoftware.com/cell-phone-spy-2/spy-text-messages-installing-software/" target="_blank" class="SmlLinks">spy on text messages</a></b>
<b><a href="http://www.textmessagespysoftware.com/spy-software/blackberry-text-message-spy-software/" target="_blank" class="SmlLinks">blackberry text message spy software</a></b>
<b><a href="http://www.textmessagespysoftware.com/spy-software/nokia-text-message-spy-software/" target="_blank" class="SmlLinks">nokia text message spy software</a></b>
<b><a href="http://www.textmessagespysoftware.com/spy-software/android-text-message-spy-software/" target="_blank" class="SmlLinks">Android text message spy software</a></b>

</span>





Reported as SPAM


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1298739
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse