database view performance issue

Question

Hello everyone,

I am using SQL Server 2008 and I have two tables which are of the same schema and I create a view which union the content of the two tables to provide a single view of "table" to external access.

One of the table is read only and the other table contains bulk insert/delete operation (on the other table, I will use bulk insert at some interval to insert everal thousand of rows and run another SQL Job to remove several Million rows daily).

My question is, if the other table is under bulk insert/delete operation, will the physical table be locked so that the access from external user to the union view of two tables are also blocked? (I am thinking of whether lock escalation applies in this scenario, row locks finally lock the table, which finally locks the access of the view?)

thanks in advance, George

Answer 1

if the other table is under bulk insert/delete operation, will the physical table be locked so that the access from external user to the union view of two tables are also blocked?

Yes, with the caveat that, if the optimiser can find a way to execute the query that does not involve accessing the bulk insert table then access will not be blocked.

If you are looking to optimise bulk loading times make sure you have a read of this blog post.

EDIT

What is the actual problem you are experiencing? Do you really need to be using this view everywhere (for example are there places that only need data from one table, that are querying it via the view?)

If you want you view to be "online" all the time consider either snapshot isolation, or if you are loading up full sets into the bulk table (eg. full content is replaced daily), you can load the data into a separate table and sp_rename the table in (in a transaction)

Answer 2

Mostly likely yes. It depends on lock escalation

To work around (not all options):

• Use the WITH (NOLOCK) table hint to ignore and don't set any locks. If used on the view it also applies to both tables

• Use WITH (READPAST) if you don't mind skipping locked rows in the BCP table

• Change the lock granularity for the BCP table. Use sp_tableoption and set "table lock on bulk load" = false.

Edit: Now I've had coffee...

If you need to query the bulk table during load/delete operations and get accurate results and not suffer performance hits, I suggest you need to consider SNAPSHOT isolation

Edit 2: SNAPSHOT isolation