Re: [sqlite] fast Java solution?

[Vitali Lovich]

My suggestion to speed up the step call backs would be to have the JNI 
code do the actual step routine and cache up the pertinent information 
into an array.


Then after it's complete or the cache limit is reached (set the cache 
limit to a sufficient size such that the time spent processing that 
information in Java outweighs your JNI communication time, at least by a 
factor of 10 I think) return the cache to Java for processing.


Other than that, you will be limited by the JNI overhead.  
http://www.excelsior-usa.com/ has a product 
thttp://www.excelsior-usa.com/xfunction.html which lets you call native 
libraries without writing any kind of JNI code.  The reason I'm pointing 
it out is that they may have some optimizations that you might not have 
though of.  Anyways, it's not free, but there is an evaluation version 
that should let you decide whether or no it fits your requirements.


Brannon King wrote:

So who here has the _fast_ Java solution for accessing SQLite3?

Here's what I've done so far. I took the wrapper from wiki page labeled
"Java wrapper incl. JDBC driver for SQLite.
¤http://www.ch-werner.de/javasqlite;. I then fixed the calloc calls so that
the params were in the right order, fixed the finalize calls that should
have been reset calls, added some bind functions, built some step functions
without the column name/type overhead, and compiled it with my 3.3.6 code
from last week. So the step function(s) for that library take a Java
callback function. Alas, that seems to be too slow. The overhead of the
Java-to-C then the C-to-Java call all in each step function is just too much
overhead in JNI calls. Anyone else seen that issue?

Is there some standard tool that will generate a JNI dll from the
sqlite.dll? And if so, will that do step functions without the callback
overhead?

I suppose I'll dig in and make a custom JNI interface so that most of my
code is done in C, but just thought I'd ask around first... Thanks for your
time. The other Java wrappers posted seem to wrap too much or too little or
not be compatible with version 3. 
_

Brannon King
¯



  

[sqlite] Resources required and Lock & recovery mechanisms

[Vivek R]

Hi Everybody,
I have the following doubt...

1. what are the resources required by SQLLite - they can be RAM/ROM,
semaphores, mail boxes, task requirements;
2. How do we have flow control?
3. what are the Lock mechanisms provided by the engine (row lock, table
lock..)? Any additional lock mechanism we need to build.

3. How to create a service component that creates these tables on HDD ( Hard
disk on Consumer products like DVD or Set top box ) before it leave the
factory.
4. recovery mechanisms (in case DB crash how do we recover/reconstruct
data?)


Thanks and Regards,
 Vivek R

Re: [sqlite] count(*)

[Jay Sprenkle]

On 7/17/06, Sripathi Raj <[EMAIL PROTECTED]> wrote:

Hi,

 I read in one of the threads that count(*) has to be hand optimized since
SQLite doesn't optimize it. Any pointers on how to do that?


When doing joins put the table that contributes the smallest number of
rows to the result set as the first table.

Don't use inexact comparisons in the where clause (like and not equal and such)

the explain option in the command helps you compare different
statements and how they are executed

--
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

[sqlite] count(*)

[Sripathi Raj]

Hi,

I read in one of the threads that count(*) has to be hand optimized since
SQLite doesn't optimize it. Any pointers on how to do that?

Raj

Re: [sqlite] timer in shell.c

[carl clemens]

Hi Brannon,

Try this:
SQLite version 3.3.6
Enter ".help" for instructions
sql3> .help
.databases List names and files of
attached databases
.dump ?TABLE? ...  Dump the database in an SQL
text format
.echo ON|OFF   Turn command echo on or off
.exit  Exit this program
.explain ON|OFFTurn output mode suitable for
EXPLAIN on or off.
.feedback ON|OFF   Print number of rows
selected/affected on or off.
.header(s) ON|OFF  Turn display of headers on or
off
.help  Show this message
.import FILE TABLE Import data from FILE into
TABLE
.indices TABLE Show names of all indices on
TABLE
.mode MODE ?TABLE? Set output mode where MODE is
one of:
 csv  Comma-separated
values
 column   Left-aligned
columns.  (See .width)
 html HTML  code
 insert   SQL insert
