Re: Rmote database inserts blocking local queries.

On Fri, 25 Jan 2008 20:59:06 -0800 (PST), quincy451@xxxxxxxxx wrote:

My gut feeling is you should try to improve the Insert side of the
equation, not the Select side.
BCP or other bulk inserts (a proper DTS package, for example) of 10000
rows should happen in a few seconds, not in 360 of them.

-Tom.




On Jan 25, 10:38 pm, quincy...@xxxxxxxxx wrote:
On Jan 25, 9:25 pm, Tom van Stiphout <no.spam.tom7...@xxxxxxx> wrote:

On Fri, 25 Jan 2008 15:02:52 -0800 (PST), quincy...@xxxxxxxxx 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.

Ok I found this reference in the documentation. I am wondering if
locking hints on
the select query on the local side my help this?




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 -- Hide quoted text -

- Show quoted text -
.

Relevant Pages

  • Re: Rmote database inserts blocking local queries.
    ...   Insert One Row ... So you are telling me INSERT in a loop like that will lock and unlock ... The remote amchine goes to clear the table and then insert all 10,000 ...  Turn these operatiiosn off and the local machine works ...
    (comp.databases.ms-sqlserver)
  • Re: Creating remote objects
    ... >> This creates an instance of Word on the local machine from Excel 2000, ... I don't have a remote server to go against. ... >> (ByVal lpszIID As String, ByVal piid As Long) As Long ... >> Dim rclsid As GUID ...
    (microsoft.public.excel.programming)
  • remote file copy using Net::Telnet
    ... " copies file from the local machine to the remote machine " ... print("Your programme will be terminated\n"); ... # fetching file from remote host to local machine ...
    (comp.lang.perl.modules)
  • Re: Configuring networked printer
    ... OK - have fixed the old issue re: the OOo configuration printing - ... just required me to stop and restart the print server:) ... in the CUPS web interface on the "remote" machine (the machine ... On your local machine, you need to have the check box named "Show ...
    (Debian-User)
  • Re: Configuring networked printer
    ... setup you local machine to use the printer on he remote machine. ... I have now set the "share" advertising checkbox as you suggested, ... On your local machine, you need to have the check box named "Show ... I have done this on Box B and get the following when the server ...
    (Debian-User)