Re: [sqlite] SQLite performance woe

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Brown, Daniel wrote:
> I am not using the amalgamation version of the source as I have our my
> VFS implementations for two of the platforms I work with based on the
> original win_os.c VFS and the amalgamation does not provide the
> necessary header files (os_common.h and sqliteInt.h) to make VFS
> integration possible.  Other than by inserting the source for my VFS
> implementations directly into the amalgamation source, which I'd rather
> not do as it would make upgrading to new SQLite versions much more
> complex.

You can do what I do which is the other way round.  I #include the
sqlite3.c amalgamation into my one file that does database access,
implements vtables and vfs etc.

You can make all the SQLite symbols private even:

/* See SQLite ticket 2554 */
#define SQLITE_API static
#define SQLITE_EXTERN static

Using the amalgamation vs individual source files gives a reported 5-10%
performance improvement.  I haven't measured what you then get when
#including the amalgamation above your database code, but did notice
when using the debugger that the compiler inlined quite a few methods.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkk05GEACgkQmOOfHg372QSPXgCfcyym8qyxmz452C01OKnUOJaJ
eycAoKyFi6kjHkua96crnZl2NcKZlCHl
=Ox7s
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite performance woe

[Brown, Daniel]

Good evening list,

 

I have been profiling the performance of SQLite version 3.6.1 against my
current custom (hacktastic) runtime database solution (which I am hoping
to replace with SQLite) and I just got a nasty and unexpected result:
SQLite is a lot slower!   I am running SQLite completely in memory
during profiling using an in memory database (:memory:) and I am setting
temp_store=MEMORY and journal_mode=MEMORY.  

 

I have ten tests statements that select data from an database that I
load completely into memory from file before each test begins.   I
iterate over the results summing the values of a column (usually max)
and record the duration over ten iterations to get a rough mean, high
and low time count:

1.   "SELECT * FROM test_item INNER JOIN test_container ON
test_item.container_code = test_container.container_code"

2.   "SELECT * FROM test_item INNER JOIN test_container ON
test_item.container_code = test_container.container_code  WHERE
(test_item.item_code > '100')"

3.   "SELECT * FROM test_item  WHERE (test_item.item_code > '100')"

4.   "SELECT * FROM test_item INNER JOIN test_container ON
test_item.container_code = test_container.container_code  WHERE
(test_item.item_code > '100')  AND (test_item.max > '50')  AND
(test_item.initial > '30')"

5.   "SELECT max  FROM test_item INNER JOIN test_container ON
test_item.container_code = test_container.container_code";

6.   "SELECT * FROM test_item INNER JOIN test_container ON
test_item.container_code = test_container.container_code ORDER BY max"

7.   "SELECT * FROM test_item INNER JOIN test_container ON
test_item.container_code = test_container.container_code WHERE
(test_item.max > '50') ORDER BY max"

8.   "SELECT max FROM test_item"

9.   "SELECT * FROM test_item"

 

SQLite: in memory.

Test 1 Mean 288.470825, Low 286.080383, High 308.041931. (milliseconds)

Test 2 Mean 270.140808, Low 267.758209, High 289.795166. (milliseconds)

Test 3 Mean 68.888512, Low 66.573952, High 88.525116. (milliseconds)

Test 4 Mean 255.051758, Low 251.905319, High 273.752533. (milliseconds)

Test 5 Mean 103.347633, Low 101.023598, High 123.561203. (milliseconds)

Test 6 Mean 2050.301025, Low 2047.153442, High 2069.840088.
(milliseconds)

Test 7 Mean 1927.293213, Low 1923.600952, High 1944.863159.
(milliseconds)

Test 8 Mean 16.426598, Low 13.990897, High 37.434727. (milliseconds)

Test 9 Mean 68.630898, Low 66.280098, High 88.732208. (milliseconds)

 

Custom DB: in memory.

Test 1 (milliseconds) mean,low,high, 10.120694,9.463699,10.540760 

Test 2 (milliseconds) mean,low,high, 10.226122,9.593320,10.600039 

Test 3 (milliseconds) mean,low,high, 9.077908,8.492440,9.602920 

Test 4 (milliseconds) mean,low,high, 10.259830,9.591120,10.668540 

Test 5 (milliseconds) mean,low,high, 3.890896,3.626060,4.679040 

