List:MaxDB« Previous MessageNext Message »
From:Zhan Hua Ping Jack  Date:October 2 2007 1:56pm
Subject:delete takes too much time.
View as plain text  
 ====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/
Thread
delete takes too much time.Zhan Hua Ping Jack 2 Oct