will updating though a view help speed up the database response time?

Question

will updating though a view help speed up the database response time?

aswani2002

SSC Journeyman

Points: 83
More actions
October 2, 2011 at 7:56 am

#243960

hello,
in our company mostly 90% of all the queries are on one table so i want to take some of the pressure by creating an index view and have all the updates to be done though the view 🙂
will that speed up the response time and minimize the blocking done by the update statement?
many thank in advance 🙂

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

No. At best it'll have no effect. At worst it'll slow things down.

If you mean an indexed view (a view with a clustered index) that will potentially slow all updates down as the indexed view will have to be kept up to date as well as the base table.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

aswani2002 SSC Journeyman Points: 83 More actions · Answer 2

GilaMonster (10/2/2011)
No. At best it'll have no effect. At worst it'll slow things down.
If you mean an indexed view (a view with a clustered index) that will potentially slow all updates down as the indexed view will have to be kept up to date as well as the base table.

thanx 🙂

how about a non index view? any idea? bcoz we do suffer from blockings from update statement mostly 🙁

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

As I said, At best it'll have no effect. At worst it'll slow things down.

If you have blocking you really have 3 options:

Tune the queries (they're unlikely to be as optimal as they could be)

Tune the indexes (there are very likely indexes that could be added, widened or removed)

Consider one of the row-version based isolation levels (read committed snapshot or snapshot). This option requires intensive testing.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

aswani2002 SSC Journeyman Points: 83 More actions · Answer 4

GilaMonster (10/2/2011)
As I said, At best it'll have no effect. At worst it'll slow things down.
If you have blocking you really have 3 options:
Tune the queries (they're unlikely to be as optimal as they could be)
Tune the indexes (there are very likely indexes that could be added, widened or removed)
Consider one of the row-version based isolation levels (read committed snapshot or snapshot). This option requires intensive testing.

thank u very much mate 🙂