|
|
Heng Xing Tian Tai Lab of Xi'an City (abbr, HXTT)
HXTT DBF
|
delete statement need hours with million records |
arya |
2006-03-22 00:00:00 |
i used hxtt driver for performing dbf operations, so far working well except it takes hours (or maybe days) to execute delete/insert with million datas. In fact i never seen its result because it takes more than 20 hours to perform delete some records of 1800907 datas. So, i don't know, the action finish completely with hours or it just never ending process ?
below i post my code :
import java.util.*;
import java.sql.*;
import java.io.*;
import com.hxtt.sql.*;
public class TestDbf {
Connection connection;
/** Creates a new instance of TestDbf */
public TestDbf(String path) {
Properties prop = new Properties();
prop.setProperty("user", "");
prop.setProperty("OtherExtensions","true");
prop.setProperty("Version Number", "03");
try {
Class.forName("com.hxtt.sql.dbf.DBFDriver").newInstance();
connection = DriverManager.getConnection("jdbc:DBF:///"+path, prop);
}
catch(SQLException sqx) {
System.out.println("SQL Error when connecting to database");
sqx.printStackTrace();
}
catch (ClassNotFoundException cfx) {
System.out.println("could ot find HXTT class, make sure the classpath have been defined in your system !");
cfx.printStackTrace();
}
catch(Exception e) {
e.printStackTrace();
}
}
public void createIndex(String tablename, String colname, String idxname) {
try {
Statement stmt = connection.createStatement();
String sqlIndex = "create index "+idxname+" on \""+tablename+"\" ("+colname+")";
boolean isCreated = stmt.execute(sqlIndex);
stmt.close();
}
catch(SQLException sqx) {
sqx.printStackTrace();
}
catch(Exception e) {
e.printStackTrace();
}
}
public void deleteData(String tablename) {
try {
Statement stmt = connection.createStatement();
String sql = "delete from \""+tablename+"\" where exists (select 'x' from pp where trim(wcode)='54' and kdpp =
kdppx)";
boolean isDeletedKdpp1 = stmt.execute(sql);
stmt.close();
}
catch(SQLException sqx) {
sqx.printStackTrace();
}
catch(Exception e) {
e.printStackTrace();
}
}
public static void main(String[] arg) {
TestDbf dbf = new TestDbf("C:\\TestDbf");
dbf.createIndex("71280206.PH8", "kdppx", "KDPPIDX71280206");
dbf.deleteData("71280206.PH8");
}
}
in above code, user just perform creating index and deleting data which involved two tables,
pp.dbf (6045 records) and 71280206.PH8 (1800907 records). SQL Statement for delete action is just :
"delete from "71280206.PH8" where exists (select 'x' from pp where trim(wcode)='54' and kdpp = kdppx)".
why this action needs hours or days or hang ? (after it ran 20 hours i closed the application, so i don't know exactly when the application will finish completely).
regards,
arya
|
Re:delete statement need hours with million records |
HXTT Support |
2006-03-22 00:00:00 |
# v2.1.125 uses automatic temporary index to quicken join sql.
# v2.1.122 optimizes IN, NOT IN, ALL, and ANY on subquery.
so those operations is in second level.
But we haven't optimized exists for delete sql. We will begin to do that task soon. That new feature should be completed in 48 hours. Thanks for your patience.
|
Re:Re:delete statement need hours with million records |
arya |
2006-03-22 00:00:00 |
thanks for your quick reply, my user wait me for solution so do i.
----------------------------------------------------------------------------------------------
# v2.1.125 uses automatic temporary index to quicken join sql.
# v2.1.122 optimizes IN, NOT IN, ALL, and ANY on subquery.
so those operations is in second level.
----------------------------------------------------------------------------------------------
i don't understand above statements, please can you explain to me again clearly ?
if exists statement is not yet optimized, can you give me suggestion for another sql statements ?
do you have ftp server ? i think it will be better if i give you the sample file, so you can test it well. The file size is more than 100 mb, so i can't attach it via email.
regards,
arya
|
Re:Re:Re:delete statement need hours with million records |
HXTT Support |
2006-03-22 00:00:00 |
It's copied from HXTT Released Version Log at http://www.hxtt.com/dbf/history.html .
Your sql is enough explicable. We needn't your dbf sample since we can produce 2000000 simulated rows by program.
>if exists statement is not yet optimized, can you give me suggestion for
>another sql statements ?
We will optimized it and released a new package in 48 hours. You will get an email notification for our speed test report. Thanks for your patience.
|
Re:Re:Re:Re:delete statement need hours with million records |
HXTT Support |
2006-03-22 00:00:00 |
First, you should remove
dbf.createIndex("71280206.PH8", "kdppx", "KDPPIDX71280206");
because the index on delete destination table will only slow delete speed, and that where clause hasn't any relation with that index. The correct index should be on kdpp column of pp table.
Secondly, please download the latest package which has enabled automatic temporary index to quicken subquery sql, so that you needn't to build index on kdpp column of pp table too.
According to our test:
pp table: 20,000 rows without any index file
71280206.PH8 table: 2,000,000 rows without any index file
sql: delete from "71280206.PH8" where exists (select 'x' from pp where trim(wcode)='54' and kdpp =kdppx)
test result: deleted 108000 rows in 41625ms.
|
Re:Re:Re:Re:Re:delete statement need hours with million records |
arya |
2006-03-23 00:00:00 |
thank's a lot for your best support.
then i'll download the latest package and try your suggestion
regards,
arya
|
|
|
Main
webmaster@hxtt.com