RE: [sqlite] sqlite performance questions.

[Robert Simpson]

> -Original Message-
> From: Mohd Radzi Ibrahim [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, October 18, 2006 3:23 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] sqlite performance questions.
> 
> Hi,
> Could you tell me what breaks the transaction? Is create 
> table/index within 
> transaction breaks it?

I'm not sure what you mean by "break" -- can you clarify?

Robert





-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Extra functions - New Project?

[RohitPatel9999]

Mike

When are you planning to put code of your SQL functions for SQLite ?

Waiting...eagerly...
I may try to use it in my app.

Thanks
Rohit

-- 
View this message in context: 
http://www.nabble.com/Extra-functions---New-Project--tf1674436.html#a6887312
Sent from the SQLite mailing list archive at Nabble.com.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] sqlite performance questions.

[Isaac Raway]

I'm going to agree with Robert here, I have an application that makes heavy
use of large blob of text in a sqlite database. Performance was unbearable,
wrapping even small sets of operations in transactions greatly improved the
performance. I don't have numbers, but suffice it to say that it went from a
noticeable and unacceptable delay when saving a record down to something I
can do automatically without a seperate thread--it's so face it's not
noticeable.

Isaac

On 10/18/06, Robert Simpson <[EMAIL PROTECTED]> wrote:


> -Original Message-
> From: Andrew Cheyne [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, October 18, 2006 8:08 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] sqlite performance questions.

[snip]
>   I have then been writing some sample C programs making use
> of the C API,
> but have been very disappointed in the performance of
> Œinsert¹ing into the
> database. For example, timing the performance of executing an insert
> statement into this table only gives me an insertion rate of
> 6 rows per
> second (³insert into Node (url, filename) values (Œfoo¹,¹bar¹);²).

3 words -- Transaction Transaction Transaction!

Start a transaction before you start bulk inserting, and commit it
afterwards.  You are being bitten by SQLite's ACID compliance.  Any
statement not wrapped in a transaction is automatically placed inside its
own transaction.  6 rows per second is the fastest rate at which a
transaction can be spun up, 1 insert performed, the buffers flushed to the
physical disk, and the transaction torn down.

By starting the transaction beforehand, and committing it when you're
done,
you save the worst parts of the task for the beginning and end, and only
perform them once.

Robert




-
To unsubscribe, send email to [EMAIL PROTECTED]

-





--
Isaac Raway
Entia non sunt multiplicanda praeter necessitatem.

http://blueapples.org - blog
http://stonenotes.com - personal knowledge management

[sqlite] Best GUI Toolkit. Was: [sqlite] SQLite GUI app that offers layouts

[drh]

