Statement Parameter Slow in Stored Procedure case

[HP_Crook]

I have a report that use two parameters. Here when I run the MDX it takes 4.5 seconds to run. Then the same query when I put that in report with the parameter it is taking 21 seconds. What can be the reason for this. It is very incredibly slow. I am able to figure out the problem for the same. Any help really appreciated.

[Praetor]

Here the query in the code is taking 4.5 seconds. As far as I know that I use StrtoMember function in the Management studio it takes around more than 20 seconds in a loan statement. I think that problem is with the filter statement. You will need to call StrToMember many times that normally depends on the set.

[Katty]

Try to used the SQL profile to get the plans for the different styles. Here the showplan all and showplan xml events lies under the performance group. You will need to check this out in the plans between the different version. Second thing you can do is try to rearrange the procedure arguments. It may be causing due to some strange boundary effect. If you have a local variable and assign the parameter value then use the variable. But on the other hand if you use the parameter directly then it will cause slow.

[kelfro]

It sometime work better if you can drop and create the new store procedure. If possible try to create a duplicate store procedure of the same with a different name and let the code same. I had tried this and it works more frequently. And if the new one is faster and then drop the old one. Other thing this one also depends on the traffic on the server at that particular time. Also if you can try execution plan in the query analyzer for both query and store procedure.