statements for TABLE
 line One value per line
 list Values delimited by
.separator string
 tabs Tab-separated values
 tcl  TCL list elements
.newline   Print a new line
.noprint ON|OFFNo output
.nullvalue STRING  Print STRING in place of NULL
values
.output FILENAME   Send output to FILENAME
.output stdout Send output to the screen
.prompt MAIN CONTINUE  Replace the standard prompts
.quit  Exit this program
.read FILENAME Execute SQL in FILENAME
.schema ?TABLE?Show the CREATE statements
.separator STRING  Change separator used by output
mode and .import
.show  Show the current values for
various settings
.tables ?PATTERN?  List names of tables matching a
LIKE pattern
.timeout MSTry opening locked tables for
MS milliseconds
.timing ON|OFF Turn elapsed time on or off
.width NUM NUM ... Set column widths for "column"
mode
sql3> 


changes are:

feedback
noprint
newline
timing


--- Brannon King <[EMAIL PROTECTED]> wrote:

> I'd like to modify the shell.c so that I get an
> "time taken" output with
> each query executed (similar to the mysql client).
> Where would I put the
> start and stop/print code in shell.c so that I
> capture the query time but
> not the time required to output the data to the
> console? Thanks.
> 
> __
> Brannon King
> ¯¯
> 
> 
> 

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Re: [sqlite] fast Java solution?

[Ulrik Petersen]

Brannon King wrote:

Is there some standard tool that will generate a JNI dll from the
sqlite.dll?
  


Have you looked at SWIG?

http://www.swig.org

It doesn't generate a JNI dll from the sqlite.dll, but does generate a 
JNI dll from the sqlite sourcecode -- there is a learning curve, so you 
might want to roll your own JNI code after all, especially if you want 
to do custom things with callbacks.


Might be worth a look.


HTH


Ulrik Petersen

[sqlite] fast Java solution?

[Brannon King]

So who here has the _fast_ Java solution for accessing SQLite3?

Here's what I've done so far. I took the wrapper from wiki page labeled
"Java wrapper incl. JDBC driver for SQLite.
¤http://www.ch-werner.de/javasqlite;. I then fixed the calloc calls so that
the params were in the right order, fixed the finalize calls that should
have been reset calls, added some bind functions, built some step functions
without the column name/type overhead, and compiled it with my 3.3.6 code
from last week. So the step function(s) for that library take a Java
callback function. Alas, that seems to be too slow. The overhead of the
Java-to-C then the C-to-Java call all in each step function is just too much
overhead in JNI calls. Anyone else seen that issue?

Is there some standard tool that will generate a JNI dll from the
sqlite.dll? And if so, will that do step functions without the callback
overhead?

I suppose I'll dig in and make a custom JNI interface so that most of my
code is done in C, but just thought I'd ask around first... Thanks for your
time. The other Java wrappers posted seem to wrap too much or too little or
not be compatible with version 3. 
_
Brannon King
¯

Re: [sqlite] Major projects using SQLite

[Stipe Tolj]

Jon García de Salazar Bilbao wrote:


Hi,
Could you give examples of some major software projects using SQLite?


Kannel -- most used open source SMS and WAP gateway, http://www.kannel.org/ ;)

Stipe

---
Kölner Landstrasse 419
40589 Düsseldorf, NRW, Germany

tolj.org system architecture  Kannel Software Foundation (KSF)
http://www.tolj.org/  http://www.kannel.org/

mailto:st_{at}_tolj.org   mailto:stolj_{at}_kannel.org
---

Re: Re: [sqlite] Use "computed" fields to get the value of other fields.

[Brad Stiles]

> SELECT TotalInvoice, (SELECT SUM(PaymentValue) FROM Payments 
> WHERE Payments.IDInvoice = IDInvoice) AS TotalPaid, TotalPaid = TotalInvoice 
> AS FullyPaid FROM Invoices;
> 
> Here, I select:
> - TotalInvoice the total amount of the invoice
> - TotalPaid the total amount paid till now
> - FullyPaid a boolean flag indicating if the invoice is paid or not

