improving performance of view by creating index

Question

improving performance of view by creating index

datawhizzz

Newbie

Points: 9
More actions
September 23, 2004 at 9:23 am

#109446

Hi,
I have a set of complex views that join a number of large tables (ranging from 7 - 40 million rows) with embedded functions. They worked well being used occasionally for executing procedures, but now these views will be accessed more often for dynamic data integrity checks. The referenced columns in all the base tables are already indexed. My question is how much of an improvement can I expect if I also created indexes on the views that would be referenced by the integrity check processes? Would the improvement be significant enough to make it worth re-creating some of the base tables to re-set ANSI_NULLS and SCHEMABINDING options etc?
Thanks

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

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

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

I have not had the opp. to set-up the same type of environment that you have however, I can say that there is only one way to find out unfortunately...

I would try and create one in your test environment AFTER gathering the STATS from the non-indexed VIEW and then compare with the INDEXED VIEW...

Wish I could help more...

Good Hunting!

AJ Ahrens

webmaster@kritter.net

datawhizzz Newbie Points: 9 More actions · Answer 2

I was digging around a bit, and found an previous discussion about using views versus temp tables. Here's an answer from Andy Warren:

'...indexed views are certainly the right option in some cases, especially if you're aggregating - but if you've done a good job on indexing your base tables then queries against your views should perform pretty well. Before you go with temp tables or an indexed view, I'd take a hard look at the query plan to see what else you can change first. A good way to find out for sure would be test the query against your view of views (!), then just try running the same query without the views (one big sql statement). Keep in mind the effects of data caching - best to run each query once to load the cache, then a second time to see what you get. You could also compare the query plans to see if SQL is unable to deciper the more complex views effectively. If you find a case where this is true (entirely possible) it would be great if you could post it here. Andy

Andy

http://www.sqlservercentral.com/columnists/awarren/ '

I guess that helps answer my question, as it would be quite a task to do all that work then compare stats. All the embedded funtions would need to be re-created. Then, there are issues using non-deterministic functions in those, which would be roadblocks. I also subsequently found out that server settings for QUOTED_IDENTIFIER and other options would require intensive regression testing for the apps using the views.

So like Andy suggested, I'll look for other ways to improve performance first.

Simon-220850 SSCrazy Points: 2789 More actions · Answer 3

Does anybody know what kind of transaction performance overhead there is with indexed views. I assume that each time changes are made to any base tables in a view then any view indexes that are derived from the affected fields in the base table have to be updated. Does this happen dynamically on the fly as the data on the base table is updated and if so, what kind of performance issues are there with this ?