[sqlite] db vs shell

Was doing some DB operations and felt they were going slower than they should. So I did this quick test: $ time -p sqlite3 sample.db 'select foo from bar ; ' | uniq | sort | uniq | wc -l 209 real 5.64 user 5.36 sys 1.51 $ time -p sqlite3 sample.db 'select count(distinct foo) from bar ; '

Re: [sqlite] threads and database lock

Quoting Dan : > Both of your threads are using the same database handle, no? Executing yes > a "BEGIN EXCLUSIVE" would lock out a second database handle. The > database > lock is a property of the database handle, not the thread from which > the "BEGIN EXCLUSIVE" happened to be executed. ok,

Re: [sqlite] db vs shell

On Tue, Jul 29, 2008 at 01:26:54AM -0500, Robert Citek wrote: > Why the difference in time? Your first test is only measuring how long sqlite needs to 'select foo from bar'; all the commands after the pipe are ignored by 'time'. Try this: time -p sh -c "sqlite3 sample.db 'select foo from bar ;

Re: [sqlite] db vs shell

On Tue, Jul 29, 2008 at 1:31 AM, <[EMAIL PROTECTED]> wrote: > On Tue, Jul 29, 2008 at 01:26:54AM -0500, Robert Citek wrote: >> Why the difference in time? > > Your first test is only measuring how long sqlite needs to 'select foo from > bar'; > all the commands after the pipe are ignored by

Re: [sqlite] db vs shell

On Tue, Jul 29, 2008 at 02:15:54AM -0500, Robert Citek wrote: > Are you sure time ignores everything after the pipe? Seems to depend on shell version - when I tested it here it definitely ignored everything after. Yours seems to do the right thing, which makes your sqlite issue an interesting

Re: [sqlite] threads and database lock

now I tried with two different handle, one in each thread (and each thread execute a sqlite3_open), but the insertion fails with : 'database is locked' error. I thought this was handled by sqlite since 3.5 version. http://www.mail-archive.com/sqlite-users@sqlite.org/msg28089.html

Re: [sqlite] db vs shell

On Tue, Jul 29, 2008 at 2:23 AM, <[EMAIL PROTECTED]> wrote: > On Tue, Jul 29, 2008 at 02:15:54AM -0500, Robert Citek wrote: >> Are you sure time ignores everything after the pipe? > > Seems to depend on shell version - when I tested it here it definitely > ignored everything after. Yours seems to

Re: [sqlite] db vs shell

On Tue, Jul 29, 2008 at 02:29:53AM -0500, Robert Citek wrote: > $ sqlite3 -version > 3.4.2 On 3.4.0 and 3.5.9 here, the pure-SQL version is -much- faster than the shell pipe. Could you tell us more about the contents of your database? Cheers, Peter ___

Re: [sqlite] db vs shell

On Tue, Jul 29, 2008 at 2:35 AM, <[EMAIL PROTECTED]> wrote: > On Tue, Jul 29, 2008 at 02:29:53AM -0500, Robert Citek wrote: >> $ sqlite3 -version >> 3.4.2 > > On 3.4.0 and 3.5.9 here, the pure-SQL version is -much- faster than the shell > pipe. Could you tell us more about the contents of your

Re: [sqlite] db vs shell

2008/7/29 Robert Citek <[EMAIL PROTECTED]>: > On Tue, Jul 29, 2008 at 2:35 AM, <[EMAIL PROTECTED]> wrote: >> On Tue, Jul 29, 2008 at 02:29:53AM -0500, Robert Citek wrote: >>> $ sqlite3 -version >>> 3.4.2 >> >> On 3.4.0 and 3.5.9 here, the pure-SQL version is -much- faster than the shell >> pipe.

Re: [sqlite] db vs shell

On Tue, Jul 29, 2008 at 03:27:20AM -0500, Robert Citek wrote: > real 3.25 .. > real 22.48 I'm seeing the second being twice as -fast- as the first one here, still. How many CPU cores are in your testing machine? Parallel execution -might- explain the difference. Cheers, Peter

[sqlite] PRAGMA synchronous

Hello! Can I'm using "PRAGMA synchronous" on per-database basis? How to attach database with "PRAGMA synchronous = OFF;" mode to main database with "PRAGMA synchronous = ON;" mode? Best regards, Alexey. ___ sqlite-users mailing list

Re: [sqlite] threads and database lock

"Sébastien Escudier" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > now I tried with two different handle, one in each thread > > In these messages I read : > "with SQLite 3.5, access to each > database connection is serialized. So even though the interface > allows you to have 20

Re: [sqlite] threads and database lock

Quoting Igor Tandetnik <[EMAIL PROTECTED]>: > How do you think this is applicable? It talks about multiple threads > running statements on the same connection Yes, and this is the first thing I tried, but in that case, the BEGIN EXCLUSIVE; is useless for queries from other threads. > With

Re: [sqlite] db vs shell

On Tue, Jul 29, 2008 at 4:25 AM, <[EMAIL PROTECTED]> wrote: > On Tue, Jul 29, 2008 at 03:27:20AM -0500, Robert Citek wrote: >> real 3.25 > .. >> real 22.48 > > I'm seeing the second being twice as -fast- as the first one here, still. I don't follow. 22/3 ~ 7. Or do you mean when you run the

