After Bulkcopy success a question

Started by stanl, November 16, 2017, 03:32:52 AM

Previous topic - Next topic

stanl

In the .net SQLBulkcopy class is an event called SQLRowsCopied.  It is fired by another event called SQLNotifyAfter.  So if NotifyAfter =1000 the rows copied event is fired every 1000 inserts. My recollection is that the WB CLR does not work with events, so this may be a moot point - but I would like to see if I could get a display of the rows copied after the operation is complete. Since I cannot test this directly as I can only test with compiled exe's I want to avoid a lot of back and forth. So I hope to get some suggestions on how I might use the event or not.

td

WIL CLR hosting does not support delegates so it does not support events.  And no that support is not likely to happen any time in the foreseeable future for technical reasons.
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

JTaylor

If there is a serial field could you do a "select Max(field)" at appropriate times and get what you want?  It would assume you are not doing any IGNORE or REPLACE actions.   

Jim

stanl

Quote from: JTaylor on November 16, 2017, 06:32:52 AM
If there is a serial field could you do a "select Max(field)" at appropriate times and get what you want?  It would assume you are not doing any IGNORE or REPLACE actions.   

Jim

Not sure that would work well with bulkcopy. I did discover that setting a moderate batch size [so there are no memory problems] has been successful when I manually go over results. I'm going to suck it up - meaning the script is incredibly fast [loaded 30,000 rows in around 3 seconds] and I have procedures to inspect on the back end. Besides, this is just a mock-up [my local SQLEXPRESS] design for the big boys to look at in the real corporate server(s).

JTaylor

Hope you find a good solution.  I have actually been working with bulk loads in MySQL.  Still trying to find a fast way to do this.  I find posts of people saying they load a million records in times like 2.6 seconds but my times for 6.6 million often takes many, many hours so not sure what is different.   Just found some more info on suggestions so getting ready to try that.


Jim

JTaylor

Guess I found the right combination...went from about 12 hours to less than 5 minutes :)

Jim

JTaylor

I found that it helps the speed A LOT if you mess up the SQL so that you are not actually loading anything.

Jim

td

The top-secret solution has finally been revealed!   ::)
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade