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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.