Post reply

Fixing Idx from Varchar to INT, why became slower !!!?

  • August 20, 2014 at 1:20 pm

    #298685

    Hi,

    I have an indexed view and i can't get why after "fixing" one of index it became slower then before.

    Goal was to switch index to INT column instead of VARCHAR, so the theory looks perfect !!!(?).

    I drop index and built new one with same name. Could it be because of Statistics? should I refresh it all ?

    the only think I changed is one of 3 idx:

    -- CustTypeCode VARCHAR(10) /* 1,2,3,4,5 */

    -- CustTypeID INT /* 1,2,3,4,5 */

    -- index Before:

    CREATE NONCLUSTERED INDEX [IdxLookTypeCode] ON [dbMacros].[vw_Lookup_Customer]

    ([CustTypeCode] ASC)

    -- index After:

    CREATE NONCLUSTERED INDEX [IdxLookTypeCode] ON [dbMacros].[vw_Lookup_Customer]

    ([CustTypeID] ASC)

    -- usage before:

    SELECT C1, C2, FROM vw_Lookup_Customer WITH (NOXPAND)

    WHERE CustTypeCode = 2

    -- usage after

    SELECT C1, C2, FROM vw_Lookup_Customer WITH (NOXPAND)

    WHERE CustTypeID = 2

    Thanks

    Mario

  • August 20, 2014 at 1:59 pm

    #1739713

    And surely enough my indexed M View was defined with all rules, after getting it indexed it performed hell faster:

    all needed option are in placed

    SET ARITHABORT ON

    SET CONCAT_NULL_YIELDS_NULL ON

    SET QUOTED_IDENTIFIER ON

    SET ANSI_NULLS ON

    SET ANSI_PADDING ON

    SET ANSI_WARNINGS ON

    SET NUMERIC_ROUNDABORT OFF

  • August 20, 2014 at 2:53 pm

    #1739727

    So everything is ok now or not? Do you still need help?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • August 21, 2014 at 3:25 am

    #1739812

    Sounds like it might be fixed, but if not, no, you shouldn't need to update statistics. When you create an index for the first time, and same thing goes for an indexed view, the statistics are created with the index doing a full scan of the data referenced. If there's a question as to how it's behaving between those two different indexes and two different data types, take a look at the execution plan to understand how the optimizer chose to resolve your query.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply