Re: [sqlite] Sqlite handler in trigger

On 9/6/2013 12:51 AM, techi eth wrote: I am not sure if my problem I have stated clearly, found below detail explanation!!! Process 1: Handler1 = OpenConn(); Sqlite_Createfunc(Handler1,

Re: [sqlite] Sqlite handler in trigger

I am not sure if my problem I have stated clearly, found below detail explanation!!! Process 1: Handler1 = OpenConn(); Sqlite_Createfunc(Handler1, my_notifier_function()..); CREATE TRIGGER Event_test1

Re: [sqlite] UPDATE question

On Thu, 5 Sep 2013 19:53:15 +0100 Simon Slavin wrote: > On 5 Sep 2013, at 7:20pm, Peter Haworth wrote: > > > That works fine but wondering if there might be a single UPDATE > > statement that could do this for me. I can use the WHERE clause to > > select

Re: [sqlite] Please help me optimize this LEFT JOIN query.

On Sep 5, 2013, at 9:45 PM, Yuzem wrote: > It is incredibly fast but it gives me the wrong result: Yeah… you forgot the 'where' clause in the subquery… you need to restrict it on the genre ___ sqlite-users mailing list

Re: [sqlite] Please help me optimize this LEFT JOIN query.

Thanks Petite Abeille, I translated your code to this: SELECT genres.genres, ( SELECT substr(group_concat(name,' '),1,60) FROM ( SELECTname FROM movies JOIN genres ON

Re: [sqlite] SQL insert performance on Windows Mobile 6.5

Richard, Thanks for the reply. I will look into the profiling tools. The only diagnostic I have is the CPU utilization of the java process which is very high (90%) for the database update. -Tony -Original Message- From: sqlite-users-boun...@sqlite.org

Re: [sqlite] SQL insert performance on Windows Mobile 6.5

On Thu, Sep 5, 2013 at 7:26 PM, Bullock, Tony wrote: > > Hi, > > I have a performance issue with SQLitejdbc with Java on Windows Mobile > 6.5 running on a MC9190-G mobile computer. I am using sqlitejdbc-v056.jar > with the NSIcom CrE-ME 4.12 JVM. > > Performance of

[sqlite] SQL insert performance on Windows Mobile 6.5

Hi, I have a performance issue with SQLitejdbc with Java on Windows Mobile 6.5 running on a MC9190-G mobile computer. I am using sqlitejdbc-v056.jar with the NSIcom CrE-ME 4.12 JVM. Performance of the insert is very low. I am getting about 1000 records added a minute. Any ideas the on

Re: [sqlite] Please help me optimize this LEFT JOIN query.

On Sep 5, 2013, at 11:27 PM, Yuzem wrote: > Any clue on why LEFT JOIN is so slow when used with genres but not with > larger tables? Sure. But your conclusion is a most likely a red herring. The crux of the matter is that inner and outer join have a wildly different

Re: [sqlite] Please help me optimize this LEFT JOIN query.

Petite Abeille-2 wrote > Sure. But your conclusion is a most likely a red herring. The crux of the > matter is that inner and outer join have a wildly different semantic. And > therefore execution plan. That's all. Seems all very reasonable from an > optimizer point of view. But I have no

Re: [sqlite] Please help me optimize this LEFT JOIN query.

On Sep 5, 2013, at 10:28 PM, Yuzem wrote: > Ok, wonderful, now it is working correctly but how do I select multiple > columns from table movies? > Should I add another sub query? Nope. You have now changed the problem definition, so scalars will not be a good fit.

Re: [sqlite] Please help me optimize this LEFT JOIN query.

Thanks but 4 rows is not what I am looking for. I found a solution, concatenation: SELECT genres.genres, ( SELECT group_concat(movie,' ') FROM ( SELECTmovies.movies||','||name movie FROM movies JOIN

Re: [sqlite] Please help me optimize this LEFT JOIN query.

Ok, wonderful, now it is working correctly but how do I select multiple columns from table movies? Should I add another sub query? Example: SELECT genres.genres, ( SELECT substr(group_concat(name,' '),1,60) FROM ( SELECTname

Re: [sqlite] Please help me optimize this LEFT JOIN query.

On Sep 5, 2013, at 8:56 PM, Yuzem wrote: > SELECT genres FROM genres LEFT JOIN movies ON genres.movies = movies.movies > GROUP BY genres ORDER BY genres; > time: 2.475s > > SELECT genres FROM genres JOIN movies ON genres.movies = movies.movies GROUP > BY genres ORDER BY

Re: [sqlite] Please help me optimize this LEFT JOIN query.

I did some testing and found some strange results. SELECT genres FROM genres LEFT JOIN movies ON genres.movies = movies.movies GROUP BY genres ORDER BY genres; time: 2.475s SELECT genres FROM genres JOIN movies ON genres.movies = movies.movies GROUP BY genres ORDER BY genres; time: 0.035s

Re: [sqlite] UPDATE question

On 5 Sep 2013, at 7:20pm, Peter Haworth wrote: > That works fine but wondering if there might be a single UPDATE statement > that could do this for me. I can use the WHERE clause to select sequence > 3,4, and 5 but the UPDATE has to process the rows in descending sequence >

Re: [sqlite] Please help me optimize this LEFT JOIN query.

I did an ANALYZE but I'm getting the same results. I do have indexes: CREATE TABLE movies ( movies UNIQUE, name, icon_modified ); CREATE TABLE genres ( genres, movies, UNIQUE(genres,movies) ); people has an index on people (people UNIQUE) and tasks is a view: CREATE VIEW tasks AS

Re: [sqlite] Please help me optimize this LEFT JOIN query.

On 5 Sep 2013, at 7:56pm, Yuzem wrote: > I did some testing and found some strange results. Please do an ANALYZE and try the same things again. Also, do you have any indexes on those tables (apart from the primary keys, of course) ? Simon.

Re: [sqlite] Please help me optimize this LEFT JOIN query.

On Sep 4, 2013, at 4:21 PM, Yuzem wrote: > I want to construct genres icons and each icon must display 4 movies. Assuming this is IMDB… what about a scalar subquery? For example, assuming a slightly different schema from yours: selectgenre.code as genre, (

Re: [sqlite] UPDATE question

Oops.. sorry.. I missed the last paragraph. If you're essentially single threaded.. I can do it in two updates... UPDATE table SET Sequence = -(Sequence + 1) WHERE Sequence >= seq_to_insert AND Name = name_to_insert UPDATE table SET Sequence = -Sequence WHERE Sequence < 0 AND Name =

Re: [sqlite] UPDATE question

How about... ? UPDATE table SET Sequence = Sequence + 1 WHERE Sequence >= seq_to_insert AND Name = name_to_insert -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Peter Haworth Sent: Thursday, September 05, 2013 2:21 PM To:

[sqlite] UPDATE question

I have a table with the following (simplified) structure PKeyINTEGER PRIMARY KEY NameTEXT Sequence INTEGER The Name/Sequence pair of columns is defined as UNIQUE I need to insert a new row into a point between two sequence numbers. For example, if the existing rows are: Name Sequence

Re: [sqlite] Collating sequences. Was: Query problems

On Thu, Sep 5, 2013 at 9:40 AM, Marc L. Allen wrote: > The left-most of the first select? Or the second? Maybe I don't > understand 'left-most?' > The left-most SELECT statement in the query where the column has an explicit collating sequence. In other words: the

Re: [sqlite] Collating sequences. Was: Query problems

The left-most of the first select? Or the second? Maybe I don't understand 'left-most?' -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp Sent: Thursday, September 05, 2013 9:36 AM To: General Discussion of

Re: [sqlite] Collating sequences. Was: Query problems

On Thu, Sep 5, 2013 at 9:31 AM, Igor Tandetnik wrote: > On 9/5/2013 7:31 AM, Richard Hipp wrote: > >> There seems to be some misinformation circulating in this thread. Please >> let me try to clear up a few points. >> > > While we are at it, an interesting question was

Re: [sqlite] Collating sequences. Was: Query problems

On 9/5/2013 7:31 AM, Richard Hipp wrote: There seems to be some misinformation circulating in this thread. Please let me try to clear up a few points. While we are at it, an interesting question was raised upthread. What happens here: create table t1(x text collate c1); create table t2(x

Re: [sqlite] about Khmer unicode with sqlite

It may also be the system which is actually doing the display to the terminal. In my case, I was displaying a UTF-8 document on Linux, using Konsole. I had set LC_ALL to en_US.UTF-8. The file had the UTF-8 sequence 0xe2 0x80 0x93, which is U+2013, or an "en dash". But I was seeing an "latin small

Re: [sqlite] Sqlite handler in trigger

On 9/5/2013 5:39 AM, techi eth wrote: I have case where one process is updating data with his private handler but trigger on update is created by other process by his private handler. I'm not sure I understand this sentence. When you run CREATE TRIGGER statement, the trigger you've just

Re: [sqlite] Documentation update request

Hi, Simon, von Simon Slavin > On 4 Sep 2013, at 3:05pm, Markus Schaber > wrote: > > > Afaics, this applies to partial indices for similar reasons. > > I did not even know partial indices was implemented. Thank you. It's new in 3.8.0 :-) Best regards Markus Schaber

Re: [sqlite] Performance question related to multiple processes using sqlite

On Wed, Sep 4, 2013 at 3:51 PM, Varadan, Yamini (SCR US) (EXT) < yamini.varadan@siemens.com> wrote: > > But would any one know if there is any kind of synchronization that is > done between different processes that connect to different sqlite databases > that might slow down one process when

Re: [sqlite] about Khmer unicode with sqlite

On Thu, Sep 5, 2013 at 6:16 AM, Sarith San wrote: > Dear Sir or Madam > > How do integrate Khmer unicode into sqlite? > When I try to use Khmer unicode with sqlite, the characters does not > display. All I see, they display in > English. > SQLite is a C-library. It does

[sqlite] commit fails when journal_mode = memory

Hello, The next problem occurs since switching to sqlite 3.8; it was never observed in 3.7.17 or earlier. I have an application in which I insert a lot of data in a transaction, started by executing "begin transaction", after having executed "PRAGMA synchronous = 0" and "PRAGMA journal_mode =

[sqlite] Performance question related to multiple processes using sqlite

Hello, We use Qt with sqldriver Sqlite-4 in our application. (Windows XP 32 bit, Visual studio 2005) We are facing a performance issue in the following scenario. There are two processes A and B. A uses sqlite DB1 and keeps populating data. Process B uses sqlite DB2 for writing and occasionally

[sqlite] about Khmer unicode with sqlite

Dear Sir or Madam How do integrate Khmer unicode into sqlite? When I try to use Khmer unicode with sqlite, the characters does not display. All I see, they display in English. Thanks, Samuel San ___ sqlite-users mailing list sqlite-users@sqlite.org

[sqlite] Collating sequences. Was: Query problems

There seems to be some misinformation circulating in this thread. Please let me try to clear up a few points. If you give a collating sequence to a column in a table definition, then that collating sequence becomes the default for that column: CREATE TABLE t1(pqr TEXT COLLATE xyzzy); In

Re: [sqlite] Different read/write behaviour when using System.Data.SQLite .NET Wrapper and using database encryption through a password leads to data inconsistency

Thank you very much for the responses. That was helpful. With best regards, Christoph Brzozowski Siemens AG Industry Sector Industry Automation Division Industrial Automation Systems Process Automation I IA AS PA CIS R 5 Karl-Legien-Str. 190 53117 Bonn, Germany Tel: +49 228 64805-215

[sqlite] Sqlite handler in trigger

Hi, I would like to check sqlite handler usability while using trigger: I have register one function to sqlite by sqlite3_create_function().Now I am creating trigger on update & selecting that function for callaback. In above scenario it is mandatory that sqlite3 handler used while create

Re: [sqlite] Different read/write behaviour when using System.Data.SQLite .NET Wrapper and using database encryption through a password leads to data inconsistency

Brzozowski, Christoph wrote: > > Our application uses the System.Data.SQLite .NET Wrapper ( version 1.0.66.0 ) > That version was released in April 2010 and is no longer officially supported. > > in a multi user scenario, where multiple users on different machines access the > same database,