I have a stored procedure with couple of CTEs and 1 of the CTEs has a section something like below:
Here, the Union part is written in a way so that the indexes on those tables are utilized in an efficient way.
Here is the problem that I am facing. This section of the Stored Procedure has been working perfectly well for the given parameters (5, 10000... etc). However, after some data munging operations on the super set i.e. the users, the UNION just fails to return, even though each of the selects work perfectly when run individually. To exasperate the situation, the whole query starts working if I change UNION to UNION ALL!!
Any suggestions as to what could be going wrong here? The logic works perfectly fine for other set of parameters. It is just this set (for which data munging was performed) that is hanging. This kind of logic is there in quite a few different Stored Procedures and I don't wanna change UNION to UNION all everywhere. What things can I/should check for?
Nitin
Code:
SELECT
DISTINCT st1.user_id as user_id
from
someTable st1 WITH(NOLOCK)
where
st_nid = 5 and st1.some_other_frn_id2 is null
union
SELECT user_id from
( SELECT sot1.user_id user_id,
sot1.id
FROM some_other_table1 sot1 WITH (NOLOCK)
WHERE user_id BETWEEN 100000000000 AND 200000000000
) allUsers
INNER JOIN
( SELECT some_other_id2
FROM some_other_table2 WITH (INDEX(someIndex), NOLOCK)
INNER JOIN someTable st2 on some_other_frn_id2 = some_other_id2
) requiredUsers
ON sot1.id = some_other_id2
Here, the Union part is written in a way so that the indexes on those tables are utilized in an efficient way.
Here is the problem that I am facing. This section of the Stored Procedure has been working perfectly well for the given parameters (5, 10000... etc). However, after some data munging operations on the super set i.e. the users, the UNION just fails to return, even though each of the selects work perfectly when run individually. To exasperate the situation, the whole query starts working if I change UNION to UNION ALL!!
Any suggestions as to what could be going wrong here? The logic works perfectly fine for other set of parameters. It is just this set (for which data munging was performed) that is hanging. This kind of logic is there in quite a few different Stored Procedures and I don't wanna change UNION to UNION all everywhere. What things can I/should check for?
Nitin