Huge INSERT batch size? 1

[gbaughma]

So, I'm doing this huge INSERT (about 1.2M Records), and my log file keeps filling up.

Is there a way to do it in chunks? I've been reading a little about batch sizes.....

Is there a command that I can put in the stored procedure that says "Do 5000 records at a time" or something similar, before my INSERT INTO ... SELECT statement?



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[gbaughma]

Well, considering I really need to run this query at, say, 1:00 a.m. to update the records for the next business day....

I re-wrote the batch so that it does it 10 projects at a time... we'll see if that does any better.



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[gmmastros]

How many rows do you think there are with 10 projects?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

I see that you are truncating your WebSample table before loading the data.... Do you have a lot of indexes on this table? If so, you will get better performance if you drop the indexes, truncate the table, fill it up again, and then re-create the indexes.

Just a thought.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gbaughma]

I don't have a lot of indexes... I think mainly SampleID and perhaps ProjectNum....

Well, I changed it to do 100 records at a time (with a little If statement so it wouldn't run past the end).... it's at 900,000 records after 26 minutes. That is acceptable (assuming that it doesn't crash on me now... lol)



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[gmmastros]

1.2 million records really isn't a lot. I would encourage you to keep playing. Maybe try 1,000 or 10,000 rows at a time.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gbaughma]

Oh, if I were doing the query from SQL to SQL, I wouldn't be happy with the time it takes.

However, since it's linking into a (copy of) an Access database, with a hella number of joins, I'm not going to complain.

The LIMS system is being re-written; but unfortunately it's being re-written in.... ACCESS. Not my choice, nor within my control.



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[gmmastros]

Right... but you went from 50,000 records in an hour (with 1 project at a time) to 900,000 records in 26 minutes with 100 projects at a time. That's a huge gain. I can't help thinking that there MAY be more gains to be had.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom