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
problem with low performance querys
Carlos Alva
2006-05-13 11:48:32.0
I have a fox pro table r_kardex.dbf with structural index r_kardex.cdx, the index has 4 tags, the third tag expression is kxseccion+str(kxlibro,4)+str(kxfolio,3)+str(kxinscrip,4), the table has 1,820,000 records.

When I executed this query

Select kxnombre
from r_kardex
where kxseccion+str(kxlibro,4)+str(kxfolio,3)+str(kxinscrip,4) = '12151 3 24'

I recived the result set after 2 o 3 minutes, when I proccess this in fox pro, the resul set is ready in just 1 or 2 seconds, can you help me?? Tahnks a lot.
Re:problem with low performance querys
HXTT Support
2006-05-13 23:27:57.0
It seems that HXTT DBF faile to utilize index to quicken query in your case. According to our test for v2.2.036, it's normal to return all rows in 2s. Please download the latest package and try again. If your issue exists still, please let us know.

/* A simple test for query on r_kardex table(2000000 rows)
Tested with AMD2500 on 2006-05-14
Select kxnombre from r_kardex where kxseccion+str(kxlibro,4)+str(kxfolio,3)+str(kxinscrip,4) = '12151 3 24'"
Spent time/row count:234ms/61
Spent time/row count:16ms/61
*/
import java.sql.*;
import java.util.Properties;

public class testDBFSpeed2{
public static void main(String argv[]){
try{
Class.forName("com.hxtt.sql.dbf.DBFDriver");


String url="jdbc:dbf:////d:/test";

Properties properties=new Properties();

Connection con = DriverManager.getConnection(url,properties);

Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
String sql="create table if not exists r_kardex (kxseccion char(1),kxlibro numeric(4,0),kxfolio numeric(3,0), kxinscrip numeric(4,0), kxnombre varchar(10)); "
;
boolean initDataBaseFlag=stmt.executeUpdate(sql)>0;
stmt.close();

if(initDataBaseFlag){
sql="insert into r_kardex (kxseccion,kxlibro,kxfolio,kxinscrip,kxnombre) values(?,?,?,?,?);";
PreparedStatement pstmt = con.prepareStatement(sql);
for(int i=0;i<2000000;i++){
pstmt.setString(1,((char)('0'+(i%10)))+"");
pstmt.setInt(2,2000+(i%1000));
pstmt.setInt(3,i%12);
pstmt.setInt(4,i%33);
pstmt.setString(5,"abc"+i);
pstmt.executeUpdate();
}
pstmt.executeUpdate("create index aaa,bbb,ccc,ddd of r_kardex.cdx on r_kardex (kxseccion,kxlibro,kxseccion+str(kxlibro,4)+str(kxfolio,3)+str(kxinscrip,4),kxinscrip);");


pstmt.close();
}


sql="Select kxnombre from r_kardex where kxseccion+str(kxlibro,4)+str(kxfolio,3)+str(kxinscrip,4) = '12151 3 24'";
PreparedStatement pstmt = con.prepareStatement(sql);
for(int i=0;i<2;i++){
long currentTime=System.currentTimeMillis();
ResultSet rs = pstmt.executeQuery(sql);
rs.beforeFirst();
int rowCount=0;
while (rs.next()) {
rs.getObject(1);
rowCount++;
}
currentTime=System.currentTimeMillis()-currentTime;

rs.close();
System.out.println("Spent time/row count:" +currentTime+"ms/"+rowCount );
}

pstmt.close();

con.close();


}
catch( SQLException sqle )
{
do
{
System.out.println(sqle.getMessage());
System.out.println("Error Code:"+sqle.getErrorCode());
System.out.println("SQL State:"+sqle.getSQLState());
sqle.printStackTrace();
}while((sqle=sqle.getNextException())!=null);
}
catch( Exception e )
{
System.out.println(e.getMessage());
e.printStackTrace();
}
}
}

Re:Re:problem with low performance querys
Carlos Alva
2006-05-15 10:24:19.0
I tried with version 2.1 and 2.2 and I have the same trouble, I'm using database pilot for execute the query, I'm using the next properties for your driver:

loadIndices true
Default Index Suffix CDX
COMPACTEDINDEX true