Test 6 (milliseconds) mean,low,high, 10.156658,9.529779,10.696919 

Test 7 (milliseconds) mean,low,high, 10.371894,9.598040,11.083039 

Test 8 (milliseconds) mean,low,high, 1.155094,1.010600,2.416280 

Test 9 (milliseconds) mean,low,high, 1.120254,1.002940,2.032100

 

As you can see the performance difference is significant, profiling
indicates that 75%-80% of the test execution for SQLite is being spent
inside sqlite3VdbeExec.  What I'd like to know is if that is normal and
if there is anything we can do with our queries, SQLite set-up or
library configuration to improve the speed?  So far I have found force
inlining sqlite3VdbeSerialTypeLen helped the performance a bit.  

 

I am not using the amalgamation version of the source as I have our my
VFS implementations for two of the platforms I work with based on the
original win_os.c VFS and the amalgamation does not provide the
necessary header files (os_common.h and sqliteInt.h) to make VFS
integration possible.  Other than by inserting the source for my VFS
implementations directly into the amalgamation source, which I'd rather
not do as it would make upgrading to new SQLite versions much more
complex.

 

I love SQLite's feature set, flexibility, tools and syntax compared to
my current solution but I need SQLite to be at least as fast as my
current solution to make it worth the switch.

 

Cheers,

 

Daniel Brown | Software Engineer

"The best laid schemes o' mice an' men, gang aft agley"

 

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] please help with NULL and NOTHING?

[aivars]

Thnaks, Mohd and Dan,

Dan, Your suggestion worked OK! Both on sqlite and MS SQL SERVER 2005.
The whole select statement as an argument to coalesce function.

Thanks

Aivars


2008/12/1 Dan <[EMAIL PROTECTED]>:
> If you are sure there is at most one entry in bilance1 where the account
> and year match then you could do this:
>
>   SELECT coalesce(
> (SELECT dbs from bilance1 where account='13100' and pYear=?),
> 0
>   ) AS summadeb;
>
>
>
>
>
> On Dec 1, 2008, at 3:26 PM, aivars wrote:
>
>> Hello,
>>
>> The simple query is like this:
>> SELECT dbs as summadeb from bilance1 where account='13100' and
>> pYear=?;
>>
>> Account number 13100 is not present in the table bilance1 when
>> pYear=2005 and it should be like this and therefore dbs is also not
>> present. Other years account number 13100 is present and query works
>> OK.
>>
>> When the query is run with a parameter pYear='2005' it returns nothing
>> - the resultset is empty or nothing? (I am doing it from python25)
>>
>> Even if I change the query to :
>> SELECT coalesce(dbs,0) as summadeb from bilance1 where account='13100'
>> and pYear='2005'; it still returns NOTHING, not 0.
>>
>> The same happens also on MS SQLServer 2005 so I think it should be
>> like that according to sql standards. If there is no account number
>> 13100 in the table then the result is NOTHING, not NULL or 0.
>> Strictly speaking I think also the value of 0 is not correct in this
>> case but I would like to have it.
>> It seams that coalesce can handle NULL not NOTHING.
>>
>> My question is:
>> Is there an SQL way to handle above query to return 0 or should I
>> handle this in client program (python)? (presently I get TypeError:
>> 'NoneType' object is unsubscriptable)
>>
>> Using sqlite 3.6.2, python2.5 and Windows XP
>>
>> Thanks in advance
>>
>> Aivars
>> ___
>> 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] query optimization for inner table join

[Igor Tandetnik]

"Jos van den Oever" <[EMAIL PROTECTED]>
wrote in message
news:[EMAIL PROTECTED]
> 2008/12/1 Igor Tandetnik <[EMAIL PROTECTED]>:
>> Try this:
>>
>> select n from map
>> group by n
>> having
>>count(case when m=3 then 1 else null end) != 0 and
>>count(case when m=5 then 1 else null end) != 0 and
>>count(case when m=7 then 1 else null end) = 0;
>>
>> Having an index on map(n) should speed it up.
>
> Thank you very much, Igor. I would have not thought of that.
>
> This is a nicely predictable single linear scan. Still not awfully
> fast, but it will have to do.

You could also try something more straightforward:

select distinct n from map m1 where
exists (select 1 from map m2 where m1.n=m2.n and m2.m=3) and
exists (select 1 from map m2 where m1.n=m2.n and m2.m=5) and
not exists (select 1 from map m2 where m1.n=m2.n and m2.m=7);

-- or

select distinct n from map where
n in (select n from map where m=3) and
n in (select n from map where m=5) and
n not in (select n from map where m=7);


If you need to run this kind of query often, and values of m are small 
(preferably less than 64), you might want to store a map from n to a 
bitmask where each bit corresponds to one value of m. Then the query 
becomes simply

select n from map
where (n & 168) = 40;

This is going to be linear, but in the number of distinct values of n, 
not in the number of all pairs.

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] multiple tables within a database

[Darren Duncan]

Loosely speaking, I put things together in the same file if some of those 
things 
are necessary to understand or interpret the other things, or if there is a 
logical dependency between things (say, a foreign key), they go in the same 
file.  Being in one file ensures that all the interdependent parts are kept 
together.  If A and B can be fully understood in complete isolation from each 
other and have no mutual constraints, it is safe to have them in separate 
files. 
  If SQLite supported user-defined data types, the type definitions and tables 
etc based on them would need to go in the same file.  Triggers and views and 
the 
tables they work on need to be in the same file. -- Darren Duncan

Eric S. Johansson wrote:
> what's the general rule for deciding when to put multiple tables within a 
> single
> sqlite db file?  I think the answer is something like you put tables together 
> in
> one database file if they refer to different aspects of the same data element
> and you put them in separate database files if there's no connection except 
> the
> databases are used in the same application.  For example, in my case, I have 
> one
> table which contains the raw original data, a  thoroughly cooked form of the
> original data, and a series of data elements which are used for searching and
> display.  The related table contains information derived from postprocessing 
> and
> will also be used for searching and graphing.  The second table's information
> could be regenerated anytime at a cost of running through every record in the
> database and recalculating it.  As a result of this association, I figure it's
> appropriate to place both records in the same database file.
> 
> The third table tracks data from another part of the process and has no
> connection to the first two tables except that it is used as part of the
> postprocessing calculations that feed the second table described above.  I
> figure the third table should go in its own database.
> 
> For what it's worth, record counts could easily cross 100,000 for each one of
> the tables.  Hope it's not time for mysql :-)
> 
> Thanks for a feedback.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] multiple tables within a database

[Eric S. Johansson]

what's the general rule for deciding when to put multiple tables within a single
sqlite db file?  I think the answer is something like you put tables together in
one database file if they refer to different aspects of the same data element
and you put them in separate database files if there's no connection except the
databases are used in the same application.  For example, in my case, I have one
table which contains the raw original data, a  thoroughly cooked form of the
original data, and a series of data elements which are used for searching and
display.  The related table contains information derived from postprocessing and
will also be used for searching and graphing.  The second table's information
could be regenerated anytime at a cost of running through every record in the
database and recalculating it.  As a result of this association, I figure it's
appropriate to place both records in the same database file.

The third table tracks data from another part of the process and has no
connection to the first two tables except that it is used as part of the
postprocessing calculations that feed the second table described above.  I
figure the third table should go in its own database.

For what it's worth, record counts could easily cross 100,000 for each one of
the tables.  Hope it's not time for mysql :-)

Thanks for a feedback.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Journal files

[Brown, Daniel]

Cheers adding the extra pragma has stopped the temporary file activity
:)

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp
Sent: Monday, December 01, 2008 6:14 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Journal files


On Dec 1, 2008, at 9:11 PM, Brown, Daniel wrote:

> I'm using "PRAGMA journal_mode = MEMORY" combined with an in memory
> database ":memory:" on version 3.6.1. And I too am seeing lots of
> temporary file activity, which is really killing our performance as  
> our
> storage medium is so slow.

Try using

 PRAGMA temp_store=MEMORY;

In addition to

 PRAGMA journal_mode=MEMORY;

