HXTT JDBC Products: MS Access JDBC, Cobol JDBC, DBF JDBC, MS Excel JDBC,CSV JDBC, Text JDBC, Corel Paradox JDBC, PDF JDBC, Word JDBC, XML JDBC

Hello Support Team,

I have currently downloaded HXTT DBF Driver 4.0 version and in the evaluation period. We are potentially looking at buying a full licensed version as it seems to fit most of our needs. But, I wanted to quickly check following thing with your team first - We have a very large DBF file which is around 190 MB and it has CDX index files defined. If I query this dbf through Java program using a PreparedStatement (query uses ORDER BY ) . It is taking more than a minute to fetch the data.How this search can be improved?

Thanks in advance,

Devika

Re:HXTT DBF Driver takes long time to search data in DBF

HXTT Support

2013-06-12 06:08:03

>It is taking more than a minute to fetch the data.How this search can be improved?
ORDER BY need external sort, a bigger maxCacheSize connection property can reduce or avoid disk file swap.

>We have a very large DBF file which is around 190 MB
If you're using "select * from atable order by column1", If there's an index on column1, HXTT DBF can utilize it for ORDER BY clause.

If your ORDER sql has a where clause, and HXTT DBF will try to utilize index for your sql.

If your where clause hasn't a matched index file, but you use a bigger delayedClose connection property, HXTT DBF will utilize automatic temporary index to quicken it.

Re:Re:HXTT DBF Driver takes long time to search data in DBF

Devika

2013-06-13 04:00:02

Dear Support Team,

Thanks for the prompt response. As p the guidelines provided - the WHERE caluse did have coumns which were a part of the index dfined on the dbf.But, there are multiple indexes defined for this dbf.

I then tried setting the delayedClose property to 15 and the time was reduced down to 20 sec, which is certainly a good sign.

Would there be any (positive / negative) implications / side-effects of setting the delayedClose property for the connection? Is it absolutely 'safe' to set this property while establishing a connection? Is there any suggested optimal value to be set?

Thanks,
Devika

Re:Re:Re:HXTT DBF Driver takes long time to search data in DBF

HXTT Support

2013-06-13 05:48:03

>Is it absolutely 'safe' to set this property while establishing a connection?
It's safe.

>the time was reduced down to 20 sec,
It's too slow still . Please let us know your sql sample, and what index expression on that table.

Re:Re:Re:Re:HXTT DBF Driver takes long time to search data in DBF

Devika

2013-06-14 02:15:57

Dear Support Team,

The dbf has in all 8 columns (say column 1 , ... column 8 ) and it has 4 indexes (.CDX ) defined on it. Also, to mention it has a date field as one of it's columns, (col6).

The indexes are as follows :

1. col1, col2, col3,col4,dtos(col6), col7
2. col1, col2,dtos(col6), col7
3. col1,dtos(col6), col7, col2
4. col3,col4,col5,col8

The SQL Query is a Java PreparedStatement as follows:

select col8 from where col1= ? AND col2 = ? AND col3 = ? AND col4= ? AND col5 = ? order by col6 desc

Kindly advise how the search can further be improved.

Thanks & Regards,
Devika

Re:Re:Re:Re:Re:HXTT DBF Driver takes long time to search data in DBF

HXTT Support

2013-06-14 02:36:55

>The indexes are as follows :
>1. col1, col2, col3,col4,dtos(col6), col7
>2. col1, col2,dtos(col6), col7
>3. col1,dtos(col6), col7, col2
>4. col3,col4,col5,col8
Your index expression are
1. col1+col2+col3+col4+dtos(col6)+col7
2. col1+col2+dtos(col6)+col7
3. col1+dtos(col6)+col7+col2
4. col3+col4+col5+col8
?

Re:Re:Re:Re:Re:Re:HXTT DBF Driver takes long time to search data in DBF

Devika

2013-06-14 02:40:30

Your index expression are
1. col1+col2+col3+col4+dtos(col6)+col7
2. col1+col2+dtos(col6)+col7
3. col1+dtos(col6)+col7+col2
4. col3+col4+col5+col8
?

Yes, Thanks for correction. The indexes are as above (with + and not with , as mentioned in my previous email)

Regards,
Devika

Re:Re:Re:Re:Re:Re:Re:HXTT DBF Driver takes long time to search data in DBF

Devika

2013-06-17 04:45:28

Dear Support Team,

Is there any update on this please?

Thanks in advance,

Regards,
Devika