 | | | Subject: delete takes too much time. | Subject: delete takes too much time. 2007-10-02 - By Jack , Zhan Hua Ping
Back
====Long time ago=============
Mark wrote:
> I have my app almost complete on MaxDB. I have just run
> into a major snag
> and would appreciate any help you can offer.
>
> I have a table "Files" that has about 400,000 rows
>
> If I run queries like
> 1) SELECT * FROM FILES WHERE FILE_NAME = "THISFILE.TXT"
> OR
> 2) SELECT * FROM FILES WHERE FILE_ID = 687
>
> I get the results in a snap
>
> However If I try
>
> DELETE FROM FILES WHERE FILE_NAME = "THISFILE.TXT"
>
> This takes about 5 minutes. I thought it might be an index
> thing so I tried
>
> DELETE FROM FILES WHERE FILE_ID = 687
>
> and it still took over 5 minutes. FILE_ID is the PRIMARY KEY
> so I know it
> is already indexed. I don't think a delete should take this
> long. Please
> help.
Then Becker, Holger responded:
Did you check the possibility of lock collisions on the table.
You should have a look into system table lockstatistics.
Then Mark said:
Under the Information - Locks in the database manager I see
row Locks 312776
Table Locks 6487
These seem kind of high for what I'm doing. How do I prevent these from
occurring?
====================
However, the final result is unknown.
I have the similar problem. and the problem might not come from locks.
Since restart the database, and then i am sure no connection to it at all.
then i open SQL Studio to delete one row, it took 30 minutes to get done.
Is there anyone know what might be the problem?
How can i check the unique index on the primary key is ok?
I have check all indexes, even recreated them.
but i am not sure if the specific index on the primary key exists.
or is it relevant?
Thanks for your respond in advance!
Jack
__ ____ ____ ____ ____ ____ ____ ____ ____ ____ ____ ____ ____ __
Express yourself instantly with MSN Messenger! Download today it's FREE!
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
|
|
|