Re: database slowness weirdness
Re: database slowness weirdness
- Subject: Re: database slowness weirdness
- From: James Cicenia <email@hidden>
- Date: Mon, 26 Apr 2004 20:59:21 -0500
Is there a way to use the database to differentiate between a read and
a write? The select shouldn't be locked out by the write. Also, a
simple write to the user table should be very quick. Hardly something
to lock up a database. If all else fails I guess you create a separate
table to maintain the audit information.
-James Cicenia
On Apr 26, 2004, at 4:47 PM, Jonathan Rochkind wrote:
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.
_______________________________________________
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.