[sqlite] Query containing correlated subquery gets "stuck"

On 27 May 2015, at 11:00am, gunnar wrote: > SELECT * > FROM ordercallback cb > WHERE sessionuuid=@SESSIONUUID >AND endstate=0 >AND working=1 >AND cb_seq_num=( >SELECT max(cb_seq_num) >FROM ordercallback >WHERE server_order_id=cb.server_order_id >

[sqlite] Query containing correlated subquery gets "stuck"

s 1 reads > data and sends it off to process 2 that tries to modify stuff)? > How are you binding values to the SESSIONUUID variable using the sqlite shell > alone? > > -Urspr?ngliche Nachricht- > Von: gunnar [mailto:gharms at hiqinvest.nl] > Gesendet: Mittwoch, 27. Ma

[sqlite] Query containing correlated subquery gets "stuck"

Hi, Sometimes when I execute (from the sqlite_shell program) a select-query that contains a correlated subquery that query gets 'stuck'. It outputs part of the expected results and then doesn't come back to command prompt and also doesn't output any more records. I suspect it is because

[sqlite] Query containing correlated subquery gets "stuck"

n: General Discussion of SQLite Database Betreff: [sqlite] Query containing correlated subquery gets "stuck" Hi, Sometimes when I execute (from the sqlite_shell program) a select-query that contains a correlated subquery that query gets 'stuck'. It outputs part of the expected results a

[sqlite] Query containing correlated subquery gets "stuck"

On 5/27/15, gunnar wrote: > Hi, > > Sometimes when I execute (from the sqlite_shell program) a select-query > that contains a correlated subquery that query gets 'stuck'. It outputs > part of the expected results and then doesn't come back to command > prompt and also doesn't output any more

[sqlite] Query on Sample SQLite Application

On 13 May 2015, at 6:59pm, raj at OnSiteSoftware.com wrote: > We Inserted 1000 rec, where we were able to insert all records, but not > able to fetch it. When you tried to fetch it, did you get an error result ? If so, what number result did you get ? Can you try fetching just 1 record,

[sqlite] Query on Sample SQLite Application

Hello, We are using Sample Sql Lite Project provided by Telerik App Builder: http://docs.telerik.com/platform/appbuilder/sample-apps/sample-sqlite We are using sql lite plugin 1.0.1 suggested by the Telerik as it works all fine on the ANDROID AND Apple. Following scenario compiles our

[sqlite] Query times vary between 0.2 s and 30 s for very

On 26 Mar 2015, at 11:52pm, James K. Lowden wrote: > I guess I was unclear. I was asking about SQLite's implementation, > specifically whether an IN clause is always sorted using an ephemeral > table, or if that complexity is subject to some minimum threshhold. The answer to this is

[sqlite] Query times vary between 0.2 s and 30 s for very

On Wed, 25 Mar 2015 10:02:24 + Simon Slavin wrote: > > On 25 Mar 2015, at 3:28am, James K. Lowden > wrote: > > > Is there some lower bound on either the size of the IN list or the > > number of rows in the table being queried? > > There's nothing in the language to stop you from

[sqlite] Query times vary between 0.2 s and 30 s for very

On 25 Mar 2015, at 3:28am, James K. Lowden wrote: > Is there some lower bound on either the size of the IN list or the > number of rows in the table being queried? There's nothing in the language to stop you from executing "... WHERE c IN (12) ..." on a zero-row table. It won't be efficient,

[sqlite] Query times vary between 0.2 s and 30 s for very

worst case -- than to sort the IN list first and scan the table, O(m n log(n)). --jkl > >-Urspr?ngliche Nachricht- > >Von: James K. Lowden [mailto:jklowden at schemamania.org] > >Gesendet: Samstag, 21. M?rz 2015 20:43 > >An: sqlite-users at mailinglists.sqlite.org

[sqlite] Query times vary between 0.2 s and 30 s for very

The temporary table is creates as CREATE TEMPORARY TABLE _tempNNN (oid INTEGER PRIMARY KEY) So the optimizer must know that the values are unique.

[sqlite] Query help

Sorry, i was too fast. Correction SELECT engine,coalesce(groupname,'*') as groupname,databasename,key,value FROM EnginePreferences left join groups on (groups.groupid = EnginePreferences.groupid); OR SELECT engine,groupname,databasename,key,value FROM EnginePreferences left join

[sqlite] Query help

Hi, SELECT engine,coalesce(groupname,*) as groupname,databasename,key,value FROM EnginePreferences left join groups on (groups.groupid = EnginePreferences.groupid); OR SELECT engine,coalesce(groupname,*) as groupname,databasename,key,value FROM EnginePreferences left join (select

[sqlite] Query help

I have a table EnginePreference: CREATE TABLE EnginePreferences (engine TEXT COLLATE NOCASE, databasename TEXT COLLATE NOCASE, key TEXT COLLATE NOCASE, value TEXT, groupid INTEGER, UNIQUE(engine,databasename,key)) and a table Groups: CREATE TABLE Groups (groupid INTEGER PRIMARY KEY, groupname

[sqlite] Query times vary between 0.2 s and 30 s for very

SQLite creates an ephemeral table for the IN list,giving O(log n) performance for lookups. >-Urspr?ngliche Nachricht- >Von: James K. Lowden [mailto:jklowden at schemamania.org] >Gesendet: Samstag, 21. M?rz 2015 20:43 >An: sqlite-users at mailinglists.sqlite.org >Betre

[sqlite] Query times vary between 0.2 s and 30 s for very

On Sat, 21 Mar 2015 14:36:47 -0600 Scott Robison wrote: > On Mar 21, 2015 1:43 PM, "James K. Lowden" > wrote: > > > > The query optimizer has to be sophisticated enough to recognize > > those conditions, which is unlikely in the case of a temporary > > table. > > Are temporary tables really

[sqlite] Query times vary between 0.2 s and 30 s for very

On Sun, Mar 22, 2015 at 2:47 PM, James K. Lowden wrote: > On Sat, 21 Mar 2015 14:36:47 -0600 > Scott Robison wrote: > > > > > > > The query optimizer has to be sophisticated enough to recognize > > > those conditions, which is unlikely in the case of a temporary > > > table. > > > > Are

[sqlite] Query times vary between 0.2 s and 30 s for very

I don?t think I can always run an analyze on the TEMP table for each query. May ruin performance worse than trying my luck with the temp table. I think this boils down why a JOIN with a 500 row table (one column) is so much (several hundred times) slower than using an IN clause with 500

[sqlite] Query times vary between 0.2 s and 30 s for very

>I don?t think I can always run an analyze on the TEMP table for each >query. May ruin performance worse than trying my luck with the temp >table. >I think this boils down why a JOIN with a 500 row table (one column) is >so much (several hundred times) slower than using an IN clause with 500

[sqlite] Query times vary between 0.2 s and 30 s for very

On Mar 21, 2015 1:43 PM, "James K. Lowden" wrote: > > The query optimizer has to be sophisticated enough to recognize > those conditions, which is unlikely in the case of a temporary table. Are temporary tables really that different? Other than being dropped automatically at the end of a

[sqlite] Query times vary between 0.2 s and 30 s for very

On Sat, 21 Mar 2015 19:01:16 +0100 "Mario M. Westphal" wrote: > For now I have increased the threshold for IN clauses (instead of > TEMP tables) and use WHERE IN (SELECT ? from TEMP) instead of a JOIN. Because the two are conceptually different, it's not surprising they run differently. IN is

[sqlite] Query times vary between 0.2 s and 30 s for very

On Mar 21, 2015 1:43 PM, "James K. Lowden" wrote: > > The query optimizer has to be sophisticated enough to recognize > those conditions, which is unlikely in the case of a temporary table. Are temporary tables really that different? Other than being dropped automatically at the end of a

[sqlite] Query times vary between 0.2 s and 30 s for very similar queries - how to trick the optimizer?

On Wed, 18 Mar 2015 14:43:26 +0100 "Mario M. Westphal" wrote: > I?m using 3.8.8.1 on Windows via the ?C? interface. > > I work with SQLite for several years with good success. And I know > that no optimizer ever will be perfect. But I?ve just stumbled upon a > case where a very similar query

[sqlite] Query times vary between 0.2 s and 30 s for very similar queries - how to trick the optimizer?

I?m using 3.8.8.1 on Windows via the ?C? interface. I work with SQLite for several years with good success. And I know that no optimizer ever will be perfect. But I?ve just stumbled upon a case where a very similar query requires between 0.2 seconds and a whopping 30 seconds. I?ve simplified

Re: [sqlite] Query Planner for Virtual Tables: link table evaluation & transitive property of constraints not used

- Original Message - From: "Josef Kucera" <jokusoftw...@gmail.com> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> Sent: Monday, December 15, 2014 5:37 PM Subject: Re: [sqlite] Query Planner for Virtual Tables: link table evaluation &

Re: [sqlite] Query Planner for Virtual Tables: link table evaluation & transitive property of constraints not used

- Original Message - From: "Nico Williams" <n...@cryptonector.com> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> Sent: Monday, December 15, 2014 5:16 PM Subject: Re: [sqlite] Query Planner for Virtual Tables: link table

Re: [sqlite] Query Planner for Virtual Tables: link table evaluation & transitive property of constraints not used

On Mon, Dec 15, 2014 at 06:23:31PM +0700, Dan Kennedy wrote: > It's tricky. As you say, xBestIndex() will currently be invoked > twice - once with no constraints usable and once with both "b.id=?" > and "b.linkid=?" usable. I guess the reason it is not invoked in the > other ways you suggest is

Re: [sqlite] Query Planner for Virtual Tables: link table evaluation & transitive property of constraints not used

- Original Message - From: "Hick Gunter" <h...@scigames.at> To: "'General Discussion of SQLite Database'" <sqlite-users@sqlite.org> Sent: Monday, December 15, 2014 2:40 PM Subject: Re: [sqlite] Query Planner for Virtual Tables: link table evaluation &

Re: [sqlite] Query Planner for Virtual Tables: link table evaluation & transitive property of constraints not used

re? Plan 3 is better only for very sparse link tables where b < a < c is true. -Ursprüngliche Nachricht- Von: Dan Kennedy [mailto:danielk1...@gmail.com] Gesendet: Montag, 15. Dezember 2014 12:24 An: General Discussion of SQLite Database Betreff: Re: [sqlite] Query Planner for

Re: [sqlite] Query Planner for Virtual Tables: link table evaluation & transitive property of constraints not used

On 12/15/2014 13:23 PM, Dan Kennedy wrote: > On 12/12/2014 09:22 PM, Josef Kučera wrote: > > Hello, > > I am trying to use SQLite's marvellous Virtual Table mechanism as a SQL > > layer for querying an in memory storage. This works good, but I have a > > problem with more complex queries. When

Re: [sqlite] Query Planner for Virtual Tables: link table evaluation & transitive property of constraints not used

On 12/12/2014 09:22 PM, Josef Kučera wrote: Hello, I am trying to use SQLite's marvellous Virtual Table mechanism as a SQL layer for querying an in memory storage. This works good, but I have a problem with more complex queries. When querying a real SQLite database it correctly moves the

[sqlite] Query Planner for Virtual Tables: link table evaluation & transitive property of constraints not used

Hello, I am trying to use SQLite's marvellous Virtual Table mechanism as a SQL layer for querying an in memory storage. This works good, but I have a problem with more complex queries. When querying a real SQLite database it correctly moves the constant conditions across joined tables to optimize

Re: [sqlite] sqlite Query Optimizer

On 16 Oct 2014, at 11:07am, Prakash Premkumar wrote: > Are there any projects where , sqlite optimizer has been extended to add > System R The optimizer currently built into SQLite does the same job as what you're thinking of as "System R". It is not possible to use

Re: [sqlite] sqlite Query Optimizer

Thanks a lot Simon. Are there any projects where , sqlite optimizer has been extended to add System R or other algorithms ? I browsed through the github repository and could not find any. Thanks a lot for your time. Regards Prakash On Thu, Oct 16, 2014 at 12:49 PM, Simon Slavin

Re: [sqlite] sqlite Query Optimizer

On 16 Oct 2014, at 7:50am, Prakash Premkumar wrote: > Does sqlite implement the pointers in the System R Algorithm ? SQLite does not implement the System R Algorithm, so no, it doesn't implement the pointers from System R. It implements the algorithms described in the

Re: [sqlite] sqlite Query Optimizer

Thanks for your reply Simon. I have read through those links. Does sqlite implement the pointers in the System R Algorithm ? Like assigning selectivity factors for predicates in where clause? Link to System R Algorithm: https://www.cs.duke.edu/courses/spring03/cps216/papers/selinger-etal-1979.pdf

Re: [sqlite] sqlite Query Optimizer

On 15 Oct 2014, at 12:54pm, Prakash Premkumar wrote: > I'm trying to understand the sqlite select query optimizer. It works by > assigning costs to each relation in FROM clause. That is only a little bit of how it works. Have you read these ?

[sqlite] sqlite Query Optimizer

Hi, I'm trying to understand the sqlite select query optimizer. It works by assigning costs to each relation in FROM clause. As far as I understand, it primarily uses the logarithmic estimate of the number of rows in the relation. Query optimization algorithms like IBM System R algorithm assigns

Re: [sqlite] Query regarding " Sqlite database opening error "

Rohit Kaushal wrote: > My application starts properly and works fine for about 15 mins. During > this time, the database is created/opened, updated and then closed. The > frequency of this update is about 1 sec. Then suddenly the database becomes > inaccessible with sqlite3_open_v2 returning error

Re: [sqlite] Query regarding " Sqlite database opening error "

On 29 Sep 2014, at 2:19pm, Rohit Kaushal wrote: > I would really appreciate some pointers on this from your side as I am kind > of stuck on this right now. You might want to turn on extended error codes and do it again.

[sqlite] Query regarding " Sqlite database opening error "

Dear Sir/Mam While working with SQLITE, we are facing an issue which is as under: My application starts properly and works fine for about 15 mins. During this time, the database is created/opened, updated and then closed. The frequency of this update is about 1 sec. Then suddenly the database

Re: [sqlite] query REAL without trailing decimal and zero?

Richard Hipp wrote on Monday, September 15, 2014 3:16 PM > On Mon, Sep 15, 2014 at 3:02 PM, Nelson, Erik - 2 < > erik.l.nel...@bankofamerica.com> wrote: > > > When I query a field defined with type 'real', I get '.0' appended to > > the results for whole numbers. For example if the value in the

Re: [sqlite] query REAL without trailing decimal and zero?

On Mon, Sep 15, 2014 at 3:02 PM, Nelson, Erik - 2 < erik.l.nel...@bankofamerica.com> wrote: > When I query a field defined with type 'real', I get '.0' appended to the > results for whole numbers. For example if the value in the field is 1, it > appears as 1.0 in the query results. > > Is there

[sqlite] query REAL without trailing decimal and zero?

When I query a field defined with type 'real', I get '.0' appended to the results for whole numbers. For example if the value in the field is 1, it appears as 1.0 in the query results. Is there some way for me to change this? I poked around in the code and it appeared that the format string

Re: [sqlite] Query help

> There are two ways to rewrite this query, with a correlated subquery: > > SELECT * > FROM table_a AS x > WHERE NOT EXISTS (SELECT 1 >FROM table_b AS y >WHERE x.id = y.id > AND x.col = y.col) > > or with an outer join: > >

Re: [sqlite] Query help

Joseph L. Casale wrote: > SELECT x.id, x.col > FROM table_a x > EXCEPT > SELECT y.id, y.col > FROM table_b y > JOIN table_a . This query is not complete, but as far as I can tell, it is intended to return table_a rows that do not have a matching table_b row. Is this correct? > now

[sqlite] Query help

I have a query I am trying to rewrite as efficient as possible and not clear. SELECT x.id, x.col FROM table_a x EXCEPT SELECT y.id, y.col FROM table_b y JOIN table_a . The right hand part of the except performs several joins and already duplicates the entire query on the left hand

[sqlite] Fwd: Passing parameter to SQlite query

Hi SQlite Support Team, I have queries to generate csv file written in a export.sql file. I'm calling export.sql from a batch file. One of the queries in export.sql has where clause , to which i have to send a value from batch file. Which syntax is used for this functionality. I need to send

[sqlite] Passing parameter to SQlite query

Hi SQlite Support Team, I have queries to generate csv file written in a export.sql file. I'm calling export.sql from a batch file. One of the queries in export.sql has where clause , to which i have to send a value from batch file. Which syntax is used for this functionality. I need to send

Re: [sqlite] Query help

> On 8 Jul 2014, at 9:01pm, Paul Sanderson wrote: > > 0, microsoft, mac > 1, oracle, mac > 2, oracle, pc SELECT t1.recno, t2.name, t3.name FROM t1 JOIN t2 ON t2.a = t1.a JOIN t3 ON t3.b = t1.b ORDER BY t1.recno If you have lots of data on your tables this

Re: [sqlite] Query help

On 7/8/2014 4:01 PM, Paul Sanderson wrote: i have three tables create table t1 (recno int, a int, b int) create table t2 (a int, name text) create table t3 (b int, name text) I want to create a query that lists all rows in t1 but rather than the integers a and b I want to display the

[sqlite] Query help

I suspect this is easy i have three tables create table t1 (recno int, a int, b int) create table t2 (a int, name text) create table t3 (b int, name text) I want to create a query that lists all rows in t1 but rather than the integers a and b I want to display the associated names from t2 and

[sqlite] SQLITE query not working

Hi All, I encounter problem with the below SQL (it does not turn data from my database): SELECT l.link_id, l.cat, l.bi_direction, l.frm_coord_id, l.to_coord_id, r.name, l.start_jtn_elev, l.end_jtn_elev, r.id, r.code, r.prefix FROM Links l, Links_Index x, Roads r WHERE l.link_id = x.link_id AND

Re: [sqlite] Query Flattening / Query Optimization / Correlated Subquery Substitution

Hello, thank you for your reply, that works for me. To give a context and a background; my comment was implying a change of design by adding an abstraction layer between the data and the representation, a change of raw-data storage with the support of a middle-man-linker e.g async data events,

Re: [sqlite] Query Flattening / Query Optimization / Correlated Subquery Substitution

On Monday, 24 February, 2014 21:53, mm.w <0xcafef...@gmail.com> said: >I don't want to be annoying but why nowadays people are >sub-abusing-sub-selecting instead of using JOINs? moreover, that is in >most cases faster (a lot) and certainly more Human Readable. Neither JOIN nor LEFT JOIN will

Re: [sqlite] Query Flattening / Query Optimization

On 25 Feb 2014, at 4:31am, Keith Medcalf wrote: > does generate a plan with only one execution of each correlated subquery, but > does not give me access to the intermediate results You might experiment with creating a view for the subquery instead of for the query as a

Re: [sqlite] Query Flattening / Query Optimization / Correlated Subquery Substitution

Hello, I don't want to be annoying but why nowadays people are sub-abusing-sub-selecting instead of using JOINs? moreover, that is in most cases faster (a lot) and certainly more Human Readable. Best Regards On Mon, Feb 24, 2014 at 8:34 PM, Keith Medcalf wrote: > >

[sqlite] Query Flattening / Query Optimization / Correlated Subquery Substitution

Previous send munged. If it managed to make it properly, my apologies for posting the same message twice. Using the following schema: CREATE TABLE TKRawData ( EmpNo text collate nocase not null, CustNo integer not null, JobNo integer not null, RawYear integer not null, RawMonth integer

[sqlite] Query Flattening / Query Optimization

Using the following schema: CREATE TABLE TKRawData ( EmpNo text collate nocase not null, CustNo integer not null, JobNo integer not null, RawYear integer not null, RawMonth integer not null, RawDays real not null, primary key (EmpNo, JobNo, CustNo, RawYear, RawMonth) ); CREATE TABLE

Re: [sqlite] Query executes in sqlite manager but not sqlite database in android

Features are not available before they are introduced. For example, CTE's will not work with 3.6.22. The "ignore cruft from brain-dead SQL code generators" feature was not added to the parser until after 3.6.22. select * from A JOIN B ON a = b is mere syntactic sugar for select * from A,B

Re: [sqlite] Query executes in sqlite manager but not sqlite database in android

Hello I have used  #sqlite3 --version at command line and got 3.6.22 as the version Does that mean the type of query i am trying wont work? On Sunday, January 26, 2014 8:57 AM, big stone [via SQLite] wrote: It seems Android use a pretty outdated

Re: [sqlite] Query executes in sqlite manager but not sqlite database in android

It seems Android use a pretty outdated SQlite motor : SQlite 3.7.1 = march 20th, 2012 http://stackoverflow.com/questions/2421189/version-of-sqlite-used-in-android SQLite 3.7.11: 19-4.4-KitKat 18-4.3-Jelly Bean 17-4.2-Jelly Bean 16-4.1-Jelly Bean SQLite 3.7.4: 15-4.0.3-Ice

Re: [sqlite] Query executes in sqlite manager but not sqlite database in android

Hi Kamulegs, Your SQLiteManager includes a version of SQLite >=3.7.16 , and your android application does not. Indeed the syntax " (b JOIN c ON b.id = c.id)" is only accepted without this errror after 3.7.16. ==> If you can rewrite your syntax without these parenthesis (like below), all should

[sqlite] Query executes in sqlite manager but not sqlite database in android

Hello Community Hope to get some pointers here because i have a hit a snag! I have 4 tables. Meters:_id, SerialNumber Tenants: _id, FirstName. TenantsMeters: _id,Tenant_id,Meter_id, (basically junction table for linking many to many relationship btn tenants and meters) MeterReading: _id,

Re: [sqlite] query optimization

Thanks alot RSmith. On Mon, Nov 18, 2013 at 6:04 PM, d b wrote: > Hi Igor/Keith, > > I tried with both queries. I expect to delete all rows belongs to key 1. > But not deleted. Am I missing something while writing queries? > > delete from emp where key = 1 and (name='' or

Re: [sqlite] query optimization

Thanks RSmith. It works. But, I am looking for single query for prepared statements. That's the actual struggle for me. Ok, but you give code examples that has nothing to do with prepared statements. Giving this one last push, I iwll try to ignore all you have said and simply show the best

Re: [sqlite] query optimization

On Mon, 18 Nov 2013 14:08:28 +0200, RSmith wrote: >Well this is the reason for my initial misunderstanding - which I then thought >I had wrong, but either you have it wrong too... or I >had it right in the first place. Ok, less cryptically now: > >It all depends on whether

Re: [sqlite] query optimization

Hi Igor/Keith, I tried with both queries. I expect to delete all rows belongs to key 1. But not deleted. Am I missing something while writing queries? delete from emp where key = 1 and (name='' or name='f'); DELETE FROM emp WHERE key = 1 AND (name IS NULL OR name = 'f');

Re: [sqlite] query optimization

ay, 18 November, 2013 00:46 >To: sqlite-users@sqlite.org >Subject: [sqlite] query optimization > >Hi, > > > I am trying to make single query instead of below two queries. Can >somebody help? > > 1. delete from emp where key = '123'; > 2. delete from emp where key = '1

Re: [sqlite] query optimization

On 11/18/2013 7:24 AM, d b wrote: bool delete_emp(int key, string name = "") { string query = ???; if(name.length() > 0) { //needs to delete specific row. by unique key. } else { //needs to delete rows belongs to

Re: [sqlite] query optimization

Thanks RSmith. It works. But, I am looking for single query for prepared statements. That's the actual struggle for me. On Mon, Nov 18, 2013 at 4:24 PM, d b wrote: > Hi RSmith, > > Thanks. Still, I could not delete with single query. > > > create table if not exists

Re: [sqlite] query optimization

Thanks, this explanation makes it easier to understand what you are tryingto achieve. I do not see any binding in your code, so let us assume you are not binding anything and just executing the query, this rework of your code should be the easiest: bool delete_emp(int key, string name = "")

Re: [sqlite] query optimization

Hi RSmith, Thanks. Still, I could not delete with single query. create table if not exists emp(key integer not null, name text not null , personaldata text not null, unique(key, name)); insert into emp (key, name, personaldata) values(1, 'a', 'z'); insert into emp (key, name, personaldata)

Re: [sqlite] query optimization

Well this is the reason for my initial misunderstanding - which I then thought I had wrong, but either you have it wrong too... or I had it right in the first place. Ok, less cryptically now: It all depends on whether he has a Column called "name" that might be Null, or whether he has a

Re: [sqlite] query optimization

On Mon, 18 Nov 2013 13:04:31 +0200, RSmith wrote: >Oops, misprint... > >name won't be null of course, the parameter needs to be null, kindly replace >the queries offered like this: > > delete from emp where ( key = ?1 ) AND (( ?2 IS NULL ) OR ( name = ?2 )); > >or in

Re: [sqlite] query optimization

Oops, misprint... name won't be null of course, the parameter needs to be null, kindly replace the queries offered like this: delete from emp where ( key = ?1 ) AND (( ?2 IS NULL ) OR ( name = ?2 )); or in the second form: delete from emp where ( key = ?1 ) AND (( ?2 = '' ) OR (

Re: [sqlite] query optimization

I might be missing something extraordinarily obvious... but I cannot understand the use case for this logic you have. My first response was to just use "delete from emp where key=123" and be done with it, who cares what the name is, right? But then it dawned on me that you may for some reason

Re: [sqlite] query optimization

Hi Luis, Those are parameters. This is the query after replacing with ?1 and ?2. delete from emp where key = '123' and (case when name = 'abc' is null THEN 1 else name = 'abc' end); It covered "delete from emp where key = '123' and name = 'abc';" but not other query. I tried with "select

Re: [sqlite] query optimization

Assuming you are using parameters you may use something like: DELETE FROM emp WHERE key=?1 AND CASE ?2 IS NULL THEN 1 ELSE name=?2; BR 2013/11/18 d b > Hi, > > > I am trying to make single query instead of below two queries. Can > somebody help? > > 1. delete from

Re: [sqlite] query optimization

d b wrote: > 1. delete from emp where key = '123'; > 2. delete from emp where key = '123' and name = 'abc'; > > if Key available, execute 1st query. if key and name available, execute 2nd > query. What do you mean with "available"? Regards, Clemens

[sqlite] query optimization

Hi, I am trying to make single query instead of below two queries. Can somebody help? 1. delete from emp where key = '123'; 2. delete from emp where key = '123' and name = 'abc'; if Key available, execute 1st query. if key and name available, execute 2nd query. Is it possible to write in

Re: [sqlite] Query / schema optimization procedure?

Thanks alot, I will read through your email in detail later and think it through, but want to send a quick reply right now. About complexity: I see that it looks overly complex, especially when using table names like 't1' instead of names which makes sense in the real world. I understand

Re: [sqlite] Query / schema optimization procedure?

On 7 Nov 2013, at 10:52am, Daniel Polski wrote: > I would be happy to get advice on how to think / what to look for when trying > to optimize a query, views, adding indexes, optimizing schema design and so > on. > > So far I've figured out that I could add appropriate

[sqlite] Query / schema optimization procedure?

Hello, I have an SQL query which fetches the requested data from the database, but is using too much resources when doing so. I would be happy to get advice on how to think / what to look for when trying to optimize a query, views, adding indexes, optimizing schema design and so on. So far

Re: [sqlite] Query "select date(\"now\")" show EPOCH time on ARM- Coertex A15 board

On Sat, Oct 19, 2013 at 5:32 AM, Drake Wilson wrote: > Quoth jitendar kumar , on 2013-10-19 14:48:46 +0530: > > but the same compiled with ARM - Cortex A15 cross compiler and the query > > executed on arm board it gives the output of EPOCH time. i

Re: [sqlite] Query "select date(\"now\")" show EPOCH time on ARM- Coertex A15 board

Quoth jitendar kumar , on 2013-10-19 14:48:46 +0530: > but the same compiled with ARM - Cortex A15 cross compiler and the query > executed on arm board it gives the output of EPOCH time. i guess the > function > gettimeofday() fails to add the time to the default EPOCH time.

[sqlite] Query "select date(\"now\")" show EPOCH time on ARM- Coertex A15 board

Dear SQLITE users, I am facing issue with sqlite3 on arm board. when compiled with gcc and used on X86 PC , the query "select date(\"now\")" shows correct date. but the same compiled with ARM - Cortex A15 cross compiler and the query executed on arm board it gives the output of EPOCH time. i

Re: [sqlite] Query preperation time does not scale linearly with growth of no. of tables

From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Clemens Ladisch [clem...@ladisch.de] Sent: Wednesday, September 11, 2013 18:57 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Query preperation time does not scale linearly with growth of no. of tables

Re: [sqlite] Query preperation time does not scale linearly with growth of no. of tables

On 12 sep. 2013, at 07:20, "James K. Lowden" > wrote: On Tue, 10 Sep 2013 12:58:21 + Harmen de Jong - CoachR Group B.V. > wrote: I think the way I wrote our timings were not that clear,

Re: [sqlite] Query preperation time does not scale linearly with growth of no. of tables

On Tue, 10 Sep 2013 12:58:21 + Harmen de Jong - CoachR Group B.V. wrote: > I think the way I wrote our timings were not that clear, since they > are definately exponentially. The numbers from my previous post refer > to the multiplier between the test cases. Just to make

Re: [sqlite] Query preperation time does not scale linearly with growth of no. of tables

Harmen de Jong - CoachR Group B.V. wrote: > http://www.coachrdevelopment.com/share/callstack_tree.html > > This shows most time is spend on sqlite3CodeRowTriggerDirect. I'd guess the actual culprit is the loop in getRowTrigger (which does not show up because it is inlined): /* It may be that

Re: [sqlite] Query preperation time does not scale linearly with growth of no. of tables

To get rid of the question of WHERE exactly the time is consumed, we did some profiling on the application that run the query (using the 1 tables test DB). As a result you will find an overview of time consumed per function (shown as percentage of the total time) at this link:

Re: [sqlite] Query preperation time does not scale linearly with growth of no. of tables

On 10 sep. 2013, at 21:24, "E.Pasma" > wrote: My suppositions that the time was spent in the execute step and that this has been fixed in the new release appeared both wrong. Thus I may be wrong again but I think to have an explanation now. It is

Re: [sqlite] Query preperation time does not scale linearly with growth of no. of tables

On 10 sep. 2013, at 21:24, "E.Pasma" wrote: > My suppositions that the time was spent in the execute step and that this has > been fixed in the new release appeared both wrong. Thus I may be wrong again > but I think to have an explanation now. > It is as Simon guesses

Re: [sqlite] Query preperation time does not scale linearly with growth of no. of tables

On 10 sep. 2013, at 21:24, "E.Pasma" wrote: > Op 10 sep 2013, om 19:48 heeft Simon Slavin het volgende geschreven: > >> >> On 10 Sep 2013, at 4:15pm, Harmen de Jong - CoachR Group B.V. >> wrote: >> >>> That is something we suspected too. We already

Re: [sqlite] Query preperation time does not scale linearly with growth of no. of tables

Op 10 sep 2013, om 19:48 heeft Simon Slavin het volgende geschreven: On 10 Sep 2013, at 4:15pm, Harmen de Jong - CoachR Group B.V. wrote: That is something we suspected too. We already made some tests where we timed the time needed for all memory allocations executed

Re: [sqlite] Query preperation time does not scale linearly with growth of no. of tables

On 10 Sep 2013, at 4:15pm, Harmen de Jong - CoachR Group B.V. wrote: > That is something we suspected too. We already made some tests where we timed > the time needed for all memory allocations executed in the entire operation. > In total for the 1 tables test this was

Re: [sqlite] Query preperation time does not scale linearly with growth of no. of tables

On 10 sep. 2013, at 16:44, "E.Pasma" wrote: > Op 10 sep 2013, om 16:36 heeft Harmen de Jong - CoachR Group B.V. het > volgende geschreven: > >> On 10 sep. 2013, at 16:16, "E.Pasma" wrote: >> >>> Op 10 sep 2013, om 11:37 heeft Harmen de Jong - CoachR

Re: [sqlite] Query preperation time does not scale linearly with growth of no. of tables

Op 10 sep 2013, om 16:36 heeft Harmen de Jong - CoachR Group B.V. het volgende geschreven: On 10 sep. 2013, at 16:16, "E.Pasma" wrote: Op 10 sep 2013, om 11:37 heeft Harmen de Jong - CoachR Group B.V. het volgende geschreven: I included 5 databases that we used for

<    1   2   3   4   5   6   7   8   9   10   >