How long should it take to drop a database?
Poster asked why dropping a 3GB test database (a PeopleSoft schema with 20,000+ mostly empty tables) took over 30 minutes. Replies said drop time depends mainly on the number of objects, not data size; smartblobs were ruled out. Another DBA with PeopleSoft databases reported the same slowness and worked around it by scripting a drop of all tables first, then dropping the database. Truncating tables first was also suggested, though one responder thought truncate and drop perform about the same and advised timing it. No single definitive fix was confirmed.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Is it a database size/objects dependent factor? Does anyone know? (Assuming there's no user connecting to it of course). Our test database is about 3GB but it takes more than 30 strange minutes to drop. Thanks.
Are you using smartblobs? From: "Kern Doe" <kern_doe@yahoo.com> To: ids@iiug.org Date: 11/18/2010 12:54 PM Subject: How long should it take to drop a database? [21981] Sent by: ids-bounces@iiug.org Is it a database size/objects dependent factor? Does anyone know? (Assuming there's no user connecting to it of course). Our test database is about 3GB but it takes more than 30 strange minutes to drop. Thanks. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
No blob whatever types data just 20 thousand plus tables and most of them are empty (People Soft database). ----- Original Message ---- From: Madison Pruet <mpruet@us.ibm.com> To: ids@iiug.org Sent: Thu, November 18, 2010 10:02:15 AM Subject: Re: How long should it take to drop a database? [21982] Are you using smartblobs? From: "Kern Doe" <kern_doe@yahoo.com> To: ids@iiug.org Date: 11/18/2010 12:54 PM Subject: How long should it take to drop a database? [21981] Sent by: ids-bounces@iiug.org Is it a database size/objects dependent factor? Does anyone know? (Assuming there's no user connecting to it of course). Our test database is about 3GB but it takes more than 30 strange minutes to drop. Thanks. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
I think it's just the number of objects ... My PS databases take forever to drop ... so, what I ended up doing is writing a script to drop all the tables .. then the db ... works faster .... Peter Peter Logan Senior Database Administrator Phone: 616/878-8309 From: "Kern Doe" <kern_doe@yahoo.com> To: ids@iiug.org Date: 11/18/2010 10:58 AM Subject: Re: How long should it take to drop a database? [21984] Sent by: ids-bounces@iiug.org No blob whatever types data just 20 thousand plus tables and most of them are empty (People Soft database). ----- Original Message ---- From: Madison Pruet <mpruet@us.ibm.com> To: ids@iiug.org Sent: Thu, November 18, 2010 10:02:15 AM Subject: Re: How long should it take to drop a database? [21982] Are you using smartblobs? From: "Kern Doe" <kern_doe@yahoo.com> To: ids@iiug.org Date: 11/18/2010 12:54 PM Subject: How long should it take to drop a database? [21981] Sent by: ids-bounces@iiug.org Is it a database size/objects dependent factor? Does anyone know? (Assuming there's no user connecting to it of course). Our test database is about 3GB but it takes more than 30 strange minutes to drop. Thanks. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Hello. You didn´t mention, but if your engine version is newer (ex 11.50 or greater) you could use the truncate, instead of delete tables, ok? Then after all, you drop the database. That´s obviously much much faster ;) Best regards.
Thanks Peter. Your input confirms that there isn't any thing wrong with the test database we have, it's just the "People Soft" type of db and that's just how long it would take unless we deal with this differently, for example, dropping tables .... ----- Original Message ---- From: "Peter_Logan@spartanstores.com" <Peter_Logan@spartanstores.com> To: ids@iiug.org Sent: Thu, November 18, 2010 11:09:49 AM Subject: Re: How long should it take to drop a database? [21985] I think it's just the number of objects ... My PS databases take forever to drop ... so, what I ended up doing is writing a script to drop all the tables .. then the db ... works faster .... Peter Peter Logan Senior Database Administrator Phone: 616/878-8309 From: "Kern Doe" <kern_doe@yahoo.com> To: ids@iiug.org Date: 11/18/2010 10:58 AM Subject: Re: How long should it take to drop a database? [21984] Sent by: ids-bounces@iiug.org No blob whatever types data just 20 thousand plus tables and most of them are empty (People Soft database). ----- Original Message ---- From: Madison Pruet <mpruet@us.ibm.com> To: ids@iiug.org Sent: Thu, November 18, 2010 10:02:15 AM Subject: Re: How long should it take to drop a database? [21982] Are you using smartblobs? From: "Kern Doe" <kern_doe@yahoo.com> To: ids@iiug.org Date: 11/18/2010 12:54 PM Subject: How long should it take to drop a database? [21981] Sent by: ids-bounces@iiug.org Is it a database size/objects dependent factor? Does anyone know? (Assuming there's no user connecting to it of course). Our test database is about 3GB but it takes more than 30 strange minutes to drop. Thanks. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Great tip Alexandre! I however never try to time "drop table" versus "truncate table" -- so it will be interesting to find out, but I do believe truncate is faster. ----- Original Message ---- From: ALEXANDRE MARINI <amarini@fazenda.ms.gov.br> To: ids@iiug.org Sent: Thu, November 18, 2010 11:43:49 AM Subject: Re: How long should it take to drop a database? [21986] Hello. You didn´t mention, but if your engine version is newer (ex 11.50 or greater) you could use the truncate, instead of delete tables, ok? Then after all, you drop the database. That´s obviously much much faster ;) Best regards. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
About the same, but it never hurts to test it yourself. I've never tried with a multi-thousand table database. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Nov 18, 2010 at 11:54 AM, Kern Doe <kern_doe@yahoo.com> wrote: > Great tip Alexandre! > I however never try to time "drop table" versus "truncate table" -- so it > will > be interesting to find out, but I do believe truncate is faster. > > ----- Original Message ---- > From: ALEXANDRE MARINI <amarini@fazenda.ms.gov.br> > To: ids@iiug.org > Sent: Thu, November 18, 2010 11:43:49 AM > Subject: Re: How long should it take to drop a database? [21986] > > Hello. > You didn´t mention, but if your engine version is newer (ex 11.50 or > greater) > you could use the truncate, instead of delete tables, ok? Then after all, > you > drop > the database. > That´s obviously much much faster ;) > > Best regards. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --20cf3054a719f4c3c2049556b1ca