SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Timothy A Wiseman / On Indexes and Views / Latest Posts

RE: On Indexes and Views

Carla Wilson-484785 — Mon, 09 Mar 2009 07:56:57 GMT

Thanks for the reply.I did set up a couple for testing, and since we have the Standard edition, I set up some queries with the hints. They worked great, greatly reducing the query time. But you pointed out a couple disadvantages, including the maintenance issues, especially since our database is still evolving. The jury is still out on which way we will go.Thanks again.

RE: On Indexes and Views

timothyawiseman — Sat, 07 Mar 2009 16:18:40 GMT

Carla;There are definitely numerous design considerations.First, the view has to be schema bound. This makes it harder to change any objects the view depends on (you have to remove schema binding first, make the change, and then recreate it). In a mature database where the schema rarely changes this is of limited concern of course, but in a newly created database this can become tedious.Next, as you said, the index has to be stored on disk. Depending on the size of the table and number of columns involved, this can be quite substantial. Also, it will slow down write operations on the tables involved. In most cases, this will not be a substantial amount, but it can be a consideration for tables that receive a lot of updates.Then, if you are not in enterprise edition, you have to deal with the complexity of the query hints to actually use the index (this is handled by the engine in Enterprise Edition.) Also, it will add complexity if you use replication and both servers are not in Enterprise Edition (the engine in Enterprise edition will handle almost all of it for you, but in Standard Edition you have to do a lot more of it manually).In many cases, the advantages to using an indexed view will greatly outweigh the disadvantages, but these are all things that must be considered.

RE: On Indexes and Views

Carla Wilson-484785 — Wed, 04 Mar 2009 12:47:34 GMT

Question:I see the Pros of creating Indexed Views, but what are the Cons? Seems I read somewhere that the materialized view has to exist somewhere. Is that in memory or on a physical disk?When loading data, will this slow down the load? Can the view be disabled during a load and then reconstructed (I'm sure that's not the best term to use)?I am in the middle of a debate on whether we go this approach or a different approach. Any input would be greatly appreciated.Thanks!

RE: On Indexes and Views

Wayne West — Thu, 23 Oct 2008 09:52:56 GMT

Thanks for the reply, Tim. I'd have to research the schema, off-hand I cannot say that all the tables have PKs, clustered or otherwise. I have two specific performance problems, one I can't remember the details of without researching it. The other involves two tables where I need a subset, and because of the table size, a filtered indexed view would (I think) give that report good performance.In this case, the data is being read by an Access report, not through the GUI, so that concern doesn't apply.

RE: On Indexes and Views

timothyawiseman — Thu, 23 Oct 2008 01:35:50 GMT

It is unlikely that you would break something, though it is possible since it will try to enforce things like unique constraints onto the base tables. Also, schemabinding may prevent schanges to the table schemas which could cause a problem during an upgrade. The better question is what do they gain you? If you are frequently accessing it through SSMS or if you can change the GUI code so that it can take advantage of the indexed views, then the performance improvements can be extremely impressive. But, if you are primarily accessing the system through a closed source GUI that you cannot direct to take advantage of the indexed view, then it will do you little if any good.(As a side note, Enterprise edition can use the index on the view if it is useful when you query against the base tables in certain cases, but it does not make a lot of since to create an indexed view with that in mind. If you want to improve access to the base tables themselves, it makes more sense to create the appropriate indexes on them. That is more of an incidental benefit if you are also using the index on the view to access the view.)

RE: On Indexes and Views

Wayne West — Wed, 22 Oct 2008 16:31:10 GMT

I would like to use an indexed view in our ERP system (SS 2000 Enterprise) but I'm paranoid because it's a canned system and we don't have the source, I'm afraid that I could break something. I have two areas where performance is routinely very sub-par and I'm certain indexed views would bring performance up to proper levels.The system has a lot of design problems, but I'm stuck with it, so that's the way it is. I'd probably lose the indexed views whenever they did an upgrade, but recreating them would be a minimal hassle.

RE: On Indexes and Views

lage_bergstrom — Wed, 10 Sep 2008 02:16:56 GMT

Thank you Timothy for your answer. This was really useful.Lage

RE: On Indexes and Views

timothyawiseman — Tue, 09 Sep 2008 18:08:51 GMT