FWIW, as all these emails were arriving I was busy working
on an application for a customer that consists of 25K+ lines
of Tcl/Tk plus some C extensions.  The whole thing compiles
into a standalone binary that is right at 4MiB.  SQLite is
used as the application file format.  (That is to say, when
you do File/Open to select a file to work on, that file is
really an SQLite database file, even though the end user
doesn't realize it.)  The program features advanced graphics
including interactive 3-D visualization using OpenGL and
the 3D-Canvas extension to Tcl/Tk.  (http://3dcanvas.tcl.tk/)
Runs on Linux and Win32.  I was planning to port to Mac OSX
next week.  (There are some issues getting the 3dcanvas to
work there.)  The GUI has a gray-metal desk look to it, but it
is quite functional and the customer is very pleased.  It
wouldn't take much work to give the GUI more eye-candy, but
as the program is intended for use by a limited set of 
engineers within a laboratory environment, that is not a 
goal of the project.

If you can tolerate a 4MiB standalone (much smaller if you
don't need all the extensions I'm using) then Tcl/Tk is
an excellent choice.  My code would be much larger and
considerably less functional in wxPython.  I've never used
lua and cannot comment on it.  You should, of course, use 
whatever toolkit you are most comfortable with.

--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLite GUI app that offers layouts

[Noel Frankinet]

Rob Sciuk a écrit :

On Wed, 18 Oct 2006, Rich Shepard wrote:
  

Rob,

   And, ... there's pysqlite2 which is a teriffic implementation of the
python database API.

   I'm using python, wxPython, and pysqlite2 in our approximate reasoning
models. It's a nice system.

Rich



So many scripting languages, and so little time.  Noel is making a valiant
case for Lua/IUP widgets, though I'm only vaguely aware of that language.
What was that link again?
  

htpp://www.lua.org
http://luaforge.net/projects/iup/


thinking about it, storing xml (or generating xml on the fly from a 
registered sqlite command) seems better than generating tcl/python/lua 
whatever code ? Its also easy to send/receive remotely.
My NGUI controls accept xml directly, so you can feed them from sqlite 
without additional step.

Anyway, there's always some tradeoff (size, speed, portability)...

Best wishes.


--
Noël Frankinet
Gistek Software SA
http://www.gistek.net


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLite GUI app that offers layouts

[Rob Sciuk]

On Wed, 18 Oct 2006, Rich Shepard wrote:
> Rob,
>
>And, ... there's pysqlite2 which is a teriffic implementation of the
> python database API.
>
>I'm using python, wxPython, and pysqlite2 in our approximate reasoning
> models. It's a nice system.
>
> Rich

So many scripting languages, and so little time.  Noel is making a valiant
case for Lua/IUP widgets, though I'm only vaguely aware of that language.
What was that link again?

Commercial success for a scripting language seems to be tied to its
critical mass and *COOL* factor, and how much is being contributed, which
has a tendancy to fade with the NEXT BIG THING (eg: Java ->Ruby/Rails??).

It seems that scripting languages are the new religion (was vi/emacs), and
each seems to have a killer appeal to some.  I was big on Forth many years
ago, and went so far as to write my own portable interpreter for mini's,
but alas, Forth has been relegated to Forth for its own sake amoung some
die-hards rather than for its interesting applications it seems (no flames
please).

I'm surprised that no Ruby/Perl/Rexx/... fanatics have waded in just yet.
Testimony again to the design of SQLite, and its ease of integration is
the fact that it supports just about every language out there.

But, in order to keep this thread on topic, once again, a slavish suckup
to DRH for his remarkable contribution to Open Source -- even to the point
of creating a new class of license.

Thanks, Richard Hipp -- SQLite is a remarkable and useful achievement!!

Rob Sciuk.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLite GUI app that offers layouts

[Rich Shepard]

On Wed, 18 Oct 2006, Rob Sciuk wrote:


Or that ... I imagine that the wxPython combination is more modern
looking, given the native look and feel of the wxWidget set project,
though I've not tried it yet.  Hmmm ... note to self ...


Rob,

  And, ... there's pysqlite2 which is a teriffic implementation of the
python database API.

  I'm using python, wxPython, and pysqlite2 in our approximate reasoning
models. It's a nice system.

Rich

--
Richard B. Shepard, Ph.D.   |The Environmental Permitting
Applied Ecosystem Services, Inc.(TM)|Accelerator
 Voice: 503-667-4517  Fax: 503-667-8863

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLite GUI app that offers layouts

[Noel Frankinet]

Rich Shepard a écrit :

On Wed, 18 Oct 2006, Rob Sciuk wrote:


Interesting, but I'm wondering why not use the tcl/tk binding to simply
generate the forms using the well crafted tcl binding which comes 
with the

SQLite language?  Tcl/TK is a very simple scripting language which is
portable to all major platforms, has a very useful GUI widget set, and
while it might not be the prettiest interface in the world, getting very
useable forms up and running using sqlite/tk is trivial. 


  Alternative #3: write your application in python and use the wxPython
widget set. This combination is portable across OSes and uses the native
widgets so the "look and feel" is consistent.

Rich


Alternative #4 : write your application in lua and use IUP widgets set.
This combination is portable across OSes and uses the native
widgets so the "look and feel" is consistent but the size is 10 time 
smaller.


--
Noël Frankinet
Gistek Software SA
http://www.gistek.net


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLite GUI app that offers layouts

[Rob Sciuk]

On Wed, 18 Oct 2006, Rich Shepard wrote:
> On Wed, 18 Oct 2006, Rob Sciuk wrote:
>
> > Interesting, but I'm wondering why not use the tcl/tk binding to simply
> > generate the forms using the well crafted tcl binding which comes with the
> > SQLite language?  Tcl/TK is a very simple scripting language which is
> > portable to all major platforms, has a very useful GUI widget set, and
> > while it might not be the prettiest interface in the world, getting very
> > useable forms up and running using sqlite/tk is trivial. 
>
>Alternative #3: write your application in python and use the wxPython
> widget set. This combination is portable across OSes and uses the native
> widgets so the "look and feel" is consistent.
>
> Rich

Or that ... I imagine that the wxPython combination is more modern
looking, given the native look and feel of the wxWidget set project,
though I've not tried it yet.  Hmmm ... note to self ...

Rob.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLite GUI app that offers layouts

[Noel Frankinet]

<.02$>
Interesting, but I'm wondering why not use the tcl/tk binding to simply
generate the forms using the well crafted tcl binding which comes with the
SQLite language?  Tcl/TK is a very simple scripting language which is
portable to all major platforms, has a very useful GUI widget set, and
while it might not be the prettiest interface in the world, getting very
useable forms up and running using sqlite/tk is trivial.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



  


yes, tcl/tk is another option, I used to use it a lot.
However tcl/tk is a little bit fat(6Mb), you need zillion of extension 
to get a good looking gui and there is no object support in the core. 
Lua is less than 300k, ngui also, so we are not playing in the same 
category.



--
Noël Frankinet
Gistek Software SA
http://www.gistek.net


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLite GUI app that offers layouts

[Rich Shepard]

On Wed, 18 Oct 2006, Rob Sciuk wrote:


Interesting, but I'm wondering why not use the tcl/tk binding to simply
generate the forms using the well crafted tcl binding which comes with the
SQLite language?  Tcl/TK is a very simple scripting language which is
portable to all major platforms, has a very useful GUI widget set, and
while it might not be the prettiest interface in the world, getting very
useable forms up and running using sqlite/tk is trivial. 


  Alternative #3: write your application in python and use the wxPython
widget set. This combination is portable across OSes and uses the native
widgets so the "look and feel" is consistent.

Rich

--
Richard B. Shepard, Ph.D.   |The Environmental Permitting
Applied Ecosystem Services, Inc.(TM)|Accelerator
 Voice: 503-667-4517  Fax: 503-667-8863

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] sqlite performance questions.

[Robert Simpson]

> -Original Message-
> From: Andrew Cheyne [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, October 18, 2006 8:08 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] sqlite performance questions.

[snip]
>   I have then been writing some sample C programs making use 
> of the C API,
> but have been very disappointed in the performance of 
> Œinsert¹ing into the
> database. For example, timing the performance of executing an insert
> statement into this table only gives me an insertion rate of 
> 6 rows per
> second (³insert into Node (url, filename) values (Œfoo¹,¹bar¹);²).

3 words -- Transaction Transaction Transaction!

Start a transaction before you start bulk inserting, and commit it
afterwards.  You are being bitten by SQLite's ACID compliance.  Any
statement not wrapped in a transaction is automatically placed inside its
own transaction.  6 rows per second is the fastest rate at which a
transaction can be spun up, 1 insert performed, the buffers flushed to the
physical disk, and the transaction torn down.

By starting the transaction beforehand, and committing it when you're done,
you save the worst parts of the task for the beginning and end, and only
perform them once.

Robert



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLite GUI app that offers layouts

[Rob Sciuk]

On Wed, 18 Oct 2006, Noel Frankinet wrote:

> Date: Wed, 18 Oct 2006 17:23:43 +0200
> From: Noel Frankinet <[EMAIL PROTECTED]>
> Reply-To: sqlite-users@sqlite.org
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] SQLite GUI app that offers layouts
>
> Sorry, the correct url is :
>
> http://www.gistek.net/gui.html

