Re: [sqlite] Performance probleme between 3.7.3 and 3.7.13

2012-07-18 Thread Stephane . Leger 
We found the problem but we don't have a good explanation for it.

All database files were read-only (in development they are for our SCM). 
Opening these files with 3.7.3 work fine. The same file open with 3.7.13 take 
more than 1 second to connect per file. If we remove the read-only attribute 
than it open correctly with 3.7.13 like 3.7.3. 

If someone has an explanation I would like to know. 

Thanks for your help, it is your questions that put me on track!


Stéphane Léger, ing.
Technical Specialist 
HMI Group
Marine Control System
L-3 MAPPS
514 787-4877


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of stephane.le...@l-3com.com
Sent: Wednesday, July 18, 2012 2:42 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Performance probleme between 3.7.3 and 3.7.13

We have a trace directly in the code to do this measurement. 
The problem is establishing the connection not the query itself.

It is when we call sqlite3_open16() after a simple debugging session with 
3.7.13 it take the 1 sec and with 3.7.3 it is no wait at all! Any idea?


Stéphane Léger, ing.
Technical Specialist 
HMI Group
Marine Control System
L-3 MAPPS
514 787-4877


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Wednesday, July 18, 2012 1:48 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Performance probleme between 3.7.3 and 3.7.13

On Wed, Jul 18, 2012 at 1:40 PM, <stephane.le...@l-3com.com> wrote:

> Hi,
>
>
>
> We are updating the version of sqlite from 3.7.3 to 3.7.13 in our 
> software. But we are having a performance problem and I suspect a new 
> configuration but I don't know which one. I read on the site but 
> didn't find anything obvious. We use sqlite to store our graphical 
> page objects data. It generate very small amount of data but we have  
> between 100 and
> 300 sqlite files to open and close. At initialisation of the software 
> we have to access all files to read some needed data. Doing it with 
> 3.7.3 take around 2 ms per file but with 3.7.13 it take 1.3 second for 
> the same call (select statement).
>

How are you making your timing measurements?

What is your schema?  What is the exact text of the query that is taking so 
much time?  Have you run EXPLAIN QUERY PLAN on this schema to see if something 
has changed between 3.7.3 and 3.7.13?  Have you tried running the suspect 
queries in an sqlite3.exe command-line shell to see how they perform there?

Have you used APIs like
sqlite3_stmt_status()<http://www.sqlite.org/c3ref/stmt_status.html>to
see where SQLite is using its time?

What else changed beside the SQLite core when you recompiled for 3.7.13?


>
>
>
> We are using :
>
>
>
> C++ (CppSQLite3 wrapper), ADO, Sqlite ODBC driver
>
> Window 7 64 bits but the application is 32 bits.
>
>
>
> The select is simple Select from PAGE where name = "aux_control" and 
> the result is always one row.
>
>
>
> My question what changes in SQLite can have this kind of impact on the 
> performance?
>
>
>
> Thanks for your help.
>
>
>
> Stéphane Léger, ing.
>
> Technical Specialist
>
> HMI Group
>
> Marine Control System
>
> L-3 MAPPS
>
> 514 787-4877
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance probleme between 3.7.3 and 3.7.13

2012-07-18 Thread Stephane . Leger 
We have a trace directly in the code to do this measurement. 
The problem is establishing the connection not the query itself.

It is when we call sqlite3_open16() after a simple debugging session with 
3.7.13 it take the 1 sec and with 3.7.3 it is no wait at all! Any idea?


Stéphane Léger, ing.
Technical Specialist 
HMI Group
Marine Control System
L-3 MAPPS
514 787-4877


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Wednesday, July 18, 2012 1:48 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Performance probleme between 3.7.3 and 3.7.13

On Wed, Jul 18, 2012 at 1:40 PM, <stephane.le...@l-3com.com> wrote:

> Hi,
>
>
>
> We are updating the version of sqlite from 3.7.3 to 3.7.13 in our 
> software. But we are having a performance problem and I suspect a new 
> configuration but I don't know which one. I read on the site but 
> didn't find anything obvious. We use sqlite to store our graphical 
> page objects data. It generate very small amount of data but we have  
> between 100 and
> 300 sqlite files to open and close. At initialisation of the software 
> we have to access all files to read some needed data. Doing it with 
> 3.7.3 take around 2 ms per file but with 3.7.13 it take 1.3 second for 
> the same call (select statement).
>

How are you making your timing measurements?

