Main   Products   Offshore Outsourcing   Customers   Partners   ContactUs  
JDBC Databases
  HXTT Access v5.2
  HXTT Cobol v2.1
  HXTT DBF v5.2
 
  Buy Now
  Support
  Download
  Document
  FAQ
  HXTT Excel v4.2
  HXTT Paradox v5.2
  HXTT Text(CSV) v5.2
  HXTT XML v1.2
Offshore Outsourcing
Oracle Data Import/Export
DB2 Data Import/Export
Sybase Data Import/Export
Free Resources
  Firewall Tunneling
  Search Indexing Robot
  Conditional Compilation
  Password Recovery for MS Access
  Password Recovery for Corel Paradox
  Checksum Tool for MD5
  Character Set Converter
  Pyramid - Poker of ZYH
   
   
   
Hongxin Technology & Trade Ltd. of Xiangtan City (abbr, HXTT)

HXTT DBF
delete statement need hours with million records
arya
2006-03-22 00:00:00.0
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.0
# 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.0
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.0
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.0
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.0
thank's a lot for your best support.

then i'll download the latest package and try your suggestion

regards,
arya

Search Key   Search by Last 50 Questions




Google
 

Address: 9 Station Rd., Xiangtan City, Hunan Province, P.R. China
Postcode: 411100
Phone: (86)731-58225727
Fax: (86)731-58225727
Email: webmaster@hxtt.com
Copyright © 1999-2011 Hongxin Technology & Trade Ltd. | All Rights Reserved. | Privacy | Legal | Sitemap