bulk insert vs CLR stored proc doing transactioned insert

I have 1) a windows application (which was the test bed used for
building this code) that takes some data and expands them into much
larger datasets and inserts them into another table. An input table of
81 rows produces 12,000 resulting rows. I then created a CLR Stored
procedure to do the equivalent task, based on this code,which rather
than using the bulk insert, creates inserts within a transaction.
The windows application completes this operation in 1.9 secs on
average.
The CLR stored procedure completes this operation in 3.2 secs on
average.
I want to optimise this as much as possible, as it is a crucial
operation processing large amounts of data (30 million rows output/
day) - is it possible to re-produce the bulk insert type performance
levels achieved by the vanilla .net Win app within a CLR stored
procedure? I realise bulk insert is highly optimised, but i would be
disappointed if it were not to produce better performance from a CLR
stored procedure running within the sql server context.
Thanks for reading.easyfx,
Actually, there is no reason to expect the CLR to be faster than an
optimized special purpose statement like BULK INSERT. In general, for data
intensive work the CLR is not as fast as pure T-SQL.
The CLR is best used for things that are difficult in TSQL. See the
following article for a brief discussion:
http://www.microsoft.com/technet/technetmag/issues/2006/01/BoostPerformance/default.aspx
RLF
"easyfx" <easyforexsignals@.gmail.com> wrote in message
news:1174984934.074902.172730@.o5g2000hsb.googlegroups.com...
>I have 1) a windows application (which was the test bed used for
> building this code) that takes some data and expands them into much
> larger datasets and inserts them into another table. An input table of
> 81 rows produces 12,000 resulting rows. I then created a CLR Stored
> procedure to do the equivalent task, based on this code,which rather
> than using the bulk insert, creates inserts within a transaction.
> The windows application completes this operation in 1.9 secs on
> average.
> The CLR stored procedure completes this operation in 3.2 secs on
> average.
> I want to optimise this as much as possible, as it is a crucial
> operation processing large amounts of data (30 million rows output/
> day) - is it possible to re-produce the bulk insert type performance
> levels achieved by the vanilla .net Win app within a CLR stored
> procedure? I realise bulk insert is highly optimised, but i would be
> disappointed if it were not to produce better performance from a CLR
> stored procedure running within the sql server context.
> Thanks for reading.
>|||"easyfx" <easyforexsignals@.gmail.com> wrote in message
news:1174984934.074902.172730@.o5g2000hsb.googlegroups.com...
>I have 1) a windows application (which was the test bed used for
> building this code) that takes some data and expands them into much
> larger datasets and inserts them into another table. An input table of
> 81 rows produces 12,000 resulting rows. I then created a CLR Stored
> procedure to do the equivalent task, based on this code,which rather
> than using the bulk insert, creates inserts within a transaction.
> The windows application completes this operation in 1.9 secs on
> average.
> The CLR stored procedure completes this operation in 3.2 secs on
> average.
> I want to optimise this as much as possible, as it is a crucial
> operation processing large amounts of data (30 million rows output/
> day) - is it possible to re-produce the bulk insert type performance
> levels achieved by the vanilla .net Win app within a CLR stored
> procedure? I realise bulk insert is highly optimised, but i would be
> disappointed if it were not to produce better performance from a CLR
> stored procedure running within the sql server context.
>
Well considering that Bulk Insert was written by the same team that wrote
the engine, has survived a lot of optimizations, I suspect you'll have a
hard time matching its performance.
Why not use bulk insert itself?
> Thanks for reading.
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html