select TotalInvoice, paid.totalpaid, (paid.totalpaid = TotalInvoice) as 
FullyPaid
from   Invoices,
   (
select  Payments.IDInvoice as IDInvoice, sum(PaymentValue) as 
totalpaid
fromPayments
where   Payments.IDInvoice = Invoices.IDInvoice
group by payments.idinvoice
   ) as paid
where  Invoices.IDInvoice = paid.IDInvoice;

Haven't checked that in any way, but it seems to be what you wanted.  Things 
like that work in MS SQL Server 2000, though the boolean thing is something 
I've never tried.  Worse comes to worse, you can always do that part in your 
code, which is where (*my* DBA says, anyway) it belongs anyway, since it's 
application logic and not data logic. :-)

You could also do it as a join if you wanted to.

Brad

Re: [sqlite] Use "computed" fields to get the value of other fields.

[Jay Sprenkle]

On 7/17/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:

Hello, and thanks for the reply.

SELECT *
 from Employees
where IDContractLevel > 4

is not enough, since I actually need to use a field that is created inside the 
SQL statement to create another field. The problem is that it seems that the 
computed fields is not existing yet when it is used for another field.

The example I posted was only a "stupid" example of what I mean, just to 
clarify the situation. Another example would be this:

SELECT TotalInvoice, (SELECT SUM(PaymentValue) FROM Payments WHERE 
Payments.IDInvoice = IDInvoice) AS TotalPaid, TotalPaid = TotalInvoice AS 
FullyPaid FROM Invoices;


You can't use the aliased column TotalInvoice since it doesn't exist until
after the query is executed.

You could do the boolean operation manually in your code, or
You might try to create a view from this query:
Create view Myview AS
  SELECT TotalInvoice, (SELECT SUM(PaymentValue) FROM Payments
WHERE Payments.IDInvoice = IDInvoice) AS TotalPaid,
 IDInvoice
FROM Invoices;

Then select from the view:
SELECT TotalInvoice, TotalPaid, TotalInvoice = TotalPaid AS FullyPaid
FROM Myview
WHERE IDInvoice = 

Re: [sqlite] Use "computed" fields to get the value of other fields.

[elemaco71-sqlite]

Hello, and thanks for the reply.

SELECT *
 from Employees
where IDContractLevel > 4

is not enough, since I actually need to use a field that is created inside the 
SQL statement to create another field. The problem is that it seems that the 
computed fields is not existing yet when it is used for another field.

The example I posted was only a "stupid" example of what I mean, just to 
clarify the situation. Another example would be this:

SELECT TotalInvoice, (SELECT SUM(PaymentValue) FROM Payments WHERE 
Payments.IDInvoice = IDInvoice) AS TotalPaid, TotalPaid = TotalInvoice AS 
FullyPaid FROM Invoices;

Here, I select:
- TotalInvoice the total amount of the invoice
- TotalPaid the total amount paid till now
- FullyPaid a boolean flag indicating if the invoice is paid or not

the problem is that I get an error "No such column: TotalPaid"

Thanks again
Marco

Jay Sprenkle <[EMAIL PROTECTED]> ha scritto:  On 7/17/06, [EMAIL PROTECTED]  
wrote:
>   Hello, everybody
>
> I'm using a SQL clause to get data from the DB, and I need to use "computed" 
> fields to get the value of other fields. A (stupid) example of what I need is 
> this:
>
> SELECT IDContractLevel, IDContractLevel > 4 as IAmBoss, (SELECT * FROM Bosses 
> WHERE Flag = IAmBoss) FROM Employees;
>
> What I get is an error indicating:
>
> "SQLite error 1 - no such column: IAmBoss"

Could you post the definition of the table Employees?

I wasn't sure if you could put boolean expressions in the select
but it appears to work fine:

SQLite version 3.0.8
Enter ".help" for instructions
sqlite> select 1>4;
0
sqlite> select 6>4;
1
sqlite>

sub selects seem to work fine:

sqlite> select (select 2);
2
sqlite>

I suspect you really want something like this?

SELECT *
 from Employees
where IDContractLevel > 4
;



--
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com


 Chiacchiera con i tuoi amici in tempo reale! 
 http://it.yahoo.com/mail_it/foot/*http://it.messenger.yahoo.com 

