Index a view or a table

I have a table that has thousands of rows inserted daily (rows are seldom updated or deleted)

The table is also involved in frequent non-simple select statements. It currently has about a million rows.

Out of the 15 odd columns in the table, I can see about 6 that would benefit being indexed to speed up the select statements.

Before I do this, I was wondering if people think that perhaps I should create an indexed view that all select statements use, rather than adding indexes directly to the table.

Can anyone advise me the performance benefits/disadvantages of indexed views over indexed tables?

ThanksOriginally posted by mattkrevs
I have a table that has thousands of rows inserted daily (rows are seldom updated or deleted)

The table is also involved in frequent non-simple select statements. It currently has about a million rows.

Out of the 15 odd columns in the table, I can see about 6 that would benefit being indexed to speed up the select statements.

Before I do this, I was wondering if people think that perhaps I should create an indexed view that all select statements use, rather than adding indexes directly to the table.

Can anyone advise me the performance benefits/disadvantages of indexed views over indexed tables?

Thanks

If you use the six in your join and where clauses but you still need all 15, then you should go with indexes on the table. Otherwise, look at an indexed view.|||http://www.sqlteam.com/item.asp?ItemID=1015 for more information.|||Originally posted by Satya
http://www.sqlteam.com/item.asp?ItemID=1015 for more information.

nice article.
thanks|||I don't see that you gain anything by using an indexed view based on a single table, especially if you aren't even aggregating the recordset.

Plus, an indexed view will slow down your inserts.

Just add indexes to your table. Composite indexes may help specific querys as well. Have you run a showplan against your queries to see where delays are occuring?|||Originally posted by blindman
I don't see that you gain anything by using an indexed view based on a single table, especially if you aren't even aggregating the recordset. you don't see, blindman, that's right (pun intended). do you even know the rules of creating indexed views? aggregation? you can't aggregate an indexed view with anything other than count_big(*)!!! do you even know what you are talking about before making such suggestions?
Originally posted by blindman
Plus, an indexed view will slow down your inserts.
man, you just spit out guessing after guessing. how do you figure? so you're saying that if inserts are coming in in a certain order and the poster builds an index on a view (clustered to start with) that accomodates for this order, then the index will slow down the inserts?

and at the same time you're giving suggestions to "just add indexes to the table"?

i love this forum, unsupported ambitions are all over the place :rolleyes:|||holy flame war, batman

look, ms, i don't care what you and blindman have going on between yourselves, there's no reason to do what you just did

please, keep your personal bitterness out of your posts

refute the facts but please keep the vitriol to yourself

when you insult someone out of the blue like that, it makes you look like an idiot|||I thought that was "an ASS out of "U"and "ME"" ?

:D :D :D :D :D|||Originally posted by r937
holy flame war, batman

look, ms, i don't care what you and blindman have going on between yourselves, there's no reason to do what you just did

please, keep your personal bitterness out of your posts

refute the facts but please keep the vitriol to yourself

when you insult someone out of the blue like that, it makes you look like an idiot before jumping on me you should read his posts|||Originally posted by ms_sql_dba
before jumping on me you should read his posts I can't speak for anyone else, but I already did.

I'm with r937, dispute the facts if you will, but I'm not interested in what appears to be a vicious response to a civil posting. I don't care whether the original posting was technically accurate or not, that isn't relevant to this observation. I don't think that your response was appropriate.

-PatP|||Originally posted by ms_sql_dba
before jumping on me you should read his posts

i did, there's only one post of his in this thread, and it was friendly

if you are talking about other threads, they do not matter to this one

this thread will be found independently by people unaware of your own personal vendetta

please, be more civil|||ok, then i'll just restate what i said in my original post while omitting "personal vendetta" comments:

using indexed views is more efficient than creating indexes on the underlying tables.

does this look better to you all? ;)|||Originally posted by ms_sql_dba
ok, then i'll just restate what i said in my original post while omitting "personal vendetta" comments:

using indexed views is more efficient than creating indexes on the underlying tables.

does this look better to you all? ;) Way more gooder, yet even!

Now, all that one of you two needs to do is to come up with something (an URL, a reference to readily available printed material, etc) to support your opinions, then we'll all have something to discuss! ;)

Pretty quick I need to prepare to pig out! You guys go on and debate stuff without me, I'll join in later if I can waddle to the tube!

-PatP|||Let' start at the top...

"you can't aggregate an indexed view with anything other than count_big(*)!!!"
Wrong. You can also aggregate with SUM, and while you can't directly use the AVG, STDEV, STDEVP, VAR, or VARP functions, you can reproduce them using combinations of SUM and COUNT_BIG. Look it up yourself; its easy to find in Books Online.

"do you even know what you are talking about before making such suggestions?"
Yes.