What is your schema?  What is the exact text of the query that is taking so 
much time?  Have you run EXPLAIN QUERY PLAN on this schema to see if something 
has changed between 3.7.3 and 3.7.13?  Have you tried running the suspect 
queries in an sqlite3.exe command-line shell to see how they perform there?

Have you used APIs like
sqlite3_stmt_status()<http://www.sqlite.org/c3ref/stmt_status.html>to
see where SQLite is using its time?

What else changed beside the SQLite core when you recompiled for 3.7.13?


>
>
>
> We are using :
>
>
>
> C++ (CppSQLite3 wrapper), ADO, Sqlite ODBC driver
>
> Window 7 64 bits but the application is 32 bits.
>
>
>
> The select is simple Select from PAGE where name = "aux_control" and 
> the result is always one row.
>
>
>
> My question what changes in SQLite can have this kind of impact on the 
> performance?
>
>
>
> Thanks for your help.
>
>
>
> Stéphane Léger, ing.
>
> Technical Specialist
>
> HMI Group
>
> Marine Control System
>
> L-3 MAPPS
>
> 514 787-4877
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance probleme between 3.7.3 and 3.7.13

2012-07-18 Thread Stephane . Leger 
First we changed the sqlite3.c and sqlite3.h and we had the problem. So we 
change also update the odbc driver to the latest version same result. 

We didnt't change the wrapper because we are not sure what should be updated!

Thanks.

Stéphane Léger, ing.
Technical Specialist 
HMI Group
Marine Control System
L-3 MAPPS
514 787-4877


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Simon Slavin
Sent: Wednesday, July 18, 2012 1:46 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Performance probleme between 3.7.3 and 3.7.13


On 18 Jul 2012, at 6:40pm, stephane.le...@l-3com.com wrote:

> We are using :
> 
> C++ (CppSQLite3 wrapper), ADO, Sqlite ODBC driver
> 
> Window 7 64 bits but the application is 32 bits.

Which of those did you change in your upgrade ?  Did you replace just your 
copies of sqlite3.c and sqlite3.h ?  Did you replace the wrapper ?  Did you 
replace the ODBC driver ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance probleme between 3.7.3 and 3.7.13

2012-07-18 Thread Richard Hipp 
On Wed, Jul 18, 2012 at 1:40 PM,  wrote:

> Hi,
>
>
>
> We are updating the version of sqlite from 3.7.3 to 3.7.13 in our
> software. But we are having a performance problem and I suspect a new
> configuration but I don't know which one. I read on the site but didn't
> find anything obvious. We use sqlite to store our graphical page objects
> data. It generate very small amount of data but we have  between 100 and
> 300 sqlite files to open and close. At initialisation of the software we
> have to access all files to read some needed data. Doing it with 3.7.3 take
> around 2 ms per file but with 3.7.13 it take 1.3 second for the same call
> (select statement).
>

How are you making your timing measurements?

What is your schema?  What is the exact text of the query that is taking so
much time?  Have you run EXPLAIN QUERY PLAN on this schema to see if
something has changed between 3.7.3 and 3.7.13?  Have you tried running the
suspect queries in an sqlite3.exe command-line shell to see how they
perform there?

Have you used APIs like
sqlite3_stmt_status()to
see where SQLite is using its time?

What else changed beside the SQLite core when you recompiled for 3.7.13?


>
>
>
> We are using :
>
>
>
> C++ (CppSQLite3 wrapper), ADO, Sqlite ODBC driver
>
> Window 7 64 bits but the application is 32 bits.
>
>
>
> The select is simple Select from PAGE where name = "aux_control" and the
> result is always one row.
>
>
>
> My question what changes in SQLite can have this kind of impact on the
> performance?
>
>
>
> Thanks for your help.
>
>
>
> Stéphane Léger, ing.
>
> Technical Specialist
>
> HMI Group
>
> Marine Control System
>
> L-3 MAPPS
>
> 514 787-4877
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance probleme between 3.7.3 and 3.7.13

2012-07-18 Thread Simon Slavin 

On 18 Jul 2012, at 6:40pm, stephane.le...@l-3com.com wrote:

> We are using :
> 
> C++ (CppSQLite3 wrapper), ADO, Sqlite ODBC driver
> 
> Window 7 64 bits but the application is 32 bits.

Which of those did you change in your upgrade ?  Did you replace just your 
copies of sqlite3.c and sqlite3.h ?  Did you replace the wrapper ?  Did you 
replace the ODBC driver ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users