I'm running an application (Pentaho user console with the Saiku Ad-hoc plugin) which creates a query and runs it using sp_cursorprepare & sp_cursorexecute standard stored procedures.
My model is mostly made up of tables joined utilising the traditional foreign to primary key method. However in the middle I have a linking table that contains foreign keys of the tables connected to it (with some other data) but its primary key is never referenced. I have a clustered index on this table which references each of these foreign keys in order of the quantity of records. When run the procedure call will the non-clustered primary key index and takes 6 - 7 minutes to run. If I run the query on it's own it again uses the non-clustered primary key index although it runs in 7 seconds!
Is there anything I could do to combat this behaviour?
