|
|
Heng Xing Tian Tai Lab of Xi'an City (abbr, HXTT)
HXTT DBF
|
JDBC 4.1 v7.1.183 much slower than JDBC 4.1 v5.1.184 |
michael |
2022-06-27 02:12:43 |
We have existing JDK 11 SpringBoot application that relies on HXTT to read data from Foxpro DBF databases. We notice the performance of the latest driver v7.1.183 is much slower than v5.1.184.
eg we have a file with 68 records. The database file contains 60+ columns.
In the 5.1.184, the time it takes to load the data is about 4 - 5 seconds
In the 7.1.183, the time it takes to load the data is about 10 -12 seconds
The only change we made is to replace the HXTT JDBC jar file.
Any idea why there is such a big performance hit?
|
Re:JDBC 4.1 v7.1.183 much slower than JDBC 4.1 v5.1.184 |
HXTT Support |
2022-06-27 02:19:36 |
|
What's your jdbc url setting? We will try to recur your speed issue.
|
Re:Re:JDBC 4.1 v7.1.183 much slower than JDBC 4.1 v5.1.184 |
michael |
2022-06-27 02:31:08 |
See below how we setup the connection in SpringBoot.
@Primary
@Bean("dataSource")
public DataSource getDataSource() throws SQLException {
return DataSourceBuilder
.create()
.type(HxttConnectionPoolDataSource.class)
.driverClassName("com.hxtt.sql.dbf.DBFDriver")
.url("jdbc:dbf:///C:\web_projects\clienta/database")
.build();
}
|
Re:Re:Re:JDBC 4.1 v7.1.183 much slower than JDBC 4.1 v5.1.184 |
michael |
2022-06-27 02:49:00 |
|
I meant to say the Foxpro DBF file contains 68k records and not 88 records.
|
Re:Re:Re:Re:JDBC 4.1 v7.1.183 much slower than JDBC 4.1 v5.1.184 |
HXTT Support |
2022-06-28 01:15:50 |
|
What's your sql?
|
Re:Re:Re:Re:Re:JDBC 4.1 v7.1.183 much slower than JDBC 4.1 v5.1.184 |
Michael |
2022-06-29 22:02:49 |
The SQL we use are
countRecordById completed in 74975 milli seconds SELECT count(sn) FROM COSTCTR WHERE custid = 'SingleSite' AND deleted <> 'Y'
Note there are a number of these queries running parallel.
|
Re:Re:Re:Re:Re:Re:JDBC 4.1 v7.1.183 much slower than JDBC 4.1 v5.1.184 |
HXTT Support |
2022-06-29 23:10:12 |
|
Please try the latest package.
|
Re:Re:Re:Re:Re:Re:Re:JDBC 4.1 v7.1.183 much slower than JDBC 4.1 v5.1.184 |
Michael |
2022-06-30 00:24:47 |
Hi there
I now use JDBC 4.3 v 7.1.185 and still getting the performance issue...
30-06-2022 14:40:04.008 [https-jsse-nio-8443-exec-2] DEBUG countRecordById completed in 71996 milli seconds SELECT count(sn) FROM COSTCTR WHERE custid = 'SingleSite' AND deleted <> 'Y'
|
Re:Re:Re:Re:Re:Re:Re:Re:JDBC 4.1 v7.1.183 much slower than JDBC 4.1 v5.1.184 |
Michael |
2022-07-01 01:11:38 |
This SQL query demonstrate the performance issue more prominently.
SELECT sn,ccgid,ccnid,cccid,cchid,ccg,ccn,ccc,cch,ccf,linkage,conetdate,status,sn1,sn2,sntype,cosd,sdt,snl,spend,costid,empno,addr,addr2,comm,lastbill,{v 'DELETED'},simserial,imei,equip,acc1,acc2,purdate,canceldate,cancelref,refno,supplier,carplan,caracc,pukcode,mgrname,mgrno,circuitsp,hwstatus,equip2,equipan,equip2an,purdate2,simtype,maintain,region,userlink,voicplan,voicsd,voicexp,dataplan,datasd,dataexp,split,blitz1,blitz2,blitz3,blitzsd,blitzed,custid FROM COSTCTR WHERE custid = 'SingleSite' AND deleted <> 'Y' ORDER BY sn ASC LIMIT 0, 100
version 5.1 driver query completed in 1 seconds
version 7.1.+ driver query completed in 6 seconds
We used the com.hxtt.sql.admin.Admin console to try this SQL.
|
Re:Re:Re:Re:Re:Re:Re:Re:JDBC 4.1 v7.1.183 much slower than JDBC 4.1 v5.1.184 |
HXTT Support |
2022-07-01 19:07:21 |
|
You can try v7.1.188
|
Re:Re:Re:Re:Re:Re:Re:Re:JDBC 4.1 v7.1.183 much slower than JDBC 4.1 v5.1.184 |
Michael |
2022-07-04 02:22:35 |
|
No joy. Still takes more than 6 seconds.
|
Re:Re:Re:Re:Re:Re:Re:Re:JDBC 4.1 v7.1.183 much slower than JDBC 4.1 v5.1.184 |
HXTT Support |
2022-07-05 03:14:05 |
Try the following sql:
explain sql SELECT sn,ccgid,ccnid,cccid,cchid,ccg,ccn,ccc,cch,ccf,linkage,conetdate,status,sn1,sn2,sntype,cosd,sdt,snl,spend,costid,empno,addr,addr2,comm,lastbill,{v 'DELETED'},simserial,imei,equip,acc1,acc2,purdate,canceldate,cancelref,refno,supplier,carplan,caracc,pukcode,mgrname,mgrno,circuitsp,hwstatus,equip2,equipan,equip2an,purdate2,simtype,maintain,region,userlink,voicplan,voicsd,voicexp,dataplan,datasd,dataexp,split,blitz1,blitz2,blitz3,blitzsd,blitzed,custid FROM COSTCTR WHERE custid = 'SingleSite' AND deleted <> 'Y' ORDER BY sn ASC LIMIT 0, 100;
to see whether there's an index expression on custid .
|
Re:Re:Re:Re:Re:Re:Re:Re:JDBC 4.1 v7.1.183 much slower than JDBC 4.1 v5.1.184 |
Michael |
2022-07-05 21:46:33 |
Hi there
I ran the explain SELECT (instead of explain sql SELECT) and there is no index on custid. In this specific use case, the custid column only has one value ie 'SingleSite'.
The explain result returns 3 rows. They are
id: 1
table: COSTCTR
expression: custid=='SingleSite'
key:
id: 1
table: COSTCTR
expression: custid=='SingleSite'
key:
id: 1
table: COSTCTR
expression: deleted!='Y'
key:
id: 1
table: COSTCTR
expression: (NOT DELETED())==true
key: NOT DELETED()
|
|
|
Main
webmaster@hxtt.com