aspose file tools*
The moose likes JDBC and the fly likes how to reduce time in deleting records Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "how to reduce time in deleting records" Watch "how to reduce time in deleting records" New topic
Author

how to reduce time in deleting records

Raju Sri
Ranch Hand

Joined: Mar 10, 2004
Posts: 108
Hi all,

I am using Prepared Statement to delete records from a table in Oracle 8i. My total number of records are 269818. The table have 15 columns in it.

I am using like below
*********************************
PreparedStatement psmtDel = null;
delSql="DELETE from CUSTOMER where id like 'ap%'";
psmtDel = con.prepareStatement(delSql);
boolean deleted = psmtDel.execute();
psmtDel.close();
**********************************

The above operation is taking 30-35 minutes. I want to reduce the timing . I tried even using Statment object and as well as Prepare Statement Batch execute command.But the result is almost same.

Pls let me know if any body can help me.

Cheers
Raju


SCJP 1.4<br />SCWCD 1.4<br />SCBCD 1.3<br />SCDJWS 1.4
Blake Minghelli
Ranch Hand

Joined: Sep 13, 2002
Posts: 331
The speed of that delete will depend more on your db then the Statement you are using to execute it, i.e. if you run the same delete as close as possible to the actual db (like a SQL editor tool most db vendors provide), you'll probably find the performance just as bad.

Here are a couple of factors that can influence the speed:
1. Number of records being affected. You mentioned 269,000 records but I'm assuing that is total records in the table, not total records to be deleted. But obviously it wil take the db considerably longer to delete 10,000 records vs. 10. How many records are you deleting?

2. Is you WHERE clause hitting on an index? If not, then the db is forced to do a full-table scan so in your case it would be scanning through 269,000 records. Most db vendors also provide a tool to show the "query plan" which can usually show you if it is hitting on an index.

3. Triggers. Remember that if there are any delete triggers on the table, then the db may be performing extra work for each record you delete. For example, if you have a delete trigger that performs some logging into another table, then for each record you delete, the db is actually executing 2 statements.

4. And of course there is always the hardware capabilities of the db server to consider.


Blake Minghelli<br />SCWCD<br /> <br />"I'd put a quote here but I'm a non-conformist"
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

using the like operator in your where clause is inherently slow as it does a full table scan... and that's a lot of tables to scan!

Try:

="DELETE from CUSTOMER where substr( id , 1, 2) = 'ap'";

not sure if this will work, but it may be able to still utilize the index on the id field.
Brahim Bakayoko
Ranch Hand

Joined: Aug 29, 2003
Posts: 155
Originally posted by Jamie Robertson:
using the like operator in your where clause is inherently slow as it does a full table scan... and that's a lot of tables to scan!

Try:

="DELETE from CUSTOMER where substr( id , 1, 2) = 'ap'";

not sure if this will work, but it may be able to still utilize the index on the id field.


Although I haven't tested it, I would say your way would be much slower than using LIKE.
Think of the mumber of temporary objects being created.

I have a table with 43,000 records and a full scan of it takes only a few hundreds milliseconds.

Clearly we have different environments and activities, but I am supprise that the operation takes more than a minute.

Do you know how many records match the parameter?


SCJP, SCWCD, SCBCD, IBM CSD WebSphere v5, <br />A+, MCP 2000 and 2000 server, CST, and few incompleted certification tracks.<br /> <br />Ivory Coast<br /> <br />Analyze your web Request/Response @ <a href="http://webtools.servehttp.com" target="_blank" rel="nofollow">http://webtools.servehttp.com</a> down for a while...
Loren Rosen
Ranch Hand

Joined: Feb 12, 2003
Posts: 156
In the special case where the wildcard for the LIKE is at the end, the Oracle optimizer will consider using an index to find the matching records (if a suitable index exists, and the index is selective enough to be useful).

In any case, it should be able to scan 300,000 records in something less than half an hour, so that's probably not the problem. (Though it wouldn't hurt to check the query plan, time a "select count(*)..." using the same where condition, etc.

One big question that hasn't been asked is whether the database has other traffic or not. Contention for locks or some other resource is at least one possible culprit.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: how to reduce time in deleting records
 
Similar Threads
How to delete a record from a table without a primary key
hql delete join
Removing duplicate records
SQL Insert and Delete at same time?
optimization of database