Execution Plan & Not using Right Indexes

I have a query that once put directly into the Query Analyzer takes 1 second while using my Index Views. This is extreamly fast.

If i put the same query inside a stored procedure and execute the stored procedure inside of Query Analyzer it takes over 30 seconds!!

any ideas [?]

Sorry for the re-post. I forgot to mention, the stored procedure uses a completely different execution plan? It is like it isn't reading the statistics or something. thanks.

Try executing the stored procedure with recopile option. Also check if the statistics are upto date.

Can you post the query here?

Gaurav

-- here is the query. Under a second with this in query analyzer, and it uses my index view

SET NOCOUNT ON

SELECT TOP 10 COUNT(dbo.tblTempClicks.EmailID) AS TotalClicks, dbo.tblDomains.DomainName
FROM dbo.tblTempClicks INNER JOIN
dbo.tblEmails ON dbo.tblTempClicks.EmailID = dbo.tblEmails.EmailID INNER JOIN
dbo.tblDomains ON dbo.tblEmails.DomainID = dbo.tblDomains.DomainID
GROUP BY dbo.tblDomains.DomainName
ORDER BY COUNT(dbo.tblTempClicks.EmailID) DESC

-- If i put it in the stored procedure it takes over 50 and it doesn't use the index view.

I have tried the RECOMPILE and i flushed the buffers.

thanks.

-----------------------
Richard Ryan King
"Why wait for the future when you can create it."

I know this is a out of track question byt is it possible to write the same query using the indexed view that u have created?

Gaurav

Gaurav Bindlish,<br /><br />Yes, here it is: <br />SELECT TOP 10 _hypmv_1_col_2 as TotalClicks, _hypmv_1_col_1 AS DomainName from _hypmv_1 ORDER BY _hypmv_1_col_2 DESC<br /><br />Now this returns the same results instantly, HOWEVER! <img src='/community/emoticons/emotion-1.gif' alt='' /> i put this in a stored procedure and it takes about 30+ seconds and the execution plan doesn't even use the views??<br /><br />i must be doing something stupid?<br /><br />thanks for the reply.<br /><br />-----------------------<br />Richard Ryan King<br />"Why wait for the future when you can create it."

Please post the view defination

Gaurav

Here is the view def:

SELECT dbo.tblDomains.DomainName AS _hypmv_1_col_1, COUNT_BIG (*) AS _hypmv_1_col_2
FROM dbo.tblEmails
INNER JOIN
dbo.tblDomains ON dbo.tblEmails.DomainID = dbo.tblDomains.DomainID
INNER JOIN
dbo.tblTempClicks ON dbo.tblEmails.EmailID = dbo.tblTempClicks.EmailID
GROUP BY dbo.tblDomains.DomainName



-----------------------
Richard Ryan King
"Why wait for the future when you can create it."

The only difference that I can see is this-

In view you are using
COUNT_BIG (*)
In query you are using
COUNT(dbo.tblTempClicks.EmailID)
Is there something about this change which is making the difference? I am positive about this as dbo.tblTempClicks.EmailID is not present in the view and so may be that is the reason why the query engine is not using this index.

What about the index? What is the clustered index on this view?

Gaurav

Actually i changed it to just COUNT on both. The indexes on the view is as follows:<br /><br />Clustered Unique on _hpmv_1_col_1<br />non-cluseter index on _hpmv_1_col_2<br /><br /><br />no matter what query i use it uses the correct execution plan. the minute i put it inside a stored procedure.. all hell <img src='/community/emoticons/emotion-1.gif' alt='' /> a completly different execution plan.<br /><br />-----------------------<br />Richard Ryan King<br />"Why wait for the future when you can create it."

Did u try changing COUNT(dbo.tblTempClicks.EmailID) to COUNT(*)?

Gaurav

Same effect either way on the particular query.

-----------------------
Richard Ryan King
"Why wait for the future when you can create it."

Maybe a SET option. For the procedure in question that is not using the indexed view what are the results of the following

select objectproperty(object_id(procedure_name),'ExecIsQuotedIdentOn')
select objectproperty(object_id(procedure_name),'ExecIsAnsiNullsOn')

Was this procedure created using QA, was it ever looked at in EM ? EM had a nasty habit of not honouring the "sticky" set options you specify when creating a procedure, specifically the default for an EM connection is SET QUOTED_IDENTIFIER OFF. I'm on SP3 and it sets it back to ON before issuing an alter in EM so it does seem to be fixed now.If either of the above queries returns 0 then that's why the optimizer is not considering the indexed view.




HTH

Jasper Smith

Thanks i'll try that.

-----------------------
Richard Ryan King
"Why wait for the future when you can create it."

gaurav_bindlish,

Thanks that worked! Is there a way to set those options by default since everything we do is usually computed coluoms or indexed views and going through OLE DB??

thanks again.

-----------------------
Richard Ryan King
"Why wait for the future when you can create it."

man! i'm bad at this.. sorry for the repost but i meant to say it worked to jasper_smith. However i don't take my thanks away from you gaurav_bindlish.

thanks guys.

however my question still stands about the defualt option settings.

-----------------------
Richard Ryan King
"Why wait for the future when you can create it."

These settings can be modified at the database level using sp_dboption. See BOL for sp_dboption and then under Ansi Nulls and Quoted Identifiers.

HTH.

Gaurav

The trouble is that these can be set at many levels and the connection settings always override any database or server settings. Your best bet (although it's a pain) is to always include the options in you create procedure script. The two options that I mentioned above (ANSI NULLS + QUOTED IDENTIFIERS) are stored with the procedure (they're "sticky") so regardless of the session or higher settings, they are in effect when the proc runs. Thus the only way to guarentee that the options are set so as to allow the optimizer to use IV's is to include them in your create proc script.


HTH

Jasper Smith

thanks guys

-----------------------
Richard Ryan King
"Why wait for the future when you can create it."