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.
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.
Indexed view is better, but what Thomas has asked will get us more idea on how best to optimize the quer.
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.
[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.
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!
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.
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.
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!
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.
See if this helps in general - http://www.sql-server-performance.com/faq/indexed_view_performance_p1.aspx
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.