Re: [sqlite] FTS statistics and stemming

Hello! В сообщении от Tuesday 29 July 2008 00:18:40 Scott Hess написал(а): > On Sat, Jul 26, 2008 at 1:28 PM, Stephen Woodbridge > > <[EMAIL PROTECTED]> wrote: > > Alexey Pechnikov wrote: > >> I'm know that ispell, myspell, hunspell and trigrams are used in > >> PostgreSQL FTS. A lot of languages

Re: [sqlite] db vs shell

I'm seeing a similar speed different with the 3X performance difference: [EMAIL PROTECTED]:~/work$ true && ( set -x > sqlite3 sample.db 'create table bar (foo text)' > seq -w 1 200 | sed 's/^/id/' > list.txt > sqlite3 sample.db '.imp "list.txt" "bar"' > time -p sqlite3 sample.db 'select

Re: [sqlite] db vs shell

Do things improve any if you increase the temporary cache size? Compile with -DSQLITE_DEFAULT_TEMP_CACHE=100 or something? How much memory does the [sort] process consume in the shell version? What percentage of records are being trimmed by the first [uniq] in the pipeline? Dan. On Jul 29,

Re: [sqlite] db vs shell

Using sqlite 3.5.9: My results: Piped shell real 27.91 user 27.21 sys 0.78 DB : real 29.59 user 28.57 sys 0.96 Perhaps this is an issue with 3.6.0 ? Piped: real 28.44 user 27.85 sys 0.86 DB: real 74.24 user 72.96 sys 0.94 I compliled sqlite 3.6.0 from the full source. When running both seem

[sqlite] what is the best amalgamation for a build on Solaris 9?

Hi, I want to build Sqlite on Solaris 9. What is the best amalgamation? Are there any build Makefiles for building on Solaris? Any input on this will be helpful. Thanks, Bill Shurtleff ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Does sqlite support stored procedure?

Adding Javascript to Sqlite as a stored procedure language was a fairly simple operation. Try it if you need stored procedures. BareFeet wrote: > Hi John, > > >>I would like to know if SQLite supports stored procedures. > > > Technically, no it doesn't. > > For what purpose do you want to

Re: [sqlite] Does sqlite support stored procedure?

On Tue, Jul 29, 2008 at 11:39 AM, John Stanton <[EMAIL PROTECTED]> wrote: > Adding Javascript to Sqlite as a stored procedure language was a fairly > simple operation. Try it if you need stored procedures. Woah - that sounds neat and something interesting to the Mozilla project. Care to

Re: [sqlite] Does sqlite support stored procedure?

John Stanton wrote: > Adding Javascript to Sqlite as a stored procedure language was a fairly > simple operation. Try it if you need stored procedures. This sounds really interesting. How to you make access to the sqlite3 api in javascript? Would you need to? Seems like you would need to at

[sqlite] solaris 9 build instructions

Hello, I found & extracted the source. Now are there any build instructions for Solaris 9 anywhere? I haven't been on UNIX in a long time. Also, if there are any dependencies is there a list somewhere? Thanks, Bill Shurtleff Bill Shurtleff Sr. Software Engineer Petris Technology

Re: [sqlite] Convert the CURRENT_TIMESTAMP

Hi All, I still have the problem to set the result of the below statement to variable so I can print out mulitple times without the executing the select statement over and over again. If you have a solution/syntax to set variable please share with me. Thank, JP  select '#device local time = ' ||

[sqlite] winDelete retry-on-failure functionality isn't working

Hi, winDelete in os_win.c has retry functionality to try multiple times to delete a file if a virus scanner or indexing program has a handle open on that file. We've seen SQLite failures that have been tracked down to other apps temporarily opening our db journal files, so we believe that the

Re: [sqlite] Does sqlite support stored procedure?

Can you send examples? Thanks Woody from his pda -Original Message- From: John Stanton <[EMAIL PROTECTED]> Sent: Tuesday, July 29, 2008 11:39 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Does sqlite support stored procedure? Adding

Re: [sqlite] Does sqlite support stored procedure?

I just added an Sqlite interface into Spidermonkey. I can send you the code if you are interested. We used Javascript instead of PL/SQL because it is so well known and the backend code which executes in the DB matches the frontend stuff running in the browser and there is less learning

Re: [sqlite] Does sqlite support stored procedure?

I wrote an Sqlite module which links into Spidermonkey and connects to Sqlite using the Sqlite API. Stephen Woodbridge wrote: > John Stanton wrote: > >>Adding Javascript to Sqlite as a stored procedure language was a fairly >>simple operation. Try it if you need stored procedures. > > >

Re: [sqlite] Does sqlite support stored procedure?

Hi everyone, Now the discussion has deviated from my original question. But the topic is interesting. We also access SQLite from javascript (through MOZILLA's firefox codebase). Mozilla has a set of XPCOM components that allow easy Javscript access to SQLite (firefox uses SQLite for