Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

>First of all, I'll check all the pragmas and stuff, plus the version of > SQliteBrowser (former DB Browser for SQlite indeed) I'm using, etc. Just to confirm (as it seems to be overlooked) that SQLite Browser (actually now called DB Browser for SQLite, rather than formerly...) is a 3rd party tool

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

Ok I didn't know abount ANALYZE existence, sorry for that. I just run ANALYZE then the query again in sqlite3.exe and this time it took like half a second. Should I close and re open the DB, cause maybe this result was influenced with some cache system or something? Plus, I ran PRAGMA compile_opt

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

ists.sqlite.org] On Behalf Of Laura BERGOENS Sent: Wednesday, September 07, 2016 1:09 PM To: SQLite mailing list Subject: Re: [sqlite] Query time execution difference between my application and SQLiteBrowser quote:"Now, you need to create a primary/foreign keys and indexes on the tables you ar

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

I haven't gone through the previous emails to see whether anyone has proposed this, but it seems possible to me that the GUI tool (oriented as it is to interaction) is not waiting for the query to finish before displaying the first results. On Wednesday, September 7, 2016 7:21 AM, Laura BE

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

On 7 Sep 2016, at 4:36pm, Laura BERGOENS wrote: > I've put ANALYZE in my code before running this SELECT query, I have no > change, and now on sqlite3.exe it runs kind of fast every time, even if i > close and re open the DB, without ANALYZE statement. Good. If should now run quickly in your o

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

quote : "Is there a reason why you can't create a copy of the database and work with a copy?" I never said I couldn't, and if I misled you I'm sorry. Of course I can play with the DB, since I'm in a dev environment, and I'm open to any alteration on the tables. I conceed I'm not giving away a lot

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

Hi, Laura, On Wed, Sep 7, 2016 at 12:25 PM, Laura BERGOENS wrote: > Hi Dominique, > > I guess it's not, but I'm not quite sure on what I can share, I'm a newbie > in a professional environment. I would be more confortable if I ask my boss > first. In the meantime, all I can say is that there are

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

Hi Dominique, I guess it's not, but I'm not quite sure on what I can share, I'm a newbie in a professional environment. I would be more confortable if I ask my boss first. In the meantime, all I can say is that there are no primary keys nor foreign keys in the table, and no indexes at all. I activ

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

On Wed, Sep 7, 2016 at 6:00 PM, Laura BERGOENS wrote: > Then I create real tables in the in-memory clone that contain the content > of the views : INSERT INTO tableA SELECT * from viewA > What matters is how you create the tables, and in particular what primary key and indexes you use on them.

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

Hi Igor, I'm working on providing those data, meanwhile I have to emphasize on how those tables are created, maybe there is something here : The idea is that i run the application using a given database. I want this database to be untouched as for its data, so i'm making a clone of it in memory.

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

Hi, Laura, On Wed, Sep 7, 2016 at 10:48 AM, Laura BERGOENS wrote: > Note : I had some real queries that use to take 100 seconds to execute, and > I optimized them myself. > It looks like this : > > I have 4 tables: tableA, tableLink, tableC and tableD > tableA, Link and C have no more than 10k r

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

I've put ANALYZE in my code before running this SELECT query, I have no change, and now on sqlite3.exe it runs kind of fast every time, even if i close and re open the DB, without ANALYZE statement. 2016-09-07 17:15 GMT+02:00 Laura BERGOENS : > Ok I didn't know abount ANALYZE existence, sorry for

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

On 7 Sep 2016, at 3:48pm, Laura BERGOENS wrote: > This query takes 100 seconds approx. Once your tables have some convincing data in (does not need to be final data, just something useful to see how the values are distributed), run "ANALYZE", just once. It might speed up later SELECTs. It m

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

Note : I had some real queries that use to take 100 seconds to execute, and I optimized them myself. It looks like this : I have 4 tables: tableA, tableLink, tableC and tableD tableA, Link and C have no more than 10k rows in it, and tableD around 50k (which is not big at all right?) The query goe

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

Hi Mr. Smith, Quote :"(though, SQLite successive iterations usually become faster, not slower, apart from compile-time options that may be different)." That's what concerns me as well, but since I'm a recent user of SQlite type database and sqlite3 I prefer blaming me for those differences. Sinc

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

On 2016/09/07 4:20 PM, Laura BERGOENS wrote: Hi Mr. Slavin, As for why the query takes so long, I do know the answer ! Long story short, my application does a lot of calculation and things, I don't want to get into the details here, but queries are built and auto-generated piece by piece. There

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

On 2016/09/07 2:53 PM, Laura BERGOENS wrote: Hello everyone, I tried to get some answers using the mailing list archives, but you guys have been communicating so much on this, it's hard to run through everything that was written ;) Basically, I'm using sqlite3 in my C application. I believe t

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

Hi Mr. Slavin, As for why the query takes so long, I do know the answer ! Long story short, my application does a lot of calculation and things, I don't want to get into the details here, but queries are built and auto-generated piece by piece. Therefore, sometimes the queries aren't optimized at

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

On 9/7/16, Simon Slavin wrote: > > On 7 Sep 2016, at 3:10pm, Laura BERGOENS wrote: > >> I guess I don't have further question regarding my issue, since I have the >> same time execution as with the sqlite3.exe tool, so there are no problems >> with my pragmas or whatsoever. > > Nevertheless, 100

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

On 7 Sep 2016, at 3:10pm, Laura BERGOENS wrote: > I guess I don't have further question regarding my issue, since I have the > same time execution as with the sqlite3.exe tool, so there are no problems > with my pragmas or whatsoever. Nevertheless, 100 seconds is a very long time for a query, a

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

Hi Mr. Hipp, First of all thanks for replying. As you recommend, I downloaded the sqlite3 executable (I'm on Debian). Just out of curiosity, I've ran both the "SELECT sqlite_source_id()" query and a long query i have on 3 systems, SQliteBrowser, sqlite3.exe and my app, here is what i get, it's... i

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

On 9/7/16, Laura BERGOENS wrote: > Hello everyone, > > I tried to get some answers using the mailing list archives, but you guys > have been communicating so much on this, it's hard to run through > everything that was written ;) > > Basically, I'm using sqlite3 in my C application. I believe the

Re: [sqlite] Query question: order by ascending, return the two largest values in ascending order

Clemens Regards, 2. Sorting the entries before LIMIT is applied. 1. Sorting the entries before group_concat() is applied; or Chris Locke wrote: > Whats the benefit of getting a sorted query and then sorting that query > again? > > On Tue, Jul 12, 2016 at 12:45 AM, Stephen Chrzanowski > wrote:

Re: [sqlite] Query question: order by ascending, return the two largest values in ascending order

Whats the benefit of getting a sorted query and then sorting that query again? On Tue, Jul 12, 2016 at 12:45 AM, Stephen Chrzanowski wrote: > Simons + My answer; > > select * from (SELECT date_time_stamp FROM general ORDER BY date_time_stamp > DESC LIMIT 2) a order by date_time_stamp; > > On Mon

Re: [sqlite] Query question: order by ascending, return the two largest values in ascending order

Thanks for all of the responses. Duplicates are OK, the date time stamps are part of a log file that I am trying to develop a query for. I couldn't get the LIMIT/OFFSET part of the query right after several attempts so I thought I'd ask the experts here. I appreciate your replies and suggestions.

Re: [sqlite] Query question: order by ascending, return the two largest values in ascending order

SELECT DISTINCT date_time_stamp FROM general ORDER BY date_time_stamp DESC LIMIT 2 isn't it simply to use DISTINCT? On Mon, Jul 11, 2016 at 4:25 PM, Keith Christian wrote: > A table has a column of dates and times that look like this: > > 2015-10-02 07:55:02 > 2015-10-02 07:55:02 > 2015-10-02 1

Re: [sqlite] Query question: order by ascending, return the two largest values in ascending order

On 2016/07/12 1:25 AM, Keith Christian wrote: A table has a column of dates and times that look like this: 2015-10-02 07:55:02 2015-10-02 07:55:02 2015-10-02 10:00:03 2015-10-02 10:05:02 2015-10-02 10:10:02 Schema: CREATE TABLE general ( id integer primary key autoincrement, server text, dat

Re: [sqlite] Query question: order by ascending, return the two largest values in ascending order

Simons + My answer; select * from (SELECT date_time_stamp FROM general ORDER BY date_time_stamp DESC LIMIT 2) a order by date_time_stamp; On Mon, Jul 11, 2016 at 7:33 PM, Simon Slavin wrote: > > On 12 Jul 2016, at 12:25am, Keith Christian > wrote: > > > A table has a column of dates and times

Re: [sqlite] Query question: order by ascending, return the two largest values in ascending order

On 12 Jul 2016, at 12:25am, Keith Christian wrote: > A table has a column of dates and times that look like this: > > 2015-10-02 07:55:02 > 2015-10-02 07:55:02 > 2015-10-02 10:00:03 > 2015-10-02 10:05:02 > 2015-10-02 10:10:02 > > > Schema: > CREATE TABLE general ( id integer primary key autoi

Re: [sqlite] Query Flattener vs. result-set column names

Simon Slavin wrote: > On 7 Jul 2016, at 3:37pm, Josef Kučera wrote: >> Imagine a query like "SELECT A.F1, B1.F FROM A JOIN (SELECT F2 AS F FROM B >> WHERE F3=0) B1". > > your natural JOIN is a little dangerous since it can collapse if you > change column names or definitions. A natural join woul

Re: [sqlite] Query Flattener vs. result-set column names

-boun...@mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Josef Kucera Gesendet: Donnerstag, 07. Juli 2016 17:42 An: SQLite mailing list Betreff: Re: [sqlite] Query Flattener vs. result-set column names - Original Message - From: "Simon S

Re: [sqlite] Query Flattener vs. result-set column names

- Original Message - From: "Simon Slavin" To: "SQLite mailing list" Sent: Thursday, July 07, 2016 5:11 PM Subject: Re: [sqlite] Query Flattener vs. result-set column names On 7 Jul 2016, at 3:37pm, Josef Kučera wrote: Imagine a query like "SELECT A.F1,

Re: [sqlite] Query Flattener vs. result-set column names

On 7 Jul 2016, at 3:37pm, Josef Kučera wrote: > Imagine a query like "SELECT A.F1, B1.F FROM A JOIN (SELECT F2 AS F FROM B > WHERE F3=0) B1". If the query flattener is active the result-set has columns > "A.F1" and "B1.F". If it is disabled the result-set columns are "F1" and "F". > The "shor

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

- Original Message - From: "Josef Kucera" To: "General Discussion of SQLite Database" Sent: Monday, December 15, 2014 5:37 PM Subject: Re: [sqlite] Query Planner for Virtual Tables: link table evaluation & transitive property of constraints not used - Orig

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

- Original Message - From: "Nico Williams" To: "General Discussion of SQLite Database" Sent: Monday, December 15, 2014 5:16 PM Subject: Re: [sqlite] Query Planner for Virtual Tables: link table evaluation & transitive property of constraints not used On Mo

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 tha

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

- Original Message - From: "Hick Gunter" To: "'General Discussion of SQLite Database'" Sent: Monday, December 15, 2014 2:40 PM Subject: Re: [sqlite] Query Planner for Virtual Tables: link table evaluation & transitive property of constraints not use

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 quer

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 constant

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. Also, please che

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 f

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 s

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: > > SELECT

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 I

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 command will execute far more quic

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 associated

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, but

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 wor

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 whole: CREATE VIEW ra

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: > > Previous send munged. If i

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 w

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 SQlite motor : SQlite 3.7.1 = march 20th,

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 C

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 b

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 name='f'); > DELETE

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 he has a Column calle

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

DELETE FROM emp WHERE key = '123' AND (name IS NULL OR name = 'abc'); assuming "available" means "is not null ... >-Original Message- >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >boun...@sqlite.org] On Behalf Of d b >Sent: Monday, 18 November, 2013 00:46 >T

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 key

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 emp(key integer not null

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) val

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 paramete

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 the second form: > > d

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 ( nam

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 (ca

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 emp where key = '123';

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 _

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 tha

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 indexes since the > 'ex

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 guess the > > function > > gettimeofday() fails t

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. That sounds like it's

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" mailto:jklow...@schemamania.org>> wrote: On Tue, 10 Sep 2013 12:58:21 + Harmen de Jong - CoachR Group B.V. mailto:har...@coachr.com>> wrote: I think the way I wrote our timings were not that clear, since they are definately exponentially. The num

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 it clear, here > foll

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 th

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: http://www.c

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

On 10 sep. 2013, at 21:24, "E.Pasma" mailto:pasm...@concepts.nl>> 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 that

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 that a list of lists is b

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 made some tests where we >>> timed the tim

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 in the entire opera

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 somewhere around 25

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 Group B.V. het >>> volgende geschreven: >>

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 testing in this link:

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

> >> het volgende geschreven: > >>> I included 5 databases that we used for testing in this link: > http://wikisend.com/download/570088/test_databases.zip > >>> > >>> The query performed on these databases is: > >>> delete from A where id=1; > >> > >> I could not resist trying this but the tables

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

Sent from my iPad On 10 sep. 2013, at 17:04, "Keith Medcalf" wrote: >>> No, it is all about preparing, so there is no need to insert data. >>> When we perform the query "delete from A where id=1;" on the >>> databases from the zip file, we get the following timings: >>> >>> 500 tables - 10 m

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

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 testing in this link: >> http://wikisend.com/download/570088/test_databases.zip >> >> The query performed on the

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

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 testing in this link: http://wikisend.com/download/570088/test_databases.zip The query performed on these databases is: delete from A where id=1; I could not res

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

On 10 sep. 2013, at 15:41, "Igor Tandetnik" wrote: > Not exponential - polynomial. Between 500 and 1 the size of input > increases x20, so the time increase of x400 would be consistent with a > quadratic algorithm. Your observed measurements are even better than that. You're right, thanks

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

On 9/10/2013 5:37 AM, Harmen de Jong - CoachR Group B.V. wrote: The time factors it takes on each database are as follows (where the time needed for the 500 tables was taken as starting point to calculate the other factors): 500 tables - 1x 1000 tables - 2.5x 5000 tables - 29x 1 tables - 9

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

On 10 sep. 2013, at 14:43, "E.Pasma" wrote: > The timings do not look truly exponential to me. It looks more as if there is > a graduated charge (NL: staffeltoeslag) on the time per table. For instance: > table 1 - 500 - 2 msec/table > table 501 - 1.000 - 3 msec/table > table 1001 - 5.000 - 5 m

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

Op 10 sep 2013, om 11:37 heeft Harmen de Jong - CoachR Group B.V. het volgende geschreven: On 9 sep. 2013, at 22:11, "E.Pasma" wrote: Ha, I did not mean the length of the names but the length of the hash table (NL: klutstabel), That is the number of buckets over which the hash values are

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

On 10 sep. 2013, at 11:37, "Harmen de Jong - CoachR Group B.V." wrote: > As you can see this is an exponential growth in time it takes to execte the > query. So far we're missing the point of why this growth should be > exponential. We tried some further debugging and it seems that SQLite spen

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

On 9 sep. 2013, at 22:11, "E.Pasma" wrote: > Ha, I did not mean the length of the names but the length of the hash table > (NL: klutstabel), That is the number of buckets over which the hash values > are distributed. I looked some further in the code and now believe that this > is 1024. That is

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

Op 9 sep 2013, om 10:06 heeft Harmen de Jong - CoachR Group B.V. het volgende geschreven: Our table and column names are not too long either as E.Pasma suggests. Ha, I did not mean the length of the names but the length of the hash table (NL: klutstabel), That is the number of buckets

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

On Fri, 6 Sep 2013 17:29:25 Harmen de Jong - CoachR Group B.V. wrote > [Explaining why there can be 10,000 tables referencing one table] ... users > can create highly customized research forms with varying number of columns, > varying field types and a lot of specific restrictions, which we stor

<    1   2   3   4   5   6   7   >