SQL Server Performance Forum – Threads Archive
TABLOCK on views out of one common table
If I have a few views created on one common table, theirrecords are mutually exclusive, can I have option TABLOCK
against each view while selecting without deadlock?
If you use TABLOCK on a view, it will indirectly holds the lock on the table itself. So, you will still have blocks and possible deadlocks.
So what I gain by creating views on the table, insert/update/select from the
views instead of table? Thanks.
(here all the views are subsets of this one table)
quote:Originally posted by ykchakri
If you use TABLOCK on a view, it will indirectly holds the lock on the table itself. So, you will still have blocks and possible deadlocks.
Only the ability to change the table structure and as long as the view stays the same, leave the application unaffected… Cheers
Twan
No performance +/-?
Say the table is myTable(col1, col2, col3), clustered index on col1.
All the subset indexed views have index on col1 as well.
This table could be on the order of 10 million records.
Each subset view is about 1/100 of the table.
Do I gain anything in any query action(Ins/Del/Upd)?
quote:Originally posted by Twan
Only the ability to change the table structure and as long as the view stays the same, leave the application unaffected…
Cheers
Twan
Twan
What exactly are you trying to do with the views? Are you able to post the DDL for the table/views? How are the views subsetting the data? an indexed view can give you better performance, but generally only if the view groups rows to return a lot less rows. (e.g. select col1, sum(col2) from tab1 group by col1 returning 10 rows) Often indexing the base table can be an alternative to this with better insert/delete/update performance Cheers
Twan
I have up to 100 Nodes, each node contains up to 100K objects.
col1 = Id, col2 = color, col3 = something else. I have an
clustered index on col1 for base table myTable.
I was trying to create an indexed view for each node from the
base table myTable. There are mostly updates and selects, but
inserts and deletes happen as well.
Any query result from the view or the table should be identical.
It is just the target data pool is different, in the table, you
are looking at up to 10 mil records (with index of course), but
in the view, it is a much smaller portion (same index). I was surprised that TABLOCK on the view would actually affect
the base table and could end up locking the base table. Any 2nd
opinion on this?
quote:Originally posted by Twan
What exactly are you trying to do with the views? Are you able to post the DDL for the table/views? How are the views subsetting the data? an indexed view can give you better performance, but generally only if the view groups rows to return a lot less rows. (e.g. select col1, sum(col2) from tab1 group by col1 returning 10 rows) Often indexing the base table can be an alternative to this with better insert/delete/update performance Cheers
Twan
What exactly are you trying to do with the views? Are you able to post the DDL for the table/views? How are the views subsetting the data? an indexed view can give you better performance, but generally only if the view groups rows to return a lot less rows. (e.g. select col1, sum(col2) from tab1 group by col1 returning 10 rows) Often indexing the base table can be an alternative to this with better insert/delete/update performance Cheers
Twan
You can’t expect any performance gain in Ins/Del/Upd with indexed views. In fact it may degrade performance because of the indexes. It sure may improve the performance with selects. But, why do you want to use TABLOCK ?
On UPDATEs, I would prefer a TABLOCK on each view instead of the default
ROWLOCK which costs more. There is a program running corresponding to each
Node. One program looks/locks one view. If it were table, then it would be
perfect, no deadlock because one program is interested in its own Node
only and will not lock other Node’s table. But now it is a view. If
I apply the with (TABLOCK) option on UPDATEs, you think the lock
would actually apply to the base table, not the intended view? The clustered index is the same as the one in the base table.
So I assume there should be not much difference in index cost:
query against the table or the view, the index is the same,
smaller index pages in views should result in fast query execution,
right? The big difference is the data pool, in the table, it is
the collection of all data in 100 Nodes. In the view, it
is aginst a much smaller data pool, for one Node only.
quote:Originally posted by ykchakri
You can’t expect any performance gain in Ins/Del/Upd with indexed views. In fact it may degrade performance because of the indexes. It sure may improve the performance with selects. But, why do you want to use TABLOCK ?
Given the information provided, I would recommend you stay away from indexed views for updates, inserts, and deletes. Indexed tables are best used for selects, and if you are not careful, indexed tables can create a drag on your system’s performance. If you are currently having performance issues, consider partitioning your data from the one common table to multiple tables, as you mentioned that the data is mutually exclusive. Also, I generally avoid the tablock hint. The reason is because SQL Server will automatically force a table lock, if appropriate, and generally speaking, SQL Server is smarter than I am. —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com
quote:Originally posted by mtmingus
But now it is a view. If
I apply the with (TABLOCK) option on UPDATEs, you think the lock
would actually apply to the base table, not the intended view?[/br]
I apply the with (TABLOCK) option on UPDATEs, you think the lock
would actually apply to the base table, not the intended view?[/br]
It’s not only what I think. It’s what I’ve tested and confirmed. And as Brad suggests partitioned table is a good idea in your case.
]]>