Indexed Views

Question

Post reply

Indexed Views

Chandra Sekhara Vyas Dhara

SSChampion

Points: 10207
More actions
February 5, 2010 at 2:32 am

#137124

We had a table with 11 million records which is highly transactional. We had new report requirement which i need to fetch static data from this table joining other table. I had plan to create a clustered index view for fetching data. Is there will be any performance bottle neck if i create an view?

Viewing 12 posts - 1 through 11 (of 11 total)

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

Sure, whenever you create index on any transactional table , it will degrade the performance.

because whenever DML operation performed on this table , index will get updated.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

sqldba4u Hall of Fame Points: 3414 More actions · Answer 2

You have mentioned about fetching static data from the transactional table.Please clarify what does it mean?If data is static, there will be no DMLs then. For reporting purpose, I guess you will mostly run Select statements.If this is so, create a view on the table which is assumed to be static and then index it.Do post if this is the different case.

Thanks

Vivek

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

vyas (2/5/2010)
We had a table with 11 million records which is highly transactional.

As above line depicts , this is transactional table, i guess

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

sqldba4u Hall of Fame Points: 3414 More actions · Answer 4

I know its transactional:cool:.I am asking something else i.e. about fetching static data.Static data means no DMLs..I guess. Vyas can you elaborate your point?

Vivek

Chandra Sekhara Vyas Dhara SSChampion Points: 10207 More actions · Answer 5

Fetch data means, select only. There is no DML operations involved

sqldba4u Hall of Fame Points: 3414 More actions · Answer 6

sqldba4u

Hall of Fame

Points: 3414

February 5, 2010 at 5:38 am

#1114347

Ok..then my previous post might help you.

Vivek

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 7

Creating an indexed view is not the same thing as creating an index. Actually, it's somewhat more expensive. If your data is highly volatile, as it gets constantly updated, it has to constantly update the indexed view, almost like it's constantly updating a table index. If you're not doing lots of joins or aggregates, and on relatively non-volatile data, then the indexed view is not the way to go. Maybe you need to look into partitioning your data?

"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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

Any way to compile the reports every day or every few hours and show the saved version?

If not you can always look at replication on a dedicated server than can handle a heavier load of rebuilding the indexes constantly.

Then there's alway the hybrid version. Onde daily run a query that compiles the data into a report table. Then when the report is running, fetch the data from the report table and then fetch and compile the "new" data for the day. It's especially effective if you have a leading column in the clustered index which keeps the inserted date. If you you can always build an index (even covering) to get that data. That should be much less expansive than the view.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 9

Vyas, i was telling the same thing as grant Frithcey mentioned in his first reply.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 10

Grant

, isnt it different objected creating whenevr we create a index on view. bcoz i read some where that whever we create any indexed view, sql server will create new object as indexed view.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

sqldba4u Hall of Fame Points: 3414 More actions · Answer 11

I was not distinguishing view and an indexed view.I know very well that they are two different things:cool:.I guess the above posts are not confusing for Vyas bcoz some posts are getting diverted from the original Q asked:ermm:.Vyas,did u find ur answer?Also, as mentioned earlier, if u can bifurcate ur data which is static and create a view on it might help.Also, I think partitioning would be one of the more efficient ways as mentioned in one of the posts.