|
|
Posted by Tom van Stiphout on 01/26/08 03:25
On Fri, 25 Jan 2008 15:02:52 -0800 (PST), quincy451@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
Navigation:
[Reply to this message]
|