Firebird SQL Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me

The Shed is going Social! Join us on FaceBook and Twitter and chime in on the conversation.

Go Back   Dev Shed ForumsDatabasesFirebird SQL Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old May 21st, 2011, 02:12 AM
CrizaldoGordo CrizaldoGordo is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: May 2011
Posts: 1 CrizaldoGordo User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 36 m 26 sec
Reputation Power: 0
Problem after Deleting huge no. of rows in table

Hello guys, i have problem with my database, it takes time to insert into my table containing 5,300,000 of rows, so idecided to delete by "DELETE FROM MYTABLE" ,then the problem arise,i can't open my table and i can't even backup by database because when gbak tries to write the data of mytable( table that i delete all the rows) it halts. Any help, or guide to tune-up my database, and how to do deleting all the rows of my table.

and how i can optimize my "insert" in huge table

thank you.

Reply With Quote
  #2  
Old May 22nd, 2011, 06:55 PM
clivew clivew is offline
Contributing User
Dev Shed Regular (2000 - 2499 posts)
 
Join Date: Jan 2006
Location: Carlsbad, CA
Posts: 2,045 clivew User rank is Major (30000 - 40000 Reputation Level)clivew User rank is Major (30000 - 40000 Reputation Level)clivew User rank is Major (30000 - 40000 Reputation Level)clivew User rank is Major (30000 - 40000 Reputation Level)clivew User rank is Major (30000 - 40000 Reputation Level)clivew User rank is Major (30000 - 40000 Reputation Level)clivew User rank is Major (30000 - 40000 Reputation Level)clivew User rank is Major (30000 - 40000 Reputation Level)clivew User rank is Major (30000 - 40000 Reputation Level)clivew User rank is Major (30000 - 40000 Reputation Level) 
Time spent in forums: 1 Week 6 Days 2 h 37 m
Reputation Power: 382
Looks like you have corrupted your database somehow.
As you seem willing to remove all 5,300,000 rows, I suggest you use a DROP TABLE command and then re-create it rather than a DELETE FROM TABLE without a WHERE
clause.
Deleting 5,300,000 rows without a COMMIT until the end may be asking a lot.

I do not know why inserting would be a problem if your PK is reasonable unless there are
a lot of indexes and FKs to maintain.

Reply With Quote
  #3  
Old May 26th, 2011, 03:45 AM
mIRCata mIRCata is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Sep 2006
Location: Plovdiv. Bulgaria
Posts: 200 mIRCata User rank is Corporal (100 - 500 Reputation Level)mIRCata User rank is Corporal (100 - 500 Reputation Level)mIRCata User rank is Corporal (100 - 500 Reputation Level)mIRCata User rank is Corporal (100 - 500 Reputation Level) 
Time spent in forums: 2 Days 6 h 50 m 14 sec
Reputation Power: 11
My English is not so good to explain exactly what Firebird does with deleted records but it does some manipulation over them on first try to "read" them.
If you delete a record Firebird just mark it as old version or something. After that when it reads the data from the table and it found a record that is deleted and no longer used by any transaction Firebird perform sweep / mark the record as free space. Something like garbage collection. So when you delete a table and execute SELECT COUNT(*) FROM table after that it's very slow operation the first time because of that background garbage collection. Or when you do backup with garbage collection option - Firebird just clear the table from old records. And perhaps this is what slows you. 5M records are 5M records.

The only fast way to insert many records is to drop the indexes for that table, insert records and recreate the indexes for the table. Bu this is not an option in multi-user environment.
This is usable only if you close all other connections to the database, do the insert of the records and start the database again for the other users.

And you must DROP the indexes, not just disabling them, because even they are disabled Firebird do some background work with them when you manipulate the data in the table.

Last edited by mIRCata : May 26th, 2011 at 03:48 AM.

Reply With Quote
Reply

Viewing: Dev Shed ForumsDatabasesFirebird SQL Development > Problem after Deleting huge no. of rows in table

Developer Shed Advertisers and Affiliates



Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.

© 2003-2013 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap