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

Laura, On Thu, Sep 8, 2016 at 5:22 AM, Laura BERGOENS wrote: > Hi everyone, > > So I've put indexes myself on the most used tables in my program, then > ANALYZE the db, and now it flies. > The automatic creation of index probably took a lot of time, and it affects >

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

Hi everyone, So I've put indexes myself on the most used tables in my program, then ANALYZE the db, and now it flies. The automatic creation of index probably took a lot of time, and it affects even more performance when it's always same queries on same tables that are repeated over and over

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

On 8 Sep 2016, at 7:27am, Laura BERGOENS wrote: > I took notes of everything you guys said, and I'll spend my morning doing > this : > > Running ANALYZE once per DB I'mUsing, Create indexes myself and contact DB > Browser to share them the thing. Do those two steps

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

sers [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On > Behalf Of Chris Locke > Sent: Wednesday, September 7, 2016 12:12 PM > To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> > Subject: Re: [sqlite] Query time execution difference between my > application

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

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 (

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

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

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

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

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

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 are creating. Then the

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

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

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

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

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

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

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

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

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

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

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.

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.

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

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

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

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...

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

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

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 tool SQLiteBrowser is using it as well. I