SQL Server Performance

my indexed view not being utilized

Discussion in 'SQL Server 2005 General Developer Questions' started by rmills, Oct 16, 2007.

  1. rmills New Member

    I have a table with approximately 10 million rows in it. Several queries we run against it are selecting all the rows in it where a bit column in the table is equal to 1. Naturally, I don't want to create an index on a bit field so the queries are horribly slow.
    I thought that the best thing to do would create an indexed view to use. I created it but even when I run a select on the view it's still scanning the underlying table. Is there an issue with creating a view this way? The index is a unique clustered one. And I've verified that the view is materialized and taking up space.
  2. thomas New Member

    What does the query look like?
  3. anandchatterjee New Member

    As per your execution plan, evenif the view is scanning the underlying table but still you would get the result in more faster compare to previous scenario.. I think, we are making fool the execution plan when implementiing Index view. Please let me know your opinion.
  4. satya Moderator

    Indexed view is better, but what Thomas has asked will get us more idea on how best to optimize the quer.
  5. Adriaan New Member

    As far as I can tell, you need an indexed view:
    (1) if you have a partitioned table,
    (2) if you want to have a composite index on columns from different tables.
  6. FrankKalis Moderator

    [quote user="rmills"]
    I have a table with approximately 10 million rows in it. Several queries we run against it are selecting all the rows in it where a bit column in the table is equal to 1. Naturally, I don't want to create an index on a bit field so the queries are horribly slow.
    I thought that the best thing to do would create an indexed view to use. I created it but even when I run a select on the view it's still scanning the underlying table. Is there an issue with creating a view this way? The index is a unique clustered one. And I've verified that the view is materialized and taking up space.
    [/quote]
    Are you on Enterprise Edition? If not, you need to specify the NOEXPAND hint.
  7. rmills New Member

    Sorry to take so long getting back. I was expecting emails to come to me when I received replies but never received an email.

    It's pretty simple. To save space I'm not including all the selected columns but it's basically selecting all columns from the table:
    CREATE VIEW [dbo].[serial_iswip]
    with schemabinding
    AS

    SELECT [Serial_Id]
    ,[Serial_Number]
    ,[Part_Id]
    ,[Customer_Order_ID]
    FROM [dbo].[serial]
    where
    iswip = 1
    GO
    CREATE UNIQUE CLUSTERED INDEX [serial_iswip_u_cidx] ON [dbo].[serial_iswip] (Pk_Id)
    GO
    I would've thought so as well but my tests didn't show any improvement. I verified that the view reduced the number of records from 10mil to 180K so it should definitely be performing better if it's working as I expected.
    No, we're using standard so I will try your suggestion.
    Thanks everyone!


  8. thomas New Member

    Is 'iswip' your BIT field? If it is, an indexed view isn't going to make any difference. An index on a bit field is useless. This query will table scan however you index the data.
  9. FrankKalis Moderator

    Actually this NOEXPAND hint is a double-edged thing. Although literally a "hint", this is an instruction which SQL Server has to follow. So, if you specify this hint, SQL Server will always consider just this view, even if there might be better access methods available. On Enterprise Edition you should stay away from this hint, and on other editions I would prefer to periodically review this strategy if it is still the "best" solution for your environment.
  10. rmills New Member

    When I use to run a select count(pk_id) on the table it would take almost 3 mins to finish. When I tried selecting the count on the indexed view with noexpand it took less than 1 sec. Fantastic! The results I was looking for.
    I will keep your suggestion in mind if we upgrade to enterprise.
    Thanks again!

  11. rmills New Member

    Well I got my main procedure working very quickly. Execution time is about 10 sec.
    But now when my asp.net app tries to execute the procedure I get the error:
    SELECT failed because the following SET options have incorrect settings: 'ARITHABORT'.
    I tried turning it on and off but get the same error. I don't really know what that setting is about either.
  12. MichaelB Member

  13. rmills New Member

    As it turns out that whenever you connect to sql server using .net it by default turns arithabort off. So the resolution was each time I open a connection I must execute the "set arithabort on" command before executing my main command.

Share This Page