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: [email protected] [mailto:sqlite-users-
> [email protected]] On Behalf Of [email protected]
> Sent: Monday, 15 April, 2013 15:20
> To: [email protected]
> 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 <[email protected]> wrote:
> >
> >
> >On 15 Apr 2013, at 9:47pm, [email protected] 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
> >[email protected]
> >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
