Need help with deleting 20 million rows out of a l
Hi gurus, My initial deletion test has indicated that it would take 4 to 5 days to purge all these records out using multiple stored procedures. I believe Art's has a much better way/tool or script that is much faster, where can I get it? does it require esql because I don't have this? (thanks in advance Art!). 2nd question, which one that is considered faster operation, delete or insert? Thanks for your inputs.
Kern Doe said: > Hi gurus, > My initial deletion test has indicated that it would take 4 to 5 days to > purge > all these records out using multiple stored procedures. I believe Art's > has a > much better way/tool or script that is much faster, where can I get it? dbdelete in utils2_ak: ftp://ftp.iiug.org/pub/informix/pub/utils2_ak.gz > does > it require esql because I don't have this? (thanks in advance Art!). It does require ESQL/C, but you can download the client SDK for free from here: http://www14.software.ibm.com/webapp/download/search.jsp?rs=ifxdl > 2nd question, which one that is considered faster operation, delete or > insert? It depends. -- Bye now, Obnoxio "There were a myriad of problems which conspired to corrupt your reason and rob you of your common sense. Fear got the best of you, and in your panic you turned to the Labour Party. They promised you order, they promised you peace, and all they demanded in return was your silent, obedient consent."
If the number of deletes is large - say over 10-20% of the entire table - it is probably faster to rewrite the table. Cleanre in the long run too - you can even put in fragmentation so that the next time you have to do this you can just detach fragments (then drop the resulting table). j. >From: Kern Doe <kern_doe@yahoo.com> >Date: 2008/06/11 Wed PM 02:23:53 CDT >To: ids@iiug.org >Subject: Need help with deleting 20 million rows out of.... [12396] >Hi gurus, >My initial deletion test has indicated that it would take 4 to 5 days to purge >all these records out using multiple stored procedures. I believe Art's has a >much better way/tool or script that is much faster, where can I get it? does >it require esql because I don't have this? (thanks in advance Art!). >2nd question, which one that is considered faster operation, delete or insert? >Thanks for your inputs. > > >******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum.
Thank you Obnoxio! Your answer to my last question was short but meaningful in a way. Can I safely say the statement "delete is a lot faster than insert" is NOT true? Thanks again. ----- Original Message ---- From: Obnoxio The Clown <obnoxio@serendipita.com> To: ids@iiug.org Sent: Wednesday, June 11, 2008 4:20:46 PM Subject: Re: Need help with deleting 20 million rows ou.... [12397] Kern Doe said: > Hi gurus, > My initial deletion test has indicated that it would take 4 to 5 days to > purge > all these records out using multiple stored procedures. I believe Art's > has a > much better way/tool or script that is much faster, where can I get it? dbdelete in utils2_ak: ftp://ftp.iiug.org/pub/informix/pub/utils2_ak.gz > does > it require esql because I don't have this? (thanks in advance Art!). It does require ESQL/C, but you can download the client SDK for free from here: http://www14.software.ibm.com/webapp/download/search.jsp?rs=ifxdl > 2nd question, which one that is considered faster operation, delete or > insert? It depends. -- Bye now, Obnoxio "There were a myriad of problems which conspired to corrupt your reason and rob you of your common sense. Fear got the best of you, and in your panic you turned to the Labour Party. They promised you order, they promised you peace, and all they demanded in return was your silent, obedient consent." ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Kern Doe said: > Thank you Obnoxio! > Your answer to my last question was short but meaningful in a way. > Can I safely say the statement > "delete is a lot faster than insert" is NOT true? Physically, I'd be surprised if there was a significant difference, I suppose it depends on the length of the row. If the rowlength is less than a page, I'd guess that an insert and a delete would take the same amount of time. If the rowlength is greater than a page, I'd expect an insert to take longer. But I'm probably talking rubbish, you'd be better off getting an answer from someone who knows what he's talking about. -- Bye now, Obnoxio "There were a myriad of problems which conspired to corrupt your reason and rob you of your common sense. Fear got the best of you, and in your panic you turned to the Labour Party. They promised you order, they promised you peace, and all they demanded in return was your silent, obedient consent."