[sqlite] Slow sqlite3_open() - possible culprits?

Hi, we are using SQLite3 + CEROD for a number of databases in an embedded systems application running on Windows CE. We're finding unexpectedly long time to open the database (0.5s ~ 2.8sec). Maybe, these times are reasonable, but they seem long to us. We are using:

Re: [sqlite] TCL Test failures on ARM

below is the backtrace after first hit at robust_ftruncate() (first hit after ts_ftruncate()) Breakpoint 1, ts_ftruncate (fd=5, n=297136) at ./src/test_syscall.c:273 273 if( tsIsFailErrno("ftruncate") ){ (gdb) break robust_ftruncate Breakpoint 2 at 0x66b14: file sqlite3.c, line 23589.

Re: [sqlite] Efficient way to store counters

> > > BTW, in case you don't do that yet your best performance will be if > > > you prepare your UPDATE and INSERT statements only once and then do > > > bind + step + reset in that 100k times loop. > > > > > > > In principle I agree, but since the temporary-table version is blindingly > >

Re: [sqlite] Efficient way to store counters

On Wed, Mar 13, 2013 at 11:48 AM, David King wrote: >> BTW, in case you don't do that yet your best performance will be if >> you prepare your UPDATE and INSERT statements only once and then do >> bind + step + reset in that 100k times loop. > > > In principle I agree, but

Re: [sqlite] TCL Test failures on ARM

Details provided helps to understand the problem ? while executing below line the failures are created. "faultsim_test_result {0 {abc def ghi truncate abc def ghi jkl mno pqr 2}}" i tried debugging this issue but could not really succeed. Thank you Brijesh -- View this message in context:

Re: [sqlite] TCL Test failures on ARM

Above code / details helps to understand the problem ??? -- View this message in context: http://sqlite.1065341.n5.nabble.com/TCL-Test-failures-on-ARM-tp67612p67699.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users

[sqlite] SQLite4 documentation error

Hi, I noticed an error in the SQLite4 documentation at http://sqlite.org/src4/doc/trunk/www/key_encoding.wiki in the section on numeric encoding. It says: "0x0d is also smaller than 0x0e, the initial byte of a text value" I believe that it should say: "0x23 is also smaller than 0x24, the

Re: [sqlite] Efficient way to store counters

On Wed, 13 Mar 2013 14:19:05 -0400 Igor Tandetnik wrote: > > I'm not sure about SQLite, but in principle the query optimizer can > > often use the base table's index for a derived value. Consider > > > > A join B on A.a = 1 + B.a > > or > > A join B on A.a <

Re: [sqlite] Windows-Specific 2-c-files Amalgamation?

On Wed, Mar 13, 2013 at 10:59 PM, Random Coder wrote: > On Wed, Mar 13, 2013 at 1:14 PM, Dominique Devienne > wrote: > > On Mon, Mar 11, 2013 at 5:21 PM, Bert Huijben wrote: > >> Since Visual C++ 2005 the limit in PDB files was

Re: [sqlite] Windows-Specific 2-c-files Amalgamation?

On Wed, Mar 13, 2013 at 1:14 PM, Dominique Devienne wrote: > On Mon, Mar 11, 2013 at 5:21 PM, Bert Huijben wrote: >> Since Visual C++ 2005 the limit in PDB files was increased to 24 bit. You >> might still get a warning for compatibility with older tools, but

Re: [sqlite] Order of ON DELETE CASCADE specified in SQLite?

On Wed, Mar 13, 2013 at 7:36 PM, Dan Kennedy wrote: > On 03/14/2013 12:09 AM, Dominique Devienne wrote: >> I stumbled upon >> http://stackoverflow.com/questions/60168/in-what-order-are-on-delete-cascade-constraints-processed >> >> Is this just happenstance in this case or

Re: [sqlite] Windows-Specific 2-c-files Amalgamation?

On Mon, Mar 11, 2013 at 5:41 PM, Richard Hipp wrote: > On Mon, Mar 11, 2013 at 12:17 PM, Dominique Devienne wrote: > > Perhaps someone knows a trick or two to work-around that MS debugging issue? > See the http://www.sqlite.org/draft/download.html for a

Re: [sqlite] Windows-Specific 2-c-files Amalgamation?

On Mon, Mar 11, 2013 at 5:21 PM, Bert Huijben wrote: > > -Original Message- > > From: ... On Behalf Of Dominique Devienne > > Except I can't debug it... 'Cause I'm primarily on Windows, which has a > > well-known limitation in the way it stores Debug Information (uses

Re: [sqlite] Efficient way to store counters

> > The logic is, "keep a given (k1, k2) pair around for one day for each > > time it's been seen". I could calculate it when it's needed, but > > since it's a computed value, I couldn't then have an index on it. > > I'm not sure about SQLite, but in principle the query optimizer can > often use

Re: [sqlite] Efficient way to store counters

> BTW, in case you don't do that yet your best performance will be if > you prepare your UPDATE and INSERT statements only once and then do > bind + step + reset in that 100k times loop. In principle I agree, but since the temporary-table version is blindingly fast up the the update-the-disk

Re: [sqlite] Efficient way to store counters

On Wednesday, 13 March, 2013 at 06:15, Michael Black wrote: > You're simply missing the where clause on your update so you're updating the > entire database every time you do an insert. > update_counter(k1,k2 count=count+1,expires=now+count*1day) where field1=k1 > and field2=k2; > > And a (k1,k2)

Re: [sqlite] Order of ON DELETE CASCADE specified in SQLite?

On 03/14/2013 12:09 AM, Dominique Devienne wrote: I stumbled upon http://stackoverflow.com/questions/60168/in-what-order-are-on-delete-cascade-constraints-processedand tried a simplified version in SQLite3, which appears to work fine (see below). Is this just happenstance in this case or will

Re: [sqlite] Efficient way to store counters

On 3/13/2013 8:49 AM, James K. Lowden wrote: I'm not sure about SQLite, but in principle the query optimizer can often use the base table's index for a derived value. Consider A join B on A.a = 1 + B.a or A join B on A.a < sqrt(B.a) An index on B.a is useful to finding the

Re: [sqlite] Joining tow tables with subset of columns from one

Yes thanks Kevin Dull question and I was just coming back here to say I have sorted it. Thanks anyway :) On 13 March 2013 17:59, Kevin Martin wrote: > > On 13 Mar 2013, at 17:44, Paul Sanderson wrote: > > > I want to join two table by doing a select in the form > > > >

Re: [sqlite] Joining tow tables with subset of columns from one

On 13 Mar 2013, at 17:44, Paul Sanderson wrote: > I want to join two table by doing a select in the form > > select col1, col2, col3 from table1 as t1, * from table2 as t2 where > t1.col1 = t2.x Are you trying to do: select t1.col1, t1.col2, t1.col3, t2.* from table1 as t1 join table2 as t2

Re: [sqlite] Non-English username in WinRT causes SQLiteException

On 13 Mar 2013, at 5:11pm, Dominique Devienne wrote: > On Wed, Mar 13, 2013 at 5:47 PM, Simon Slavin wrote: >> I don't know what the convention is for handling strings in >> .NET. If it is that every string can be in any codepage, then the SQLite >>

Re: [sqlite] Non-English username in WinRT causes SQLiteException

On Wed, Mar 13, 2013 at 5:47 PM, Simon Slavin wrote: > On 13 Mar 2013, at 3:39pm, Dominique Devienne wrote: > > Indeed, it's whoever wrote that SQLite3.Open() .NET wrapper on top of the > > C-API that's responsible from doing proper conversion from that

[sqlite] Order of ON DELETE CASCADE specified in SQLite?

I stumbled upon http://stackoverflow.com/questions/60168/in-what-order-are-on-delete-cascade-constraints-processedand tried a simplified version in SQLite3, which appears to work fine (see below). Is this just happenstance in this case or will it work every-time there are similar "pseudo cycles"

Re: [sqlite] Non-English username in WinRT causes SQLiteException

