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