>
>
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Stephen  
> Abbamonte
> Sent: Monday, December 01, 2008 3:50 PM
> To: 'General Discussion of SQLite Database'
> Subject: Re: [sqlite] Journal files
>
> I just tried this line also and the journal files are still being
> created
> here is the code I am running:
>
>
>int32_t ret = sqlite3_open(filename, m_DatabaseRef);
> if( ret == SQLITE_OK )
>{
>   sqlite3_stmt*  sqlStmt = NULL;  
>   ret = sqlite3_prepare_v2(m_DatabaseRef, s_ "PRAGMA
> main.journal_mode = OFF;", -1, , NULL);
>   if( ret == SQLITE_OK )
>   {
>   sqlite3_step(sqlStmt);
>   const unsigned char* colValue =
> sqlite3_column_text(sqlStmt, 0);  <- returns "off"
>   sqlite3_finalize(sqlStmt);
>   }
> }
>
> Anything wrong with the way I am doing this?
> Thanks for the help.
>
>
>> Did you set
>>
>>  PRAGMA journal_mode = OFF;
>>
>> ? The way I read the documentation (on a second or third close
>> reading, I think), this only sets the default value for new databases
>> to be attached, but doesn't affect your main connection and any
>> databases that have already been attached.  So what I do is
>>
>>  PRAGMA main.journal_mode = OFF;
>>
>> IIRC, I needed this line to actually turn off the journals (tested
>> with 3.4.x and 3.5.x versions of SQLite only, though).
>>
>> Cheers,
>> Stefan
>
>
>
> ___
> 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

D. Richard Hipp
[EMAIL PROTECTED]



___
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] Journal files

[D. Richard Hipp]

On Dec 1, 2008, at 6:49 PM, Stephen Abbamonte wrote:

> I just tried this line also and the journal files are still being  
> created
> here is the code I am running:
>
>
>int32_t ret = sqlite3_open(filename, m_DatabaseRef);
> if( ret == SQLITE_OK )
>{
>   sqlite3_stmt*  sqlStmt = NULL;  
>   ret = sqlite3_prepare_v2(m_DatabaseRef, s_ "PRAGMA
> main.journal_mode = OFF;", -1, , NULL);
>   if( ret == SQLITE_OK )
>   {
>   sqlite3_step(sqlStmt);
>   const unsigned char* colValue =
> sqlite3_column_text(sqlStmt, 0);  <- returns "off"
>   sqlite3_finalize(sqlStmt);
>   }
> }
>
> Anything wrong with the way I am doing this?
> Thanks for the help.
>


I originally thought "maybe we introduced a bug and broke  
journal_mode".  But I tried it myself and everything works correctly.   
And the regression tests for journal_mode=OFF are running.  So I do  
not have any idea what you are doing wrong.  Everything is working  
great here.


D. Richard Hipp
[EMAIL PROTECTED]



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Journal files

[D. Richard Hipp]

On Dec 1, 2008, at 9:11 PM, Brown, Daniel wrote:

> I'm using "PRAGMA journal_mode = MEMORY" combined with an in memory
> database ":memory:" on version 3.6.1. And I too am seeing lots of
> temporary file activity, which is really killing our performance as  
> our
> storage medium is so slow.

Try using

 PRAGMA temp_store=MEMORY;

In addition to

 PRAGMA journal_mode=MEMORY;

>
>
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Stephen  
> Abbamonte
> Sent: Monday, December 01, 2008 3:50 PM
> To: 'General Discussion of SQLite Database'
> Subject: Re: [sqlite] Journal files
>
> I just tried this line also and the journal files are still being
> created
> here is the code I am running:
>
>
>int32_t ret = sqlite3_open(filename, m_DatabaseRef);
> if( ret == SQLITE_OK )
>{
>   sqlite3_stmt*  sqlStmt = NULL;  
>   ret = sqlite3_prepare_v2(m_DatabaseRef, s_ "PRAGMA
> main.journal_mode = OFF;", -1, , NULL);
>   if( ret == SQLITE_OK )
>   {
>   sqlite3_step(sqlStmt);
>   const unsigned char* colValue =
> sqlite3_column_text(sqlStmt, 0);  <- returns "off"
>   sqlite3_finalize(sqlStmt);
>   }
> }
>
> Anything wrong with the way I am doing this?
> Thanks for the help.
>
>
>> Did you set
>>
>>  PRAGMA journal_mode = OFF;
>>
>> ? The way I read the documentation (on a second or third close
>> reading, I think), this only sets the default value for new databases
>> to be attached, but doesn't affect your main connection and any
>> databases that have already been attached.  So what I do is
>>
>>  PRAGMA main.journal_mode = OFF;
>>
>> IIRC, I needed this line to actually turn off the journals (tested
>> with 3.4.x and 3.5.x versions of SQLite only, though).
>>
>> Cheers,
>> Stefan
>
>
>
> ___
> 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

D. Richard Hipp
[EMAIL PROTECTED]



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Journal files

[Brown, Daniel]

I'm using "PRAGMA journal_mode = MEMORY" combined with an in memory
database ":memory:" on version 3.6.1. And I too am seeing lots of
temporary file activity, which is really killing our performance as our
storage medium is so slow.  

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Stephen Abbamonte
Sent: Monday, December 01, 2008 3:50 PM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Journal files

I just tried this line also and the journal files are still being
created
here is the code I am running:


int32_t ret = sqlite3_open(filename, m_DatabaseRef);
  if( ret == SQLITE_OK )
{
sqlite3_stmt*  sqlStmt = NULL;  
ret = sqlite3_prepare_v2(m_DatabaseRef, s_ "PRAGMA
main.journal_mode = OFF;", -1, , NULL);
if( ret == SQLITE_OK )
{
sqlite3_step(sqlStmt);
const unsigned char* colValue =
sqlite3_column_text(sqlStmt, 0);  <- returns "off"
sqlite3_finalize(sqlStmt);
}
  }

Anything wrong with the way I am doing this?
Thanks for the help.


>Did you set
>
>   PRAGMA journal_mode = OFF;
>
>? The way I read the documentation (on a second or third close  
>reading, I think), this only sets the default value for new databases  
>to be attached, but doesn't affect your main connection and any  
>databases that have already been attached.  So what I do is
>
>   PRAGMA main.journal_mode = OFF;
>
>IIRC, I needed this line to actually turn off the journals (tested  
>with 3.4.x and 3.5.x versions of SQLite only, though).
>
>Cheers,
>Stefan



___
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] Journal files

[Stephen Abbamonte]

I just tried this line also and the journal files are still being created
here is the code I am running:


int32_t ret = sqlite3_open(filename, m_DatabaseRef);
  if( ret == SQLITE_OK )
{
sqlite3_stmt*  sqlStmt = NULL;  
ret = sqlite3_prepare_v2(m_DatabaseRef, s_ "PRAGMA
main.journal_mode = OFF;", -1, , NULL);
if( ret == SQLITE_OK )
{
sqlite3_step(sqlStmt);
const unsigned char* colValue =
sqlite3_column_text(sqlStmt, 0);  <- returns "off"
sqlite3_finalize(sqlStmt);
}
  }

Anything wrong with the way I am doing this?
Thanks for the help.


>Did you set
>
>   PRAGMA journal_mode = OFF;
>
>? The way I read the documentation (on a second or third close  
>reading, I think), this only sets the default value for new databases  
>to be attached, but doesn't affect your main connection and any  
>databases that have already been attached.  So what I do is
>
>   PRAGMA main.journal_mode = OFF;
>
>IIRC, I needed this line to actually turn off the journals (tested  
>with 3.4.x and 3.5.x versions of SQLite only, though).
>
>Cheers,
>Stefan



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Journal files

[Stefan Evert]

> I tried that on sqlite3 version 3.6.6 and the return value was "OFF"  
> but the
> journals are still being created. Any reason why this wouldn't work?

Did you set

PRAGMA journal_mode = OFF;

? The way I read the documentation (on a second or third close  
reading, I think), this only sets the default value for new databases  
to be attached, but doesn't affect your main connection and any  
databases that have already been attached.  So what I do is

PRAGMA main.journal_mode = OFF;

IIRC, I needed this line to actually turn off the journals (tested  
with 3.4.x and 3.5.x versions of SQLite only, though).

Cheers,
Stefan

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Journal files

[Stephen Abbamonte]

I tried that on sqlite3 version 3.6.6 and the return value was "OFF" but the
journals are still being created. Any reason why this wouldn't work?

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp
Sent: Monday, December 01, 2008 9:56 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Journal files


On Dec 1, 2008, at 10:50 AM, Eric Minbiole wrote:

>>I am looking for a way to completely turn off the  
>> creation
>> of journal files. Any help is much appreciated.
>
> http://www.sqlite.org/pragma.html

And in particular http://www.sqlite.org/pragma.html#pragma_journal_mode

>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

D. Richard Hipp
[EMAIL PROTECTED]



___
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] Journal files

[Brown, Daniel]

Does that control the creation of all temporary files created at
runtime?  

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp
Sent: Monday, December 01, 2008 7:56 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Journal files


On Dec 1, 2008, at 10:50 AM, Eric Minbiole wrote:

>>I am looking for a way to completely turn off the  
>> creation
>> of journal files. Any help is much appreciated.
>
> http://www.sqlite.org/pragma.html

And in particular http://www.sqlite.org/pragma.html#pragma_journal_mode

>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

D. Richard Hipp
[EMAIL PROTECTED]



___
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] Generating CRC values for tables

[Alexey Pechnikov]

Hello!

В сообщении от Wednesday 26 November 2008 22:37:19 Brown, Daniel написал(а):
> Is there any functionality built into SQLite to generate CRC values for
> tables?  We would like to be able to verify that the contents of the
> table we just updated matches the intended contents.  Currently using
> our old database solution we generate a CRC value for each table and
> compare them.  Is there similar functionality in SQLite?

For table rows:
select md5sum(*) from mytable;
(md5 hash)

For table values:
select md5sum(value) from mytable;
(md5 hash)

For single value:
select md5(value) from mytable;
(md5 hash 1)
(md5 hash 2)
(md5 hash ...)

Functions md5sum and md5 is realised in my extensions and you can find this in 
archive of the 
maillist.

Best regards, Alexey.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] query optimization for inner table join

[Jos van den Oever]

2008/12/1 Igor Tandetnik <[EMAIL PROTECTED]>:
> Try this:
>
> select n from map
> group by n
> having
>count(case when m=3 then 1 else null end) != 0 and
>count(case when m=5 then 1 else null end) != 0 and
>count(case when m=7 then 1 else null end) = 0;
>
> Having an index on map(n) should speed it up.

Thank you very much, Igor. I would have not thought of that.

This is a nicely predictable single linear scan. Still not awfully
fast, but it will have to do.
Using an index on map(n,m) seems faster. This may be because the m
values are in the index and there is no need to access the table.

Cheers,
Jos
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Missing TCL/TEA source code

[Ribeiro, Glauber]

I think the source code for SQLite with TCL for version 3.6.6.2 is
missing from sqlite.org.

Thanks,

glauber
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite aggregate functions by Tcl

[Alexey Pechnikov]

Hello!

В сообщении от Monday 01 December 2008 18:16:04 D. Richard Hipp написал(а):
> The current TCL interface for SQLite does not provide the ability to  
> add aggregate functions written in TCL.  So in that sense, it is not  
> possible.  However, the TCL interface could be extended to add this  
> capability.

Do you think that this feature is not better way? May be aggregate operations 
is more preferable 
doing in application layer?

Best regards, Alexey.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Journal files

[D. Richard Hipp]

On Dec 1, 2008, at 10:50 AM, Eric Minbiole wrote:

>>I am looking for a way to completely turn off the  
>> creation
>> of journal files. Any help is much appreciated.
>
> http://www.sqlite.org/pragma.html

And in particular http://www.sqlite.org/pragma.html#pragma_journal_mode

>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

D. Richard Hipp
[EMAIL PROTECTED]



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Journal files

[Eric Minbiole]

> I am looking for a way to completely turn off the creation
> of journal files. Any help is much appreciated.

http://www.sqlite.org/pragma.html
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Journal files

[Stephen Abbamonte]

Hello all,

I am looking for a way to completely turn off the creation
of journal files. Any help is much appreciated.

 

Thanks.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite aggregate functions by Tcl

[D. Richard Hipp]

On Dec 1, 2008, at 9:56 AM, Alexey Pechnikov wrote:

> Hello!
>
> Is it possible?
>

The current TCL interface for SQLite does not provide the ability to  
add aggregate functions written in TCL.  So in that sense, it is not  
possible.  However, the TCL interface could be extended to add this  
capability.


D. Richard Hipp
[EMAIL PROTECTED]



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite aggregate functions by Tcl

[Alexey Pechnikov]

Hello!

Is it possible?

Best regards, Alexey.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Arranging of ids in Sqlite3

[Alexey Pechnikov]

Hello!

В сообщении от Wednesday 26 November 2008 13:57:02 Nikhil Kansal написал(а):
> But if I delete a chunk of data then how can I know the id number.

You can get rowid for last inserted row as

select last_insert_rowid();

Best regards, Alexey.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] query optimization for inner table join

[Igor Tandetnik]

"Jos van den Oever" <[EMAIL PROTECTED]>
wrote in message
news:[EMAIL PROTECTED]
> I've trouble optimizing for an N:M mapping table. The schema of the
> table is this:
>
> CREATE TABLE map (n INTEGER NOT NULL, m INTEGER NOT NULL);
>
> I want to retrieve a list of n filtered on the presence of certain
> values of m, e.g. give me all n for which there is an m = 3 and m = 5,
> but no m = 7.

Try this:

select n from map
group by n
having
count(case when m=3 then 1 else null end) != 0 and
count(case when m=5 then 1 else null end) != 0 and
count(case when m=7 then 1 else null end) = 0;

Having an index on map(n) should speed it up.

Igor Tandetnik



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why must one write a mini SQL parser to read the columnnames?

[Ben Harper]

Thanks, that's perfect!

On Mon, Dec 1, 2008 at 3:06 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote:
> "Ben Harper" <[EMAIL PROTECTED]> wrote in
> message
> news:[EMAIL PROTECTED]
>> To the best of my findings, it seems to me that one needs to write a
>> mini SQL parser in order to read the 'sql' field from sqlite_master,
>> in order to discover the fields in an Sqlite table.
>
> Have you looked at PRAGMA table_info?
>
> Igor Tandetnik
>
>
>
> ___
> 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] Why must one write a mini SQL parser to read the columnnames?

[Igor Tandetnik]

"Ben Harper" <[EMAIL PROTECTED]> wrote in
message
news:[EMAIL PROTECTED]
> To the best of my findings, it seems to me that one needs to write a
> mini SQL parser in order to read the 'sql' field from sqlite_master,
> in order to discover the fields in an Sqlite table.

Have you looked at PRAGMA table_info?

Igor Tandetnik



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Why must one write a mini SQL parser to read the column names?

[Ben Harper]

To the best of my findings, it seems to me that one needs to write a
mini SQL parser in order to read the 'sql' field from sqlite_master,
in order to discover the fields in an Sqlite table. Is this really a
necessary design? Would it not be better if
sqlite3_table_column_metadata had a mode that could enumerate the
columns in a table? Or am I missing something obvious?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Partial search with fts

[Rael Bauer]

Previously someone advised that I use the "*" char to achieve partial search 
results with fts. eg ver* will match version. This works ok, but only for end 
parts of a word. 
 
Is there anyway to get partial matches for beginning or middle parts of a word?
 
e.g. *sion - to match version or
*si* to match version
 
Thanks
Rael


  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] please help with NULL and NOTHING?

[Dan]

If you are sure there is at most one entry in bilance1 where the account
and year match then you could do this:

   SELECT coalesce(
 (SELECT dbs from bilance1 where account='13100' and pYear=?),
 0
   ) AS summadeb;





On Dec 1, 2008, at 3:26 PM, aivars wrote:

> Hello,
>
> The simple query is like this:
> SELECT dbs as summadeb from bilance1 where account='13100' and  
> pYear=?;
>
> Account number 13100 is not present in the table bilance1 when
> pYear=2005 and it should be like this and therefore dbs is also not
> present. Other years account number 13100 is present and query works
> OK.
>
> When the query is run with a parameter pYear='2005' it returns nothing
> - the resultset is empty or nothing? (I am doing it from python25)
>
> Even if I change the query to :
> SELECT coalesce(dbs,0) as summadeb from bilance1 where account='13100'
> and pYear='2005'; it still returns NOTHING, not 0.
>
> The same happens also on MS SQLServer 2005 so I think it should be
> like that according to sql standards. If there is no account number
> 13100 in the table then the result is NOTHING, not NULL or 0.
> Strictly speaking I think also the value of 0 is not correct in this
> case but I would like to have it.
> It seams that coalesce can handle NULL not NOTHING.
>
> My question is:
> Is there an SQL way to handle above query to return 0 or should I
> handle this in client program (python)? (presently I get TypeError:
> 'NoneType' object is unsubscriptable)
>
> Using sqlite 3.6.2, python2.5 and Windows XP
>
> Thanks in advance
>
> Aivars
> ___
> 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