Re: [sqlite] This great!

[Martin Jenkins]

Hi,

The subscribe/unsubscribe info you want is in the message headers:

List-Id: SQLite Users Mailing List 
List-Post: 
List-Help: 
List-Unsubscribe: 
List-Subscribe: 

Regards,

Martin

Mohammad Shiful Islam wrote:

Hi,

I want to unsubscribe. Please help on this.

Re: [sqlite] This great!

[John Stanton]

I think you have an interesting idea.  Split the DB across disks so 
concurrent activities are on different spindles, preferably at 15,000 
rpm and use a multiprocessor/core cpu with the application threaded so 
that you deploy more than one processor concurrently.  If you could be 
sure to schedule each thread to a different processor you could have a 
poor mans processor cluster.


It would be neither "lite" nor simple, but could potentially be very fast.

Cesar David Rodas Maldonado wrote:

I don't know I  just was  wondering... but i think it will be faster if you
have a really  great DB and when you and insert just will be locked the
table where you are doing the insert not the whole DB... what do you think?

Re: [sqlite] This great!

[Mohammad Shiful Islam]

Hi,

I want to unsubscribe. Please help on this.

Regards,
Arif



__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Re: [sqlite] This great!

[Cesar David Rodas Maldonado]

Thanks Christian :D

On 7/17/06, Christian Smith <[EMAIL PROTECTED]> wrote:


Cesar David Rodas Maldonado uttered:

> I am wondering if it will have a better performance if i split every
index
> and table into different files, i know that i will loose the LITE
concept,
> but i am wondering if it will have a better performance...
>


Not unless each individual file is on a different hard disk. The
bottleneck for a hard disk is the head actuator and spindle speed. The OS
will already optimise data IO to make best use of the harddisk, whether
that be one one or many files.

Your laptop will only have a single hard disk. You'll get no more
performance splitting the file.

Non-LITE databases that use table spaces for improved performance only
improve performance when each tablespace is on a different device.

Christian


--
 /"\
 \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
  X   - AGAINST MS ATTACHMENTS
 / \

Re: [sqlite] This great!

[Christian Smith]

Cesar David Rodas Maldonado uttered:


I am wondering if it will have a better performance if i split every index
and table into different files, i know that i will loose the LITE concept,
but i am wondering if it will have a better performance...




Not unless each individual file is on a different hard disk. The 
bottleneck for a hard disk is the head actuator and spindle speed. The OS 
will already optimise data IO to make best use of the harddisk, whether 
that be one one or many files.


Your laptop will only have a single hard disk. You'll get no more 
performance splitting the file.


Non-LITE databases that use table spaces for improved performance only 
improve performance when each tablespace is on a different device.


Christian


--
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
 X   - AGAINST MS ATTACHMENTS
/ \

Re: [sqlite] This great!

[Cesar David Rodas Maldonado]

I don't know I  just was  wondering... but i think it will be faster if you
have a really  great DB and when you and insert just will be locked the
table where you are doing the insert not the whole DB... what do you think?

Re: [sqlite] This great!

[Jay Sprenkle]

On 7/17/06, Cesar David Rodas Maldonado <[EMAIL PROTECTED]> wrote:

I am wondering if it will have a better performance if i split every index
and table into different files, i know that i will loose the LITE concept,
but i am wondering if it will have a better performance...



I doubt it, unless you put them on separate hard disks.
Why do you think this will be faster?

Re: [sqlite] This great!

[Cesar David Rodas Maldonado]

I am wondering if it will have a better performance if i split every index
and table into different files, i know that i will loose the LITE concept,
but i am wondering if it will have a better performance...

Re: [sqlite] This great!

[Eduardo]

I am wondering if it will have a better performance if i split every index
and table into a file, i know that i will loose the LITE concept, but i am
wondering if it will have a better performance...


If you already played with pragmas you can try on windows 7-max 
(depends on your hardware) with SQLite for more speed (only on 
medium-big databases sizes). Check www.7-max.org and download 7-max 
4.x (not 5.x, it's for 64 bits). Before use it, MAKE BACKUP of 
everything and read carefully documentation. On Linux, you must 
recompile kernel.




Thank to Dr. Hipp and everyone who did it possible!


Idem ;)