<.02$>
Interesting, but I'm wondering why not use the tcl/tk binding to simply
generate the forms using the well crafted tcl binding which comes with the
SQLite language?  Tcl/TK is a very simple scripting language which is
portable to all major platforms, has a very useful GUI widget set, and
while it might not be the prettiest interface in the world, getting very
useable forms up and running using sqlite/tk is trivial.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLite GUI app that offers layouts

[Noel Frankinet]

Sorry, the correct url is :

http://www.gistek.net/gui.html

--
Noël Frankinet
Gistek Software SA
http://www.gistek.net


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLite GUI app that offers layouts

[Noel Frankinet]

COS a écrit :

Hi Tom,

- Original Message - 
From: "T" <[EMAIL PROTECTED]>

To: 
Sent: Tuesday, October 17, 2006 9:47 PM
Subject: [sqlite] SQLite GUI app that offers layouts


  

Hi all,

There seem to be several SQLite GUI apps around. But I haven't seen
any that offer building of layouts, ie positioning test frames for
fields on a page for form data entry or printing. This feature is
typical of proprietary apps such as FileMaker, AppleWorks, 4D etc.

Anyone know of a GUI app that offers layouts (ie form creation),
especially on a Mac (but even other platforms)?



DBManager Enterprise Edition (http://www.dbtools.com.br/EN/dbmanagerpro) can
do that. It has a form designer which allows to create and execute forms
like MSAccess does. This manager is for windows only and there is a TRIAL
Edition which you can test and see how it works.

  

Is there a conventional way to store the layout information in the
SQLite database itself, so it can be moved from one SQLite GUI app to
another, and retain compatible forms?



I doubt you will find something like. Usually the form is handled only by
the GUI which created it. For example, you can't execute MSAcess forms on
any other GUI. Because of that it doesn't matter if the form is saved in the
SQLite DB or not, since most GUI's will not know what to do with it.

Hope that helps,

COS


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



  

Hello Tom,

With my NGUI (http://www.gistek.net/ngui.htm) framework, I define a form 
layout in XML and the form behavior (on events) in lua.
That info can be stored in a sqlite table and a registered sqlite 
command could create the form on the fly, populate it and wait for user 
input.


My framework in win32/windowsCE (pda) only, but I suppose the same could 
be done with gtk or Qt.


Lua fits well with sqlite because its very compact

Best regards

--
Noël Frankinet
Gistek Software SA
http://www.gistek.net


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] sqlite performance questions.

[Andrew Cheyne]

Hello,

  I¹m relatively new to SQLite and have a couple questions centered around
performance. I am running SQLite 3.1.3 on Mac OSX 10.4 (this is the version
that is pre-installed).

  I created a very simple database with one Table:

sqlite3 test-db "create table Node (NodeId INTEGER PRIMARY KEY, url TEXT,
filename TEXT);"

  I have then been writing some sample C programs making use of the C API,
but have been very disappointed in the performance of Œinsert¹ing into the
database. For example, timing the performance of executing an insert
statement into this table only gives me an insertion rate of 6 rows per
second (³insert into Node (url, filename) values (Œfoo¹,¹bar¹);²).

  I am wondering what options I have for speeding up these results. I know
submitting my inserts as a batch transaction would help, but I¹m not sure
that will be possible. I have also read about the PRAGMA SYNCHRONOUS
setting, and while this gives me the speed boost I¹m looking for, I don¹t
like giving up the security of the database not getting corrupted.

  How much of a performance boost will I see by precompiling my SQL
statements?

  I¹m currently making use of sqlite3_get_table() - is it any slower than
using sql3_exec() ?

Thanks for any answers and pointers to performance tweaks for sqlite!

Cheers,
Andrew Cheyne
GridIron Software

Re: [sqlite] bail out patch

[drh]

Jim Ursetto <[EMAIL PROTECTED]> wrote:
> Hi,
> 
> I created a patch which adds "-bail" and ".bail" commands to the sqlite3
> shell.  When bail is enabled, the shell will exit with a non-zero return
> code immediately upon encountering the first SQL error.  This is useful
> when you pass a number of commands to the shell non-interactively and
> wish to, for example, abandon a transaction if any errors occur.  It's
> also useful because the calling program receives an indication of
> success or failure.  Bail mode works for standard input, init files,
> ..read commands and, for completeness, interactively.
> 
> Patch is against 3.3.7.  I hope someone finds it of use.
> 

Please go to http://www.sqlite.org/cvstrac/tktnew and write an
enhancement request ticket.  Make your patch an attachment.
I'll look into it when I have a chance.
--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Transactions across attached databases

[drh]

"Nakarada, Bob" <[EMAIL PROTECTED]> wrote:
> Could someone definitively answer the following question about version
> 2.8 of SQLite and transactions on an attached database? 
> 
>  Is this expected behaviour?
> 

At this point the "expected behavior" of 2.8 is whatever it does.
If somebody finds a bug in 2.8 that can cause database corruption
or which is a realistic security vulnerability, then I'll look into
fixing it.  But beyond that, 2.8 is what it is.  I have no intent
of doing further development on 2.8.  It is maintenance only.
Any strange or goofy behavior is defined to be a feature, not a 
bug.

3.3.8 is current.  Look for 3.3.9 soon.  The world has moved on...

--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLite GUI app that offers layouts

[COS]

Hi Tom,

- Original Message - 
From: "T" <[EMAIL PROTECTED]>
To: 
Sent: Tuesday, October 17, 2006 9:47 PM
Subject: [sqlite] SQLite GUI app that offers layouts


> Hi all,
>
> There seem to be several SQLite GUI apps around. But I haven't seen
> any that offer building of layouts, ie positioning test frames for
> fields on a page for form data entry or printing. This feature is
> typical of proprietary apps such as FileMaker, AppleWorks, 4D etc.
>
> Anyone know of a GUI app that offers layouts (ie form creation),
> especially on a Mac (but even other platforms)?

DBManager Enterprise Edition (http://www.dbtools.com.br/EN/dbmanagerpro) can
do that. It has a form designer which allows to create and execute forms
like MSAccess does. This manager is for windows only and there is a TRIAL
Edition which you can test and see how it works.

> Is there a conventional way to store the layout information in the
> SQLite database itself, so it can be moved from one SQLite GUI app to
> another, and retain compatible forms?

I doubt you will find something like. Usually the form is handled only by
the GUI which created it. For example, you can't execute MSAcess forms on
any other GUI. Because of that it doesn't matter if the form is saved in the
SQLite DB or not, since most GUI's will not know what to do with it.

Hope that helps,

COS


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Transactions across attached databases

[Nakarada, Bob]

Could someone definitively answer the following question about version
2.8 of SQLite and transactions on an attached database? 

 

Following is "pseudo code' for the type of transactions we issue. The
statement to the table in the attached database sometimes fails with a
busy error; the busy wait handler kicks in and when the statement
successfully retries. When we have a condition like this we find that
the commit fails with a 'cannot commit - no transaction is active'
error. The result is that Statement #1 is not executed and yet Statement
#2 and #3 are successful. It appears that the failure causes an
"implicit' rollback. Is this expected behaviour?

 

 

Begin Transaction

Statement #1

Statement attached table - Failed Busy

Statement attached table - Success on retry

Statement #2

Statement #3

Commit Transaction

 

 

 

Bob Nakarada
Software Developer
Equitrac www.equitrac.com
Ph. (519) 885-2458 x2235 Fax. (519) 746-7931