This topic has been archived. It cannot be replied.
-
工作学习 / IT技术讨论 / I want to delete half a million records in a table in the sybase. I used set rowcount 5000 and delete.(For logs reason) It takes me one hour. Does anybody know the better way that I can save some times? Thanks a lot!!
-zsxabc(zsxabc);
2002-12-27
(#938959@0)
-
1.using correct indexes if there is where condition 2.make the SQL unrecoverable if you feel it appropriate 3.check if it is a cascade delete.
-kkkkkkkk(Toronto123);
2002-12-27
(#939509@0)
-
Thank you. What do you mean "make the SQL unrecoverable ", do not write log? And how can I do a "a cascade delete" in my program?
-zsxabc(zsxabc);
2002-12-28
(#940290@0)
-
yes,"unrecoverable" means making your transaction un-rollbackedyes,"unrecoverable" means making your transaction un-rollbacked
when the rows are the foreign key of other tables, you can delete them in cascad way. But all these i mentioned are based on Oracle. I think Sybase has similar feature.
-sharkmouth(鲨鱼嘴);
2002-12-28
{235}
(#941007@0)
-
It also depends on the number of connections to the table while you were deleting, the type of locks they had, etc.
-bnjmn(爱喝咖啡的牛);
2002-12-28
(#940253@0)
-
Thank you. At that time, maybe just my program is running. And now the cluster index is not on the where clause. So I do not know how can I improve the performance.
-zsxabc(zsxabc);
2002-12-28
(#940293@0)
-
1 hour, i think that's reasonablebut, if the rest record number is small, you may select them into another table, and truncate the old table.
-dibert(呆伯);
2002-12-28
{108}
(#940270@0)
-
and, you may post the question to some sybase forums, you can get more professional answer there.news://forums.sybase.com
-dibert(呆伯);
2002-12-28
{24}
(#940282@0)
-
Thank you. Could please you know any sybase forums? The left records are three times of the deleted one. About 1.5million records.
-zsxabc(zsxabc);
2002-12-28
(#940299@0)
-
http://sybase.com/support/newsgroups
-blackswan(uglyduck);
2002-12-28
(#940520@0)
-
ok, if it's 1.5 million, then i think 1 hour is the time you must spend :-)a good way i suggest is to delete each record in a seperate transcation. (according to a id, increase it one by one, or something else), this can avoid big lock, and your other programs can still run smoothly. (don't forget to truncate your log, it will increase quickly)
-dibert(呆伯);
2002-12-28
{272}
(#940716@0)
-
Thank you guys! Thank you for your good advices and informations!
-zsxabc(zsxabc);
2002-12-28
(#940763@0)