I still have a slow performance, I was checking the use of memory and the access to disc, the memory was fine and the access to disc withouth the use of index was 1,820,000 readings, using index it was almost 200,000 readings, the time that this take continues being of more than 2 minutes.

I really need help, What can I do??? How can I be sure that the driver is using the correct tag??? Can I force the use of the correct tag content in the index??? I will appreciate very much your help.

Re:Re:Re:problem with low performance querys
HXTT Support
2006-05-15 18:42:48.0
First, you should download the latest package, and run testDBFSpeed2 by changing "jdbc:dbf:////d:/test"; to your url. Then you should see a printed speed message. Check whether that speed is more than 2 minutes, or in 1s. Then tell us the test result. The speed result will help us to dig out your issue since the speed in our simulation is only 0.2s.

>loadIndices true
It's default property value, and you needn't to set it.

>Default Index Suffix CDX
>COMPACTEDINDEX true
If you needn't to create index in database pilot, you needn't to set it too.

>the access to disc withouth the use of index was 1,820,000 readings,
>using index it was almost 200,000 readings, the time that this take
>continues being of more than 2 minutes.
It seems that your query are getting a very big matched result so that HXTT DBF need to scan most of index page to get the result. Maybe most column values for kxseccion column is 1, and kxlibro is 2151?



Re:Re:Re:Re:problem with low performance querys
Carlos Alva
2006-05-15 22:23:14.0
I executed TestDBFSpeed2 and obtained the next results:

Spent time/row count:157ms/61
Spent time/row count:0ms/61

When I changed the table created with TestDBFSpeed2 for my table I obtained the next results:

Spent time/row count:120047ms/1
Spent time/row count:119062ms/1

About the amount of records than match with xkseccion = '1' is 1,594,292

the amount of records that match with xkseccion = '1' and kxlibro = 2151 is just 609

the amount of records that match with kxseccion = '1' and kxlibro = 2151 and kxfolio = 3 is 84

and just 1 record match with all the values used in the query.

I will wait for your answer, Thanks a lot.
Re:Re:Re:Re:Re:problem with low performance querys
Carlos Alva
2006-05-15 23:22:40.0
I want to be thankful to you by the support that offered me, obtaining the amounts of registries that cumplian with the query to send you, I realized of which I needed to include a new index str(kxlibro,4)+str(kxfolio,3)+(kxinscrip,4)+kxseccion

I made the index and test again the next query

select kxnombre
from r_kardex
where str(kxlibro,4)+str(kxfolio,3)+str(kxinscrip,4)+kxseccion = '2151 3 241'

I obteined the result in less than 1 second, Thank you for you help
Re:Re:Re:Re:Re:Re:problem with low performance querys
HXTT Support
2006-05-16 00:20:25.0
Yeah:)

You need an index:)

HXTT DBF supports more:
If you have one of the following index expressions:
1. str(kxlibro,4)+str(kxfolio,3)+str(kxinscrip,4)+kxseccion
2. str(kxlibro,4)+str(kxfolio,3)+str(kxinscrip,4)
3. str(kxlibro,4)+str(kxfolio,3)
4. str(kxlibro,4)

HXTT DBF will utilize that index for str(kxlibro,4)+str(kxfolio,3)+str(kxinscrip,4)+kxseccion = '2151 3 241' clause.

It means that HXTT DBF can use str(kxlibro,4) index expression for str(kxlibro,4)+str(kxfolio,3)+str(kxinscrip,4)+kxseccion=? clause, and use str(kxlibro,4)+str(kxfolio,3)+str(kxinscrip,4)+kxseccion=? index expression for str(kxlibro,4)=? clause:)





Re:Re:Re:Re:Re:Re:Re:problem with low performance querys
HXTT Support
2006-05-16 00:27:37.0
Sorry. It means that HXTT DBF can't use str(kxlibro,4) index expression for str(kxlibro,4)+str(kxfolio,3)+str(kxinscrip,4)+kxseccion=? clause, but can use str(kxlibro,4)+str(kxfolio,3)+str(kxinscrip,4)+kxseccion index expression for str(kxlibro,4)=? clause:)
Re:Re:Re:Re:Re:Re:Re:Re:problem with low performance querys
Registro
2006-11-14 15:57:40.0
Si Jalo Carlos Munchas Gracias
Alfonso

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