SQL Server Performance

Execution Plan & Not using Right Indexes

Discussion in 'T-SQL Performance Tuning for Developers' started by richking, May 6, 2003.

  1. richking New Member

    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 [?]

  2. richking New Member

    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.
  3. gaurav_bindlish New Member

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

    Can you post the query here?

    Gaurav
  4. satya Moderator

    Check the information using DBCC PROCCACHE.

    _________
    Satya SKJ
  5. richking New Member

    -- 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."
  6. gaurav_bindlish New Member

    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
  7. richking New Member

    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."
  8. gaurav_bindlish New Member

    Please post the view defination

    Gaurav
  9. richking New Member

    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."
  10. gaurav_bindlish New Member

    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
  11. richking New Member

    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."
  12. gaurav_bindlish New Member

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

    Gaurav
  13. richking New Member

    Same effect either way on the particular query.

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

    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
  15. richking New Member

    Thanks i'll try that.

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

    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."
  17. richking New Member

    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."
  18. gaurav_bindlish New Member

    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
  19. jasper_smith New Member

    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
  20. richking New Member

    thanks guys

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

Share This Page