Re: [sqlite] Considerations with in-memory SQLite3

Ken wrote: > I'll take a stab at this. > > Each connection would have its own private memory database. > > Create an additional temp table id_list , Insert the user id set values into > this table. > > Then > INSERT INTO temptab tt SELECT mt.* FROM maintab mt, id_list >

Re: [sqlite] Query Optimization

Mitchell Vincent wrote: > SELECT customer_id FROM customers WHERE cust_balance != (select > coalesce(sum(balance_due), 0) FROM invoice WHERE status='Active' AND > invoice.customer_id = customers.customer_id) > > The above query is used to determine if any stored balances are out of > date. It

Re: [sqlite] rtree performance problems?

I'll have to look again. I was sure I had mucked with estimatedCost and found that it wasn't doing the trick for what I meant to do - but somehow the context has swapped out of my short-term memory, so I'll have to recreate it before I can say why this was the case. -scott On Wed, Sep 10, 2008

[sqlite] Query Optimization

SELECT customer_id FROM customers WHERE cust_balance != (select coalesce(sum(balance_due), 0) FROM invoice WHERE status='Active' AND invoice.customer_id = customers.customer_id) The above query is used to determine if any stored balances are out of date. It works very well but is *really* slow

Re: [sqlite] rtree performance problems?

see sqlite3_index_info.estimatedCost http://www.sqlite.org/cvstrac/chngview?cn=5649 > AFAICT, when you have a join where one table has a good index, the > virtual table cannot signal that it has an even better index. I could > not follow the index-selection logic well enough to have any >

Re: [sqlite] Considerations with in-memory SQLite3

Ken wrote: > I'll take a stab at this. > > Each connection would have its own private memory database. > > Create an additional temp table id_list , Insert the user id set values into > this table. > > Then > INSERT INTO temptab tt SELECT mt.* FROM maintab mt, id_list >

Re: [sqlite] Considerations with in-memory SQLite3

I'll take a stab at this. Each connection would have its own private memory database. Create an additional temp table id_list , Insert the user id set values into this table. Then INSERT INTO temptab tt SELECT mt.* FROM maintab mt, id_list WHERE mt.id =

Re: [sqlite] Considerations with in-memory SQLite3

Hi Richard Thanks for the quick response! D. Richard Hipp wrote: > On Sep 10, 2008, at 2:09 PM, Mohit Sindhwani wrote: > >> >> I've been using SQLite3 for a while though to be honest, it's been >> more >> of a data store rather than a dynamic database in my applications this >> far. I'm

Re: [sqlite] Considerations with in-memory SQLite3

On Sep 10, 2008, at 2:09 PM, Mohit Sindhwani wrote: > Hi Everyone, > > I'm new to the list and would like to start by saying hello! So, > hello! > > I've been using SQLite3 for a while though to be honest, it's been > more > of a data store rather than a dynamic database in my applications

Re: [sqlite] Memory suggestion for DRH

DRH, Many thanks. I learned something new everyday thanks to Sqlite !!! Regards, Ken --- On Wed, 9/10/08, D. Richard Hipp <[EMAIL PROTECTED]> wrote: From: D. Richard Hipp <[EMAIL PROTECTED]> Subject: Re: [sqlite] Memory suggestion for DRH To: "General Discussion of SQLite Database"

Re: [sqlite] Memory suggestion for DRH

On Sep 10, 2008, at 2:02 PM, Ken wrote: > > My suggestion for a future enhancement: > Provide a temporary storage pool of memory. > If the temporary pool overflows then go to disk based temp store. > > That way order by query results can generally be quickly satisfied > by the average case

[sqlite] Considerations with in-memory SQLite3

Hi Everyone, I'm new to the list and would like to start by saying hello! So, hello! I've been using SQLite3 for a while though to be honest, it's been more of a data store rather than a dynamic database in my applications this far. I'm now starting on something where my needs are as such: *

[sqlite] Memory suggestion for DRH

My suggestion for a future enhancement: Provide a temporary storage pool of memory. If the temporary pool overflows then go to disk based temp store. That way order by query results can generally be quickly satisfied by the average case memory consumption and Large order by queries will

Re: [sqlite] Crashed on sqlite3_exec(pDb, "PRAGMA synchronous=OFF ", NULL, 0, );

