Re: [sqlite] just a test
Igor's messages sometimes get marked as spam by gmail. -- Clive Hayward On 2012-12-03, at 7:57 AM, e-mail mgbg25171 <mgbg25...@blueyonder.co.uk> wrote: > I've posted a couple of mails lately...I'm not getting them via the list or > any responses. > Admin says Igor responded to one of them...Thanks Igor! > This is just a test to see if the mail is coming to me (as a member of the > list). > Therefore please just ignore this. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to build a new sqlite3.dylib?
Tobias, Heres how you can make your own shared library under Mac OS X. Download http://www.sqlite.org/download.html/sqlite-amalgamation-3071300.zip # In the terminal export LD_LIBRARY_PATH=. # Make the library call it libsqlite3_mybuild.dylib gcc -o libsqlite3_mybuild.dylib sqlite3.c -dynamiclib # Build the shell using the new libsqlite3_mybuild.dylib shared library. gcc -o shell shell.c -lsqlite3_mybuild -L$LD_LIBRARY_PATH ./shell SQLite version 3.7.13 2012-06-11 02:05:22 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .exit # Display the names and version numbers of the shared libraries that the object file uses. otool -L ./shell ./shell: libsqlite3_mybuild.dylib (compatibility version 0.0.0, current version 0.0.0) /usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 159.1.0) Best of Luck, Clive Hayward On Mon, Aug 6, 2012 at 3:48 PM, Tobias Giesen <tobiasgie...@gmail.com> wrote: >> Do you absolutely need to use a dynamic library ? > > I have spent a full day trying to compile & link the c and h file with > my Pascal code and I have given up. The DB library I have expects a > dylib so I will have to feed it what it can eat. > > Trouble is, I don't know how to build the dylib either so I need some > mercy from somebody who has a recent build. > > Thanks! > > Cheers, > Tobias -- Clive Hayward ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Temporary index
In the documentation below under the Pragmas section there seems to be a hint that I can create a temporary index. However the CREATE INDEX syntax does not allow the word TEMPORARY to be used. Anyone know what is going on? Clive PRAGMA temp_store; PRAGMA temp_store = DEFAULT; (0) PRAGMA temp_store = FILE; (1) PRAGMA temp_store = MEMORY; (2) Query or change the setting of the "temp_store" parameter. When temp_store is DEFAULT (0), the compile-time C preprocessor macro TEMP_STORE is used to determine where temporary tables and indices are stored. When temp_store is MEMORY (2) temporary tables and indices are kept in memory. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] step back
sqlite3_step() is great for scrolling forward through a result set. Is there a way to scroll backwards? If not, did anyone try implementing it? (I guess that the indexes would need backward pointers in order to do it.) Clive - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Index size in file
Let's assume that my whole database can be in the cache. If my indexes have duplicate data, then I will either need a bigger cache or have to page out row data in favour of index data. In that case it will either be slower or require more memory to keep duplicate data for the indexes as opposed to referencing the original data. Clive John Stanton <[EMAIL PROTECTED]> on 05/10/2007 00:54:21 Please respond to sqlite-users@sqlite.org To: sqlite-users@sqlite.org cc:(bcc: clive/Emultek) Subject: Re: [sqlite] Index size in file Trevor Talbot wrote: > On 10/4/07, John Stanton <[EMAIL PROTECTED]> wrote: > >>A B-Tree index holds keys in sorted sequence. They are in random >>sequence in the database. That requires holding the keys in the B-Tree >>nodes. > > > Actually, it doesn't strictly require that; it could store references > to the keys. An obvious tradeoff is I/O; an index walk is less useful > if you have to do random seeks to the locations of row data just to > get the keys to walk the tree in the first place. IOW in simplistic > terms, an index walk suddenly doubles in disk I/O. > > The information on SQL Server would be interesting, as I know it > stores sort keys under some conditions, which is effectively duplicate > data. > One would need to be a paleontologist to measure the performance of an ordered index with indirect key references. - To unsubscribe, send email to [EMAIL PROTECTED] - This footnote confirms that this email message has been scanned by PineApp Mail-SeCure for the presence of malicious code, vandals & computer viruses. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Index size in file
Actually yes, I am programming for a cellphone and you are right, that is the only reason I am thinking about it! Clive "Griggs, Donald" <[EMAIL PROTECTED]> on 04/10/2007 21:23:17 Please respond to sqlite-users@sqlite.org To: sqlite-users@sqlite.org cc:(bcc: clive/Emultek) Subject: RE: [sqlite] Index size in file Regarding: >>Looking at the binary of the file, I see that the index has a copy of >>all the data being indexed. >>1. Is this necassary? Unless you're programming for a cellphone or some other embedded gadget, you might want to calculate the cost (on the margin) of the disk storage for the estimated amount of duplicated data. You might find it's vastly less than the cost of your time to think about it. YMMV. This message has been scanned for viruses by MailControl - www.mailcontrol.com - To unsubscribe, send email to [EMAIL PROTECTED] - This footnote confirms that this email message has been scanned by PineApp Mail-SeCure for the presence of malicious code, vandals & computer viruses. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Index size in file
>From what I read SQL Server has 2 basic types of index: 1. Clustered, that holds the single instance of the data itself (actaully the whole row) 2. Non-clustered that hold a pointer to the single instance of the data, but not the data itself. Clive John Stanton <[EMAIL PROTECTED]> on 04/10/2007 20:02:16 Please respond to sqlite-users@sqlite.org To: sqlite-users@sqlite.org cc: (bcc: clive/Emultek) Subject: Re: [sqlite] Index size in file A B-Tree index holds keys in sorted sequence. They are in random sequence in the database. That requires holding the keys in the B-Tree nodes. A hashed type access does not have that storage overhead, but it does not deliver the rows in sorted sequence. [EMAIL PROTECTED] wrote: > > > I am not an expert on indexes, however it does seem strange to me that a > database should keep duplicate data in it. > This prompted me to look up how indexes are stored in other databases. To tell > the truth I only looked at one, and that is SQL Server. > They do not store any duplicate data. If you would like the reference I can give > it to you. > I agree with you that it means an extra lookup that could make things slower, ( > I say could because you use more space in the cache which could result in more > reads) > Anyway given that that is the way it is implemented, does anyone know if it is > possible to create an index in memory? > > Clive > > > > > > John Stanton <[EMAIL PROTECTED]> on 03/10/2007 17:36:58 > > Please respond to sqlite-users@sqlite.org > > To: sqlite-users@sqlite.org > cc:(bcc: clive/Emultek) > > Subject: Re: [sqlite] Index size in file > > > > An index which does not hold keys is not an index. If you don't want to > allocate space for indexing then you put up with slow performance and > use row searches. > > [EMAIL PROTECTED] wrote: > >> >>I created an index on a TEXT column as I want to be able to >>I noticed a large increase in the file size. >>Looking at the binary of the file, I see that the index has a copy of all the >>data being indexed. >>1. Is this necassary? >>2. Is there a way to keep the index only in memory and not in the file. >> >>Clive >> >> >> >>- >>To unsubscribe, send email to [EMAIL PROTECTED] >>- >> > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > > > > > > > This footnote confirms that this email message has been scanned by > PineApp Mail-SeCure for the presence of malicious code, vandals & computer > viruses. > > > > > > > > > > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > - To unsubscribe, send email to [EMAIL PROTECTED] - This footnote confirms that this email message has been scanned by PineApp Mail-SeCure for the presence of malicious code, vandals & computer viruses. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Index size in file
I am not an expert on indexes, however it does seem strange to me that a database should keep duplicate data in it. This prompted me to look up how indexes are stored in other databases. To tell the truth I only looked at one, and that is SQL Server. They do not store any duplicate data. If you would like the reference I can give it to you. I agree with you that it means an extra lookup that could make things slower, ( I say could because you use more space in the cache which could result in more reads) Anyway given that that is the way it is implemented, does anyone know if it is possible to create an index in memory? Clive John Stanton <[EMAIL PROTECTED]> on 03/10/2007 17:36:58 Please respond to sqlite-users@sqlite.org To: sqlite-users@sqlite.org cc:(bcc: clive/Emultek) Subject: Re: [sqlite] Index size in file An index which does not hold keys is not an index. If you don't want to allocate space for indexing then you put up with slow performance and use row searches. [EMAIL PROTECTED] wrote: > > > I created an index on a TEXT column as I want to be able to > I noticed a large increase in the file size. > Looking at the binary of the file, I see that the index has a copy of all the > data being indexed. > 1. Is this necassary? > 2. Is there a way to keep the index only in memory and not in the file. > > Clive > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > - To unsubscribe, send email to [EMAIL PROTECTED] - This footnote confirms that this email message has been scanned by PineApp Mail-SeCure for the presence of malicious code, vandals & computer viruses. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Index size in file
I created an index on a TEXT column as I want to be able to I noticed a large increase in the file size. Looking at the binary of the file, I see that the index has a copy of all the data being indexed. 1. Is this necassary? 2. Is there a way to keep the index only in memory and not in the file. Clive - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Stack usage
This is very worrying since it means that the statement cannot be compiled on a
low memory device.
I am new to Sqlite, but I would guess that a precompiled query could be used,
where memory is low
and I also suppose that variable values could be bound to that precompiled
query.
Clive
Joe Wilson <[EMAIL PROTECTED]> on 05/06/2007 14:33:42
Please respond to sqlite-users@sqlite.org
To: sqlite-users@sqlite.org
cc:(bcc: clive/Emultek)
Subject: Re: [sqlite] Stack usage
--- [EMAIL PROTECTED] wrote:
> I'v read in change log that some stack allocted memory were moved to the heap,
but I think that
> there is still to much allocated memory on the stack.
> After creating a table with 2000 columns, jdbc driver created a query that run
out of stack.
> Default java's stack limit is low, but it wasn't hard to create simillar query
that crashed C
> application with default stack limit. And the fact that it crashed instead
repoting an error
> isn't really nice.
> The query created by the driver looks like that:
>
> select null as TABLE_CAT, null as TABLE_SCHEM, 'test' as TABLE_NAME, cn as
COLUMN_NAME, dt as
> DATA_TYPE, tn as TYPE_NAME, 20 as COLUMN_SIZE, 20 as
BUFFER_LENGTH, 10 as
> DECIMAL_DIGITS, 10 as NUM_PREC_RADIX, colnullable as NULLABLE, null as
REMARKS, null as
> COLUMN_DEF, 0as SQL_DATA_TYPE, 0as SQL_DATETIME_SUB, 20 as
CHAR_OCTET_LENGTH,
> ordpos as ORDINAL_POSITION, (case colnullable when 0 then 'N' when 1 then 'Y'
else '' end)as
> IS_NULLABLE, null as SCOPE_CATLOG, null as SCOPE_SCHEMA, null as SCOPE_TABLE,
null as
> SOURCE_DATA_TYPE from (select 0 as ordpos, 1 as colnullable, 'col1' as cn,
'double' as tn, 8 as
> dt union all select 1 as ordpos, 1 as colnullable, 'col2' as cn, 'double' as
tn, 8 as dt union
> all select 2 as ordpos, 1 as colnullable, 'col3' as cn, 'double' as tn, 8 as
dt union all select
> 3 as ordpos, 1 as colnullable, 'col4' as cn, 'double' as tn, 8 as dt union all
select 4 as
> ordpos, 1 as colnullable, 'lastcol' as cn, 'double' as tn, 8 as dt);
>
> but uses more columns.
SQLite uses recursion to generate code for SELECT UNION chains.
** SELECT a FROM t1 UNION SELECT b FROM t2 UNION SELECT c FROM t3
**
** This statement is parsed up as follows:
**
** SELECT c FROM t3
** |
** `-> SELECT b FROM t2
**|
**`--> SELECT a FROM t1
So for your example you will have a stack of 2000 nested calls
of sqlite3Select's (via multiSelect).
Using gcc -O2 on x86 I see that each level's stack is 480 bytes.
So for 2000 unions in a select, SQLite will consume at least
480 bytes/frame x 2000 frames = 960,000 bytes. I'm guessing that Java
limits the stack to 1M per thread in your case, hence your problem.
It is not easy to adapt SQLite's code to not use recursion for
code generation, although with enough time and effort anything is
possible. Conceivably, the processing of compound queries could
be turned into a for loop.
The SQLite authors have recently added a number of maximum limits
via #defines. Perhaps the SQLITE_MAX_EXPR_DEPTH limit could also
be applied here:
/* If there is are a sequence of queries, do the earlier ones first.
*/
if( p->pPrior ){
if( p->pRightmost==0 ){
Select *pLoop;
for(pLoop=p; pLoop; pLoop=pLoop->pPrior){
pLoop->pRightmost = p;
}
}
return multiSelect(pParse, p, eDest, iParm, aff);
}
It's here! Your new message!
Get new email alerts with the free Yahoo! Toolbar.
http://tools.search.yahoo.com/toolbar/features/mail/
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Sorted index
I would like to maintain a sorted list of ~3000 entries. I know that I can create a table and the SELECT from it with the ORDER BY clause in order to sort it. However I do not want the overhead of doing this after adding a new entry. It would be good if I could create an index that was sorted, and then when I add a new entry to the table it would automatically be inserted in the index at the correct position. Is this possibe? If not, can anyone suggest any other solution, baring in mind that RAM memory needs to be kep to a minimum? Thanks Clive - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Not getting the speed I think is possoble. Basic select statment slow?
Did you try increasing the page cache size. Your data set is very big. pragma page_cache = 2; This should at least improve the speed for queries. Clive "Chris Schirlinger" <[EMAIL PROTECTED]> on 02-02-2005 09:07:49 Please respond to [EMAIL PROTECTED] To: sqlite-users@sqlite.org cc:(bcc: clive/Emultek) Subject: Re: [sqlite] Not getting the speed I think is possoble. Basic select statment slow? > What speed were you expecting? > Are you comparing it to another database? If so what are the results for that > database? Anything better than what we got. The results are the worst we have gotton from any DB or any self rolled data system (Jet is better, het shouldn't be better) After more tests, it is dipping to 10 records per second update time. Based on the speed showen on the web site, I was expecting to at least get 1000 records a second updating and somewhere above 10k when selecting I am assuming we MUST have mucked something up
Re: [sqlite] Not getting the speed I think is possoble. Basic select statment slow?
What speed were you expecting? Are you comparing it to another database? If so what are the results for that database? Clive "Chris Schirlinger" <[EMAIL PROTECTED]> on 02-02-2005 06:00:45 Please respond to [EMAIL PROTECTED] To: sqlite-users@sqlite.org cc:(bcc: clive/Emultek) Subject: [sqlite] Not getting the speed I think is possoble. Basic select statment slow? I've got a 6 million row DB in SQLite 3, but getting... odd results which don't match up with the speed tests I've seen The statement: SELECT * FROM myTable WHERE myKey=1000 takes between 1 second to 4 or 5 on spikes. The returned result set is ~2000 records. I havn't seen more than 2000 recs/second usually less Similarly, the query (A basic APPEND or INSERT) INSERT OR REPLACE INTO myTable (myKey, NumOne, NumTwo, NumThree, NumFour, NumFive, NumSix, NumSeven) VALUES (1000, 1, 2, 3, 4, 5, 6, 7) is doing at MOST about 300 records per second and at worst 100 a second. I have about 3000 inserts/updates all wrapped inside a single Transaction unless doing a complete population of the DB in which case it is batched but still all wrapped in transactions The schema is VERY basic: CREATE TABLE [myTable] ( [myKey] [bigint] NULL , [NumOne] [int] NULL , [NumTwo] [real] NULL , [NumThree] [real] NULL , [NumFour] [real] NULL , [NumFive] [real] NULL , [NumSix] [float] NULL , [NumSeven] [float] NULL ); CREATE INDEX myMyIndex ON myTable (myKey); CREATE UNIQUE INDEX [pkPrimary] ON [myTable] ([myKey], [NumOne]); CREATE INDEX myNumOneIndex ON myTable (NumOne); Now initially I didn't have anything except the UNIQUE index, though adding the second two hasn't made any difference once way or the other Personalyl I'd LOVE for that UNIQUE index to be a clustered index (it was elsewhere) but that just doesn't seem to be an option Any ideas where I am going wrong here? Or are these the numbers I am expected to see? (Note: I am using transactions in case I didn't make that clear, I am also doing this in Delphi using the open source Aducom.nl componants, but at the raw end it seems their code is mostly fairly close to the bare bones of the DLL exported functions. Doesn't seem to be an issue there but who knows)
Re: [sqlite] Compacting a database
Thanks. I am not sure this is very useful, because it copies the database, which requires that there be enough file space for a copy. It would be nice if there was something that worked by truncating the file. Clive Dick Davies <[EMAIL PROTECTED]> on 01-02-2005 13:40:13 Please respond to Dick Davies <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org cc: (bcc: clive/Emultek) Subject: Re: [sqlite] Compacting a database * [EMAIL PROTECTED] <[EMAIL PROTECTED]> [0212 11:12]: > > > > Is there a way to compact a database after removing data? vacuum ? -- 'One cannot make an omelette without breaking eggs -- but it is amazing how many eggs one can break without making a decent omelette.' -- Charles P. Issawi Rasputin :: Jack of All Trades - Master of Nuns
[sqlite] Compacting a database
Is there a way to compact a database after removing data? Clive
Re: [sqlite] slow "INSERT"
You should use a transaction otherwise sqlite syncs the file for every INSERT.
Clive
"Alessandro Renzi (RM/TEI)" <[EMAIL PROTECTED]> on 31-01-2005
11:49:24
Please respond to sqlite-users@sqlite.org
To: "'sqlite-users@sqlite.org'" <sqlite-users@sqlite.org>
cc:(bcc: clive/Emultek)
Subject: [sqlite] slow "INSERT"
Hi,
I'm a new sqlite user.
For sure I'm doing something wrong with sqlite because it takes about 1 second
to perform 10 INSERT operation !
If instead I use the sqlite client and read an external file with thousands of
INSERT operation the updating is
almost immediate.
The test program I'm using is the following one:
char * sql = "INSERT INTO PEOPLE (NAME, AGE) VALUES ('John','25')";
for (int i=0; i<100; i++) {
rc = sqlite_exec(db,sql,callback,0,);
if( rc!=SQLITE_OK ){
fprintf(stderr, "SQL error: %s\n", zErrMsg);
return 1;
}
}
Where I'm wrong ?
Thanks, Ale
Re: [sqlite] Memory usage (3.1.0 alpha)
The environment I am using is RapidPlus. It makes calls directly to the DLL. I
changed the sqlite3 functions just to return in order to eliminate the
possibility of it being a problem with the environment, and there was no memory
loss.
Since I am using sqlite3_exec I do not think I need to use sqlite3_finalize.
Is that correct?
Perhaps the normal behaviour of sqlite3 is to use system memory until there is
non left? I cannot find a #define that specifies how many database pages are
cached in memory.
Clive
Ulrik Petersen <[EMAIL PROTECTED]> on 30-01-2005 00:44:01
Please respond to sqlite-users@sqlite.org
To: sqlite-users@sqlite.org
cc:(bcc: clive/Emultek)
Subject: Re: [sqlite] Memory usage (3.1.0 alpha)
Hi Clive,
[EMAIL PROTECTED] wrote:
>I am benchmarking sqlite3 as a potential database for Windows and embedded
>applications.
>I am running the following code in a Rapid development environment that calls
>the equivalent sqlite3 functions
> in a Window's DLL that I built from the release .
>I am seeing that memory usage goes up and up with every loop, until Windows
runs
> out of virtual memory.
>Am I doing something wrong?
>
>while(true)
> SQL exec: 'BEGIN TRANSACTION';
> for from 1 to 1000 step 1
> SQL query: 'INSERT INTO Contacts values(''aaa'',''bbb'',''4'')';
> SQL exec: 'COMMIT TRANSACTION';
>
>
>
>
It looks like you've wrapped it in some sort of Visual Basic. Is that true?
If you are using the sqlite3_prepare/sqlite3_step/sqlite3_finalize API,
yhe behavior you experience may be because you don't call
sqlite3_finalize. Do you use that API?
HTH
Ulrik P.
[sqlite] Memory usage (3.1.0 alpha)
I am benchmarking sqlite3 as a potential database for Windows and embedded
applications.
I am running the following code in a Rapid development environment that calls
the equivalent sqlite3 functions
in a Window's DLL that I built from the release .
I am seeing that memory usage goes up and up with every loop, until Windows runs
out of virtual memory.
Am I doing something wrong?
while(true)
SQL exec: 'BEGIN TRANSACTION';
for from 1 to 1000 step 1
SQL query: 'INSERT INTO Contacts values(''aaa'',''bbb'',''4'')';
SQL exec: 'COMMIT TRANSACTION';
