|
|
Posted by quincy451 on 01/26/08 04:59
On Jan 25, 10:38 pm, quincy...@yahoo.com wrote:
> On Jan 25, 9:25 pm, Tom van Stiphout <no.spam.tom7...@cox.net> wrote:
>
> > On Fri, 25 Jan 2008 15:02:52 -0800 (PST), quincy...@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
> 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 -
Navigation:
[Reply to this message]
|