Hi Guys,

I have a scenario where in a stored procedure I am having to get data from more than 20 tables and insert them all into one table by using union all . To get the attributes I am having to join all those 20 tables with same look up tables. Something like below. Here table1 and table 4 are part of the 20 tables I am talking about and table 2 , 3 & 5 are look up tables. Such a query is producing Intra Query parallelism deadlocks.

We have max dop set to 4 at server level.

Can someone please guide?

EXAMPLE:

Insert Into dbo.tbl_Test

Select t1.Col1, t2.col2, t3.col3

From

table1 t1
join
table2 t2
join 
table3 t3

Where table1.latestload = 1 or table1.latestupdate = 1

Union All

Select t4.Col1, t2.col2, t3.col3

From

table4 t4
join
table2 t2
join 
table3 t3
join
table5 t5

Where t4.latestload = 1 or t4.latestupdate = 1 or t5.latestload = 1 or t5.latestupdate = 1


Union All
.
.
.
.  23 SUCH Union All
.
.

Turn it into 20 separate insert statements within a transaction, should be equivalent.

Make sure your isolation level is read committed, assuming that's OK, but don't accidentally run in serialized mode.

Josh

Thanks Josh and Uri for your instant response.

I will try splitting query into multiple inserts instead of 1 big union all and 1 insert.

I will also try workaround 2 mentioned in the blog Uri suggested.

And will post the results.