I corrected the "DTC" to 'DTC' and undid the analyze since that seemed to be
slowing things down quite a bit.
Unfortunately, the speed did not improve.
Here is the latest explain query plan:
0|0|6|SEARCH TABLE Object_Types USING COVERING INDEX
sqlite_autoindex_Object_Types_1 (Object_Type=?) (~1 rows)
0|1|1|SCAN TABLE DTC_Statuses (~1000000 rows)
0|2|0|SEARCH TABLE Objects USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
0|3|7|SEARCH TABLE DisplayFormats USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
0|4|2|SEARCH TABLE Object_TextTables USING INDEX Object_TextTables_1_index
(Object_ID=?) (~10 rows)
0|5|3|SEARCH TABLE Object_Transactions USING INDEX Object_Transactions_2_index
(Object_ID=?) (~10 rows)
0|6|4|SEARCH TABLE Equations USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
0|7|5|SEARCH TABLE Equation_Types USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
Vance
on Apr 15, 2013, ven...@intouchmi.com wrote:
>
>The integrity check came back with OK.
>
>My software was not locked up totally. It did finally come back. For some
>reason
>executing analyze slow other queries way down.
>
>Here is the new explain query plan:
>0|0|6|SEARCH TABLE Object_Types USING COVERING INDEX
>sqlite_autoindex_Object_Types_1
>(Object_Type=?) (~1 rows)
>0|1|7|SCAN TABLE DisplayFormats (~7 rows)
>0|2|5|SCAN TABLE Equation_Types (~10 rows)
>0|3|3|SEARCH TABLE Object_Transactions USING AUTOMATIC COVERING INDEX
>(Equation_Type_ID=?)
>(~5 rows)
>0|4|4|SEARCH TABLE Equations USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
>0|5|0|SEARCH TABLE Objects USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
>0|6|1|SEARCH TABLE DTC_Statuses USING INDEX DTC_Statuses_1_index (Object_ID=?)
>(~1
>rows)
>0|7|2|SEARCH TABLE Object_TextTables USING INDEX Object_TextTables_1_index
>(Object_ID=?)
>(~4 rows)
>
>The explain query plan for the slowed down query is:
>0|0|5|SCAN TABLE Header (~1 rows)
>0|1|2|SCAN TABLE Object_Types USING COVERING INDEX
>sqlite_autoindex_Object_Types_1
>(~5 rows)
>0|2|4|SCAN TABLE Service_Types (~5 rows)
>0|3|10|SEARCH TABLE Transaction_Types USING COVERING INDEX
>sqlite_autoindex_Transaction_Types_1
>(Transaction_Type=?) (~1 rows)
>0|3|10|SEARCH TABLE Transaction_Types USING COVERING INDEX
>sqlite_autoindex_Transaction_Types_1
>(Transaction_Type=?) (~1 rows)
>0|3|10|SEARCH TABLE Transaction_Types USING COVERING INDEX
>sqlite_autoindex_Transaction_Types_1
>(Transaction_Type=?) (~1 rows)
>0|3|10|SEARCH TABLE Transaction_Types USING COVERING INDEX
>sqlite_autoindex_Transaction_Types_1
>(Transaction_Type=?) (~1 rows)
>0|3|10|SEARCH TABLE Transaction_Types USING COVERING INDEX
>sqlite_autoindex_Transaction_Types_1
>(Transaction_Type=?) (~1 rows)
>0|3|10|SEARCH TABLE Transaction_Types USING COVERING INDEX
>sqlite_autoindex_Transaction_Types_1
>(Transaction_Type=?) (~1 rows)
>0|4|6|SEARCH TABLE Services USING INDEX Services_3_index (Service_Type_ID=?)
>(~2 rows)
>0|5|3|SCAN TABLE DisplayFormats (~7 rows)
>0|6|9|SCAN TABLE Equation_Types (~10 rows)
>0|7|1|SEARCH TABLE Object_Transactions USING AUTOMATIC COVERING INDEX
>(Equation_Type_ID=?)
>(~5 rows)
>0|8|8|SEARCH TABLE Equations USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
>0|9|7|SEARCH TABLE Transactions USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
>0|10|0|SEARCH TABLE Objects USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
>0|11|11|SEARCH TABLE Objects AS t1 USING INTEGER PRIMARY KEY (rowid=?) (~1
>rows)
>0|12|12|SEARCH TABLE Units AS t2 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
>0|0|0|USE TEMP B-TREE FOR ORDER BY
>
> and the query was:
>SELECT v1.*, t1.Object_Name, t2.Unit_Name
>FROM Select_Inbound_Objects AS v1, Objects AS t1 INNER JOIN Units t2 ON
>t1.Unit_ID
>= t2.Unit_ID
>WHERE v1.Object_ID=t1.Object_ID
>
>I'm pretty sure other queries were slowed down by explain as well. This was
>just
>the first query I saw a large delay during execution.
>
>Vance
>
>on Apr 15, 2013, Simon Slavin <slav...@bigfraud.org> wrote:
>>
>>
>>On 15 Apr 2013, at 9:22pm, ven...@intouchmi.com wrote:
>>
>>> This is the create index statement:
>>> CREATE INDEX IF NOT EXISTS
>>> "DTC_Statuses_1_index" ON
>>> "DTC_Statuses" ("Object_ID");
>>>
>>> For some reason, when I execute analyze, my software locks up somewhere
>>> when running its test queries. I'll delve further into this.
>>
>>Worth noting that ANALYZE may end up having to read your entire database file
>>into
>>cache. So it can be slow.
>>
>>But if you're still having trouble with it try a "PRAGMA integrity_check":
>>
>><http://www.sqlite.org/pragma.html#pragma_integrity_check>
>>
>>Having got ANALYZE working, please post another EXPLAIN QUERY PLAN so we can
>>compare
>>the two.
>>
>>I also note this:
>>
>>> WHERE ((([Object_Types].[Object_Type])="DTC"))
>>
>>This is technically incorrect. It should use single quotes around DTC, not
>>double
>>quotes.
>>
>>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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
