Re: [sqlite] SQLite Profiler

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 30/03/12 01:45, TeDe wrote:
> We 've been using profilers for Sybase and MySQL, where you can see a 
> lot more: index usage, number of page reads, returning rows and time - 
> for the whole query and for every subquery.

Note that SQLite runs as a library within your process so you can use
whatever tools are normal for your platform.  For example on Linux/Unix
you can use getrusage - here is the man page:

  http://linux.die.net/man/2/getrusage

Windows doesn't have an API with all that info in one place, but you can
find APIs for subsets such as GetProcessTimes to get timing information.

One gotcha is that SQLite only does enough calculations to get the next
result row when you call sqlite3_step.  For example if you called "select
* from large_table" then sqlite3_step does virtually no work each time you
call it.  You can address this by wrapping sqlite3_step and saving before
and after values and then accumulate them until finalize/reset.

SQLite itself also has some instrumentation which you can get using these
calls:

  http://www.sqlite.org/c3ref/db_status.html
  http://www.sqlite.org/c3ref/stmt_status.html

Finally you can also use EXPLAIN (not EXPLAIN QUERY PLAN) information.  To
a first approximation, the more longer the resulting output the more steps
are being taken and the more complex your query is, so you should try to
keep them short.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk92Fw8ACgkQmOOfHg372QS84wCgrU9STHsDtKQkDX4IHF3Eqlhl
k1kAnjycDBBQaHiyk95Rp6AaD9DSvelP
=oRXe
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Profiler

[Simon Slavin]

On 30 Mar 2012, at 9:45am, TeDe  wrote:

> is anyone aware of a profiler for SQLite? I know the "Explain Query
> Plan", but this is too simple for what I'm looking for.

As well as EXPLAIN QUERY PLAN there is also EXPLAIN (without the QUERY PLAN):



> We 've been using profilers for Sybase and MySQL, where you can see a
> lot more: index usage, number of page reads, returning rows and time -
> for the whole query and for every subquery.
> 
> Is such a tool available or in development? I really would pay money for
> that.

EXPLAIN does some of the above.  You can get some of the other details you 
asked for by using the profiling callback functions:



though you'll be writing your own log functions to generate your trace in 
whatever format you want it.  There are a bunch of other functions which you 
could call regularly to note down what they return.  For instance you can put 
various options to these three:





You might want to browse the full set of functions and see if any others offer 
logging options useful to you:



There is also a tool which will analyse a database file and tell you details of 
what's in it and how it's organised.  Download the appropriate 'analyzer' 
package from here:



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

[sqlite] SQLite Profiler

[TeDe]

Hello,

is anyone aware of a profiler for SQLite? I know the "Explain Query
Plan", but this is too simple for what I'm looking for.
We 've been using profilers for Sybase and MySQL, where you can see a
lot more: index usage, number of page reads, returning rows and time -
for the whole query and for every subquery.

Is such a tool available or in development? I really would pay money for
that.

Thomas

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

Re: [sqlite] Sqlite profiler

[George Bills]

On 09/23/2009 11:16 PM, mcnamaragio wrote:
> Hello,
>
> Would anyone be interested in sqlite profiler? If yes what features would
> you expect from it?
>
> Thank you.
>
Yes, I would be interested. A high level breakdown of what SQLite is 
doing, and the time it's taking to do it would be useful for me - e.g. 
"finding appropriate rows from index, 3.123 seconds, grouping by rows 
and summing column, 2.456 seconds, ordering column by that sum, 2.987 
seconds, taking top 10 rows, 0.123 seconds, joining to table B, 0.927 
seconds" would be kind of what I'd be hoping for.

As for features, I wouldn't want much beyond the above. It wouldn't have 
to be graphical, or need to attach to a running query / SQLite session 
or anything like that for it to be useful to me.