Hi, I am currently using 3.5.9. Thanks for the respond JP - Original Message From: Dennis Cote <[EMAIL PROTECTED]> To: General Discussion of SQLite Database Sent: Wednesday, September 10, 2008 10:46:17 AM Subject: Re: [sqlite] Crashed on sqlite3_exec(pDb,

Re: [sqlite] Crashed on sqlite3_exec(pDb, "PRAGMA synchronous=OFF ", NULL, 0, );

Joanne Pham wrote: > Any idea about these error messages: > 0xb6f67ca5 in enterMem () at ../src/mem1.c:66 > 66sqlite3_mutex_enter(mem.mutex); > Current language: auto; currently c What version of sqlite are you using? Line 66 in mem.c is a comment in the current version.

Re: [sqlite] rtree performance problems?

[Sorry for the blast from the past.] I think this class of problem does also happen on fts, there was a thread on August 7 on sqlite-dev about it. Unfortunately, I don't see any open-access web-mirrors of that list to refer to, but here's a members-only ref:

Re: [sqlite] Crashed on sqlite3_exec(pDb, "PRAGMA synchronous=OFF ", NULL, 0, );

Hi All, Any idea about these error messages:   0xb6f67ca5 in enterMem () at ../src/mem1.c:66     66    sqlite3_mutex_enter(mem.mutex);     Current language:  auto; currently c Your help is greatly appreciated. Thanks, JP - Original Message From: Joanne Pham <[EMAIL PROTECTED]> To:

Re: [sqlite] Trim everything that is entered into database

On 9/10/08 11:11 AM, "Dennis Cote" <[EMAIL PROTECTED]> wrote: > Josh Millstein wrote: >> On 9/9/08 11:46 AM, "Igor Tandetnik" <[EMAIL PROTECTED]> wrote: >>> Josh Millstein <[EMAIL PROTECTED]> >>> wrote: Is there anyway to perform a trim to everything that is entered into a table

Re: [sqlite] Trim everything that is entered into database

On 9/10/08, Josh Millstein <[EMAIL PROTECTED]> wrote: > The trim before I put data in was based on using a programming language and > not the db language. I want to do it all in sql syntax > What Igor suggested *is* SQL syntax INSERT INTO table (col) VALUES (trim()) > > > On 9/9/08 12:52

Re: [sqlite] Trim everything that is entered into database

Josh Millstein wrote: > On 9/9/08 11:46 AM, "Igor Tandetnik" <[EMAIL PROTECTED]> wrote: >> Josh Millstein <[EMAIL PROTECTED]> >> wrote: >>> Is there anyway to perform a trim to everything that is entered into >>> a table instead of trimming before I put data in? >> >> update mytable set

Re: [sqlite] Trim everything that is entered into database

Yes, automatically like using triggers. That is exactly what I'm talking about On 9/9/08 2:03 PM, "Enrique Ramirez" <[EMAIL PROTECTED]> wrote: > I'm guessing he means like automatically (IE using triggers). > > Which also would be my answer (use triggers). > > On Tue, Sep 9, 2008 at 1:52 PM,

Re: [sqlite] Trim everything that is entered into database

The trim before I put data in was based on using a programming language and not the db language. I want to do it all in sql syntax On 9/9/08 12:52 PM, "P Kishor" <[EMAIL PROTECTED]> wrote: > On 9/9/08, Josh Millstein <[EMAIL PROTECTED]> wrote: >> Hello, >> >> Is there anyway to perform a

Re: [sqlite] Trim everything that is entered into database

On 9/9/08 11:46 AM, "Igor Tandetnik" <[EMAIL PROTECTED]> wrote: > Josh Millstein <[EMAIL PROTECTED]> > wrote: >> Is there anyway to perform a trim to everything that is entered into >> a table instead of trimming before I put data in? > > I'm not sure I understand the question. Are you perhaps

Re: [sqlite] valgrind leak summary shows possible memory loss

On Sep 10, 2008, at 10:09 AM, jerry wrote: > I recently upgraded to latest sqlite 3.6.2 and valgrind shows possible > memory loss of 272 bytes. I narrowed it down to a 2 line program of > just sqlite3_open and sqlite_close. Valgrind doc says that this is > probably a memory leak unless you are

Re: [sqlite] Using Sqlite in place of BDB

BDB 4.3.28 has other facet like a) crash when zero diskspace. b) if there is abrupt crash then recovery can fail. also the time taken during recover is very long. c) foot print is too big. d) Performance is very good on most platforms. e) Support is not very responsive. f) Cannot copy the

Re: [sqlite] Error A0A

[EMAIL PROTECTED] wrote: > Do you think this is causing my problem? No, not your immediate problem. > I've added reset call there because without that this error was more frequent. > With the reset call in place the sqlite_step will re-execute the entire statement from the beginning after a

Re: [sqlite] bug in the RTree module

>> I think I have found a bug in the RTree extension (v3.6.2) It got fixed a day or two ago here: http://www.sqlite.org/cvstrac/chngview?cn=5682 Grab the new rtree.c file from here if you want the fix before 3.6.3 comes out: http://www.sqlite.org/cvstrac/getfile?f=sqlite/ext/rtree/

Re: [sqlite] Error A0A

