I need to speed this process up.
Insert into dbo.temp_table
(record_number, etc...)
select record_number, etc...
from
dbo.incoming_temp
set Indentity _insert temp_table on
It's about 10 million records and usally runs 2-3 hours. Any ideas?????Break it up int to 10 separate transaction?|||You mean like 2 million record at a time.
Select yadA, YADA YADA,
YADA, YADA, YADO
FROM YADA
WHERE RECORD_id < 2000000
SOMETHING LIKE THAT|||Something like?
DECLARE @.x int, @.y int
SELECT @.x=0, @.y = SELECT COUNT(*) FROM myTable99
WHILE @.X < @.y
BEGIN
BEGIN TRAN
INSERT...
COMMIT TRAN
SELECT @.x = @.x + 2000000
END|||thanks, this gave me some insight on how to structure other thing with my group,.|||Let us know if it helps...
Also Don't forget the last set of records...
You may fall out of the loop before everything is done...
Do
WHERE Record_ID > @.x|||Look for nonlogged operations in BOL. Try "select into" to avoid logging.|||Breaking the insert will not speed up the completion of the entire batch. Either SELECT * INTO... or bulk copy utility (BCP/BULK INSERT)|||The fastest possible speed you can get is:
1. Use BCP.
2. Set your recovery mode to Bulk-logged or simple while you do the operation.
Also, how long is your SELECT taking? If the length is in the select, these ideas aren't going to do you a lot of good.|||Bulk-logged will defeat the purpose of using BCP/BULK INSERT because it will log those operations, as opposed to Simple which will not.
Even if SELECT is slow, non-logged operations would still buy you some advantage over INSERT, which is logged (unless you set recovery mode to Bulk-Logged, in which case it would be a wash ;))|||The logging on bcp, bulk insert, INSERT INTO, CREATE INDEX, text and image manipulations are kept to a minimum when using bulk-logged and it performs faster than full recovery mode, while still providing a level of protection to your other transactions. It is actually quite a bit faster on really large bulk operations.
I agree with the second part, except for the part in ().|||Breaking the insert will not speed up the completion of the entire batch. Either SELECT * INTO... or bulk copy utility (BCP/BULK INSERT)
I mean bcp is the way to go...but if you break up the work into chunks you will notice a difference...
What'dya think the log will look like?
What'dya think it'd be like if he had to roll the whole thing back...|||Bulk-logged "permits high-performance bulk copy operations", but does alow for other DDL/DML to be logged. If data recovery is important while performing the process at hand, then Bulk-logged is the one to use. Else, - Simple will be just fine.
Brett, I don't think you can roll back a bulk copy operation. Do you know the way? On either BCP or BULK INSERT you can specify the number of records to be viewed as transaction. This way there is not need to break anything up.|||Someone tell me why BCP is faster than a non-logged insert. Is it just because there is minimal data checking and verification? It doesn't make sense to me that spitting everything out to disk and then reading it in again would be particularly fast.|||derrickleggett is right, and using bulk copy utility is not the same as selecting or inserting. it is a much more efficient operation for large volumes of data.|||Still long on repetition, short on substance...|||BCP/BULK INSERT may not necessarily be faster than non-logged insert. The discussion started with how to introduce transactional control while handling large amounts of data. In addition, you do not need to drop the table every time you need to populate it AGAIN, if you choose to use BCP or BULK INSERT, as opposed to SELECT...INTO...FROM.|||Good enough for me.|||Oye...
First: Non-Logged is a misnomer...everything, and I mean everything, that is a database transaction, is logged...it's a matter of at what level...
I don't think bcp out is logged...so there's a savings...
bcp in must be logged, at the page level, just like BULK INSERT...
Or SELECT * INTO
If they fail, for ANY reason, it WILL rolback
And I question how the original table gets populated....|||Hmmm, never tried to force a BCP to fail in the middle of the load, but I'll guess that if you unplug your cat-5 while it's posting records, - at a minimum you'll get a partial load, at a maximum corrupted table. I don't think it'll roll back. But then again, as I said - never tried it, so go easy if you prove me wrong ;)|||I need to speed this process up.
Insert into dbo.temp_table
(record_number, etc...)
select record_number, etc...
from
dbo.incoming_temp
set Indentity _insert temp_table on
It's about 10 million records and usally runs 2-3 hours. Any ideas?????
Locate the filegroups for dbo.temp_table and for dbo.incoming_temp on seperate dedicated physical disks.|||I haven't even used BCP for a long time, but I seem to recall it loading data in distinct batches, hence the batch size parameter.
Yeah, I'd be interested in seeing if it rolled back if the process got interupted. This sounds like a call to Super-Brett to me.|||This is a very good response. I think the bcp utility might be the best course of action. Again, this table hold aproximately 10,000,000 records which represent 6 months historical data. That has been pulled out of the primary table that hold 290,000,000 records that have never been purged. The strucktures are the same. I just need to truncate the primary table and pull in my 10,000 records. When I attempted a straight
Insert into table
(1,2,3,4etc.......
selecte 1,2,3,4etc.....
from primary table.
it run a-round three hours and the tranaction log grows as large as the data file. Besides it bomb after three hour yesterday. Do you agree with my plan??|||I haven't even used BCP for a long time, but I seem to recall it loading data in distinct batches, hence the batch size parameter.
BCP and BULK INSERT load data in distinct batches, but it's independent of batch size parameter. A switch "-b" specifies how many rows should be loaded at a time before a COMMIT gets issued. It has nothing to do what you see on the output of BCP or BULK INSERT.|||This is what I came up with so far.
bulk insert websense.dbo.[temp_table]
FROM websense.dbo.[incoming_temp]
-b = 100,000
I want to insert commit 100,00 records at a time.|||First you BCP...OUT from the source table into a host file (BCP utility terminology, means a text file). While forming the command, make sure to specify AND REMEMBER whether you're going to use character "-c" or native "-n" file format.
Then, in QA, type your statement:
bulk insert websense.dbo.[temp_table]
FROM '<path_to_your_source_file>'
WITH (ROWS_PER_BATCH = 100000, DATAFILETYPE = 'char')
If your BCP was using "-n" then instead of 'char' you'll need 'native' for FORMATFILE parameter in BULK INSERT.|||bulk insert websense.dbo.[temp_table]
FROM '<websens.dbo.[incoming_temp]>
WITH (ROWS_PER_BATCH = 100000)|||If your BCP was using "-n" then instead of 'char' you'll need 'native' for FORMATFILE parameter in BULK INSERT.
It should be DATAFILETYPE instead of FORMATFILE. That's what copy-paste does to me :o|||I haven't even used BCP for a long time, but I seem to recall it loading data in distinct batches, hence the batch size parameter.
Yeah, I'd be interested in seeing if it rolled back if the process got interupted. This sounds like a call to Super-Brett to me.
WHAT?
Why not?
DTS?
No thanks...
The pages are logged on the BCP in...without the batch it will roll the whole thing...page by page, back...not at the row level...
Instead of guessing, can you tell us what the process you are really trying to accomplish is?
It's not often that there's a need to move 10 million around in 1 shot...
What's up?|||DTS sucks. I'd still use BCP over it whenever possible, but these days a lot of requirements specify DTS, and frankly for the past few years I've mostly worked on virgin data entry systems that haven't needed much bulk-loaded data.|||I've mostly worked on virgin data entry systems
Dude, leave those high school girls alone....|||Dude, leave those high school girls alone....High school? You really have been "out of the loop" for a while, haven't you ?!?!
Try grade school. There might be some left there!
-PatP|||It this some new code I'm not aware of?LOL. I get it. It's a way to keeps things relavent. Like a good politian huh.|||It this some new code I'm not aware of?LOL. I get it. It's a way to keeps things relavent. Like a good politian huh.
It's been a rough weekend, hey? :D|||High school? You really have been "out of the loop" for a while, haven't you ?!?!
Try grade school. There might be some left there!
-PatP
Well it sounds like that process has sped up...
Damn...now I gotta buy a shotgun...
Synergyauto: