469,571 Members | 1,249 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,571 developers. It's quick & easy.

Indexed Views Vs temp tables

With my understanding of indexed views and according to books I read
"indexed views" are supposed to perform much better than "temp tables"
(temp table having primary key and indexed view with clustered index
on the same keys).

But when I tried in my system I am getting opposite results. With
Indexed Views it takes 3 times more time.

Any body has any reasons for that? Or my understanding was wrong?
thanks
Raghu Avirneni
Jul 20 '05 #1
2 7027
Indexed views are expensive when adding data, since adding to the base table
also has to update the view's indexes as well as the base table.

It only helps on the retrieve (and only if the index on the view is used in
the query plan).

I have only used them for lookup tables that rarely change.

"Avirneni" <ra*******@trafficmp.com> wrote in message
news:15**************************@posting.google.c om...
With my understanding of indexed views and according to books I read
"indexed views" are supposed to perform much better than "temp tables"
(temp table having primary key and indexed view with clustered index
on the same keys).

But when I tried in my system I am getting opposite results. With
Indexed Views it takes 3 times more time.

Any body has any reasons for that? Or my understanding was wrong?
thanks
Raghu Avirneni


Jul 20 '05 #2
Temp tables and views are different animals. Like David mentioned, examine
the query plan to ensure the index on the view is actually being used.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Avirneni" <ra*******@trafficmp.com> wrote in message
news:15**************************@posting.google.c om...
With my understanding of indexed views and according to books I read
"indexed views" are supposed to perform much better than "temp tables"
(temp table having primary key and indexed view with clustered index
on the same keys).

But when I tried in my system I am getting opposite results. With
Indexed Views it takes 3 times more time.

Any body has any reasons for that? Or my understanding was wrong?
thanks
Raghu Avirneni

Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by teddysnips | last post: by
7 posts views Thread by Ioannis Vranos | last post: by
8 posts views Thread by Martijn van Oosterhout | last post: by
5 posts views Thread by lukster | last post: by
1 post views Thread by Mr,Goody | last post: by
1 post views Thread by Jason Wilson | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.