Proper and careful use of a global temp table (##temp) First time I am using

Question

Proper and careful use of a global temp table (##temp) First time I am using

Jawid

SSC Enthusiast

Points: 142
More actions
April 1, 2016 at 6:18 pm

#317658

I am dealing with huge number of transaction simultaneously accessing to get ONE ID from its primary key field, where it has not been used.
Another word, a NULL value exists in another filed (NULLField).
At the same time, I am BULK loading (via SSIS) millions of new ID records into the same field.
Every time, I BULK load, timeout happens as result of tooooo many UPDATE locks. I have to kill the BULK load to eliminate the timeout.
I am trying to modify the stored procedure that calls for a new ID as such:
I am creating a ##temp table then capture top1 id (not exits in the ##temp yet) and inert it in to the ##temp table.
I want to make sure when 1000 connections come in, to get a new ID, my procedure works without timeout.
******************************************
This is how currently done:
---------------------------
UPDATE top (1) dbo.table with (rowlock)
SET NULLField = @inputVariable
WHERE ....
AND NULLField IS NULL
SELECT @ID = ID FROM dbo.table WHERE ID = @ID
******************************************
******************************************
This is how I want to do it:
---------------------------
IF OBJECT_ID('tempdb.dbo.##temp') IS NULL Create table ##temp (ID varchar(30) primary key)
SELECT TOP 1 @ID = a.ID
FROM dbo.Table a with (rowlock)
LEFT JOIN ##temp b ON a.ID = b.ID
WHERE ....
AND b.ID IS NULL
AND NULLField IS NULL
IF @ID IS NOT NULL
INSERT INTO ##temp (ID) values(@ID)
UPDATE dbo.Table SET NULLField = @inputVariable
WHERE ID = @ID
DELETE FROM ##temp WHERE ID = @ID
******************************************
I am not sure if this will cause more SELECT locks than
updated TOP 1 locks from before
******************************************
I am dealing with 500 to 5000 connection simultaneously most of the time.
------------------------------------------------------------------------
Please do not give any design idea.
All I want to know is if you have had this experience and if this would cause more locks and timeout

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

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

Jeff Moden SSC Guru Points: 1003891 More actions · Answer 1

You posted this in the 7/2000 forum and I'm assuming that 2000 is the version you have?

If so, the partitioning the table to take advantage of SWITCH-in is not possible. However, there's still the possibility of using a Partitioned View, which can be nearly as effective. Is there something in the "millions of rows" that you're loading that would constitute and "ever increasing value", such as a data column or some such?

If so, you would load a separate table as a new "partition" and simply regenerate the view to include the new table. Total "interference" time would be measure in milliseconds in a manner similar to SWITCH-in with Partitioned Tables.

There are some caveats that go along with Partitioned Views. You might want to seriously study the caveats before making such a move but, if it works out, you won't ever have to stop a BULK Load ever again.

--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)

Jawid SSC Enthusiast Points: 142 More actions · Answer 2

Thanks, Jeff;

I have been looking into portioned view. I have done some homework on it. My dilemma is that the table need to be partitioned by GUID. I have been playing with partitioning by GUID. I have not been successful, yet.

My goal is to have the table partitioned by GUID and, as you said, create a new table for each upload, and add it in the view.

If you can help me with a sample partitioned view by GUID, I would be very happy.

Thanks;

Jawid