"so you're saying that if inserts are coming in in a certain order and the poster builds an index on a view (clustered to start with) that accomodates for this order, then the index will slow down the inserts?"
Yes, I am. Indexed views are stored in the database, and it stands to reason that since they reflect any updates on their underlying tables then the process of updating the indexed view will require processor time. From Books Online:
"You should create indexes only on views where the improved speed in retrieving results outweighs the increased overhead of making modifications. This usually occurs for views mapped over relatively static data, that process many rows, and are referenced by many queries."

"and at the same time you're giving suggestions to "just add indexes to the table"?"
Yes. I have found, after long years of experience, experiment and investigation, that indexes tend to speed up query processing. What makes no sense is to create an indexed view of a single base table, thus creating a copy of it, and then index the copy.

"using indexed views is more efficient than creating indexes on the underlying tables."
Yes, it does look better. But it is still wrong if you are only dealing with a single table without aggregation, as a stated in my post. I hope you don't create indexed views on all of your base tables instead of simply indexing the base tables themselves.

'Nuff said.|||100 human (hu-man)
200 noun.
300 A carbon based device that allows the user to quickly and
400 efficiently repeat the same mistake 50,000 times.
500 goto 100|||Looks like spaghetti code to me!|||Originally posted by blindman
Let' start at the top...

"you can't aggregate an indexed view with anything other than count_big(*)!!!"
Wrong. You can also aggregate with SUM, and while you can't directly use the AVG, STDEV, STDEVP, VAR, or VARP functions, you can reproduce them using combinations of SUM and COUNT_BIG. Look it up yourself; its easy to find in Books Online.

"do you even know what you are talking about before making such suggestions?"
Yes.

"so you're saying that if inserts are coming in in a certain order and the poster builds an index on a view (clustered to start with) that accomodates for this order, then the index will slow down the inserts?"
Yes, I am. Indexed views are stored in the database, and it stands to reason that since they reflect any updates on their underlying tables then the process of updating the indexed view will require processor time. From Books Online:
"You should create indexes only on views where the improved speed in retrieving results outweighs the increased overhead of making modifications. This usually occurs for views mapped over relatively static data, that process many rows, and are referenced by many queries."

"and at the same time you're giving suggestions to "just add indexes to the table"?"
Yes. I have found, after long years of experience, experiment and investigation, that indexes tend to speed up query processing. What makes no sense is to create an indexed view of a single base table, thus creating a copy of it, and then index the copy.

"using indexed views is more efficient than creating indexes on the underlying tables."
Yes, it does look better. But it is still wrong if you are only dealing with a single table without aggregation, as a stated in my post. I hope you don't create indexed views on all of your base tables instead of simply indexing the base tables themselves.

'Nuff said. indexed views have just been introduced. what years?|||can you guys smell smoke? faintly reminicsent of when i used to play with matches as a kid.
you know that feeling? when your doing something that you know you shouldnt. like fighting|||"indexed views have just been introduced. what years?"

My post was:
"I have found, after long years of experience, experiment and investigation, that INDEXES tend to speed up query processing."

Please read my posts more carefully before criticising them.|||i wasn't. and your answer is very christomatic. of course indexes speed up queries (if properly built) do you read my posts carefully?|||Originally posted by Ruprect
can you guys smell smoke? faintly reminicsent of when i used to play with matches as a kid.
you know that feeling? when your doing something that you know you shouldnt. like fighting At least from what I've seen in this thread, blindman has been both civil and technically correct. I'm willing to wait for ms_sql_dba to take a shot at validating their claims, but so far I can't fault blindman.

Maybe I'm missing something (I've been prone to do that lately), but I see this as a pretty one sided screaming match, with blindman presenting the civilized side. Please let me know if you disagree.

-PatP|||But I do like the word "christomatic". It's not on dictionary.com, but it should be! Very appropriate for the holiday!|||Originally posted by Pat Phelan
At least from what I've seen in this thread, blindman has been both civil and technically correct. I'm willing to wait for ms_sql_dba to take a shot at validating their claims, but so far I can't fault blindman.

Maybe I'm missing something (I've been prone to do that lately), but I see this as a pretty one sided screaming match, with blindman presenting the civilized side. Please let me know if you disagree.

-PatP ok, mr. judge, so you choose to ignore the technical stuff coming from me, and acknowledge only blindman's answers like "I have found, after long years of experience, experiment and investigation, that INDEXES tend to speed up query processing." wow, it only takes that long to figure this one out :D

