Re: [sqlite] DMV available in Sqlite?

[Simon Slavin]

On 25 Jul 2014, at 2:54am, andy  wrote:

> All I want to do is write a conditional-compilation-flag-driven approach
> that investigates every single SQLite query and dumps the query results to a
> table, so I can inspect it later in order to fix worse queries, missing
> indexes, etc.

Would EXPLAIN QUERY PLAN not be more suited to your purposes ?  You could look 
for any SELECTs where SQLite had decided to scan a large table.  That's usually 
a sign that either the SELECT needs rephrasing or an index should be created.

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

Re: [sqlite] DMV available in Sqlite?

[andy]

Thanks for the tip. I was not aware of sqlite3_stmt_status().

Now I was trying to find the equivalent call in System.Data.Sqlite.dll (.NET
wrapper) but could not find it. I looked a little at the source code and
found that there is an UnsafeNativeMethods class that exports this C call to
the managed wrapper, but it is internal.  So, how would this be called with
the managed wrapper?

All I want to do is write a conditional-compilation-flag-driven approach
that investigates every single SQLite query and dumps the query results to a
table, so I can inspect it later in order to fix worse queries, missing
indexes, etc If there is a better solution let me know, but I could not
find any, therefore the ask for sqlite3_stmt_status()...

Thanks a lot.
Andy



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/DMV-available-in-Sqlite-tp76950p76960.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] DMV available in Sqlite?

[Richard Hipp]

On Thu, Jul 24, 2014 at 3:32 PM, Andreas Hofmann <andreas.hofm...@ku7t.org>
wrote:

> Hi,
>
>
>
> I know how to use SQL server DMV for analyzing query performance.   I am
> wondering if something similar to the MS SQL Server DMV is available for
> Sqlite (http://msdn.microsoft.com/en-us/library/ms188754.aspx)?   Anyone?
>

DMV accumulates statistics *on the server*.  But SQLite doesn't have a
server.  So

That said, SQLite does provide information about queries that are not using
indices effectively.  It's up to the application to ask for that
information, though, and then deal with it, because there is no server
available to remember it.  The API you are interested in is

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

Note that your application has to be engineered to call
sqlite3_stmt_status() after each SQL statement runs, gather the statistics,
then do something with those statistics when the application shuts down.


-- 
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] DMV available in Sqlite?

[Simon Slavin]

On 24 Jul 2014, at 8:32pm, Andreas Hofmann <andreas.hofm...@ku7t.org> wrote:

> I am
> wondering if something similar to the MS SQL Server DMV is available for
> Sqlite

DMV returns information about the database server.  SQLite does not involve any 
servers: all database access is done by looking at the file directly.  There is 
no need for DMV since there is no server to optimize.

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

Re: [sqlite] DMV available in Sqlite?

[Andreas Hofmann]

If not anything available, I could consider porting my db from sqlite to
MSSQL and just change the data access.   Wonder if anyone has attempted this
and if this would give me the information what I am looking for (like: is
the db designed correctly wrt indexes etc.).

 

Thanks,

Andy

 

 

From: Andreas Hofmann [mailto:andreas.hofm...@ku7t.org] 
Sent: Thursday, July 24, 2014 12:33 PM
To: 'General Discussion of SQLite Database'
Subject: DMV available in Sqlite?

 

Hi,

 

I know how to use SQL server DMV for analyzing query performance.   I am
wondering if something similar to the MS SQL Server DMV is available for
Sqlite (http://msdn.microsoft.com/en-us/library/ms188754.aspx)?   Anyone?

 

Thanks

Andy

 

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

[sqlite] DMV available in Sqlite?

[Andreas Hofmann]

Hi,

 

I know how to use SQL server DMV for analyzing query performance.   I am
wondering if something similar to the MS SQL Server DMV is available for
Sqlite (http://msdn.microsoft.com/en-us/library/ms188754.aspx)?   Anyone?

 

Thanks

Andy

 

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