Re: [sqlite] Performance in a case of big columns number

> It'll work, but SQLite does not use a balanced tree to store the > columns for a particular record. So if you're seeking the 700th > column of a particular row, it has to look through 699 others before > it gets to it. Unless you always handle all the columns of a row > together, it'll

Re: [sqlite] Performance in a case of big columns number

Please sorry for my terrible Engilsh. :) Thanks for the answer. Yes, I know that it is bad design in the common case. But I have to use it because I have data which has following format: time | value_1 | value_2|value_ |---|-|

Re: [sqlite] blocking when locking

This is not a good example i think. If a transaction is intent to update after the select, it should start a write lock before the select. And as described in previous 'dead lock' example, the update in this example could fail due to 'dead lock' I believe the 'read lock' is designed for a 'read

Re: [sqlite] Most wanted features of SQLite ?

On 19 Sep 2009, at 3:07am, Igor Tandetnik wrote: > Simon Slavin wrote: >> Thanks to you and Jay for explanations. I hadn't encountered ICU at >> all before. Your descriptions make perfect sense and are very >> interesting since ICU is a good attempt to get around one of the >> fundamental

Re: [sqlite] Most wanted features of SQLite ?

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hamish Allan wrote: > Not sure whether this would need a rewrite, but for debug purposes I'd > love to be able to view the SQL for a prepared statement with its > values bound. I am always confused by requests like this. Your code called prepare and

Re: [sqlite] Most wanted features of SQLite ?

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Simon Slavin wrote: > Your descriptions make perfect sense and are very > interesting since ICU is a good attempt to get around one of the > fundamental problems of Unicode. Errr, this is not the fault of Unicode. It is the fault of people!

Re: [sqlite] Most wanted features of SQLite ?

Simon Slavin wrote: > Thanks to you and Jay for explanations. I hadn't encountered ICU at > all before. Your descriptions make perfect sense and are very > interesting since ICU is a good attempt to get around one of the > fundamental problems of Unicode. Out of curiosity - what do you consider

Re: [sqlite] Most wanted features of SQLite ?