but this is why indexes exist, to improve performance of queries. please read carefully what the topic is all about, - indexed views vs. indexes on underlying tables (at least that's what it bottled down to)

...and thanks for stopping by. your arrival means it's time to go back to real life :p|||Originally posted by blindman
Looks like spaghetti code to me!

I'm sorry. Is this better? :)

CREATE TABLE thread(
thread_id VARCHAR(55),
thread_certain_participant_description VARCHAR(55),
thread_certain_participant_part_of_speech VARCHAR(55),
thread_certain_participant_definition VARCHAR(255))

GO

INSERT thread(
thread_id,
thread_certain_participant_description,
thread_certain_participant_part_of_speech,
thread_certain_participant_definition)

SELECT
'index a view or a table',
'human (hu-man)',
'noun',
'A carbon based device that allows the user to quickly and efficiently repeat the same mistake 50,000 times.'

GO

DECLARE @.int_counter INT

SELECT @.int_counter = 1

WHILE @.int_counter <= 50000
BEGIN

SELECT
thread_id,
thread_certain_participant_description,
thread_certain_participant_part_of_speech,
thread_certain_participant_definition
FROM
thread
WHERE
thread_id = 'index a view or a table'

SELECT @.int_counter = @.int_counter + 1
END

GO|||Originally posted by ms_sql_dba
ok, mr. judge, so you choose to ignore the technical stuff coming from me, and acknowledge only blindman's answers like "I have found, after long years of experience, experiment and investigation, that INDEXES tend to speed up query processing." wow, it only takes that long to figure this one out :D

but this is why indexes exist, to improve performance of queries. please read carefully what the topic is all about, - indexed views vs. indexes on underlying tables (at least that's what it bottled down to)

...and thanks for stopping by. your arrival means it's time to go back to real life :p I contribute here because I like to help others when I've got a minute or two to spare. I try to behave civilly and professionally.

While you've offered some good technical content in other threads, I haven't seen that you've offered anything I value in this thread. I'm not sure what's wrong, whether it's my percepcion or your behavior.

Blindman has done a good job of defending his position. Your behavior struck me as poor, and your technical support of your position was minimal at best. Do you have anything technically useful to contribute?

-PatP|||when someone refuses to be civil, just add them to your ignore list (http://www.dbforums.com/misc.php?action=faq&page=1#buddy)

"Ignore lists are used for those people whose messages you wish not to read. By adding someone to your ignore list, those messages posted by these individuals will be hidden when you read a thread."

sounds tailor-made for certain people, don't it|||Except that then you may end up posting something somebody already has posted. I don't have anybody on my ignore list because it would be like having a conversation with four other people and only be able to hear one of them.

Do you have anybody on your ignore list, Rudy?

Rudy?

Hello? Can you hear me?

.
.
.

Hmmm.... :(|||don't it?

gotta be the Queens English...:D

Actually I find it all amusing...

I know that the net provides a huge amount of anonymity...

Makes you wonder how people actually behave in the real personal lives...

And yes it's all about the technology (if you can call it that)

MOO, of course...|||Originally posted by blindman
Except that then you may end up posting something somebody already has posted.
yeah, but if you do, your post will be seen by everybody else as coming from a reputable source, whereas the post from the ignored person, well, you wouldn't have put her on the ignore list for no good reason...|||Originally posted by blindman
Except that then you may end up posting something somebody already has posted.

Happens anyway, doesn't it?

Oh...[sniped]|||Is it just me .. or tempers on dbForums seem to be on a short fuse ?|||Like I said...I find it amusing...

Like it really matters...

lot of hard cash on the line.....|||Thats why I usually never get into Flame war ... (http://www.dbforums.com/showthread.php?threadid=972040)|||Ah yes, good old Sundial and his "Bob Cratchet" theory of employee loyalty. What the Dickens was he thinking?|||Hey...any job in this market is a good job...

And I can't believe that thread is 6 months old already...

Damn Mondays...|||Originally posted by Brett Kaiser
Damn Mondays... I refuse to let the "Monday-ness" of a day interfere with my enjoyment of the day! Somebody (John Ruskin?) once said that: "Monday is one H#)) of a way to spend one seventh of your life." I think that he "missed the boat" on this one, and that Monday really IS one heck of a way to spend one seventh of your life!

-PatP|||Originally posted by Pat Phelan
I refuse to let the "Monday-ness" of a day interfere with my enjoyment of the day! Somebody (John Ruskin?) once said that: "Monday is one H#)) of a way to spend one seventh of your life." I think that he "missed the boat" on this one, and that Monday really IS one heck of a way to spend one seventh of your life!

-PatP

Well I guess it's better than have 1/7 less....|||or 2/7 less

which is me, usually friday afternoon to sunday afternoon is a huge fog|||That's cause the leafs split at home...

Off to Ottowa...

Damn devils....|||on the contrary, i don't even know who's playing, i am so not a hockey fan|||picking myself off the floor

I though it was part of being allowed to live in Canada

Are you a transplant?

Has it stopped snowing yet?|||transplant? yes

the kid in the lederhosen is me in germany

however, i have lived in and around toronto for over 50 years

i useta, as we say, watch hockey when it was hockey, back when there were 6 teams...

last snow here was a month or so ago

today it's 9C and sunny|||You should post a more recent photo of you in Lederhosen.|||the only pics i'm willing to share are on my personal site ;)

der lederhosen guy is cute, isn't he?

i couldn't think of changing it, it's like a brand

did you buy the new coke when it came out?

neither did i