Rmote database inserts blocking local queries.

[quincy451]

I am running MS SQL 2000 server. The table involved is only about
10,000 records. But this is the behavior I am seeing.

The local machine is querying the table looking for a particular
record. Everything works fine.
The remote amchine goes to clear the table and then insert all 10,000
records, into the table the following happens.

1) the local machines queries do not compilete until the remote
machine is done.
2) the remote machine can take up to 6 minutes to do these 10,000
insert operations. So nothing on the local machine works right for
these 6 minutes.

I do not have access to the remote machines source to see what is
running but I am told it is simply a for loop with a insert query in
it. Nothing of a locking natture.

Any idea the types of things I should look for to track this down? I
found this by doing SQL profiler profiling and finding the remote
operations. Turn these operatiiosn off and the local machine works
fine again, with no other action.

Thanks,
David

[Tom van Stiphout]

On Fri, 25 Jan 2008 15:02:52 -0800 (PST), qu*******@yahoo.com wrote:

I take it the code on the remote server is something like this:
truncate table SomeTable
for i=1 to 10000
Insert One Row
next i

Inserting a record will put an Exclusive lock on the table. From BOL:
Exclusive (X) Used for data-modification operations, such as INSERT,
UPDATE, or DELETE. Ensures that multiple updates cannot be made to the
same resource at the same time.

If you don't have control over the remote server process, you will
have to live with it. If you do have control, you could investigate
more efficient data import strategy (perhaps BCP) as well as more
acceptable timing (2AM).

-Tom.

>I am running MS SQL 2000 server. The table involved is only about
10,000 records. But this is the behavior I am seeing.

The local machine is querying the table looking for a particular
record. Everything works fine.
The remote amchine goes to clear the table and then insert all 10,000
records, into the table the following happens.

1) the local machines queries do not compilete until the remote
machine is done.
2) the remote machine can take up to 6 minutes to do these 10,000
insert operations. So nothing on the local machine works right for
these 6 minutes.

I do not have access to the remote machines source to see what is
running but I am told it is simply a for loop with a insert query in
it. Nothing of a locking natture.

Any idea the types of things I should look for to track this down? I
found this by doing SQL profiler profiling and finding the remote
operations. Turn these operatiiosn off and the local machine works
fine again, with no other action.

Thanks,
David

[quincy451]

On Jan 25, 9:25*pm, Tom van Stiphout <[e-mail]:

On Fri, 25 Jan 2008 15:02:52 -0800 (PST), [e-mail] wrote:

I take it the code on the remote server is something like this:
truncate table SomeTable
for i=1 to 10000
* Insert One Row
next i

Yes.

>
Inserting a record will put an Exclusive lock on the table. From BOL:
Exclusive (X) *Used for data-modification operations, such as INSERT,
UPDATE, or DELETE. Ensures that multiple updates cannot be made to the
same resource at the same time.

So you are telling me INSERT in a loop like that will lock and unlock
the table
repeatedly, but possibly because of the tightness of the loop never
let me in
to get my stuff done. And are you saying UPDATE, and DELETE work the
same
way? Now for the dumb question: What is BOL: Exclusive (X)? I guess
it
remains a open question what might make the remote side take 6 minutes
to
complete these insert operations. But knowing what will lock the
other side
out is very important.

>
If you don't have control over the remote server process, you will
have to live with it. If you do have control, you could investigate
more efficient data import strategy (perhaps BCP) as well as more
acceptable timing (2AM).

Well, the other side is under my influence. I am the developer of the
local
application. The other side is someone else's code for which they
have
full control. I can advise and point in a different direction, if I
have that direction,
and most likely get things done. We are currently doing the late
night trick But
not always...

>
-Tom.

I am running MS SQL 2000 server. *The table involved is only about
10,000 records. *But this is the behavior I am seeing.

The local machine is querying the table looking for a particular
record. *Everything works fine.
The remote amchine goes to clear the table and then insert all 10,000
records, into the table the following happens.

1) the local machines queries do not compilete until the remote
machine is done.
2) the remote machine can take up to 6 minutes to do these 10,000
insert operations. *So nothing on the local machine works right for
these 6 minutes.

I do not have access to the remote machines source to see what is
running but I am told it is simply a for loop with a insert query in
it. *Nothing of a locking natture.

Any idea the types of things I should look for to track this down? *I
found this by doing SQL profiler profiling and finding the remote
operations. *Turn these operatiiosn off and the local machine works
fine again, with no other action.

Thanks,
David- Hide quoted text -

- Show quoted text -

[Erland Sommarskog]

(qu*******@yahoo.com) writes:

So you are telling me INSERT in a loop like that will lock and unlock
the table repeatedly, but possibly because of the tightness of the loop
never let me in to get my stuff done.

That may be what Tom is saying, but, no, that it's not the way it works.

A single INSERT should only lock what is needed to insert the row. However,
if the remote process performs the DELETE and the INSERTs in a single
ransaction, the result is the same. As long as the inserted rows have
not been committed, you cannot read any rows from the table.

And, I like to add, nor are you interested in reading any rows. I don't
know what data this is about, or how much that changes when the remote
process refreshes the data. But if you were able to perform a SELECT
when the old rows have been deleted, and only 10 rows have been inserted,
you SELECT would probably come back empty. Which cannot be right.

So the remote process is probably right in keeping all in one transaction
and locking you out. But six minutes to load 10000 rows is not acceptable.

I don't think there is not much you can do on your side. Had you been on
SQL 2005, snapshot isolation could permit you retrieve the old version of
the data until the remote process has completed. But on SQL 2000, you only
work with the author of the remote process to see what it can do to
improve.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Jack Vamvas]

Without seeing exactly what your code /configuration is like: You could try
such things as :
1)BULK INSERT strategy.
2) use of TABLOCK
3)Potentially change to Simple Recovery (although I strongly warn you to
consider whether this is acceptable)
4)Break the BULK INSERTs into smaller chunks

--

Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com


<qu*******@yahoo.comwrote in message
news:31**********************************@p69g2000 hsa.googlegroups.com...

>I am running MS SQL 2000 server. The table involved is only about
10,000 records. But this is the behavior I am seeing.

The local machine is querying the table looking for a particular
record. Everything works fine.
The remote amchine goes to clear the table and then insert all 10,000
records, into the table the following happens.

1) the local machines queries do not compilete until the remote
machine is done.
2) the remote machine can take up to 6 minutes to do these 10,000
insert operations. So nothing on the local machine works right for
these 6 minutes.

I do not have access to the remote machines source to see what is
running but I am told it is simply a for loop with a insert query in
it. Nothing of a locking natture.

Any idea the types of things I should look for to track this down? I
found this by doing SQL profiler profiling and finding the remote
operations. Turn these operatiiosn off and the local machine works
fine again, with no other action.

Thanks,
David