Does anyone know why this works in approx 1 sec:
select Q.USR_LOAD_NBR from QUAD0066..QTS_LOAD_INFO Q where Q.KY_TO_STP = 2142870
but this takes more than 10 secs (and up to 5 minutes):
declare @groupId int
set @groupId = 2142870
select Q.USR_LOAD_NBR from QUAD0066..QTS_LOAD_INFO Q where Q.KY_TO_STP = @groupId
I'm having the exact same problem with ibatis with this query, except ibatis is timing out in like 15 seconds and causing my code to fail. ( less than 1 sec hard-coded, time-out with int parameter)
When you have the value hardcoded the query optimizer can select the best plan possible. That does not happen when the value is a variable since the query optimizer doesn't know what the value might be.
Now what you can do is create a index for the KY_TO_STP column, prolly you be a nonclustered:
create nonclustered index on QTS_LOAD_INFO(KY_TO_STP)
You can add unique if you want every value in KY_TO_STP to be unique and you can replace nonclustered by clustered if KY_TO_STP is a primary key or if you can uniquelly identify each row with it (but don't forget that you can only have one clustered index per table).