February 23, 2016 at 6:43 am
I have a system that requires me to move an approximate 4 million records from one table into another (4 integer fields and an email address)
the select statement takes 17 seconds to execute, but when coupled with the insert it takes the query plan in an entirely new direction (due to foreign key constraints etc) the query runs for over an hour.
so, I don't really care too much how fast the records get into the target table (there is a process that reads from the target table and performs a stored procedure for each new row), but I need to lock the source table for as little as possible.
we've considered batching the inserts, but I feel that we could just put the data in a service broker queue and spin up queue readers to deal with reading from queue and inserting into target table.
what I really want to avoid is building a dataset and looping through it to individually add to the queue. has anyone any experience of sending an entire query result set to a service broker queue.
there seem to be lots of examples of how to read from the queue, but I want to push to the queue as fast and cheap as possible
MVDBA
February 26, 2016 at 5:30 am
I realize that this is technically not an answer to your question, but that's because I think Service Broker is not the right tool for your problem.
If inserting data into the destination table takes a long time due to indexes and constraint checking, why not drop/disable all the constraints and indexes, do the insert, then rebuild the indexes and reenable the constraints? (For the latter, please don't forget to add WITH CHECK to the statement, or you will end up with untrusted constraints)
February 26, 2016 at 9:25 am
this is a table that is used by many processes, dropping the indexes and constraints would affect customer usage.
MVDBA
February 26, 2016 at 12:18 pm
Oh, missed that. Since your only concern was the locking on the source table, not on the destination table.
Alternative #1 - use an intermediate table. Do the select on the source and insert into an intermediate table with no constraints and only a clustered index. Then as step 2 insert from that table into the destination table.
Alternative #2 - just like alternative #1, but add batching to the second step so that the destination table is also not locked for prolonged periods.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply