Hi there,
we are on an challanging project with very high requirements on performance.
When doing some debugging we discover, that the sqlite method for creating
an memory-based database is much slower than using e.g /dev/shm on linux or
/tempfs on solaris. (We have measured an 20min performance
Hi all,
i'm using "sqlite3_create_function()" to create own functions in my
database.
this is a really nice possibility to extend the database with powerful
functions.
but i did not find a way to create own pragmas like "pragma foo=bar" to
pass some global information to my application.
i can
Dave Dyer wrote:
At 08:04 PM 11/30/2006, John Stanton wrote:
Those are Macintosh issues, not Sqlite, and you need to handle them in your
application.
Yes indeed. I'm only suggesting that sqlite would be a better
substrate if it provided a supported way to tell me "I can't open
the database"
Günter Greschenz <[EMAIL PROTECTED]> wrote:
i'm using "sqlite3_create_function()" to create own functions in my
database.
this is a really nice possibility to extend the database with powerful
functions.
but i did not find a way to create own pragmas like "pragma foo=bar"
to pass some global
hi,
i implemented crypting for values:
insert into test (x, y, z) values(encrypt('this is a test', 'pwd'),
'foo', 'bar')
insert into test (x, y, z) values(encrypt('this is test 2', 'pwd'),
'foo2', 'bar2')
...
select decrypt(x, 'pwd') x, y, z from test where decrypt(x, 'pwd')
Try instead of "SELECT * FROM table WHERE name LIKE ?" as your sql query,
"SELECT * FROM table WHERE name LIKE :comparison"
Thomas Zangl wrote:
Vitali Lovich schrieb:
Regarding your code snippet:
// SQL Statement is: "SELECT * FROM table WHERE name LIKE ?"
search = '%test%';
I want to preallocate disk space for database. I have only one table with ~1
milion entries, each entry takes about 30 bytes. Entries are added/removed
but there is some maximum number of items, which can be put into the table
at the same time. Is it possible to allocate a fixed disk space for
Hello List
I encounterd an strange behaviour with rowid and last_insert_rowid().
Here's the Code:
BEGIN TRANSACTION;
DROP TABLE IF EXISTS tb_language;
CREATE TABLE tb_language (
tag INTEGER PRIMARY KEY AUTOINCREMENT
,lang_code VARCHAR(7) NOT NULL
,desc_german VARCHAR(25) NOT NULL
,desc_native
Inoqulath <[EMAIL PROTECTED]> wrote:
>
> Here's the Code:
>
> BEGIN TRANSACTION;
> []
> COMMIT;
>
> When I do a "SELECT tag FROM vw_last_insert_language_tag" I think 5
> should be returned (since there are 5 INSERTS with 5 autoinc'd), but I
> receive 1 as result. Is there anything else I
"kamil" <[EMAIL PROTECTED]> wrote:
> I want to preallocate disk space for database. I have only one table with ~1
> milion entries, each entry takes about 30 bytes. Entries are added/removed
> but there is some maximum number of items, which can be put into the table
> at the same time. Is it
Dave Dyer wrote:
At 08:04 PM 11/30/2006, John Stanton wrote:
Those are Macintosh issues, not Sqlite, and you need to handle them in your
application.
Yes indeed. I'm only suggesting that sqlite would be a better
substrate if it provided a supported way to tell me "I can't open
the
[EMAIL PROTECTED] wrote:
Hi there,
we are on an challanging project with very high requirements on performance.
When doing some debugging we discover, that the sqlite method for creating
an memory-based database is much slower than using e.g /dev/shm on linux or
/tempfs on solaris. (We have
kamil wrote:
I want to preallocate disk space for database. I have only one table with ~1
milion entries, each entry takes about 30 bytes. Entries are added/removed
but there is some maximum number of items, which can be put into the table
at the same time. Is it possible to allocate a fixed
Günter Greschenz <[EMAIL PROTECTED]> wrote:
i implemented crypting for values:
insert into test (x, y, z) values(encrypt('this is a test', 'pwd'),
'foo', 'bar')
insert into test (x, y, z) values(encrypt('this is test 2', 'pwd'),
'foo2', 'bar2')
...
select decrypt(x, 'pwd') x, y, z
Günter Greschenz wrote:
hi,
i implemented crypting for values:
insert into test (x, y, z) values(encrypt('this is a test', 'pwd'),
'foo', 'bar')
insert into test (x, y, z) values(encrypt('this is test 2', 'pwd'),
'foo2', 'bar2')
...
select decrypt(x, 'pwd') x, y, z from test
You need to get help from Dr Hipp for that. I believe he has a special
cut down version of Sqlite which omits the compiler and has a special
way of storing prepared SQL statements. It is obviously intended for
small footprint embedded applications with a specific functionality.
Kalyani
On Thu, Nov 30, 2006 at 10:52:55PM -0600, John Stanton wrote:
> Sqlite has a carefully thought through minimalism. Feature creep would
> detract from its function as a small footprint, embedded DB. If you
> want different features there is nothing to stop you adding your own
> library
You could look at the Sqlite data structures and write a program to
build the free pages list. I still don't understand why you need to
pre-allocate space.
If you are using Linux or Unix you can make a file system of a fixed
size for the DB. On Windows you could partition the disk into a
I cannot see a reason for what you propose, but you could do it by brute
force and ignorance - populate the DB with 1 million rows then delete
them all to add all the space to the free pages list. Then your
insertions will use the freed pages, not fresh ones.
kamil wrote:
I want to
On Fri, Dec 01, 2006 at 08:35:24AM +0100, kamil wrote:
> I want to preallocate disk space for database. I have only one table with ~1
> milion entries, each entry takes about 30 bytes. Entries are added/removed
> but there is some maximum number of items, which can be put into the table
> at
You might discover that a memory based database has few advantages over
a disk based one, since Sqlite uses cacheing and the OS uses virtual
memory file cacheing. The main difference might be initial accesses
being slower while the cache fills up.
I understand that Sqlite uses a less
On Fri, 1 Dec 2006, John Stanton wrote:
I cannot see a reason for what you propose, but you could do it by brute
force and ignorance - populate the DB with 1 million rows then delete them
all to add all the space to the free pages list. Then your insertions will
use the freed pages, not
John Stanton <[EMAIL PROTECTED]> wrote:
>
> I understand that Sqlite uses a less efficient algorithm for :memory
> files than for the cacheing on regular files and that means lower
> performance on those memory DBs.
>
It uses exactly the same algorithm. It is just that the hash
tables are
My recollection of using MPE was performance measured in furlongs per
fortnight and the need to do that allocation strictly to get contiguous
space to try to counter the dismal performance of the HPIB disks which
were boat anchors on some of the HP/3000's.
Fortunately we don't have to indulge
> -Ursprüngliche Nachricht-
> Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Gesendet: Freitag, 1. Dezember 2006 18:06
> An: sqlite-users@sqlite.org
> Betreff: Re: [sqlite] for what reason :memory: is much slower than
> /dev/shm/dummy.db
>
>
> John Stanton <[EMAIL PROTECTED]> wrote:
[EMAIL PROTECTED] wrote:
> >
> > It uses exactly the same algorithm. It is just that the hash
> > tables are fixed sized and are designed for a cache, not for
> > an entire database.
>
> Does this mean, that if i use :memory: databases, the cache-layer of =
> sqlite
> is disabled?
>
No. It
At 09:34 01/12/2006, you wrote:
Hi there,
we are on an challanging project with very high requirements on performance.
When doing some debugging we discover, that the sqlite method for creating
an memory-based database is much slower than using e.g /dev/shm on linux or
/tempfs on solaris. (We
Eduardo Morras wrote:
At 09:34 01/12/2006, you wrote:
Hi there,
we are on an challanging project with very high requirements on
performance.
When doing some debugging we discover, that the sqlite method for
creating
an memory-based database is much slower than using e.g /dev/shm on
linux
Because our project needs to be ported to windows - the /dev/shm is not an
option - because win2000 does not support any temporary memory based file
system.
Not so.
"FILE: Ramdisk.sys sample driver for Windows 2000"
http://support.microsoft.com/kb/257405
Even includes C code, along with a
On 12/1/06, Isaac Raway <[EMAIL PROTECTED]> wrote:
> Because our project needs to be ported to windows - the /dev/shm is not an
> option - because win2000 does not support any temporary memory based file
> system.
Not so.
"FILE: Ramdisk.sys sample driver for Windows 2000"
Hello all,
I've read the documentation, and the wiki and the pages at
http://www.sqlite.org/compile.html
However, I cannot find what is the purpose of the compiler option
SSQLITE_OMIT_PARSER
Clearly it "Omits" the "Parser", but my real questions are:
what is the purpose of the parser.
What are
Having a reasonably big SQLite database, 21 tables, one table with some
millions of rows, overall file size about 1.3 Gb. This table will only get
SELECT statements once it has been created. I have now run analyze on this
file and it does speed queries up indeed.
Do I run analyze after the table
On 12/1/06, RB Smissaert <[EMAIL PROTECTED]> wrote:
Having a reasonably big SQLite database, 21 tables, one table with some
millions of rows, overall file size about 1.3 Gb. This table will only get
SELECT statements once it has been created. I have now run analyze on this
file and it does
"RB Smissaert" <[EMAIL PROTECTED]> wrote:
> Having a reasonably big SQLite database, 21 tables, one table with some
> millions of rows, overall file size about 1.3 Gb. This table will only get
> SELECT statements once it has been created. I have now run analyze on this
> file and it does speed
"RB Smissaert" <[EMAIL PROTECTED]> wrote:
> Having a reasonably big SQLite database, 21 tables, one table with some
> millions of rows, overall file size about 1.3 Gb. This table will only get
> SELECT statements once it has been created. I have now run analyze on this
> file and it does speed
You send it SQL statements. It has to parse and compile them for
execution. The parser understands the grammar of SQL and applies it.
Noah Hart wrote:
Hello all,
I've read the documentation, and the wiki and the pages at
http://www.sqlite.org/compile.html
However, I cannot find what is the
"Noah Hart" <[EMAIL PROTECTED]> wrote:
>
> However, I cannot find what is the purpose of the compiler option
> SSQLITE_OMIT_PARSER
>
> Clearly it "Omits" the "Parser", but my real questions are:
> what is the purpose of the parser.
> What are the ramification of omitting it from sqlite?
>
OK, thanks.
I can see it makes sense to run analyze after the DB is completed.
I take it I can run the analyze before the commit, just as any other action
query.
This is the output from SELECT * FROM sqlite_stat1;
tbl idx stat
---
ENTRY IDX14ENTRY 3121645 104
ENTRY
> And also the output of running sqlite3_analyzer on your database
Have run that now and the output looks impressive, although I have no idea
yet how to use it. Output file is 72 Kb. Shall I send it as an attachement
to this user-group?
RBS
-Original Message-
From: [EMAIL PROTECTED]
"RB Smissaert" <[EMAIL PROTECTED]> wrote:
> > And also the output of running sqlite3_analyzer on your database
>
> Have run that now and the output looks impressive, although I have no idea
> yet how to use it. Output file is 72 Kb. Shall I send it as an attachement
> to this user-group?
>
Thank you, so as I understand it, the option SQLITE_OMIT_PARSER mean
that I've already parsed the statements, and are not supporting "ad-hoc"
SQL.
What about the compiler option SQLITE_OMIT_CHECK
Thanks,
Noah Hart
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
OK, have sent the file to [EMAIL PROTECTED]
RBS
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: 02 December 2006 00:00
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] When to run analyze?
"RB Smissaert" <[EMAIL PROTECTED]> wrote:
> > And also the output of
"Noah Hart" <[EMAIL PROTECTED]> wrote:
> Thank you, so as I understand it, the option SQLITE_OMIT_PARSER mean
> that I've already parsed the statements, and are not supporting "ad-hoc"
> SQL.
>
> What about the compiler option SQLITE_OMIT_CHECK
>
CHECK constrants.
--
D. Richard Hipp <[EMAIL
I think this is a different question, unrelated to the previous
sqlite_open thread.
I'm in a WIN32 environment. I'm using:
h = CreateFile(path, GENERIC_READ, FILE_SHARE_READ ||
FILE_SHARED_WRITE,
NULL, OPEN_EXISTING, FILE_ATTRIBUTE_NORMAL, NULL);
to establish if my DB exists
On 12/1/06, Dixon Hutchinson <[EMAIL PROTECTED]> wrote:
I have multiple processes accessing the resulting DB. One of those
processes only queries the DB. I call:
h = CreateFile(path, GENERIC_READ, FILE_SHARE_READ,
NULL, OPEN_EXISTING, FILE_ATTRIBUTE_NORMAL, NULL);
to verify the
I am busy adding virtual table support to APSW and have some comments
from my progress so far:
The structure types and "subclassing" method of using them works well
and the API is easy to use. (Admittedly I did spend a lot of quality
time with the xBestIndex method documentation but that is to
Cnichols wrote:
> I disagree I myself see this as a feature. I think the programmer should be
> responsible for file checking because if it is not a SQLite database SQLite
> will inform you.
The one fly in the ointment is all the Unicode/UTF-8 stuff. Your own
code has to go through the exact
On 12/1/06, Dixon Hutchinson <[EMAIL PROTECTED]> wrote:
I think this is a different question, unrelated to the previous
sqlite_open thread.
I'm in a WIN32 environment. I'm using:
h = CreateFile(path, GENERIC_READ, FILE_SHARE_READ ||
FILE_SHARED_WRITE,
NULL, OPEN_EXISTING,
Trevor,
Just to be safe, I tried specifying FILE_SHARE_WRITE, that did not help.
FILE_SHARE_WRITE would allow other "writers" to open the file shared,
but should not have any effect on whether this open succeeds.
If I ignore the failure and try to open the DB with sqlite3_open, the
open
On 12/1/06, Dixon Hutchinson <[EMAIL PROTECTED]> wrote:
Just to be safe, I tried specifying FILE_SHARE_WRITE, that did not help.
FILE_SHARE_WRITE would allow other "writers" to open the file shared,
but should not have any effect on whether this open succeeds.
Sharing semantics are
50 matches
Mail list logo