TSQL SELECT and TABLOCK

Question

Post reply

TSQL SELECT and TABLOCK

davshu

Old Hand

Points: 312
More actions
January 6, 2010 at 11:04 am

#156555

I have a TSQL SELECT similar in form to that shown below.
The main SELECT has 2 sub-SELECTS.
The data that is read by each sub-SELECT for each table must be identical.
Nothing can be allowed to alter the data in any of the tables during the execution of the main SELECT.
Will the WITH (TABLOCK) hint on each table prevent anything else from altering any of the tables during the execution of the entire main SELECT statement?
NOTE: This will be used in the definition of a VIEW, so I am limited in what I can do.
SELECT Table1.RD,
Table1.BMP,
Table2.EMP,
[...]
FROM (SELECT [...]
FROM Table1 WITH (TABLOCK)
INNER JOIN TableR WITH (TABLOCK)
ON [...]
LEFT OUTER JOIN TableR AS TableR_From WITH (TABLOCK)
ON [...]
LEFT OUTER JOIN Table1 AS Table1_2 WITH (TABLOCK)
ON [...]
LEFT OUTER JOIN Table1 AS Table1_1 WITH (TABLOCK)
ON [...]
WHERE [...]) AS Table1
INNER JOIN (SELECT [...]
FROM Table1 WITH (TABLOCK)
INNER JOIN TableR WITH (TABLOCK)
ON [...]
LEFT OUTER JOIN TableR AS TableR_To WITH (TABLOCK)
ON [...]
LEFT OUTER JOIN Table1 AS Table1_2 WITH (TABLOCK)
ON [...]
LEFT OUTER JOIN Table1 AS Table1_1 WITH (TABLOCK)
ON [...]
WHERE [...]) AS Table2
ON Table1.RD = Table2.RD
AND [...]
ORDER BY [...]

Viewing 7 posts - 1 through 7 (of 7 total)

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

SQLRNNR SSC Guru Points: 281344 More actions · Answer 1

Some questions and a suggestion

Suggestion: Remove the subqueries and turn them into their own views.

Question: How long are your queries taking?

Question: Why do you want to prevent data from being updated during the query?

Question: Are you prepared for the side-effects of locking the entire table for the duration of your queries (even if they should take 10 minutes or longer)?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

davshu Old Hand Points: 312 More actions · Answer 2

Question: How long are your queries taking?

Answer: ~13 ms.

Question: Why do you want to prevent data from being updated during the query?

Answer: Without getting into the details, the query would return incorrect results.

Question: Are you prepared for the side-effects of locking the entire table for the duration of your queries (even if they should take 10 minutes or longer)?

Answer: Yes.

My question is: Will this lock the tables as I think it will?

SQLRNNR SSC Guru Points: 281344 More actions · Answer 3

striking this comment - tired eyes turned ms into Mins

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 4

I have to admit, Jason... you've confused even me on this one 😛 The query runs in about 13 ms according to the op... how will query tuning prevent any underlying data from changing?

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

SQLRNNR SSC Guru Points: 281344 More actions · Answer 5

Jeff Moden (1/6/2010)
I have to admit, Jason... you've confused even me on this one 😛 The query runs in about 13 ms according to the op... how will query tuning prevent any underlying data from changing?

ms was converted to Mins when I read it. My bad - carry on and ignore that last statement.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 6

Heh... and I missed your previous comment on that very thing. Guess it's time to get some shut-eye.

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