[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Insert Performance with CLR Table-Valued Function

jwilson128

3/29/2007 5:53:00 PM

I have developed complex logic in VB.NET that generates a set of 360
cash-flow records (18 fields) for each loan record and have
implemented this logic through the SQL Server 2005 CLR functionality.

I have reduced the time to read 10K loan records from the db and run
through the VB logic to create the collections for each loan to 18s. I
am now trying to figure out the fastest way to get these 3.6mil
records loaded to a database table.

I first used a CLR stored procedure and the sqlpipe object which took
~4.5min. I then used a table-valued function that returns the cash-
flow records for a single loan, stepped thorugh the loan table with a
cursor using a INSERT INTO.. calling the function for each loan - this
reduced the time to ~2.5min.

I'm looking for any suggestions in how I might improve this further?
I'm not experienced enough to really know what to expect. I realize
disk operations are more time-consuming, but it doesn't feel right
that these records take 18s to produce and 2+ min. to load.

System Info:
Running everything on my workstation (P4 3.4Ghz,2G Ram), SQL Server
Express
No indexes on table
Simple recovery

Thanks. -Jeff
4 Answers

Kent Tegels

3/29/2007 6:07:00 PM

0

Hello jwilson128,

If you already have a CLR TVF that generates the desired data, you might
want to try using the new insert into... select ... from openrowset(BULK...)
against that TVF and see if that helps some.

Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.co...

jwilson128

3/29/2007 6:25:00 PM

0

Hi Kent -

Thanks for the suggestion - I've looked through the documentation on
openrowset(BULK...) and I'm not understanding how I would use this
with my TVF. Would the importing data not have to be in some form of
text file before using this BULK option?

-Jeff

Andrew

12/9/2011 5:32:00 PM

0

On Dec 9, 9:26 am, James Pablos <james.pab...@gmail.com> wrote:
> On Dec 9, 12:12 pm, highgreenchilly <ddimsd...@me.com> wrote:
>
> > I enjoy this one as well. Are you sure you don't mean "So Many Roads"?
> > "BD" is a box of the most recent studio LP's...
>
> Yes, my bad. It's "So Many Roads" that's such an awkward listening
> experience.

For my money, the SMR box was well worth it... Discs 1 and 4 are a bit
of a mishmash, but the rest of it holds together pretty well... But
then again, I don't regularly listen to CDs straight through
anymore...

9fingers

12/14/2011 6:03:00 PM

0

On Dec 9, 12:32 pm, Andrew <amuraw...@gmail.com> wrote:
> On Dec 9, 9:26 am, James Pablos <james.pab...@gmail.com> wrote:
>
> > On Dec 9, 12:12 pm, highgreenchilly <ddimsd...@me.com> wrote:
>
> > > I enjoy this one as well. Are you sure you don't mean "So Many Roads"?
> > > "BD" is a box of the most recent studio LP's...
>
> > Yes, my bad. It's "So Many Roads" that's such an awkward listening
> > experience.
>
> For my money, the SMR box was well worth it... Discs 1 and 4 are a bit
> of a mishmash, but the rest of it holds together pretty well... But
> then again, I don't regularly listen to CDs straight through
> anymore...

I think So Many Roads is a bit of a disappointment. It definitely has
a lot of great music, but there's a lot of mediocre stuff too. I wish
the studio version of Mason's Children was on the re-issue of
Workingman's instead of being in this set.

I do like Phil Zone a lot, although I'm not a fan of those endless
Midnight Hours. I find them pretty boring. Off the top of my head, I
think of Dancin', Box, Visions, and H2H as being the highlights.