0

I have two tables say for ex: table1 and table2 as below Table1(id, desc ) Table2(id, col1, col2.. col10.....)

col1 to col10 in table 2 could be linked with id field in table1.

I write a query which has 10 instances of table1 (each one to link col1 to col10 of table2)

select t2.id, t1_1.desc, t1_2.desc,.....t1_10.desc from table2 t2
 left outer join table1 t1_1 on t1_1.id = t2.col1
 left outer join table1 t1_2 on t1_2.id = t2.col2
 left outer join table1 t1_3 on t1_3.id = t2.col3
.
.
.
  left outer join table1 t1_10 on t1_10.id = t2.col10
where t2.id ='111'

This query is inside the Sp and when i try to execute the Sp in SSMS, it works without any problems.

However When my web application runs, the query works for few where clause value and hangs for few.

I have checked the cost of the query, and created one nonclusteredindex with this 10 columns in table2. The cost found to be reduced to 0 on joins. However, I am still seeing the query hangs

The table 1 has 500 rows and table 2 has 700 rows in it. Can any one help.

Muthukumar Palaniappan
  • 1,622
  • 5
  • 25
  • 49
  • Please include a query execution plan for when it runs fast in SSMS. could it be that parameter values are different when running in SSMS? How many records can each left outer join match? If it could match more than one it can be an extremely expensive query if there are not the correct constraints and statistics for the query optimizer to "understand" the query. – Ross Bradbury Aug 10 '14 at 06:13

1 Answers1

0

First of all, why are you rejoining to the table 10 times rather than one join with 10 predicates?

 left outer join table1 t1_1 on t1_1.id = t2.col1
 left outer join table1 t1_2 on t1_2.id = t2.col2
 left outer join table1 t1_3 on t1_3.id = t2.col3
.
.
.
  left outer join table1 t1_10 on t1_10.id = t2.col10

vs.

 left outer join table1 t1 on t1.col1 = t2.col1 
 and t1.col2 = t2.col2
 and t1.col3 = t2.col3

just wanted to bring that up because its very unusual to rejoin to the same table like that 10 times.

As far as your query plan goes, sql server sniffs the first parameter used in the query and caches that query plan for use in future queries. This query plan can be a good plan for certain where clause values and a bad plan for other where clause values which is why sometimes it is performing well and other times it is not. If you have skews in your table columns (some where clause values have a high number of recurring values) then you could consider using OPTION(RECOMPILE) in your query to force it to develop a new execution plan each time it is called. This has pros and cons, see this answer for a discussion OPTION (RECOMPILE) is Always Faster; Why?

Hunter Nelson
  • 1,707
  • 3
  • 20
  • 37