I'm querying tables from the datawarehouse that don't have a primary key and no index (at least I guess so and I don't know how to prove). I am not the admin of the DWH and I most likely won't be heard if I would ask that the cube's tables were build in a different way. So I have to live with the set-up that is there.
SQL Management Studio
- My parameterized query takes 0 seconds to execute for one parameter returning 2000 records,
but 3 minutes when executed with a different paramter that returns 200 record.
- When removing the option (recompile), both querys are executed within a second
When calling these querys from inside a web application:
- the query with the 2000 records is executed immediately
- the query with the parameter that returns 200 records takes again some minutes and causes a timeout.
- OPTION (recompile) does not have an impact on this behavior
Any explication of what is going on or suggestion of how to solve it?