[sqlite] SQLite and Java

[boysen]

Hi,

I am using SQLite from within Java. Unfortunately, the Java wrapper 
linked from the SQLite website tends to be rather slow when returning 
large resultsets from a query. Has anybody a handy solution for / 
experiences with that?

Thanks,
 Bo
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

[sqlite] java wrapper JDBC

[boysen]

Hallo,

as just discovered with the help of this mailing list, the Java wrapper from
http://www.ch-werner.de/javasqlite/
turned out to be a bit slow in returning large result sets.
Does anybody know of another Java wrapper resp. JDBC driver for SQLite?

Best,
  Bo
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] performance question

[boysen]

Thanks for the hint, Frank!

It is indeed the Java wrapper which takes its time and it has nothing to 
do with SQLite itself.
From the command line the query is done in a blink.
So I have to find a workaround for the Java wrapper since I do not think 
there is another one out there.

Thank you all for the kind help!
  Bo

Hi,


I have a question about the performance of my SQLite DB, where the
db-file has about 20MB and which I use in a Java application via the
Java wrapper.


First, your timing figures look indeed slower than what I would expect
(using a somewhat similar DB in type and size and a similar select even on
an embedded system)
The Java wrapper might be your first suspective.
Did you try the command line program as a reference?

TABB has 14785 rows, TABG 7111 rows.
On my PC the following query requires about 53 seconds:
select * from TABG a, TABB b where (a.S='3' or a.S='12 or...) and
b.G=a.G order by a.G asc;


Depending upon how many "or" conditions you have, you might try ot use
the "in" keyword. (Although I would not expect much improvement)

The times are used only for the query, not connecting etc. I guess it
has something to do with building up the data structures for the first
query resp. caching.


I do not think the behaviour you see is sqlite-internal, I would suspect
the Java wrapper.
How large is the output of your selection?
Maybe it is just the transfer (socket, whatever) which takes so much time.
Regards,

Frank Baumgart

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]




-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] performance question

[boysen]

Thanks! That decreased query time about 3 seconds in the first as well 
as the second (!) query.
I suppose that the 50 extra seconds of the first query really have 
something to do with the initialization of the DB.

Bo

 > On my PC the following query requires about 53 seconds:
 >
 > select * from TABG a, TABB b
 > where (a.S='3' or a.S='12 or...) and b.G=a.G order by a.G asc;
 >
 > On Oracle with the same scheme and data it requires only 0.4 seconds.
 >
SQLite does not optimize OR terms in a WHERE clause.  Try using
an IN operator instead.  Like this:
   SELECT * FROM tabg AS a, tabb AS b
   WHERE a.s IN ('3','12',...) AND b.g=a.g
   ORDER BY a.g ASC;


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

[sqlite] performance question

[boysen]

Hi,

I have a question about the performance of my SQLite DB, where the 
db-file has about 20MB and which I use in a Java application via the 
Java wrapper.

This are the relevant parts of the scheme:

create table TABB (B INTEGER,GB INTEGER,G,PRIMARY KEY(B,GB));

create table TABG (G INTEGER PRIMARY KEY,S);

create index G_IDX ON TABG(S);

create index B_IDX ON TABB(G);

TABB has 14785 rows, TABG 7111 rows.
On my PC the following query requires about 53 seconds:
select * from TABG a, TABB b where (a.S='3' or a.S='12 or...) and 
b.G=a.G order by a.G asc;

(On Oracle with the same scheme and data it requires only 0.4 seconds.)

Second, when my application does a very simliar second query right after 
that first one, that requires less than 4 seconds.
select * from TABB b, TABG a where (a.S='3' or a.S='12 or...) and 
a.G=b.G order by b.B asc, b.GB asc;

The times are used only for the query, not connecting etc. I guess it 
has something to do with building up the data structures for the first 
query resp. caching.

Does anybody know if that is the performance I should expect?
Does anybody has some hints on how to improve this bit of the db scheme?
Thanks in advance,
  Bo
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

[sqlite] how to switch to asynchronous mode?

[boysen]

Hi,

I just converted a DB from Oracle to SQLite and am now trying to improve
  the performance. On the performance comparison page of the sqlite.org
website I read about an asynchronous mode, which performed faster in
many cases.
How can I switch SQLite to asynchronous mode?

Thanks in advance,
Bo
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]