[quote][b]lage_bergstrom (9/9/2008)[/b][hr]Thank you for an interesting article! The company I work for is still using SQL Server 2000. I tried the example and it seem like the NOEXPAND just gave an extra overhead to the query. It didn't expand without the tag, and with the tag it took a little bit more resources. Without NOEXPAND total cost was 0.0634, with NOEXPAND it was 0.0642. My question is if anyone knows the best practice for SQL Server 2000? Shall I use NOEXPAND to be prepared for a possible migration to SQL Server 2005?[/quote]Lage,All of my testing was on SQL Server 2005 and 2008 (and all my references are specifically for 2005), but I believe the same things apply to 2000, and if you are using something other than Enterprise Edition you likely want to use noexpand where it is appropriate.As for the time trials, make certain you are testing them in separately and not in a batch, otherwise the caching, execution plan generation, etc will cause one portion to affect the others. When I try running them together in one batch, my results are inconsistent with it coming out faster with the noexpand sometimes and without other times. When I run them separately the one with noexpand comes out slightly, but consistently better. Of course, I kept these sample sizes small, if you increase the table sizes the difference becomes much more marked.To provide an anecdote, I once reduced a query that was being run routinely from taking over an hour to taking less than a minute by indexing the view and adding with (noexpand). But that was a very large query which needed several joins against large tables.(edited to correct a typo)

RE: On Indexes and Views

lage_bergstrom — Tue, 09 Sep 2008 15:32:55 GMT

Thank you for an interesting article! The company I work for is still using SQL Server 2000. I tried the example and it seem like the NOEXPAND just gave an extra overhead to the query. It didn't expand without the tag, and with the tag it took a little bit more resources. Without NOEXPAND total cost was 0.0634, with NOEXPAND it was 0.0642. My question is if anyone knows the best practice for SQL Server 2000? Shall I use NOEXPAND to be prepared for a possible migration to SQL Server 2005?

RE: On Indexes and Views

Jerry Hung — Tue, 09 Sep 2008 14:07:05 GMT

Oops, didn't know I have to use NOEXPAND to use Indexed Viewsall this time I thought when I selected on my view, it used the Clustered IndexGood thing it wasn't slowing anything down

RE: On Indexes and Views

Andy Lennon — Tue, 09 Sep 2008 10:14:54 GMT

a well-written article, and with appropriate citations, no less!Thanks Timothy. I'll be looking for more articles from you...Cheers!A lowly developer

RE: On Indexes and Views

timothyawiseman — Tue, 09 Sep 2008 09:32:58 GMT

Adrian, that is a great idea on the nomenclature. Thanks for pointing it out.

RE: On Indexes and Views

Adrian Hains — Tue, 09 Sep 2008 09:22:48 GMT

I have been using the same approach for my sql2k5 standard boxes with great success. I have found that I routinely want to know if a view is (1) an indexed view, (2) a wrapper view for an indexed view, or (3) a traditional view. To that end, I use a nomenclature of *_BaseIV and *_IV for the first two cases-[code]create table t1 (c1 int primary key);go/*indexed view*/create view myview1_BaseIVwith schemabinding asselect c1 from dbo.t1;gocreate unique clustered index myview1baseiv_ucidx_c1 on dbo.myview1_baseiv(c1);go/*wrapper for indexed view*/create view myview1_IVas select c1 from dbo.myview1_BaseIV with (noexpand);[/code]Naming them this way makes it so I can easily discern what type of view each is when scanning through the list of views in SSMS. I'll often times be looking for an indexed view that is an aggregate rollup of some table, so this makes it quite easy to locate.

RE: On Indexes and Views

Tom Garth — Tue, 09 Sep 2008 08:08:37 GMT

Timothy,That's good information, and a great example.Thanks!One question anyone?I thought that Developer Edition was equivalent to Enterprise Edition, but mine acts like Standard Edition when I don't use NO EXPAND.Anybody know why?

RE: On Indexes and Views

Jeff Moden — Tue, 09 Sep 2008 06:01:30 GMT

Timothy! Great article on NO EXPAND. Nicely done!

RE: On Indexes and Views

Jeff Moden — Tue, 09 Sep 2008 06:00:15 GMT

[quote][b]M Roush (9/9/2008)[/b][hr]This is nice however, how can u make a view more efficient when you are using other views or a linked query in the view? I guess you can't create an index on these views so are there any tips out there on these types of views? :)Thanks,Mike[/quote]Depending on what you filter a view-of-a-view on in a query, views of views can be horrible for performance because you will usually cause the inner view to materialize in full if you filter or join on a calculated column.

RE: On Indexes and Views

M Roush — Tue, 09 Sep 2008 05:50:24 GMT

This is nice however, how can u make a view more efficient when you are using other views or a linked query in the view? I guess you can't create an index on these views so are there any tips out there on these types of views? :)Thanks,Mike

RE: On Indexes and Views

SuperDBA-207096 — Tue, 09 Sep 2008 05:44:20 GMT

Excellent article! Indexed views are a useful tool to solve performance problems when properly implemented.

RE: On Indexes and Views

Anipaul — Tue, 09 Sep 2008 03:54:08 GMT

Nice one.

On Indexes and Views

timothyawiseman — Tue, 09 Sep 2008 00:45:51 GMT

Comments posted to this topic are about the item [B]On Indexes and Views[/B]