Hi All,
I am getting strange situation. These r the steps I have followed:
1. Created an EMPLOYEE table with around 14 fields & 688038 records.
(so a large table indeed).
2. Tried to delete all the rows in the table using the traditional
DELETE FROM EMPLOYEE stmt. It is taking around 53 secs to delete all
the records.
So I have done the below steps to make it fatster:
(i) Create an empty file called No_Data.DEL in the C:\ drive.
(Location & name of file doesn't matter , BUT it shud be an empty
file.)
(ii) Executed the following stmt: IMPORT FROM C:\No_Data.DEL OF DEL
REPLACE INTO Employee.
This stmt took less that 1 sec to delete all the data in the Employee
table.
THIS IS THE FASTER APROACH TO DELETE RECORDS FROM A TABLE.
But I wnat to know ,how did it happen so? Why did the IMPORT stmt take
that much less time to delete all the records , which the traditional
DELETE took a large amount of time.
Thanx in advance....
Awaiting ur replies,
Satish.. 6 11257
On Mar 20, 8:30 am, "satish mullapudi" <satishmullapud ...@gmail.com>
wrote:
Hi All,
I am getting strange situation. These r the steps I have followed:
1. Created an EMPLOYEE table with around 14 fields & 688038 records.
(so a large table indeed).
2. Tried to delete all the rows in the table using the traditional
DELETE FROM EMPLOYEE stmt. It is taking around 53 secs to delete all
the records.
So I have done the below steps to make it fatster:
(i) Create an empty file called No_Data.DEL in the C:\ drive.
(Location & name of file doesn't matter , BUT it shud be an empty
file.)
(ii) Executed the following stmt: IMPORT FROM C:\No_Data.DEL OF DEL
REPLACE INTO Employee.
This stmt took less that 1 sec to delete all the data in the Employee
table.
THIS IS THE FASTER APROACH TO DELETE RECORDS FROM A TABLE.
But I wnat to know ,how did it happen so? Why did the IMPORT stmt take
that much less time to delete all the records , which the traditional
DELETE took a large amount of time.
Thanx in advance....
Awaiting ur replies,
Satish..
You could do this:
ALTER TABLE EMPLOYEE NOT LOGGED INITIALLY WITH EMPTY TABLE;
This really works. Here in this cmd u r specifying to replace with an
EMPTY TABLE and that too WITHOUT LOGGING. So, the operation is
performed Faster. But in the one am asking using the IMPORT utility,
can u plz say why it is done faster than the normal DELETE operation.
--
Satish.
Otto Carl Marte wrote:
On Mar 20, 8:30 am, "satish mullapudi" <satishmullapud ...@gmail.com>
wrote:
Hi All,
I am getting strange situation. These r the steps I have followed:
1. Created an EMPLOYEE table with around 14 fields & 688038 records.
(so a large table indeed).
2. Tried to delete all the rows in the table using the traditional
DELETE FROM EMPLOYEE stmt. It is taking around 53 secs to delete all
the records.
So I have done the below steps to make it fatster:
(i) Create an empty file called No_Data.DEL in the C:\ drive.
(Location & name of file doesn't matter , BUT it shud be an empty
file.)
(ii) Executed the following stmt: IMPORT FROM C:\No_Data.DEL OF DEL
REPLACE INTO Employee.
This stmt took less that 1 sec to delete all the data in the Employee
table.
THIS IS THE FASTER APROACH TO DELETE RECORDS FROM A TABLE.
But I wnat to know ,how did it happen so? Why did the IMPORT stmt take
that much less time to delete all the records , which the traditional
DELETE took a large amount of time.
Thanx in advance....
Awaiting ur replies,
Satish..
You could do this:
ALTER TABLE EMPLOYEE NOT LOGGED INITIALLY WITH EMPTY TABLE;
satish mullapudi wrote:
Hi All,
I am getting strange situation. These r the steps I have followed:
1. Created an EMPLOYEE table with around 14 fields & 688038 records.
(so a large table indeed).
Not really that large...
2. Tried to delete all the rows in the table using the traditional
DELETE FROM EMPLOYEE stmt. It is taking around 53 secs to delete all
the records.
So I have done the below steps to make it fatster:
(i) Create an empty file called No_Data.DEL in the C:\ drive.
(Location & name of file doesn't matter , BUT it shud be an empty
file.)
(ii) Executed the following stmt: IMPORT FROM C:\No_Data.DEL OF DEL
REPLACE INTO Employee.
It's ever simpler to do:
IMPORT FROM NUL OF DEL REPLACE INTO ...
Then you don't have to create the empty file.
This stmt took less that 1 sec to delete all the data in the Employee
table.
THIS IS THE FASTER APROACH TO DELETE RECORDS FROM A TABLE.
But I wnat to know ,how did it happen so? Why did the IMPORT stmt take
that much less time to delete all the records , which the traditional
DELETE took a large amount of time.
When a row is deleted in a table, DB2 has to log the data change. This is
necessary because you may issue a ROLLBACK at EOT, and then DB2 must be
able to restore the table to the state it was before the DELETE (actually
to the state it was at BOT). So all the data of the deleted rows is needed
somehow. With a lot of rows, you get a lot of log records being written.
The IMPORT/REPLACE does not log each row. It writes a single table
truncation log record, which is much faster.
The alternative to use NOT LOGGED INITIALLY WITH EMPTY TABLE also avoids the
logging and, thus, is much faster than a DELETE statement.
--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
Thanx Knut for ur explaination.
satish...
Knut Stolze wrote:
satish mullapudi wrote:
Hi All,
I am getting strange situation. These r the steps I have followed:
1. Created an EMPLOYEE table with around 14 fields & 688038 records.
(so a large table indeed).
Not really that large...
2. Tried to delete all the rows in the table using the traditional
DELETE FROM EMPLOYEE stmt. It is taking around 53 secs to delete all
the records.
So I have done the below steps to make it fatster:
(i) Create an empty file called No_Data.DEL in the C:\ drive.
(Location & name of file doesn't matter , BUT it shud be an empty
file.)
(ii) Executed the following stmt: IMPORT FROM C:\No_Data.DEL OF DEL
REPLACE INTO Employee.
It's ever simpler to do:
IMPORT FROM NUL OF DEL REPLACE INTO ...
Then you don't have to create the empty file.
This stmt took less that 1 sec to delete all the data in the Employee
table.
THIS IS THE FASTER APROACH TO DELETE RECORDS FROM A TABLE.
But I wnat to know ,how did it happen so? Why did the IMPORT stmt take
that much less time to delete all the records , which the traditional
DELETE took a large amount of time.
When a row is deleted in a table, DB2 has to log the data change. This is
necessary because you may issue a ROLLBACK at EOT, and then DB2 must be
able to restore the table to the state it was before the DELETE (actually
to the state it was at BOT). So all the data of the deleted rows is needed
somehow. With a lot of rows, you get a lot of log records being written.
The IMPORT/REPLACE does not log each row. It writes a single table
truncation log record, which is much faster.
The alternative to use NOT LOGGED INITIALLY WITH EMPTY TABLE also avoids the
logging and, thus, is much faster than a DELETE statement.
--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
Knut Stolze wrote:
>
The alternative to use NOT LOGGED INITIALLY WITH EMPTY TABLE also avoids the
logging and, thus, is much faster than a DELETE statement.
Beware, though, because doing this will affect your ability to recover a
database to a point in time. That is why some people prefer using IMPORT.
On Mar 21, 4:06 am, Ian <ianb...@mobile audio.comwrote:
Knut Stolze wrote:
The alternative to use NOT LOGGED INITIALLY WITH EMPTY TABLE also avoids the
logging and, thus, is much faster than a DELETE statement.
Beware, though, because doing this will affect your ability to recover a
database to a point in time. That is why some people prefer using IMPORT.
when it cames to delete parts of 300 millions records , i can't tell
which approach is better .
1. alter table ... activate not logged initially ; delete ...
(affect recovery)
2. export&truncate &import (need more file spaces and seems ugly)
3. traditional delete ( slow and have to worry about db log space )
4. build a procedure using a cursor to control the commitcount
( slove db log space problem , but slowest) This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Alvin |
last post by:
Hi All
I need opinions on how to approach my task.
I currently have 3 tables: the master table, the archive and a temp
table.
MASTER: has 3 fields ProductID and ProductNo and Released
ARCHIVE: Has 3 ProductID, ProductNo, SoldDate
TEMP: ProductID, ProductNo, SoldDate
I have a trigger on the master table upon deletion to archive. This is
|
by: DaveDiego |
last post by:
I've had a user delete one of the client records, I do have a version of
the DB with all records intact before the deletion occured.
Whats the best approach to getting all the related records in each of
the tables? I have about 12 tables to put data back into and multiple
records for each. Would I need to make an append or update query for...
|
by: Paul T. Rong |
last post by:
Dear all,
Here is my problem:
There is a table "products" in my access database, since some of the
products are out of date and stopped manufacture, I would like to delete
those PRODUCTS from the table, but I was not allowed to do that, because
"there are records related with those PRODUCTS in other tables (e.g. in
table "ORDER_DETAIL").
|
by: Danny Dy |
last post by:
Hi To All,
I always write My VBA code in SQL(see Example).
Private Sub cbxAEName_NotInList(NewData As String, Response As Integer)
Dim stSQL as String
Dim strMsg As String
strMsg = "'" & NewData & "' is not an available AE Name " & vbCrLf &
vbCrLf
|
by: Yisroel Markov |
last post by:
Greetings,
I have this database I converted from Access 97 to Access 2000. Front
end on the PC, back end on the server. It works fine, except for one
thing: one of the reports is extremely slow (20-25 minutes to process
less than a thousand records), and even crashes Access sometimes. Yet
another very similar report runs almost instantly.
...
| |
by: Dave |
last post by:
(My apologies for posting this on two forums. I have just found out the other
one was the incorrect location)
I am writing a VB.NET 2003 web application to operate on my company's
intranet. It accesses data in an SQL Server database. I have developed a
couple of pages that display data successfully. However, there is one area
that I am...
|
by: Cornelius Buschka |
last post by:
Hi,
we saw the following problem:
We deleted all rows from a table B referencing table A (~500000 records). No
problem, but the following try to delete all records from table A (~180000) lead
to a "never ending" statement. We found out, that vacuuming table B after delete
did the trick.
It seems to us the database has to do scan thru...
|
by: scott.tang |
last post by:
I'm experiencing a very strange problem. My application is MS Access
front-end and MS SQL server back-end database. I have a SQL statement
that deletes records from a table after an export process. The
problem is occasionally when the delete statement is executed, these
records no longer display on List Box (not even in the MS Access link...
|
by: Andy |
last post by:
To further follow up on my last post regarding the docmd.quit vs.
Application.quit using access 2007, I noticed that docmd.quit will correctly
compact the database (program file) if you have the "Compact on Close"
option set for the current database.
However, even with Compact on Close set, if you use the Application.Quit
acQuitSaveNone,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |