removing the parenthesises (what is the plural?) made no difference to the query plan which is: 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)
which changed after analyze to: 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) which seems much better. But the query now takes longer. It was about 380 ms. Now it is taking about 610 ms. These times include the prepare and stepping through the results, but not retrieving any field contents. Both the prepare and stepping takes longer after analyze is executed. The time for the same query via DAO/MS Access takes about 150 ms. Here is the sqlite_stat1 table after analyze: Objects|Objects_5_index|5495 1 Objects|Objects_4_index|5495 393 Objects|Objects_3_index|5495 1374 Objects|Objects_2_index|5495 5495 Objects|Objects_1_index|5495 41 Objects|sqlite_autoindex_Objects_1|5495 1 Dimensions||1 Header_Stream_Values|Header_Stream_Values_2_index|3 1 Header_Stream_Values|Header_Stream_Values_1_index|3 3 Category_Types||8 Object_TextTables|Object_TextTables_2_index|15718 1965 Object_TextTables|Object_TextTables_1_index|15718 4 Addresses||256 Object_Types|sqlite_autoindex_Object_Types_1|5 1 SwitchKeys|SwitchKeys_1_index|1 1 Equation_Types||10 Graph_Types||4 Equations||43 Header||1 Object_Equations|Object_Equations_3_index|2 2 Object_Equations|Object_Equations_2_index|2 1 Object_Equations|Object_Equations_1_index|2 2 Nodes|Nodes_2_index|1 1 Node_Headers|Node_Headers_2_index|4 4 Node_Headers|Node_Headers_1_index|4 1 DTC_Statuses|DTC_Statuses_1_index|5234 1 Header_Stream|sqlite_autoindex_Header_Stream_1|3 1 TextTables|TextTables_1_index|21 1 Byte_Types||4 Transaction_Types|sqlite_autoindex_Transaction_Types_1|8 1 Request_Parameters||5 Limits||14 Object_Categories|Object_Categories_2_index|1 1 Object_Categories|Object_Categories_1_index|1 1 DisplayFormats||7 Message_Filters||5 Object_Transactions|Object_Transactions_3_index|5747 40 Object_Transactions|Object_Transactions_2_index|5747 2 Object_Transactions|Object_Transactions_1_index|5747 160 Units|sqlite_autoindex_Units_1|45 1 SConfig|sqlite_autoindex_SConfig_1|2 1 Service_Types|sqlite_autoindex_Service_Types_1|5 1 Config||1 Services|Services_3_index|12 6 Services|Services_2_index|12 2 Services|Services_1_index|12 12 Services|sqlite_autoindex_Services_1|12 1 Categories|Categories_3_index|2 2 Categories|Categories_2_index|2 1 Strings|sqlite_autoindex_Strings_1|5465 1 Transactions|Transactions_3_index|268 54 Transactions|Transactions_2_index|268 39 Transactions|Transactions_1_index|268 2 Transactions|sqlite_autoindex_Transactions_1|268 1 I'm not sure how breaking down the joins would give us good info since I'm not likely to do it in the same order as SQLite would. Vance on Apr 16, 2013, Keith Medcalf <kmedc...@dessus.com> wrote: > > >Have you tried removing all the useless brackets that Micro$oft adds to the >query? > I don't know if the parser strips them out automagically or if they are used > as >join order hints. You are better off letting the optimizer optimize unless >you really >really want to force a specific order. While some SQL databases ignore such >tomfoolery, >others do not. I do not know off-hand whether SQLite ignores tomfoolery or >not. > I just habitually remove them. > >SELECT Objects.Object_ID, > Object_TextTables.Lower_Limit, > Object_TextTables.Upper_Limit, > Object_TextTables.TextTable_ID, > DTC_Statuses.DTC_Status_ID, > DTC_Statuses.Env_Data, > DTC_Statuses.Env_Offset, > DTC_Statuses.DTCs_Follow, > DTC_Statuses.Upper_Limit, > DTC_Statuses.Lower_Limit, > DTC_Statuses.Physical_Value, > Object_Transactions.Element_Offset, > Equation_Types.Equation_Type, > Equations.BitMask, > Equations.ByteSpecific, > Equations.ObjectSpecific, > Equations.InternalEncoder, > Equations.Equation, > Objects.Object_Size, > Objects.Signed, > Objects.Visible, > Object_Types.Object_Type, > DisplayFormats.DisplayFormat > FROM Objects INNER JOIN DTC_Statuses ON > Objects.Object_ID=DTC_Statuses.Object_ID > > INNER JOIN Object_TextTables ON > Objects.Object_ID=Object_TextTables.Object_ID > > INNER JOIN Object_Transactions ON > Objects.Object_ID=Object_Transactions.Object_ID > > INNER JOIN Equations ON > Object_Transactions.Equation_ID=Equations.Equation_ID > > INNER JOIN Equation_Types ON > Object_Transactions.Equation_Type_ID=Equation_Types.Equation_Type_ID > > INNER JOIN Object_Types ON > Objects.Object_Type_ID=Object_Types.Object_Type_ID > INNER JOIN DisplayFormats ON > Objects.DisplayFormat_ID=DisplayFormats.DisplayFormat_ID > WHERE Object_Types.Object_Type='DTC'; > >I presume that the optimizer will then actually devolve this into the >optimizable >form. Some SQL parsers do, and some do not. Personally, except for extremely >trivially >joins (or where JOIN syntax must be used for OUTER joins) I always use the >less semantically >loaded form of speech thus, to ensure that the parsing order does not >influence the >optimizer. Technically, if a different query plan is obtained when using the >M$ >bracketed form, the above form, and the below form, the optimizer is using the >brackets >and JOIN syntax ordering to provide hints -- which may not result in the >optimum >query plan unless you know what you are doing (and what the optimizer in >question >will do when parsing the input semantics) > >SELECT Objects.Object_ID, > Object_TextTables.Lower_Limit, > Object_TextTables.Upper_Limit, > Object_TextTables.TextTable_ID, > DTC_Statuses.DTC_Status_ID, > DTC_Statuses.Env_Data, > DTC_Statuses.Env_Offset, > DTC_Statuses.DTCs_Follow, > DTC_Statuses.Upper_Limit, > DTC_Statuses.Lower_Limit, > DTC_Statuses.Physical_Value, > Object_Transactions.Element_Offset, > Equation_Types.Equation_Type, > Equations.BitMask, > Equations.ByteSpecific, > Equations.ObjectSpecific, > Equations.InternalEncoder, > Equations.Equation, > Objects.Object_Size, > Objects.Signed, > Objects.Visible, > Object_Types.Object_Type, > DisplayFormats.DisplayFormat > FROM Objects, > DTC_Statuses, Object_TextTables, > Object_Transactions, > Equations, > Equation_Types, > Object_Types, > DisplayFormats, > WHERE Objects.Object_ID=DTC_Statuses.Object_ID > AND Objects.Object_ID=Object_TextTables.Object_ID > AND Objects.Object_ID=Object_Transactions.Object_ID > AND Object_Transactions.Equation_ID=Equations.Equation_ID > AND Object_Transactions.Equation_Type_ID=Equation_Types.Equation_Type_ID > AND Objects.Object_Type_ID=Object_Types.Object_Type_ID > AND Objects.DisplayFormat_ID=DisplayFormats.DisplayFormat_ID > AND Object_Types.Object_Type='DTC'; > >And then properly handle transitive equalities so that it can generate the >best query >plan, assuming that every column mentioned as a conditional column is included >in >an appropriate index (appropriately set as unique, if it is unique), and that >tables >which require more than one column to be indexed have *all* the referenced >columns >included in the index. If a table has two mentioned columns you should create >both >indexes (mutatis mutandis for more than two columns)). Ensure unique columns >are >actually constrained unique in the table definition (or by a manually created >unique >index, which is the same thing). > >Then do an analyze and look at the generated output from EXPLAIN QUERY PLAN >which >will tell you the optimum join order so you can eliminate unneeded indexes and >extend >the indexes used to be covering indexes if you really want the maximum >performance. > >Are you using a version of SQLite3 that supports transitive conditions (what >version >are you using)? If not, you should specify them in the query, eg: > >WHERE A=B and B=C > >Should be stated as > >WHERE A=B and B=C and A=C > >Assuming that Object_Types.Object_Type='DTC' selects one row, that should >likely be >selected as the outer query loop since the optimizer should be seeking to >minimize >selection and using indexes to SEARCH inner tables using a minimum selection >product >at each step (that is what the row count in the EXPLAIN QUERY PLAN indicates >-- it >indicates selectivity of at that step, not the number of rows in the table -- >a SCAN >of a table containing a billion rows with the annotation (~1 ROW) means that >after >scanning the all the rows, the constraints to that point are expected to match >~1 >row out of the billion scanned [needless to say this is very slow] -- a SEARCH >resulting >in (~1 row) means that the optimizer expects to be able to use an index to >select >one row from the table (lightening fast) -- (~1000000 rows) means that there >is no >information to determine how many rows will be selected and this step might or >does >devolve to a cross join which may result in a huge number of candidates goi > ng into the next loop). > >--- >() ascii ribbon campaign against html e-mail >/\ www.asciiribbon.org > >> -----Original Message----- >> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >> boun...@sqlite.org] On Behalf Of ven...@intouchmi.com >> Sent: Monday, 15 April, 2013 15:20 >> To: sqlite-users@sqlite.org >> Subject: Re: [sqlite] Help speed up query >> >> SQLite was much slower on many queries AFTER analyze was executed. >> >> here are the referenced tables, # of rows, primary key, and additional >> indexes: >> Objects = 5495 rows, Primary key=Object_ID, other indexes on Address_ID, >> Dimension_ID, DisplayFormat_ID, Limit_ID, and Object_ID; >> DTC_Statuses = 5234 rows, Primary key=DTC_Status_ID, other index on >> Object_ID; >> Object_TextTables = 15718 rows, other indexes on Object_ID and >> TextTable_ID; >> Object_Transactions = 5747 rows, Primary key=Object_Transaction_ID, other >> indexes on Equation_ID, Object_ID, Transaction_ID; >> Equations = 43 rows, Primary key=Equation_ID; >> Equation_Types = 10 rows, Primary key=Equation_Type_ID; >> Object_Types = 5 rows, Primary key=Object_Type_ID; >> DisplayFormats = 7 rows, Primary key=DisplayFormat_ID. >> >> I'm not sure where it is getting 1000000 rows! >> >> Vance >> >> on Apr 15, 2013, Simon Slavin <slav...@bigfraud.org> wrote: >> > >> > >> >On 15 Apr 2013, at 9:47pm, ven...@intouchmi.com wrote: >> > >> >> The integrity check came back with OK. >> > >> >Good. >> > >> >> My software was not locked up totally. It did finally come back. For >> some reason >> >executing analyze slow other queries way down. >> > >> >I am surprised by anyone reporting that ANALYZE has substancially slowed >> down any >> >operation. Please excuse me because I have trouble understanding English >> sometimes. >> > Can you verify for us: >> > >> >A) The slow-down you're talking about. Are you saying that other >> operations ran slowly >> >while you were doing ANALYZE, or that the SELECTs ran more slowly after >> the ANALYZE >> >than they did before it ? >> > >> >B) Reading the results of the EXPLAIN QUERY PLAN after the ANALYZE, you >> should see >> >approximate row counts like >> > >> >> 0|5|3|SCAN TABLE DisplayFormats (~7 rows) >> > >> >This means that the table DisplayFormats has about 7 rows. If you take >> this as meaning >> >that it has between 1 and 70 rows, are these rowcounts correct as far as >> you know, >> >or has it completely miscounted a table which actually has thousands of >> rows as having >> >just 1 row ? >> > >> >C) Are these EXPLAIN QUERY PLANs you're posting all for the same SELECT >> statement >> >or are you switching from one to another ? >> > >> >Also I see in your latest EQP >> > >> >> 0|1|1|SCAN TABLE DTC_Statuses (~1000000 rows) >> > >> >Obviously, scanning a million rows is something you want to avoid. Have >> you created >> >an appropriate index ? >> > >> >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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