Alien.org contacted...waiting for Ripley 

Re: [sqlite] This great!

[Jay Sprenkle]

On 7/17/06, Cesar David Rodas Maldonado <[EMAIL PROTECTED]> wrote:

Hello to everybody...!!!

I was using my old notebook the weekend, and i was doing some programs in C
to try SQLite performance, and  I am happy, because i could insert
4.000.000rows in 10 minutes in my old notebook( Pentium II 400 MHZ 128
M.RAM). Thank for everybody who made it possible (share this coll DB), that
was on my Windows XP...  then I try on my Linux FC4, i could insert
8.000.000 of row in 15 minutes.. that's great faster that every DB
engine that I know.


bulk inserts are much faster if you delete the indexes, insert the data, then
create the indexes. Did you try that?



And with the select it took about 10 ms to select 50 rows of 8.000.000 (
that's very cool!)

I am wondering if it will have a better performance if i split every index
and table into a file, i know that i will loose the LITE concept, but i am
wondering if it will have a better performance...


more RAM and higher rotational speed on the hard disks are your best bet.

[sqlite] This great!

[Cesar David Rodas Maldonado]

Hello to everybody...!!!

I was using my old notebook the weekend, and i was doing some programs in C
to try SQLite performance, and  I am happy, because i could insert
4.000.000rows in 10 minutes in my old notebook( Pentium II 400 MHZ 128
M.RAM). Thank for everybody who made it possible (share this coll DB), that
was on my Windows XP...  then I try on my Linux FC4, i could insert
8.000.000 of row in 15 minutes.. that's great faster that every DB
engine that I know.

And with the select it took about 10 ms to select 50 rows of 8.000.000 (
that's very cool!)

I am wondering if it will have a better performance if i split every index
and table into a file, i know that i will loose the LITE concept, but i am
wondering if it will have a better performance...

Thank to Dr. Hipp and everyone who did it possible!

Cesar Rodas

Re: [sqlite] Use "computed" fields to get the value of other fields.

[Jay Sprenkle]

On 7/17/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:

  Hello, everybody

I'm using a SQL clause to get data from the DB, and I need to use "computed" 
fields to get the value of other fields. A (stupid) example of what I need is this:

SELECT IDContractLevel, IDContractLevel > 4 as IAmBoss, (SELECT * FROM Bosses 
WHERE Flag = IAmBoss) FROM Employees;

What I get is an error indicating:

"SQLite error 1 - no such column: IAmBoss"


Could you post the definition of the table Employees?

I wasn't sure if you could put boolean expressions in the select
but it appears to work fine:

SQLite version 3.0.8
Enter ".help" for instructions
sqlite> select 1>4;
0
sqlite> select 6>4;
1
sqlite>

sub selects seem to work fine:

sqlite> select (select 2);
2
sqlite>

I suspect you really want something like this?

SELECT *
from Employees
where IDContractLevel > 4
;



--
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

[sqlite] Use "computed" fields to get the value of other fields.

[elemaco71-sqlite]

  Hello, everybody

I'm using a SQL clause to get data from the DB, and I need to use "computed" 
fields to get the value of other fields. A (stupid) example of what I need is 
this:

SELECT IDContractLevel, IDContractLevel > 4 as IAmBoss, (SELECT * FROM Bosses 
WHERE Flag = IAmBoss) FROM Employees;

What I get is an error indicating:

"SQLite error 1 - no such column: IAmBoss"

How can I solve this? Any help is appreciated

Thanks and kind regards,
Marco 
 Chiacchiera con i tuoi amici in tempo reale! 
 http://it.yahoo.com/mail_it/foot/*http://it.messenger.yahoo.com 

Re: [sqlite] Major projects using SQLite

[Jens Miltner]

Am 14.07.2006 um 15:32 schrieb Jon García de Salazar Bilbao:


Could you give examples of some major software projects using SQLite?


Did you check out the list at ?

May not list _all_ the major projects, but sure lists quite a few...