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

[Igor Korot]

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

[Laura BERGOENS]

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

[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

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

[Laura BERGOENS]

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 <b...@tmgca.com>:

> 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 <sqlite-users@mailinglists.sqlite.org>
> 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 <slav...@bigfraud.org> wrote:
>
> >
> > On 7 Sep 2016, at 3:48pm, Laura BERGOENS <laura.bergo...@imerir.com>
> > 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

[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 <sqlite-users@mailinglists.sqlite.org>
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 <slav...@bigfraud.org> wrote:

>
> On 7 Sep 2016, at 3:48pm, Laura BERGOENS <laura.bergo...@imerir.com>
> 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

[Bob McFarlane]

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 <sqlite-users@mailinglists.sqlite.org>
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 <slav...@bigfraud.org>:

>
> On 7 Sep 2016, at 3:48pm, Laura BERGOENS <laura.bergo...@imerir.com>
> 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

[Chris Locke]

>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

[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*
___
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

[David Raymond]

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

[Robert Weiss]

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

[Simon Slavin]

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

[Laura BERGOENS]

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

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

[Laura BERGOENS]

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

[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

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

[Laura BERGOENS]

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 

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

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

[Laura BERGOENS]

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

[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

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

[Laura BERGOENS]

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

[Laura BERGOENS]

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

[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

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

[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

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

[Laura BERGOENS]

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

[Richard Hipp]

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

[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

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

[Laura BERGOENS]

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

[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