I can probably find most of the "what is SQLite doing" info by looking 
at the "EXPLAIN" output, but so far I haven't put any effort into 
learning the opcodes. It seems like that output is a little more 
low-level than what I'd really want, and it doesn't give me the running 
time of each phase. Not to say that it isn't useful, but a profiler that 
fits somewhere in between "EXPLAIN" and "EXPLAIN QUERY PLAN" would be 
great for my needs.

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

Re: [sqlite] Sqlite profiler

[mcnamaragio]

I am thinking about developing a profiler but I am at a very early stage. I
have only ideas and nothing more yet. Before I start actual coding I would
like to know if anyone is interested in it as it will motivate me if I know
that people will benefit from the tool.

I am not sure whether I will be able to capture all the information but I
hope it will be a useful tool.

Thank you for sharing your opinion :)


TeDe wrote:
> 
> mcnamaragio schrieb:
>> Hello,
>>
>> Would anyone be interested in sqlite profiler? If yes what features would
>> you expect from it?
>>
>> Thank you. 
>>   
> If you mean, that every sub-query and its execution-time is displayed:
> yes, definitely! I would even pay money for it.
> 
> Features I would expect:
> - execution-time of every sub-query
> - records affected by every where-clause
> - indexes used
> - maybe a small graphical interface?
> - tabbed interface to compare different versions of a query during
> optimization
> 
> What are your plans?
> 
> Best Regards,
> 
> Thomas
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Sqlite-profiler-tp25531129p25586644.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Sqlite profiler

[Jim "Jed" Dodgen]

how would your profiler differ from "explain"?

http://sqlite.org/lang_explain.html

On Wed, Sep 23, 2009 at 6:32 AM, Christian Schwarz
 wrote:
>> Maybe, what is it?
>
> http://en.wikipedia.org/wiki/Profiling_(computer_programming)
>
> Cheers, Christian
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Jim "Jed" Dodgen
j...@dodgen.us
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite profiler

[TeDe]

mcnamaragio schrieb:
> Hello,
>
> Would anyone be interested in sqlite profiler? If yes what features would
> you expect from it?
>
> Thank you. 
>   
If you mean, that every sub-query and its execution-time is displayed:
yes, definitely! I would even pay money for it.

Features I would expect:
- execution-time of every sub-query
- records affected by every where-clause
- indexes used
- maybe a small graphical interface?
- tabbed interface to compare different versions of a query during
optimization

What are your plans?

Best Regards,

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

Re: [sqlite] Sqlite profiler

[RB Smissaert]

OK, thanks.
In that case I do that in my application code.

RBS


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Christian Schwarz
Sent: 23 September 2009 14:33
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Sqlite profiler

> Maybe, what is it?

http://en.wikipedia.org/wiki/Profiling_(computer_programming)

Cheers, Christian
___
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] Sqlite profiler

[Christian Schwarz]

> Maybe, what is it?

http://en.wikipedia.org/wiki/Profiling_(computer_programming)

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

Re: [sqlite] Sqlite profiler

[RB Smissaert]

Maybe, what is it?

RBS


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of mcnamaragio
Sent: 23 September 2009 14:16
To: sqlite-users@sqlite.org
Subject: [sqlite] Sqlite profiler


Hello,

Would anyone be interested in sqlite profiler? If yes what features would
you expect from it?

Thank you. 
-- 
View this message in context:
http://www.nabble.com/Sqlite-profiler-tp25531129p25531129.html
Sent from the SQLite mailing list archive at Nabble.com.

___
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] Sqlite profiler

[mcnamaragio]

Hello,

Would anyone be interested in sqlite profiler? If yes what features would
you expect from it?

Thank you. 
-- 
View this message in context: 
http://www.nabble.com/Sqlite-profiler-tp25531129p25531129.html
Sent from the SQLite mailing list archive at Nabble.com.

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

[sqlite] Sqlite profiler

[giorgi giorgi]

Hello,

Is there anyone interested in sqlite profiler? If yes what features would
you like to see?

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