Re: database slowness weirdness
Re: database slowness weirdness
- Subject: Re: database slowness weirdness
- From: Jonathan Rochkind <email@hidden>
- Date: Mon, 26 Apr 2004 16:47:52 -0500
I think that MS SQL Server may sometimes do table level locking even
when a better db woudln't have to. But in this case, maybe even a
better db would have to.
The more I think about it, the more I think I know the problem
though. Yes, lots of other stuff accesses this table. It is the
"User" table for my content management system, so of course it gets
lots of use. But in particular, I have one part of my app that does
a SELECT with sub-query, and it's querying this User table. The
sub-select is not to a single table, but in fact involves a join
itself. I have noticed that MS SQL Server does not perform well with
a sub-query which involves a join. In some cases, that query can take
10 or more seconds to execute (if the sub-query did not involve two
tables, but just involved one table, MS SQL Server does a lot better
with it).
So I put that in a WOLongResponse, figured okay, it might take a
while to execute, no big deal.
But now I'm worried that for as long as it takes to execute, nobody
else can get in to do an UPDATE to that table. And since an UPDATE
to that table is done every time someone logs in... that's bad. It
keeps people from logging in.
Okay, time to go and fix my logic so a sub-select is not needed. Too
bad, because this forces me to have some much messier code (there's a
lot of abstraction going on, unfortunately).
--Jonathan
At 2:26 PM -0700 4/26/04, Chuck Hill wrote:
Sounds like the db / your connection is doing table level locking
instead of row level locking. Or something is reading or updating
so much of that table that the entire thing is being locked. Is
anything else accessing the table?
On Apr 26, 2004, at 1:35 PM, Jonathan Rochkind wrote:
I guess there's something bad going on with db-level locking. The
db is locking out that update SQL because someone else is doing
something that requires a db-level lock. All underneath the
hood---none of my code explicitly aquires a lock, but maybe a lock
is implied by an 'update' whether you like it or not. I don't
really know what I'm talking about, I'm just guessing here, in case
anyone has anything useful to suggest.
_______________________________________________
webobjects-dev mailing list | email@hidden
Help/Unsubscribe/Archives: http://www.lists.apple.com/mailman/listinfo/webobjects-dev
Do not post admin requests to the list. They will be ignored.