[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] -
