Loading a local temp table taking more than 24 hours

Question

Loading a local temp table taking more than 24 hours

dndaughtery

SSChampion

Points: 11290
More actions
September 29, 2016 at 4:52 am

#400806

Hey guys, I am trying to run a stored procedure that loads a local temp table prefixed with a single # using s select into statement joining s table with 120 million rows, several joins, and an extensive where clause. The plan was to load the temp table with the rows that needed to be updated and then run and update (In the same sproc) for top 50,000 rows at a time. Its been more than 24 hours and the temp table load is still running. I'm running 3 of these type of stored procedures at the same time. Is there any better way to do this that will increase performance? I've stopped one and started running an Update top 50,000 without using the temp table. Something like this:
While 1=1
BEGIN
update top 50k where conditions in the were clause are correct (Join on tables and where clause)
If @@Rowcount = 0
Break
END
I'm concerned that this new way may actually take longer since I'm having to join the tables several times instead of once as I tried when loading a temp table once then updating in batches of 50k.
Any help is appreciated.
Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

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

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 1

Without all the details, what I'm about to say should be taken with a large grain of salt...

I'd be inclined to do any updates directly against the table in question, even if you're doing a JOIN to other tables as part of that update. Your current approach is to duplicate the data, and then some. I presume, to a degree, you're denormalizing data into this temp table? So you're creating this massive hit on the system to store the existing table+, but, you're doing it one of the single most contentious locations in SQL Server, tempdb. Everything uses tempdb from sort operations to hash joins to versioning for snapshot isolation to... And you're fighting with all of that to move a very large table into tempdb. Then, you're going to leave that data in tempdb to do a bunch of udpates, additional writes, additional contention for resources, then, I presume again, you're using this big denormalized table to then write back to the original table? What are you saving in this operation? JOINs. It sure sounds like we're spending tons of resources to save something that SQL Server, as a relational database engine, is pretty good at.

Yes, if you're updating hundreds of millions of rows, you're probably better off batching that process to reduce the amount of locking you're incurring on the table at any one time and to keep the log file size down. However, I'd do this work directly against the table in question and do the JOINs as needed. Assuming your system is relatively well put together, it should handle this fine. If it's not, that's where I'd focus my efforts, not on attempting to avoid JOINs.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ScottPletcher SSC Guru Points: 101120 More actions · Answer 2

Several things to consider here. I'll number them so that everyone can comment on them more easily:

1) Make sure IFI is on (just in case).

2) Pre-allocate additional space in tempdb, particularly log space, if tempdb needs more space to hold all this data.

3) Make sure the tables in the query that loads the data have the proper indexes for the joins and WHERE conditions used.

4A) Create the temp table structure first:

SELECT TOP (0) <column_list> INTO #temp_table

4B) Then add a clustered index, with a fillfactor = 100, that matches the order of the data updates to be done

CREATE /*UNIQUE*/ CLUSTERED INDEX #temp_table__CL ON #temp_table ( ... ) WITH ( FILLFACTOR = 100 );

Edit: Adding ROW compression, if available, is probably worth it: ( DATA_COMPRESSION = ROW, FILLFACTOR = 100 )

4C) Finally load the table. Naturally load the minimum amount of data into the temp table as possible:

INSERT INTO #temp_table

SELECT ...

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".