[sqlite] query optimization for inner table join

[Jos van den Oever]

Hi all,

I've trouble optimizing for an N:M mapping table. The schema of the
table is this:

CREATE TABLE map (n INTEGER NOT NULL, m INTEGER NOT NULL);

I want to retrieve a list of n filtered on the presence of certain
values of m, e.g. give me all n for which there is an m = 3 and m = 5,
but no m = 7.
A naive query would look like this:

SELECT a.n FROM map a, map b, map c
  WHERE a.n = b.n AND a.n = c.n AND a.m = 3 AND b.m = 5
 AND c.id not in (select id from map where c.m = 7);

This can be slow, even for the more simple case with only positive selection:

SELECT a.n FROM map a, map b
  WHERE a.n = b.n AND a.m = 3 AND b.m = 5;

And this variation does not make it a lot faster:

SELECT n FROM map WHERE m = 3 INTERSECT SELECT n FROM map where m = 5;

There are about a million entries in the table map and want to
increase to about 10 million.

The current indexes are

CREATE INDEX map_n ON map(n);
CREATE INDEX map_m ON map(n,m);

Is there a cleverer way of doing these queries?

The fraction of n's that has a particular m can be anywhere between 0 and 1.

Cheers,
Jos
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] please help with NULL and NOTHING?

[Mohd Radzi Ibrahim]

You should handle this in your client program. Even in MS SQL or Oracle, it 
will not return any resultset.
If you were to use left join, you may get it as NULL for any missing links.

rgd,
Radzi.

- Original Message - 
From: "aivars" <[EMAIL PROTECTED]>
To: 
Sent: Monday, December 01, 2008 4:26 PM
Subject: [sqlite] please help with NULL and NOTHING?


> Hello,
>
> The simple query is like this:
> SELECT dbs as summadeb from bilance1 where account='13100' and pYear=?;
>
> Account number 13100 is not present in the table bilance1 when
> pYear=2005 and it should be like this and therefore dbs is also not
> present. Other years account number 13100 is present and query works
> OK.
>
> When the query is run with a parameter pYear='2005' it returns nothing
> - the resultset is empty or nothing? (I am doing it from python25)
>
> Even if I change the query to :
> SELECT coalesce(dbs,0) as summadeb from bilance1 where account='13100'
> and pYear='2005'; it still returns NOTHING, not 0.
>
> The same happens also on MS SQLServer 2005 so I think it should be
> like that according to sql standards. If there is no account number
> 13100 in the table then the result is NOTHING, not NULL or 0.
> Strictly speaking I think also the value of 0 is not correct in this
> case but I would like to have it.
> It seams that coalesce can handle NULL not NOTHING.
>
> My question is:
> Is there an SQL way to handle above query to return 0 or should I
> handle this in client program (python)? (presently I get TypeError:
> 'NoneType' object is unsubscriptable)
>
> Using sqlite 3.6.2, python2.5 and Windows XP
>
> Thanks in advance
>
> Aivars
> ___
> 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

[sqlite] please help with NULL and NOTHING?

[aivars]

Hello,

The simple query is like this:
SELECT dbs as summadeb from bilance1 where account='13100' and pYear=?;

Account number 13100 is not present in the table bilance1 when
pYear=2005 and it should be like this and therefore dbs is also not
present. Other years account number 13100 is present and query works
OK.

When the query is run with a parameter pYear='2005' it returns nothing
- the resultset is empty or nothing? (I am doing it from python25)

Even if I change the query to :
SELECT coalesce(dbs,0) as summadeb from bilance1 where account='13100'
and pYear='2005'; it still returns NOTHING, not 0.

The same happens also on MS SQLServer 2005 so I think it should be
like that according to sql standards. If there is no account number
13100 in the table then the result is NOTHING, not NULL or 0.
Strictly speaking I think also the value of 0 is not correct in this
case but I would like to have it.
It seams that coalesce can handle NULL not NOTHING.

My question is:
Is there an SQL way to handle above query to return 0 or should I
handle this in client program (python)? (presently I get TypeError:
'NoneType' object is unsubscriptable)

Using sqlite 3.6.2, python2.5 and Windows XP

Thanks in advance

Aivars
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users