On 13 Mar 2013, at 3:39pm, Dominique Devienne wrote: > On Wed, Mar 13, 2013 at 4:01 PM, Ercan Özdemir wrote: > >> However, does every developer have to write or change his code like this? >> > > Indeed, it's whoever wrote that SQLite3.Open() .NET

Re: [sqlite] Non-English username in WinRT causes SQLiteException

On Wed, Mar 13, 2013 at 4:01 PM, Ercan Özdemir wrote: > However, does every developer have to write or change his code like this? > Indeed, it's whoever wrote that SQLite3.Open() .NET wrapper on top of the C-API that's responsible from doing proper conversion from that

Re: [sqlite] Non-English username in WinRT causes SQLiteException

Thanks Simon, problem solved with two ways First option: var dbPath2 = Path.Combine(Windows.Storage.ApplicationData.Current.RoamingFolder.Path, "test.db"); string utf8String = String.Empty; // Get UTF16 bytes and

Re: [sqlite] Efficient way to store counters

On Tue, Mar 12, 2013 at 11:03 PM, David King wrote: >> > At first I was just doing something like this pseducode: >> > update_counter(k1, k2, count=count+1, expires=now+count*1day) >> > if rows_updated != 1: insert_counter(k1, k2, count=1, expires=now+1day) >> >> Assuming

Re: [sqlite] Non-English username in WinRT causes SQLiteException

On 13 Mar 2013, at 11:45am, Ercan Özdemir wrote: > I have some applications in Windows 8 store using SQLite as database. > I discovered that if there are any non-English character in logged on > username, SQLite couldn't open datase file. > > Here is my test code: > >

Re: [sqlite] Non-English username in WinRT causes SQLiteException

I don't know which encoding is used by Windows to store usernames. The code receives this error even before creating any single table, because I couldn't open a connection. This is the detail of the exception * SQLite.SQLiteException was caught

Re: [sqlite] Efficient way to store counters

You're simply missing the where clause on your update so you're updating the entire database every time you do an insert. update_counter(k1,k2 count=count+1,expires=now+count*1day) where field1=k1 and field2=k2; And a (k1,k2) index would help that update a lot.

Re: [sqlite] Non-English username in WinRT causes SQLiteException

On Wed, 13 Mar 2013 13:45:55 +0200 Ercan Özdemir wrote: > I have some applications in Windows 8 store using SQLite as database. > I discovered that if there are any non-English character in logged on > username, SQLite couldn't open datase file. What encoding is used for

Re: [sqlite] Efficient way to store counters

On Tue, 12 Mar 2013 21:20:11 -0700 David King wrote: > > > At first I was just doing something like this pseducode: > > > update_counter(k1, k2, count=count+1, expires=now+count*1day) > > > if rows_updated != 1: insert_counter(k1, k2, count=1, expires=now > > > +1day) > > >

[sqlite] Non-English username in WinRT causes SQLiteException

Hello, This is my first message in this mail group, so I am sorry if I'm missing any rule. I have some applications in Windows 8 store using SQLite as database. I discovered that if there are any non-English character in logged on username, SQLite couldn't open datase file. Here is my test

Re: [sqlite] Efficient way to store counters

>> First of all in the statement above you don't gain benefit from >> uniqueness and replace about 10k rows twice. > > Are you sure? The SELECT in the INSERT OR UPDATE selects "FROM > trans_counters_v AS c", the grouped temporary view. So it should only see any > given key pair once before it

Re: [sqlite] TCL Test failures on ARM

Hi, Below is the code sequence after making a breakpoint at robust_ftruncate() i could see that "robust_ftruncate" and "ts_ftruncate" is called couple of times before the error is given to the user Breakpoint 2, ts_ftruncate (fd=5, n=297136) at ./src/test_syscall.c:273 273 if(

Re: [sqlite] Efficient way to store counters

> > At first I was just doing something like this pseducode: > > update_counter(k1, k2, count=count+1, expires=now+count*1day) > > if rows_updated != 1: insert_counter(k1, k2, count=1, expires=now+1day) > > Assuming these 2 statements constitute each of the 10k-100k steps you > mentioned above