Faster way to INSERT INTO large table

Question

Faster way to INSERT INTO large table

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

Amy.G (11/21/2011)
I would sincerely look into 2-staging this process however. Holding insert/exclusive locks on the Alvis_22 schema table until this completes is just bad form. Do the long work shared first, then do a final 'quick strike' on the table to get your records in.
You have now totally lost me.

Heh, sorry. Let me break it down.

Doing the inserts this way, you'll hold locks on all the records in an insert statement, which looks for exclusive locks on anything it's changing/inserting. While this should all happen at the tail of the process, it may or may not internal to the engine. Once you get to 5000 records, SQL Server starts looking for a table lock on the table. If it can, nothing else can get in unless it's willing to do dirty reads. That's the concurrency concern.

Now, the largest portion of the time this query will run is the comparison to determine if the record already exists. So, perform the process in two stages. It should also help speed things up by doing a direct seek.

In the Transfer table, add a field 'existing' and default it to 0. Now, do an update on Transfer to update this field to 1 by joining to the Alvis_22 table on the necessary fields.

Now, you just insert anything in Transfer with an existing of 0 into Alvis_22 as a second step in the same script.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

Evil Kraig F (11/21/2011)
Amy.G (11/21/2011)
I would sincerely look into 2-staging this process however. Holding insert/exclusive locks on the Alvis_22 schema table until this completes is just bad form. Do the long work shared first, then do a final 'quick strike' on the table to get your records in.
You have now totally lost me.
Heh, sorry. Let me break it down.
Doing the inserts this way, you'll hold locks on all the records in an insert statement, which looks for exclusive locks on anything it's changing/inserting. While this should all happen at the tail of the process, it may or may not internal to the engine. Once you get to 5000 records, SQL Server starts looking for a table lock on the table. If it can, nothing else can get in unless it's willing to do dirty reads. That's the concurrency concern.
Now, the largest portion of the time this query will run is the comparison to determine if the record already exists. So, perform the process in two stages. It should also help speed things up by doing a direct seek.
In the Transfer table, add a field 'existing' and default it to 0. Now, do an update on Transfer to update this field to 1 by joining to the Alvis_22 table on the necessary fields.
Now, you just insert anything in Transfer with an existing of 0 into Alvis_22 as a second step in the same script.

"Divide'n'Conquer". I like it and that's the way I would do it. That, notwithstanding, I wonder how MERGE would fair on such a thing?

It would also be interesting to see how EXCEPT faired in such a process.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Amy.G SSCrazy Points: 2305 More actions · Answer 3

In the Transfer table, add a field 'existing' and default it to 0. Now, do an update on Transfer to update this field to 1 by joining to the Alvis_22 table on the necessary fields.
Now, you just insert anything in Transfer with an existing of 0 into Alvis_22 as a second step in the same script.

See, this is why I love these forums. I never would have come up with something like this on my own. Again, thank you.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 4

Jeff Moden (11/21/2011)
"Divide'n'Conquer". I like it and that's the way I would do it. That, notwithstanding, I wonder how MERGE would fair on such a thing?
It would also be interesting to see how EXCEPT faired in such a process.

Except would probably be poor here because not all fields are involved in the process so you're sub-querying for the allowance list and bringing it back to the Transfer table. I'm not sure about MERGE but since the idea is to completely avoid updating I don't know if there's a way to utterly avoid that when matched.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA