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 > even more performance when it's always same queries on same tables that are > repeated over and over again. > > ALl your advice was really precious, thanks a lot No problem at all! We are here to help, since we were in your boat at least once... ;-) Thank you. > > 2016-09-08 9:44 GMT+02:00 Simon Slavin : > >> >> 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 the other way around. ANALYZE not only analyses your >> tables, it also analyzes your indexes. So you need to make the indexes >> first, then do ANALYZE. >> >> Simon. >> ___ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > > > > -- > Laura BERGOENS > Technicienne supérieure en Informatique et étudiante à l'IMERIR de Perpignan > > *Institut Méditerranéen d'Étude etde Recherche en Informatique* > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 again. ALl your advice was really precious, thanks a lot 2016-09-08 9:44 GMT+02:00 Simon Slavin : > > 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 the other way around. ANALYZE not only analyses your > tables, it also analyzes your indexes. So you need to make the indexes > first, then do ANALYZE. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Laura BERGOENS Technicienne supérieure en Informatique et étudiante à l'IMERIR de Perpignan *Institut Méditerranéen d'Étude etde Recherche en Informatique* ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 the other way around. ANALYZE not only analyses your tables, it also analyzes your indexes. So you need to make the indexes first, then do ANALYZE. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query time execution difference between my application and SQLiteBrowser
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. (I tend to call the 3rd party tool SQLiteBrowser since it's the name of the package when you install it through apt-get) I'll come back to you guys with the results of this asap. Thanks again for sharing your time 2016-09-08 2:09 GMT+02:00 Bob McFarlane : > Please reply if you sent this. Thanks. > > > > > > -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On > Behalf Of Chris Locke > Sent: Wednesday, September 7, 2016 12:12 PM > To: SQLite mailing list > Subject: 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. > I can post your query to their issues board though ... its still in high > development, and its curious the version number irregularities... > https://github.com/sqlitebrowser/sqlitebrowser/issues > > > Thanks, > Chris > > > On Wed, Sep 7, 2016 at 4:04 PM, Simon Slavin wrote: > > > > > 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 might not. But a situation where you have many 'AND' > > clauses looking at different columns is exactly what ANALYZE is most > helpful for. > > > > And yes, a 50 kilorow table is not big by SQLite standards. I have > > tables with a thousand times that that yield answers to SELECT in 5ms. > > > > Simon. > > ___ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > TMGID:S1141121912621015 > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Laura BERGOENS Technicienne supérieure en Informatique et étudiante à l'IMERIR de Perpignan *Institut Méditerranéen d'Étude etde Recherche en Informatique* ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query time execution difference between my application and SQLiteBrowser
Please reply if you sent this. Thanks. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Chris Locke Sent: Wednesday, September 7, 2016 12:12 PM To: SQLite mailing list Subject: 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. I can post your query to their issues board though ... its still in high development, and its curious the version number irregularities... https://github.com/sqlitebrowser/sqlitebrowser/issues Thanks, Chris On Wed, Sep 7, 2016 at 4:04 PM, Simon Slavin wrote: > > 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 might not. But a situation where you have many 'AND' > clauses looking at different columns is exactly what ANALYZE is most helpful for. > > And yes, a 50 kilorow table is not big by SQLite standards. I have > tables with a thousand times that that yield answers to SELECT in 5ms. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users TMGID:S1141121912621015 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query time execution difference between my application and SQLiteBrowser
Please reply if you sent this. Thanks. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Laura BERGOENS Sent: Wednesday, September 7, 2016 11:15 AM To: SQLite mailing list Subject: 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_options for the 3 platforms I mentionned before and I tried to regroup the results in a libreOffice calc document, I don't know if that can help or if it's relevant 2016-09-07 17:04 GMT+02:00 Simon Slavin : > > 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 might not. But a situation where you have many 'AND' > clauses looking at different columns is exactly what ANALYZE is most helpful for. > > And yes, a 50 kilorow table is not big by SQLite standards. I have > tables with a thousand times that that yield answers to SELECT in 5ms. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Laura BERGOENS Technicienne supérieure en Informatique et étudiante à l'IMERIR de Perpignan *Institut Méditerranéen d'Étude etde Recherche en Informatique* ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users TMGID:S1141121912621017 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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. I can post your query to their issues board though ... its still in high development, and its curious the version number irregularities... https://github.com/sqlitebrowser/sqlitebrowser/issues Thanks, Chris On Wed, Sep 7, 2016 at 4:04 PM, Simon Slavin wrote: > > 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 might not. But > a situation where you have many 'AND' clauses looking at different columns > is exactly what ANALYZE is most helpful for. > > And yes, a 50 kilorow table is not big by SQLite standards. I have tables > with a thousand times that that yield answers to SELECT in 5ms. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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_options for the 3 platforms I mentionned before and I tried to regroup the results in a libreOffice calc document, I don't know if that can help or if it's relevant 2016-09-07 17:04 GMT+02:00 Simon Slavin : > > 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 might not. But > a situation where you have many 'AND' clauses looking at different columns > is exactly what ANALYZE is most helpful for. > > And yes, a 50 kilorow table is not big by SQLite standards. I have tables > with a thousand times that that yield answers to SELECT in 5ms. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Laura BERGOENS Technicienne supérieure en Informatique et étudiante à l'IMERIR de Perpignan *Institut Méditerranéen d'Étude etde Recherche en Informatique* ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query time execution difference between my application and SQLiteBrowser
Just a reminder that the automatic_index pragma does not create permanent indexes, it's for places where SQLite feels that "hey, if I take the time to create this temporary index then it'll save me more than that time during the rest of this one query." Those indexes get wiped at the end of the query. So in a bad case then you're creating a temporary index every time you run the query. There're some good notes on that at http://www.sqlite.org/optoverview.html#autoindex -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.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 are creating. Then the execution time will improve even more" I will probably try that tomorrow and I'll tell you how that goes, but I'm not sure if that will be very effective since i activated the PRAGMA to create indexes automatically. I'm guessing it has created some (hopefully). ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 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. Therefore, sometimes the queries aren't optimized at all. I solved that issue myself by touching up the queries a bit before executing them, and everything is fine now. I was concerned mainly because I figured that maybe some queries were a bit longer to execute as they should have (let's say 200 ms instead of 100 ms), and I'm running a lot of queries in the app (approx 1000 per seconds). I know now that there is nothing to worry about regarding my settings or pragmas choices, since I get the same execution time with the sqlite3 tool Thanks again for taking some time to answer 2016-09-07 16:14 GMT+02:00 Simon Slavin : > > 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, and we can > probably improve on it if you're willing to include your "SELECT" command > and your schema. It might be something as simple as creating one > additional index. > > You might also try executing "ANALYZE" in sqlite3.exe and see whether that > speeds things up. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Laura BERGOENS Technicienne supérieure en Informatique et étudiante à l'IMERIR de Perpignan *Institut Méditerranéen d'Étude etde Recherche en Informatique* ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 own program too. This is the improvement in speed we expected to see. The results of ANALYZE are stored in the database file. You only have to do it once. SQLite creates some hidden tables in the database file and saves the results there. Later commands with a "WHERE" or "ORDER BY" clause cause SQLite to take a look at those tables to find the best way to do the search or sort. So having done ANALYZE once, perhaps using sqlite3.exe, you do not need to include it in your own program. If will find the results that were saved in the database file. The only reason to do ANALYZE again is if you change your database schema (make/delete columns, TABLEs or INDEXes), or if the nature of your data changes (if you expand from 2 countries to 10 countries, or if you once had 50 managers and how have just 5 managers). If the 'chunkiness' of the values in a column does not change much, then there is no need to run ANALYZE again just because you added or deleted many rows. At most, some people include it in an end-of-year procedure. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 information, but when I do I feel like there is not much to say or too add. quote:"Now, you need to create a primary/foreign keys and indexes on the tables you are creating. Then the execution time will improve even more" I will probably try that tomorrow and I'll tell you how that goes, but I'm not sure if that will be very effective since i activated the PRAGMA to create indexes automatically. I'm guessing it has created some (hopefully). Anyway, thank you all for having the patience to read and reply. 2016-09-07 18:52 GMT+02:00 Igor Korot : > 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 no primary keys > nor > > foreign keys in the table, and no indexes at all. I activated the > > automatic_index PRAGMA. > > > > All tables look the same more or less : > > > > CREATE TABLE TABLEA (ID_A INTEGER,NUM_A INTEGER,NUM_GROUPE_A > INTEGER,TYPE_A > > VARCHAR(255),LIBELLE_A VARCHAR(255),NOM_A VARCHAR(255)); > > If you don't have keys nor indexes in any of the tables than the > query time will definitely be slower. > > Now let me ask you this: > Is there a reason why you can't create a copy of the database and work > with a copy? > Just open the Terminal and do: > > cp real_database.db my_database.db > > Then you can experiment with the database itself without screwing up > the real data. > Besides, that's how actual dev environment should be - you have access > to the development > database on which you do the experiments and then the DB Admin will > populate the schema > based on you analysis for production. > > Now, you need to create a primary/foreign keys and indexes on the > tables you are creating. > Then the execution time will improve even more. > > Try it and see. > > Then when you are ready ask the DB Admin to make those changes for > production database. > > Thank you. > > BTW, if its so scary, you can make the field name as field1, field2, > field3, etc. > That way the design of the database and the application will stay as > the company secret. > > Thank you. > > > > > 2016-09-07 18:13 GMT+02:00 Dominique Devienne : > > > >> On Wed, Sep 7, 2016 at 6:00 PM, Laura BERGOENS < > laura.bergo...@imerir.com> > >> 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. > >> > >> If you don't have indexes (or a PK) on the columns you're joining on, > >> it can't be as fast as it could be (and SQLite might end-up creating > >> those "indexes" on-the-fly, and recreate them on the next query, > etc...). > >> > >> So as advised, share your schema (see Richard's post), not your data, > >> and share your queries and explain query plan for your queries. > >> > >> You're schema is not confidential, is it? The data, sure. But the > schema??? > >> --DD > >> ___ > >> sqlite-users mailing list > >> sqlite-users@mailinglists.sqlite.org > >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >> > > > > > > > > -- > > Laura BERGOENS > > Technicienne supérieure en Informatique et étudiante à l'IMERIR de > Perpignan > > > > *Institut Méditerranéen d'Étude etde Recherche en Informatique* > > ___ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Laura BERGOENS Technicienne supérieure en Informatique et étudiante à l'IMERIR de Perpignan *Institut Méditerranéen d'Étude etde Recherche en Informatique* ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 no primary keys nor > foreign keys in the table, and no indexes at all. I activated the > automatic_index PRAGMA. > > All tables look the same more or less : > > CREATE TABLE TABLEA (ID_A INTEGER,NUM_A INTEGER,NUM_GROUPE_A INTEGER,TYPE_A > VARCHAR(255),LIBELLE_A VARCHAR(255),NOM_A VARCHAR(255)); If you don't have keys nor indexes in any of the tables than the query time will definitely be slower. Now let me ask you this: Is there a reason why you can't create a copy of the database and work with a copy? Just open the Terminal and do: cp real_database.db my_database.db Then you can experiment with the database itself without screwing up the real data. Besides, that's how actual dev environment should be - you have access to the development database on which you do the experiments and then the DB Admin will populate the schema based on you analysis for production. Now, you need to create a primary/foreign keys and indexes on the tables you are creating. Then the execution time will improve even more. Try it and see. Then when you are ready ask the DB Admin to make those changes for production database. Thank you. BTW, if its so scary, you can make the field name as field1, field2, field3, etc. That way the design of the database and the application will stay as the company secret. Thank you. > > 2016-09-07 18:13 GMT+02:00 Dominique Devienne : > >> 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. >> >> If you don't have indexes (or a PK) on the columns you're joining on, >> it can't be as fast as it could be (and SQLite might end-up creating >> those "indexes" on-the-fly, and recreate them on the next query, etc...). >> >> So as advised, share your schema (see Richard's post), not your data, >> and share your queries and explain query plan for your queries. >> >> You're schema is not confidential, is it? The data, sure. But the schema??? >> --DD >> ___ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > > > > -- > Laura BERGOENS > Technicienne supérieure en Informatique et étudiante à l'IMERIR de Perpignan > > *Institut Méditerranéen d'Étude etde Recherche en Informatique* > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 activated the automatic_index PRAGMA. All tables look the same more or less : CREATE TABLE TABLEA (ID_A INTEGER,NUM_A INTEGER,NUM_GROUPE_A INTEGER,TYPE_A VARCHAR(255),LIBELLE_A VARCHAR(255),NOM_A VARCHAR(255)); 2016-09-07 18:13 GMT+02:00 Dominique Devienne : > 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. > > If you don't have indexes (or a PK) on the columns you're joining on, > it can't be as fast as it could be (and SQLite might end-up creating > those "indexes" on-the-fly, and recreate them on the next query, etc...). > > So as advised, share your schema (see Richard's post), not your data, > and share your queries and explain query plan for your queries. > > You're schema is not confidential, is it? The data, sure. But the schema??? > --DD > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Laura BERGOENS Technicienne supérieure en Informatique et étudiante à l'IMERIR de Perpignan *Institut Méditerranéen d'Étude etde Recherche en Informatique* ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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. If you don't have indexes (or a PK) on the columns you're joining on, it can't be as fast as it could be (and SQLite might end-up creating those "indexes" on-the-fly, and recreate them on the next query, etc...). So as advised, share your schema (see Richard's post), not your data, and share your queries and explain query plan for your queries. You're schema is not confidential, is it? The data, sure. But the schema??? --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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. Then I create views, let's call them viewA, viewB, viewC and viewD with the statement CREATE VIEW etc... Then I create real tables in the in-memory clone that contain the content of the views : INSERT INTO tableA SELECT * from viewA This way I can work on real tables, without altering my original database. I'm trying to give as much intel as I can, and maybe the fact that I am using views a bit, and creating real tables thanks to those views, maybe there is a performance issue I'm not aware of or something like that. 2016-09-07 17:39 GMT+02:00 Igor Korot : > 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 rows in it, and tableD around > 50k > > (which is not big at all right?) > > The query goes like this : > > SELECT DISTINCT A1.idA, A1.column1, A1.column2 > > FROMtableA A1, > > tableA A2, > > tableLink link, > > tableC C1, > > tableD D1, > > tableD D2, > > WHERE C1.idA = A1.idA > > ANDC1.idD = D1.idD > > AND A1.idD = D1.idD > > AND A1.column2 = 'VALUE' > > AND A2.idA = link.id_item_1 > > AND A1.idA = link.id_item_2 > > AND D2.idD = A2.idD > > AND A2.idA = 100 > > > > This query takes 100 seconds approx. > > I don't know if that can help you in any way, but the tables have been > > created with a query like INSERT INTO SELECT * FROM A_View, so they have > > been created from a view. > > After populating the data, do create any indexes? > Also, I presume that all 4 tables are created like this, not just tableA, > right? > BTW, if you can show the schema (no data necessary) for those 4 tables, we > can > see if there is a way to improve. > > Thank you. > > > > As we can see, only columns from tableA A1 are selected, so most of the > > joins here can be replaced with something of the form : > > AND EXISTS (SELECT 1 FROM "test join") > > > > I managed to drastically reduce time execution on this query with the > > EXISTS trick, and now it has a normal time execution (below 300 ms for > > sure, can't tell you how much exactly) > > > > > > I've check the EXPLAIN QUERY PLAN of the original query, and I understood > > that I was scanning tables in nested loops, so that this can take some > time > > (in fact the product of the sizes of all the tables in the FROM clause > > right?) > > > > Here is what i can give you for now, now i'll do some tests that you've > > recommended earlier > > > > 2016-09-07 16:33 GMT+02:00 R Smith : > > > >> > >> 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. Therefore, sometimes the queries aren't > >>> optimized at all. > >>> I solved that issue myself by touching up the queries a bit before > >>> executing them, and everything is fine now. > >>> I was concerned mainly because I figured that maybe some queries were a > >>> bit > >>> longer to execute as they should have (let's say 200 ms instead of 100 > >>> ms), > >>> and I'm running a lot of queries in the app (approx 1000 per seconds). > >>> > >> > >> Magic goalposts... > >> > >> "A query" taking 100s is a VERY VERY different problem to 1000 queries > >> taking 100ms each. And to get technical, you shouldn't really need to > >> optimize the queries (apart from avoiding the obvious silliness), you > only > >> need to know how to ask for the data correctly. Optimization is the job > of > >> the query planner in the DB engine - it should get the best fastest > query > >> results possible as long as it has all the information (which is what > >> ANALYZE will do as others mentioned already) and as long as you provide > the > >> best Index for the job (which is something we might have some > suggestions > >> on if we know the schema and typical query is). > >> > >> > >> I know now that there is nothing to worry about regarding my settings or > >>> pragmas choices, since I get the same execution time with the sqlite3 > tool > >>> > >> > >> Maybe nothing to worry about, but that is no reason to leave it be - > >> whatever the case is, if some previous version of SQLite can run it in > >> 1/10th the time, there MUST be opportunity for improvement
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 rows in it, and tableD around 50k > (which is not big at all right?) > The query goes like this : > SELECT DISTINCT A1.idA, A1.column1, A1.column2 > FROMtableA A1, > tableA A2, > tableLink link, > tableC C1, > tableD D1, > tableD D2, > WHERE C1.idA = A1.idA > ANDC1.idD = D1.idD > AND A1.idD = D1.idD > AND A1.column2 = 'VALUE' > AND A2.idA = link.id_item_1 > AND A1.idA = link.id_item_2 > AND D2.idD = A2.idD > AND A2.idA = 100 > > This query takes 100 seconds approx. > I don't know if that can help you in any way, but the tables have been > created with a query like INSERT INTO SELECT * FROM A_View, so they have > been created from a view. After populating the data, do create any indexes? Also, I presume that all 4 tables are created like this, not just tableA, right? BTW, if you can show the schema (no data necessary) for those 4 tables, we can see if there is a way to improve. Thank you. > As we can see, only columns from tableA A1 are selected, so most of the > joins here can be replaced with something of the form : > AND EXISTS (SELECT 1 FROM "test join") > > I managed to drastically reduce time execution on this query with the > EXISTS trick, and now it has a normal time execution (below 300 ms for > sure, can't tell you how much exactly) > > > I've check the EXPLAIN QUERY PLAN of the original query, and I understood > that I was scanning tables in nested loops, so that this can take some time > (in fact the product of the sizes of all the tables in the FROM clause > right?) > > Here is what i can give you for now, now i'll do some tests that you've > recommended earlier > > 2016-09-07 16:33 GMT+02:00 R Smith : > >> >> 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. Therefore, sometimes the queries aren't >>> optimized at all. >>> I solved that issue myself by touching up the queries a bit before >>> executing them, and everything is fine now. >>> I was concerned mainly because I figured that maybe some queries were a >>> bit >>> longer to execute as they should have (let's say 200 ms instead of 100 >>> ms), >>> and I'm running a lot of queries in the app (approx 1000 per seconds). >>> >> >> Magic goalposts... >> >> "A query" taking 100s is a VERY VERY different problem to 1000 queries >> taking 100ms each. And to get technical, you shouldn't really need to >> optimize the queries (apart from avoiding the obvious silliness), you only >> need to know how to ask for the data correctly. Optimization is the job of >> the query planner in the DB engine - it should get the best fastest query >> results possible as long as it has all the information (which is what >> ANALYZE will do as others mentioned already) and as long as you provide the >> best Index for the job (which is something we might have some suggestions >> on if we know the schema and typical query is). >> >> >> I know now that there is nothing to worry about regarding my settings or >>> pragmas choices, since I get the same execution time with the sqlite3 tool >>> >> >> Maybe nothing to worry about, but that is no reason to leave it be - >> whatever the case is, if some previous version of SQLite can run it in >> 1/10th the time, there MUST be opportunity for improvement. >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > > > > -- > Laura BERGOENS > Technicienne supérieure en Informatique et étudiante à l'IMERIR de Perpignan > > *Institut Méditerranéen d'Étude etde Recherche en Informatique* > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 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_options for the 3 platforms I mentionned before > and I tried to regroup the results in a libreOffice calc document, I don't > know if that can help or if it's relevant > > 2016-09-07 17:04 GMT+02:00 Simon Slavin : > >> >> 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 might not. But >> a situation where you have many 'AND' clauses looking at different columns >> is exactly what ANALYZE is most helpful for. >> >> And yes, a 50 kilorow table is not big by SQLite standards. I have >> tables with a thousand times that that yield answers to SELECT in 5ms. >> >> Simon. >> ___ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > > > > -- > Laura BERGOENS > Technicienne supérieure en Informatique et étudiante à l'IMERIR de > Perpignan > > *Institut Méditerranéen d'Étude etde Recherche en Informatique* > -- Laura BERGOENS Technicienne supérieure en Informatique et étudiante à l'IMERIR de Perpignan *Institut Méditerranéen d'Étude etde Recherche en Informatique* ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 might not. But a situation where you have many 'AND' clauses looking at different columns is exactly what ANALYZE is most helpful for. And yes, a 50 kilorow table is not big by SQLite standards. I have tables with a thousand times that that yield answers to SELECT in 5ms. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 goes like this : SELECT DISTINCT A1.idA, A1.column1, A1.column2 FROMtableA A1, tableA A2, tableLink link, tableC C1, tableD D1, tableD D2, WHERE C1.idA = A1.idA ANDC1.idD = D1.idD AND A1.idD = D1.idD AND A1.column2 = 'VALUE' AND A2.idA = link.id_item_1 AND A1.idA = link.id_item_2 AND D2.idD = A2.idD AND A2.idA = 100 This query takes 100 seconds approx. I don't know if that can help you in any way, but the tables have been created with a query like INSERT INTO SELECT * FROM A_View, so they have been created from a view. As we can see, only columns from tableA A1 are selected, so most of the joins here can be replaced with something of the form : AND EXISTS (SELECT 1 FROM "test join") I managed to drastically reduce time execution on this query with the EXISTS trick, and now it has a normal time execution (below 300 ms for sure, can't tell you how much exactly) I've check the EXPLAIN QUERY PLAN of the original query, and I understood that I was scanning tables in nested loops, so that this can take some time (in fact the product of the sizes of all the tables in the FROM clause right?) Here is what i can give you for now, now i'll do some tests that you've recommended earlier 2016-09-07 16:33 GMT+02:00 R Smith : > > 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. Therefore, sometimes the queries aren't >> optimized at all. >> I solved that issue myself by touching up the queries a bit before >> executing them, and everything is fine now. >> I was concerned mainly because I figured that maybe some queries were a >> bit >> longer to execute as they should have (let's say 200 ms instead of 100 >> ms), >> and I'm running a lot of queries in the app (approx 1000 per seconds). >> > > Magic goalposts... > > "A query" taking 100s is a VERY VERY different problem to 1000 queries > taking 100ms each. And to get technical, you shouldn't really need to > optimize the queries (apart from avoiding the obvious silliness), you only > need to know how to ask for the data correctly. Optimization is the job of > the query planner in the DB engine - it should get the best fastest query > results possible as long as it has all the information (which is what > ANALYZE will do as others mentioned already) and as long as you provide the > best Index for the job (which is something we might have some suggestions > on if we know the schema and typical query is). > > > I know now that there is nothing to worry about regarding my settings or >> pragmas choices, since I get the same execution time with the sqlite3 tool >> > > Maybe nothing to worry about, but that is no reason to leave it be - > whatever the case is, if some previous version of SQLite can run it in > 1/10th the time, there MUST be opportunity for improvement. > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Laura BERGOENS Technicienne supérieure en Informatique et étudiante à l'IMERIR de Perpignan *Institut Méditerranéen d'Étude etde Recherche en Informatique* ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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. Since I'm working on confidential/sensitive data, I need to take some time to process your reply and see what can I show you guys and I can't. 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. I'll try to come back at you asap 2016-09-07 16:24 GMT+02:00 R Smith : > > > 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 the tool >> SQLiteBrowser is using it as well. >> > > Yes, though the versions might be hugely different. Further to this (and > regarding the rest of your post), SQLitebrowser sounds like the name it > used to have before it became "DB Browser for SQLite" quite some time ago, > perhaps you'd want to update to the latest to be sure the differences are > real (though, SQLite successive iterations usually become faster, not > slower, apart from compile-time options that may be different). > > To find out the exact difference, you can SELECT the following: > sqlite_version() > sqlite_source_id() > > and this query will tell which options were compiled-in: > PRAGMA compile_options;* > > *If the new version is truly slower, then the most likely culprit is a > regression for an optimization (or a regression introduced by an > optimization, compile-time option or new functionality) that causes the > current version to perform less quick than a previous version. If this is > the case, we would definitely like to know about it. Could you supply the > Schema and query (at a minimum) and perhaps upload the DB somewhere (if the > data is not too sensitive). > > Further to this, a 100-second query on a DB the size you describe is > ludicrous, even for terribly formulated queries - if this is in fact a > normal situation (i.e. not an obvious regression), might we have a go at > your query to see if it could possibly be optimized? > * > *Thanks, > Ryan > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Laura BERGOENS Technicienne supérieure en Informatique et étudiante à l'IMERIR de Perpignan *Institut Méditerranéen d'Étude etde Recherche en Informatique* ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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. Therefore, sometimes the queries aren't optimized at all. I solved that issue myself by touching up the queries a bit before executing them, and everything is fine now. I was concerned mainly because I figured that maybe some queries were a bit longer to execute as they should have (let's say 200 ms instead of 100 ms), and I'm running a lot of queries in the app (approx 1000 per seconds). Magic goalposts... "A query" taking 100s is a VERY VERY different problem to 1000 queries taking 100ms each. And to get technical, you shouldn't really need to optimize the queries (apart from avoiding the obvious silliness), you only need to know how to ask for the data correctly. Optimization is the job of the query planner in the DB engine - it should get the best fastest query results possible as long as it has all the information (which is what ANALYZE will do as others mentioned already) and as long as you provide the best Index for the job (which is something we might have some suggestions on if we know the schema and typical query is). I know now that there is nothing to worry about regarding my settings or pragmas choices, since I get the same execution time with the sqlite3 tool Maybe nothing to worry about, but that is no reason to leave it be - whatever the case is, if some previous version of SQLite can run it in 1/10th the time, there MUST be opportunity for improvement. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 the tool SQLiteBrowser is using it as well. Yes, though the versions might be hugely different. Further to this (and regarding the rest of your post), SQLitebrowser sounds like the name it used to have before it became "DB Browser for SQLite" quite some time ago, perhaps you'd want to update to the latest to be sure the differences are real (though, SQLite successive iterations usually become faster, not slower, apart from compile-time options that may be different). To find out the exact difference, you can SELECT the following: sqlite_version() sqlite_source_id() and this query will tell which options were compiled-in: PRAGMA compile_options;* *If the new version is truly slower, then the most likely culprit is a regression for an optimization (or a regression introduced by an optimization, compile-time option or new functionality) that causes the current version to perform less quick than a previous version. If this is the case, we would definitely like to know about it. Could you supply the Schema and query (at a minimum) and perhaps upload the DB somewhere (if the data is not too sensitive). Further to this, a 100-second query on a DB the size you describe is ludicrous, even for terribly formulated queries - if this is in fact a normal situation (i.e. not an obvious regression), might we have a go at your query to see if it could possibly be optimized? * *Thanks, Ryan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 all. I solved that issue myself by touching up the queries a bit before executing them, and everything is fine now. I was concerned mainly because I figured that maybe some queries were a bit longer to execute as they should have (let's say 200 ms instead of 100 ms), and I'm running a lot of queries in the app (approx 1000 per seconds). I know now that there is nothing to worry about regarding my settings or pragmas choices, since I get the same execution time with the sqlite3 tool Thanks again for taking some time to answer 2016-09-07 16:14 GMT+02:00 Simon Slavin : > > 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, and we can > probably improve on it if you're willing to include your "SELECT" command > and your schema. It might be something as simple as creating one > additional index. > > You might also try executing "ANALYZE" in sqlite3.exe and see whether that > speeds things up. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Laura BERGOENS Technicienne supérieure en Informatique et étudiante à l'IMERIR de Perpignan *Institut Méditerranéen d'Étude etde Recherche en Informatique* ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 seconds is a very long time for a query, and we can > probably improve on it if you're willing to include your "SELECT" command > and your schema. It might be something as simple as creating one additional > index. > > You might also try executing "ANALYZE" in sqlite3.exe and see whether that > speeds things up. > +1 First run ANALYZE and then retry your query. Then run ".fullschema -indent" from the command-line shell and send us the output together with the complete text of the SELECT statement that is running slowly. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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, and we can probably improve on it if you're willing to include your "SELECT" command and your schema. It might be something as simple as creating one additional index. You might also try executing "ANALYZE" in sqlite3.exe and see whether that speeds things up. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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... interesting :) : sqlite3.exe : - Version : 2016-08-11 18:53:32 a12d8059770df4bca59e321c266410344242bf7b - Execution time : Approx 100 seconds my app : - Version : 2015-07-29 20:00:57 cf538e2783e468bbc25e7cb2a9ee64d3e0e80b2f - Execution time : Approx 100 seconds SQliteBrower : - Version : 2014-10-29 13:59:56 3b7b72c4685aa5cf5e675c2c47ebec10d9704221 - Execution time : Approx 5 seconds Needless to say that i have the same set of results in all three tests (it's a SELECT type query) 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. That being said, I wonder what is the reason why SQliteBrowser is doing so great comparing to the others. But as you said that's not the place for this question ;) Thanks for replying again. 2016-09-07 15:19 GMT+02:00 Richard Hipp : > 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 tool > > SQLiteBrowser is using it as well. > > > > I noticed that the difference of query time execution between my app and > > the browser, with the same queries of course, can be widely different. In > > fact, it feels like it's exponential. > > (1) SQLiteBrowser is a 3rd-party tool that is not supported by the > SQLite developers nor this website. You are welcomed to use > SQLiteBrowser if it meets your needs. You won't hurt anyones > feelings. But neither will we support it here. You'll need to > contact the developers of SQLiteBrowser to get support for that tool. > > To get the best response from this mailing list, it is important to > show a difference in the query performance of SQLite in your > application versus the "sqlite3.exe" command-line tool found on the > http://sqlite.org/download.html page. Have you tried running your > queries using sqlite3.exe? How is the performance there. > > (2) Performance differences like this can sometimes arise because the > slower application is using an older version of SQLite. What does > "SELECT sqlite_source_id()" return in the two applications you are > comparing? > > -- > D. Richard Hipp > d...@sqlite.org > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Laura BERGOENS Technicienne supérieure en Informatique et étudiante à l'IMERIR de Perpignan *Institut Méditerranéen d'Étude etde Recherche en Informatique* ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 tool > SQLiteBrowser is using it as well. > > I noticed that the difference of query time execution between my app and > the browser, with the same queries of course, can be widely different. In > fact, it feels like it's exponential. (1) SQLiteBrowser is a 3rd-party tool that is not supported by the SQLite developers nor this website. You are welcomed to use SQLiteBrowser if it meets your needs. You won't hurt anyones feelings. But neither will we support it here. You'll need to contact the developers of SQLiteBrowser to get support for that tool. To get the best response from this mailing list, it is important to show a difference in the query performance of SQLite in your application versus the "sqlite3.exe" command-line tool found on the http://sqlite.org/download.html page. Have you tried running your queries using sqlite3.exe? How is the performance there. (2) Performance differences like this can sometimes arise because the slower application is using an older version of SQLite. What does "SELECT sqlite_source_id()" return in the two applications you are comparing? -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[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 noticed that the difference of query time execution between my app and the browser, with the same queries of course, can be widely different. In fact, it feels like it's exponential. The difference is really tiny for fast queries, but i have some queries that take 10 seconds of execution in SQLiteBrowser, and 100 seconds in my application. Context : the databases i'm working on aren't large at all (between 10Mb and 30 Mb, no more than 50 Mb for sure). Tables im working on don't exceed 100k rows, which is kind of small when i compare to some of the things i read on the mailing list. In order to measure that execution on time on my app, I've surrounded the exec() function of sqlite3 with a clock() mechanism. To finish, I'm working on a in-memory database, with those pragmas : page_size = 4096, cache_size = 1, thread safe, automatic index = on, synchronous = off, journal_mode = MEMORY", locking_mode = exclusive, temp_store=memory. The reason why for those pragmas is that I'm essentially running SELECT queries, a very few INSERT or UPDATE but I'm mainly reading in the database. I've tried to check the compile options of SQLiteBrowser to begin with, but I was unable to find them. Anyway, I was wondering why is there such a difference, I feel like the pragmas are ok, and I've tested with same database, same queries. Any ideas? Thanks for reading, if you guys need to know more I'm here -- Laura BERGOENS Technicienne supérieure en Informatique et étudiante à l'IMERIR de Perpignan *Institut Méditerranéen d'Étude etde Recherche en Informatique* ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users