On 19 Sep 2009, at 12:55am, Igor Tandetnik wrote: > Using ICU extension does require certain discipline. You must run > icu_load_collation soon after opening the database, and all users of > the > database must agree to map the same identifiers to the same locales > (the > best way to

Re: [sqlite] Most wanted features of SQLite ?

On Sat, Sep 19, 2009 at 12:14:37AM +0100, Simon Slavin scratched on the wall: > > On 18 Sep 2009, at 9:57pm, Igor Tandetnik wrote: > > > Simon Slavin > > wrote: > >> On 18 Sep 2009, at 9:07pm, Roger Binns wrote: > >> > >>> Simon Slavin wrote: > * Unicode

Re: [sqlite] Most wanted features of SQLite ?

Simon Slavin wrote: > On 18 Sep 2009, at 9:57pm, Igor Tandetnik wrote: > >> Simon Slavin >> wrote: >>> On 18 Sep 2009, at 9:07pm, Roger Binns wrote: >>> Simon Slavin wrote: > * Unicode support from the ground up SQLite

Re: [sqlite] full outer join questions

Stef Mientki wrote: > create table RT1 ( PID integer, V1 text ); > insert into RT1 values ( '684', 'aap' ); > insert into RT1 values ( '685', 'other empty' ); > create table RT2 ( PID integer, V2 text ); > insert into RT2 values ( '684', 'beer' ); > insert into RT2 values (

Re: [sqlite] Force the use of a specified index?

OK, it looks the construction with DATE instead of Julianday is a bit faster, so best option here seems to be: DELETE FROM TABLE1 WHERE ENTRY_ID NOT IN ( SELECT T1.ENTRY_ID FROM TABLE1 T1 INNER JOIN TABLE2 T2 ON (T1.PATIENT_ID = T2.PATIENT_ID) WHERE DATE(T1.ADDED_DATE, '+15 month') >

Re: [sqlite] Most wanted features of SQLite ?

On 18 Sep 2009, at 9:57pm, Igor Tandetnik wrote: > Simon Slavin > wrote: >> On 18 Sep 2009, at 9:07pm, Roger Binns wrote: >> >>> Simon Slavin wrote: * Unicode support from the ground up >>> >>> SQLite already has "unicode support from the ground up". Try using

Re: [sqlite] Most wanted features of SQLite ?

On 18 Sep 2009, at 9:43pm, Noah Hart wrote: > Stored Procedures How do those differ from what can be done with triggers ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] full outer join questions

thanks Pavel, and sorry for mixing the wikipedia example with the real situation. create table RT1 ( PID integer, V1 text ); insert into RT1 values ( '684', 'aap' ); insert into RT1 values ( '685', 'other empty' ); create table RT2 ( PID integer, V2 text ); insert into RT2 values ( '684', 'beer'

Re: [sqlite] Force the use of a specified index?

Had a look at this suggestion now and it works and uses the PATIENT_ID, ADDED_DATE index, but it is as slow as my delete with Julianday. It looks Pavel's suggestion is the way to do this. Just will have a look now and see if doing the construction with DATE( instead of Julianday is any faster.

Re: [sqlite] Most wanted features of SQLite ?

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Noah Hart wrote: > Stored Procedures Stored procedures don't make sense as a core part of SQLite because there is no one solution that fits all. For example what language would you write them in, and how would you deal with security (blindly loading

Re: [sqlite] Force the use of a specified index?

DELETE FROM TABLE2 WHERE ENTRY_ID NOT IN (SELECT T2.ENTRY_ID FROM TABLE2 T2 INNER JOIN TABLE1 T1 ON (T1.PATIENT_ID = T2.PATIENT_ID) WHERE julianday(T2.START_DATE, '+15 month') > julianday(T1.START_DATE) ) That is indeed a lot faster and then slightly faster than my approach with the

Re: [sqlite] Most wanted features of SQLite ?

StepSqlite brings powerful Stored Procedure support with full power of PL/SQL syntax to SQLite. It is a 'compiler' as opposed to a mere 'wrapper' so it generates much more efficient code than any wrapper could ever achieve - for instance, it pre-compiles all SQL in your code right when the

Re: [sqlite] Most wanted features of SQLite ?

We don't really need that "SQLite." We already have it. It is commonly called MySQL. It take well over 150MB of disk space and major management efforts to maintain any level of performance. Just what the client/server guys love to play with. SQLite is way too small and Bring to catch

[sqlite] Index usage

Hi, I'm trying to optimize a query for 2 different scenarios, and I'm having trouble getting something that works good in general. I want to be sure I'm not missing something. Here are the tables and indexes used in my database: sqlite> CREATE TABLE path_table (idx INTEGER PRIMARY KEY, path

Re: [sqlite] Force the use of a specified index?

From: "Bart Smissaert" Then the SQL I was trying to improve: DELETE FROM TABLE2 WHERE PATIENT_ID NOT IN ( SELECT TABLE1.PATIENT_ID FROM TABLE1 WHERE JULIANDAY(TABLE2.START_DATE, '-14 month') > JULIANDAY(TABLE1.START_DATE) AND TABLE1.PATIENT_ID

Re: [sqlite] Force the use of a specified index?

Thanks, will have a look at your suggestion and yes, I had a feeling I was overlooking some elemental things here. I typed it out all bit quick (hence the typo's and difference in the deletes), but I thought it would make clear what was going on. Will test now and see if your suggestion is indeed

Re: [sqlite] Force the use of a specified index?

*I'm leaving aside the rant that your first delete is not identical to combination of the select and delete in the second approach and select in second approach contains typos...* But did you try to combine your insert and delete statements from the second approach? This approach quicker because

Re: [sqlite] Most wanted features of SQLite ?

> On 18 Sep 2009, at 9:07pm, Roger Binns wrote: > > > Simon Slavin wrote: > >> * Unicode support from the ground up > > > > SQLite already has "unicode support from the ground up". Try using > > non-Unicode strings and you'll see! > > SQLite's indexing correctly understands how to order Unicode

Re: [sqlite] Most wanted features of SQLite ?

Simon Slavin wrote: > On 18 Sep 2009, at 9:07pm, Roger Binns wrote: > >> Simon Slavin wrote: >>> * Unicode support from the ground up >> >> SQLite already has "unicode support from the ground up". Try using >> non-Unicode strings and you'll see! > > SQLite's indexing

Re: [sqlite] Most wanted features of SQLite ?

Hello! On Saturday 19 September 2009 00:43:18 Noah Hart wrote: > Stored Procedures There are Tiny C compiler extension and realization of stored procedures for SQLite 2 and Lua extension and other. So you can use one or all of these. Best regards, Alexey Pechnikov. http://pechnikov.tel/

Re: [sqlite] Force the use of a specified index?

This is what I am dealing with: 2 tables with exactly the same schema (but could be slightly different, so can't put in same table): CREATE TABLE TABLE1( [PATIENT_ID] INTEGER, [ENTRY_ID] INTEGER PRIMARY KEY, [READ_CODE] TEXT, [ADDED_DATE] TEXT, [START_DATE] TEXT) The last 2 date

Re: [sqlite] Most wanted features of SQLite ?

Stored Procedures Noah Simon Slavin-2 wrote: > > So if you had a team of programmers to write something like SQLite > which didn't have the drawbacks SQLite has, which drawbacks would you > identify ? I'm asking not about minor faults with specific SQLite > library calls, but about

Re: [sqlite] Most wanted features of SQLite ?

On 18 Sep 2009, at 9:07pm, Roger Binns wrote: > Simon Slavin wrote: >> * Unicode support from the ground up > > SQLite already has "unicode support from the ground up". Try using > non-Unicode strings and you'll see! SQLite's indexing correctly understands how to order Unicode strings ? It

Re: [sqlite] Most wanted features of SQLite ?

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Simon Slavin wrote: > * Unicode support from the ground up SQLite already has "unicode support from the ground up". Try using non-Unicode strings and you'll see! The issue some developers have is that they also want collations, case comparisons etc

[sqlite] Most wanted features of SQLite ?

So if you had a team of programmers to write something like SQLite which didn't have the drawbacks SQLite has, which drawbacks would you identify ? I'm asking not about minor faults with specific SQLite library calls, but about the sort of things which require rewriting from the ground

Re: [sqlite] blocking when locking

Igor Tandetnik wrote: > Angus March wrote: > >>Yes, I see. So what is key to the problem is that someone tries to >> change their read lock to a write lock. I guess I just thought that >> the kernel that manages fcntl() would have a way of dealing with >> this. Can this

Re: [sqlite] Force the use of a specified index?

On 18 Sep 2009, at 4:49pm, bartsmissa...@blueyonder.co.uk wrote: >> Did something in the documentation make >> you think SQLite wouldn't use a >> multi-column index unless you forced it ? > > No, but I just noticed it didn't use the index I thought would be > best. As > it turned out it looks

Re: [sqlite] blocking when locking

Angus March wrote: >Yes, I see. So what is key to the problem is that someone tries to > change their read lock to a write lock. I guess I just thought that > the kernel that manages fcntl() would have a way of dealing with > this. Can this situation not be averted if at

Re: [sqlite] blocking when locking

>> You mean you will lock this extra-file before doing any update and >> unlock when update is done? Then ok, it will work. But again be aware >> of possible dead locks. >> > >You mean deadlocks are still possible in that scenario? How? I mean just that I don't know exactly what do you want

Re: [sqlite] blocking when locking

Pavel Ivanov wrote: > >>How does this preclude me from coming up w/my own lock file with >> POSIX locks? If a bunch of process start making incompatible requests on >> a single lock file, then they'll be queued and processed in order. I >> don't see how you can have a deadlock when you have

Re: [sqlite] blocking when locking

>> I think because they need to detect dead locks. BTW, I believe in case >> of dead lock even busy_handler will not be called, just SQLITE_BUSY is >> returned... >> >I guess that makes sense, in cases where multiple tables are involved. No, that makes sense when you're starting deferred

Re: [sqlite] blocking when locking

Angus March wrote: > Pavel Ivanov wrote: >>> Hell if I know why they use fcntl() for locks, and don't even give >>> you the option to block. >>> >> >> I think because they need to detect dead locks. BTW, I believe in >> case of dead lock even busy_handler will not be called,

Re: [sqlite] blocking when locking

Pavel Ivanov wrote: >> Hell if I know why they use fcntl() for locks, and don't even give >> you the option to block. >> > > I think because they need to detect dead locks. BTW, I believe in case > of dead lock even busy_handler will not be called, just SQLITE_BUSY is > returned... > I

Re: [sqlite] blocking when locking

Pavel Ivanov wrote: >>The kernel grants them: http://www.manpagez.com/man/2/flock . Or I >> might use fcntl(). >> > > That's why I've asked what is different here from what SQLite already > does because SQLite uses fcntl() on database file already. You can try > Then it must use

Re: [sqlite] blocking when locking

>The kernel grants them: http://www.manpagez.com/man/2/flock . Or I > might use fcntl(). That's why I've asked what is different here from what SQLite already does because SQLite uses fcntl() on database file already. You can try to change it to flock() of course but be aware that SQLite

Re: [sqlite] blocking when locking

Pavel Ivanov wrote: > >>To be clear, my idea of blocking is as follows: if one tries to >> achieve a lock, and it is not possible, the request is put into a queue, >> and the caller stops consuming cycles. Locks are then granted (when >> feasible) in the queue in the order that they were

Re: [sqlite] Running test after misc7-6.1.2

On Fri, Sep 18, 2009 at 7:51 AM, Stephan Wehner wrote: > On Fri, Sep 18, 2009 at 2:35 AM, D. Richard Hipp wrote: >> >> On Sep 17, 2009, at 10:56 PM, Stephan Wehner wrote: >> >>> I downloaded the sqlite3 source ( sqlite-3.6.18.tar.gz ) and managed >>> to

Re: [sqlite] blocking when locking

> Since then I've come to realize that > sqlite doesn't have such a blocking feature. Is that correct? Yes, that's correct. >I was thinking that a good solution would be to have a lock file, > with POSIX locks (I'm doing this in Linux) on it whenever one tries to > access the db in such a

[sqlite] blocking when locking

I'm writing this system wherein I want operations performed on the database to block when a lock cannot be achieved, and I'm looking at my options. This system that has multiple processes accessing a single sqlite file with a single database with a single table. I was disappointed to find out

Re: [sqlite] Indexes problem in unicode extension support

Alexey, >I'm using extension for base unicode support >(http://mobigroup.ru/files/sqlite-ext/unicode/), but in last two >releases find the problem with indexes by columns with redefined >NOCASE collation This code has many problems and the version on your site (the same version is available

Re: [sqlite] Running test after misc7-6.1.2

On Fri, Sep 18, 2009 at 2:35 AM, D. Richard Hipp wrote: > > On Sep 17, 2009, at 10:56 PM, Stephan Wehner wrote: > >> I downloaded the sqlite3 source ( sqlite-3.6.18.tar.gz ) and managed >> to complete "make". >> >> Now with  "make fulltest", there is no progress for over  almost

Re: [sqlite] List of active savepoints names

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Lukas Gebauer wrote: > I am searching some API function for get list of active savepoint > names. Is this possible? Thank you! Since your code is creating and releasing savepoints, why not just record them in your code? Also IIRC you can also have

Re: [sqlite] List of active savepoints names

> I am searching some API function for get list of active savepoint > names. Is this possible? Thank you! No, this is not possible. Why do you need it in the first place? Pavel On Fri, Sep 18, 2009 at 5:55 AM, Lukas Gebauer wrote: > > Hello all! > > I am searching some API

Re: [sqlite] full outer join questions

> Now the strange thing is that this query returns the correct number of rows, > but all the columns from the employee-table are empty. There's no "employee-table" in your query. > If I change "UNION" to "UNION ALL" the join works as expected. > Is there an explanation for this behavior ? As we

Re: [sqlite] cygwin and sqlite

> MacBook Mac OS X 10.5.8 > 2 GHz Intel Core Duo > 1 GB memory: > 17 minutes 46 seconds. > > IBM ThinkPad > Windows XP (latest patches) > 1.70 GHz, 512 MB memory: > 6 hours 25 minutes 57 seconds Windows is very slow in starting new processes if compared to any Unix system (especially if compared

Re: [sqlite] Force the use of a specified index?

I was trying to force the use of a multi-column index. Will have a better look and see what is going on here. For now I get best performance with a 2-stage approach with the use of a intermediate temp table. Will post the exact details of this later. RBS > If neither index individually offers a

Re: [sqlite] Running test after misc7-6.1.2

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 D. Richard Hipp wrote: > It is suppose to use usleep() (or the equivalent, depending > on your OS) to sleep for short intervals Note that on non-Windows platforms the default is to use sleep() which has a one second granularity. For people who

Re: [sqlite] Force the use of a specified index?

If neither index individually offers a performance boost, it's possible a single multi-column index might be better. Sam On Thu, Sep 17, 2009 at 3:43 PM, Bart Smissaert wrote: > Have tried INDEXED BY and it does indeed work and force the use of the > specified index. >

[sqlite] Indexes problem in unicode extension support

Hello! I'm using extension for base unicode support (http://mobigroup.ru/files/sqlite-ext/unicode/), but in last two releases find the problem with indexes by columns with redefined NOCASE collation (groups.name autoindex and composite index with const_telephony_direction.name):

[sqlite] List of active savepoints names

Hello all! I am searching some API function for get list of active savepoint names. Is this possible? Thank you! -- Lukas Gebauer. E-mail: gebau...@mlp.cz http://synapse.ararat.cz/ - Ararat Synapse - TCP/IP Lib. ___ sqlite-users mailing list

Re: [sqlite] New function request: sqlite3_busy_timeout_???(sqlite3*, int ms, int minSleepMs)

On Sep 18, 2009, at 4:52 AM, s.breith...@staubli.com wrote: > In my multithreaded applications I use > sqlite3_busy_timeout(sqlite3*, int > ms) to avoid failures on temporary locked databases. > > This works fine so far. The only problem is the very poor computer I > have > to use. I made a

Re: [sqlite] Running test after misc7-6.1.2

On Sep 17, 2009, at 10:56 PM, Stephan Wehner wrote: > I downloaded the sqlite3 source ( sqlite-3.6.18.tar.gz ) and managed > to complete "make". > > Now with "make fulltest", there is no progress for over almost two > hours. The present output is > misc7-6.1.1... Ok > misc7-6.1.2... Ok > >

[sqlite] New function request: sqlite3_busy_timeout_???(sqlite3*, int ms, int minSleepMs)

In my multithreaded applications I use sqlite3_busy_timeout(sqlite3*, int ms) to avoid failures on temporary locked databases. This works fine so far. The only problem is the very poor computer I have to use. I made a test to let 3 applications do nothing, but write to the database. When I use

Re: [sqlite] cygwin and sqlite

Pavel Ivanov wrote: >> At least I think that is what you suggest, and think it just >> may work! But I could be wrong! > > Yes, that's exactly what I suggest. > > Pavel It worked! Fortunately I had already parameterized SQLITE3 as a preference variable so I could have the same scripts run