Do you think this is causing my problem? I've added reset call there because without that this error was more frequent. [EMAIL PROTECTED] wrote: > I'm using some wrapper but modified. > Here is my Exec method: > > > if ( rc == SQLITE_BUSY) > { > Sleep(0); > rc = _sqlite3_reset(m_stmt); >

[sqlite] Problems with FTS NEAR operator

Hi all I'm having problems with using the FTS NEAR/n operator and I'm really not sure why. Code below, any help gratefully received, the 'product NEAR announcement' produces a hit but 'product NEAR/20 announcement ' doesn't. Using 3.6.2 Thanks in advance Mike sqlite3*

Re: [sqlite] Insert statement is ok but after reopening the db data is missing ?

Am 09.09.2008 um 22:49 schrieb Dennis Cote: > Lothar Behrens wrote: >> >> But when you say, that, if jornal files are open, transactions are >> opened, I would set a >> breakpoint at the line of code the transaction opens these jornal >> file >> and I could look >> arount there from who the

[sqlite] valgrind leak summary shows possible memory loss

I recently upgraded to latest sqlite 3.6.2 and valgrind shows possible memory loss of 272 bytes. I narrowed it down to a 2 line program of just sqlite3_open and sqlite_close. Valgrind doc says that this is probably a memory leak unless you are doing funny things with pointers. I saw the early

Re: [sqlite] bug in the RTree module

Hi, I think there has to be something wrong with the first insert that you do in a RTree table. If before copying the records to the virtual table you do a dummy insert then everything works OK. sqlite> CREATE VIRTUAL TABLE test_rtree USING RTREE(pkid, xmin, xmax, ymin, ymax); sqlite> INSERT

Re: [sqlite] Bigger table and query optimization

2008/9/9 Igor Tandetnik <[EMAIL PROTECTED]>: > "Stephen Oberholtzer" > <[EMAIL PROTECTED]> wrote in > message > news:[EMAIL PROTECTED] >> Idea: Submit a patch that allows LIKE expressions that start with a >> fixed >> string (i.e. don't start with '%') to use the index to improve >> performance.

Re: [sqlite] Bigger table and query optimization

On Tue, Sep 9, 2008 at 10:18 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote: > "Stephen Oberholtzer" > <[EMAIL PROTECTED]> wrote in > message > news:[EMAIL PROTECTED]<[EMAIL PROTECTED]> > > Idea: Submit a patch that allows LIKE expressions that start with a > > fixed > > string (i.e. don't start

Re: [sqlite] Multithreaded SQLite

"Gene Allen" <[EMAIL PROTECTED]> wrote in message news:!&[EMAIL PROTECTED] > I'm using SQLite v. 3.5 in my multithreaded application. > > I have 2 threads both writing to the same database, different tables > but same database. All is good until one of the threads goes under > HEAVY load, then

Re: [sqlite] Using Sqlite in place of BDB

I would strongly consider using normal sqlite to store your key values pairs. do you have a performance spec that you need to meet that would not allow that? Also you could just try it out to see if it is "good enough". On Tue, Sep 9, 2008 at 2:59 PM, Lawrence Gold <[EMAIL PROTECTED]> wrote: >

Re: [sqlite] Re installing original sqlite 3.1.3 on OS X

Here's the original sqlite binary and libraries from Tiger, hopefully you should be all set after installing these. To install, run: sudo ditto -V -x -z sqlite_tiger.cz /usr - adam On Sep 9, 2008, at 4:30 PM, elizagu wrote: Hi -- I wonder if anybody can help? I recently tried to

[sqlite] Using Sqlite in place of BDB

Hello, I work for a company which is seeking to replace its homegrown database engine with a more robust, modern engine. I've looked at BerkeleyDB, which would be ideal since all we really need are key/ value pairs for our records and indexes, but its cost is rather prohibitive. At this

Re: [sqlite] memsys3 vs memsys5

Thanks Ralf, this info confirms my observations. I believe it should be possible to write a function that initialises SQLite, calls SQLITE_STATUS_PAGECACHE_SIZE to calculate the required page-cache overhead for a given page size, and then reinitialises SQLite with the new page-cache settings.

[sqlite] memsys5 MallocInit

Hi list. Just thought I'd pass this along as a suggestion: I mistakenly called: sz = pgSz * pgCache... pBuf = malloc (sz).    sqlite3_config(SQLITE_CONFIG_PAGECACHE,pBuf, sz,  pgCache  ); This functioned however the sqlite MallocInit cored due to a pageSize of 32Meg. The doucmentation does

Re: [sqlite] char to int conversion

Dennis, I did not know about cast expression but it looks like a better solution. Thank you for your quick response. Jerry > >> > I have a CHAR field which is usually an integer. I would like to >> sort > this field as if it is an integer so that 1a 5b 10c 12xxx does >> not get > sorted as 10c