SELECT x.Q, x.W, x.E, x.R, y.T
FROM TableX x, TableY y
WHERE x.Id = y.Id
INSERT #TempB (B_2, B_3, B_4, B_5)
SELECT W, E, R, T, Y
FROM TableX x, TableY y, TableZ z
WHERE x.Id = y.Id AND y.Id = z.Id
SELECT t1.B_2, t1.B_3, MAX(t1.B_4)
FROM #TempB t1, #TempB t2
WHERE t1.B_1 = (t2.B_1 - 1)
AND t1.B_2 = t2.B_2
AND t1.B_3 = t2.B_3
AND t1.B_5 <> t2.B_5
GROUP BY t1.B_2, t1.B_3
SELECT * FROM #TempC
DROP TABLE #TempA
DROP TABLE #TempB
DROP TABLE #TempC
The problem is on the query that inserts information on the table #TempC.
If executed the procedure gets stuck on the "SELECT ... FROM #TempB t1, #TempB t2 ...".
Executing all queries one by one (without using the procedure) this problem doesn't occur
and all is processed normally.
One more piece of information. Normally the #TempB table contains around 120000 rows.
Since #TempB is a somewhat large table joined
to itself, an index on it shoud speed the join.
For instance, on cols B_1, B_2 and B_3.
There's the chance that ASE creates a dinamic
index when the statements are run outside a
stored proc; this could be the reason why
it run quite quickly despite the lack of indexes.