Same statement executes 10 times faster as raw sql in Query Analyzer then in a Stored Proc

Hi,
I apologize for the long post but I am trying to give as much information as I can about the steps I've taken to troubleshoot this.
We have a stored procedure that builds a sql statement and executes it using the Execute command. When I execute the stored procedure through query analyzer it takes close to 5 seconds to execute. When I print out the exact same statement and execute it directly in query analyzer as "raw sql", it takes 0.5 seconds - meaning it takes 10 times longer for the code to execute in the stored proc. I altered the stored proc to execute the printed sql instead of building but it still takes the full 5 seconds and there were no changes in the execution plan. This makes me confident that the issue is not caused by the dynamic sql. I've used with recompile to make sure that the stored procedure caches the most recent execution plan. When I compare the execution plans, the stored proc uses a nested loop whereas the raw sql statement uses a hash join. Seeing that, I added the hash hint to the stored proc and doing so brought down the execution time down from 5 secs to 2 secs but still the raw sql statement uses a clustered index whereas the stored proc uses a non-clustered index and that makes the statement 4 times slower. This proves how efficient clustered indexes are over non-clustered ones, but it doesn't help me since, as far as I know, I can't force SQL Server to use the clustered index.
Does anyone know why sql server is coming up with such an inefficient execution plan for the stored proc compared to the execution plan when executing the raw sql statement? The only thing I can think of is that some stats are not updated and that somehow throws off the stored proc. But then again, shouldn't it affect the raw sql statement?
Thank you,
Michael Tzoanos

>>>> I've used with recompile to make sure that the stored procedure caches the most recent execution plan.
If you used with recompile, the proc is recompiled each time. So the query plan is recalculated each time. which is an additional overhead.
I think what you are seeing is a case of "Parameter sniffing". What it means is when you use dynamic SQL, SQL Server tries to be smart and tries to guess your parameters even before it reads them. Since its dynamic SQL there can be different query plans for diff combinations of the resulting T-SQL. So, out of all the available query plans, SQL Server chooses the most optimum one that works for MOST scenarios, which means it may not be the best query plan. Its probably a good one but not the best one.
What can be done is to use index hint (the clustered index if you think its best) and leave it like that for a while. Then after a few days to weeks, remove the index hint and see if the proc works well, if it doesnt, put the index hint back in.

Thank you very much for the reply.
You're right. Using "with recompile" adds overhead and I used it to generate the proc and when executing it to make sure that it would refresh the query plan in case it was "inefficient". I removed the statement after testing so the overhead is no longer there. It's not parameter sniffing.
Your suggestion of using an index hint made me realize that sql server should have used the clustered index since the indexed view that we're going against doesn't have any other indexes other than the clustered index. The fact that it would be using another index didn't make any sense. I decided to take a closer look at the execution plans and I found out that the stored proc is ignoring the indexed view and its clustered index and going against the underlying tables and an index on one of those tables!!! Needless to say, I'm totally and absolutely stunned. Is there a way to share an execution plan on a forum to show this to you?
When I execute the same sql in query analyzer, sql server is happy to use the indexed view and its clustered index which explains why it's 10 times faster. How can SQL Server ever decide to ignore an indexed view and use the underlying tables?
Michael

Try using the NOEXPAND hint to see if the optimizer chooses the clustered index on the view..

Dinakar,
Again, thank you very much for your help. I tried the NOEXPAND but it failed and I was able to figureout why. Before the Create Proc statement we had SET QUOTED_IDENTIFIER OFF. When I looked up NO EXPAND in Books Online, I found that the optimizer will use an indexed view when the following conditions are met.
The following session options must be set to ON:
QUOTED_IDENTIFIERS, ANSI_NULL, ANSI_PADDING, ANSI_WARNINGS and others
So technically, it was our fault when we wrote the stored procedure which explains why the stored proc used the tables instead of the indexed view. I still think SQL Server should have thrown a warning to let us know that it can't use the indexed due to session options.
Thank you again for your help,
Michael Tzoanos

[quote user="mtzoanos"] When I looked up NO EXPAND in Books Online ... [/quote]
Thats the open secret. Keep books on line handy...all the time.. []