Re: [sqlite] Help speed up query
On Tue, Apr 16, 2013 at 8:52 PM, ven...@intouchmi.com wrote: Simon and everyone who has thrown ideas into the mix, I very much appreciate the effort that you folks have put into this! If nothing else, I am learning from this exorcise. At this point, sounds like the only way to possibly make forward progress on this would be to post your DB somewhere, for hands-on testing by anyone. The fact you haven't done so suggests you cannot do that, but perhaps if you could somehow anonymize the data, like dropping all selected columns that do not participate in the where and join clauses, and dropping irrelevant tables, then maybe you could share something where the query continues to be slower than MS Access, that someone could explain or improve on somehow. Just my $0.02. --DD PS: I would also allow to load that same data into another RDBMS and see how it performs there too. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help speed up query
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) -- (~100 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 100 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 (~100 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
Re: [sqlite] Help speed up query
On Mon, Apr 15, 2013 at 11:02 PM, Simon Slavin slav...@bigfraud.org wrote: On 15 Apr 2013, at 9:47pm, ven...@intouchmi.com wrote: 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 ? Perhaps you can share the sqlite_stat1 and sqlite_stat3 tables generated by the ANALYZE? You could perhaps also manually decompose your slow query, adding one join at a time. You also never shared actual timings in SQLite or the equivalent timings in MS ACCESS. Are we talking seconds or milliseconds? Just curious. --DD ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help speed up query
) -- (~100 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 100 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 (~100 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
Re: [sqlite] Help speed up query
On 16 Apr 2013, at 2:32pm, ven...@intouchmi.com wrote: It was about 380 ms. Now it is taking about 610 ms. Can I check with you that the database file is stored on a disk of the computer which is executing the SQLite commands ? In other words, that this is a local hard disk and not one shared over a network. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help speed up query
Interesting ... The query is simply descending a tree and I should think it would be fast. Object_Types Objects DTC_Statuses Object_TextTables DisplayFormats Object_Transactions Equations Equation_Types Which would need the following indexes for traversal. For column retrieval, you can cover the data items required by appending them to the end of each index if required to completely remove the necessity of accessing the base table b-tree ... Object_Types(Object_Type, Object_Type_ID) Objects(Object_Type_ID, Object_ID, DisplayFormat_ID) DTC_Statuses(Object_ID) Object_TextTables(Object_ID) DisplayFormats(DisplayFormat_ID) Object_Transactions(Object_ID, Equation_ID, Equation_Type_ID) Equations(Equation_ID) Equation_Types(Equation_Type_ID) It should be fast and the optimizer has little choice on how to perform the query because you only have a single non-join constraint and it would be placed at the top of the tree (in the outermost 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: Tuesday, 16 April, 2013 07:33 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Help speed up query 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 (~100 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
Re: [sqlite] Help speed up query
Yes, the DBs are on my local disk. The quoted times are after the first run so mostly in cache. It takes about twice the time the first run for both SQLite and MS Access. Vance on Apr 16, 2013, Simon Slavin slav...@bigfraud.org wrote: On 16 Apr 2013, at 2:32pm, ven...@intouchmi.com wrote: It was about 380 ms. Now it is taking about 610 ms. Can I check with you that the database file is stored on a disk of the computer which is executing the SQLite commands ? In other words, that this is a local hard disk and not one shared over a network. 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
Re: [sqlite] Help speed up query
I tried transfering the database to an in memory copy and running the queries. It is actually a little slower but not much. I'm sure there are tables that are not being accessed and so loading them into memory would add to the time. Vance on Apr 16, 2013, ven...@intouchmi.com wrote: Yes, the DBs are on my local disk. The quoted times are after the first run so mostly in cache. It takes about twice the time the first run for both SQLite and MS Access. Vance on Apr 16, 2013, Simon Slavin slav...@bigfraud.org wrote: On 16 Apr 2013, at 2:32pm, ven...@intouchmi.com wrote: It was about 380 ms. Now it is taking about 610 ms. Can I check with you that the database file is stored on a disk of the computer which is executing the SQLite commands ? In other words, that this is a local hard disk and not one shared over a network. 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
Re: [sqlite] Help speed up query
On 16 Apr 2013, at 7:10pm, ven...@intouchmi.com wrote: I tried transfering the database to an in memory copy and running the queries. It is actually a little slower but not much. I'm sure there are tables that are not being accessed and so loading them into memory would add to the time. I'm sorry I'm not responding to your posts any more, but I don't think I can help. I'm not familiar with anything that would cause ANALYZE to make a query slower, and I'm hoping an expert will see this and help you. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help speed up query
Have you tried increase sqlite cache size and page size? pragma cache_size; pragma page_size; Try making page_size=4096 Then make cache_size*page_size as big as your database file. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of ven...@intouchmi.com Sent: Tuesday, April 16, 2013 1:10 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Help speed up query I tried transfering the database to an in memory copy and running the queries. It is actually a little slower but not much. I'm sure there are tables that are not being accessed and so loading them into memory would add to the time. Vance on Apr 16, 2013, ven...@intouchmi.com wrote: Yes, the DBs are on my local disk. The quoted times are after the first run so mostly in cache. It takes about twice the time the first run for both SQLite and MS Access. Vance on Apr 16, 2013, Simon Slavin slav...@bigfraud.org wrote: On 16 Apr 2013, at 2:32pm, ven...@intouchmi.com wrote: It was about 380 ms. Now it is taking about 610 ms. Can I check with you that the database file is stored on a disk of the computer which is executing the SQLite commands ? In other words, that this is a local hard disk and not one shared over a network. 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
Re: [sqlite] Help speed up query
Simon and everyone who has thrown ideas into the mix, I very much appreciate the effort that you folks have put into this! If nothing else, I am learning from this exorcise. I set the page_size to 4096 and cache_size to 1024. (I picked the next binary size above the 2976 KB size of the DB under test.) No difference in speed. (I ran both in memory and off of disk versions). Vance on Apr 16, 2013, Michael Black mdblac...@yahoo.com wrote: Have you tried increase sqlite cache size and page size? pragma cache_size; pragma page_size; Try making page_size=4096 Then make cache_size*page_size as big as your database file. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of ven...@intouchmi.com Sent: Tuesday, April 16, 2013 1:10 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Help speed up query I tried transfering the database to an in memory copy and running the queries. It is actually a little slower but not much. I'm sure there are tables that are not being accessed and so loading them into memory would add to the time. Vance on Apr 16, 2013, ven...@intouchmi.com wrote: Yes, the DBs are on my local disk. The quoted times are after the first run so mostly in cache. It takes about twice the time the first run for both SQLite and MS Access. Vance on Apr 16, 2013, Simon Slavin slav...@bigfraud.org wrote: On 16 Apr 2013, at 2:32pm, ven...@intouchmi.com wrote: It was about 380 ms. Now it is taking about 610 ms. Can I check with you that the database file is stored on a disk of the computer which is executing the SQLite commands ? In other words, that this is a local hard disk and not one shared over a network. 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Help speed up query
Hi, I've been trying to convince my boss that SQLite should be faster than MS Access. I wrote a conversion program that reads a Access DB and generates an SQL statement files and then sends the table creation and insert statements to SQLite. I also wrote a C++ wrapper that I can call from VB6 in order to access the SQLite DB. It is a little faster except in some cases where it seems quite a bit slower. This is a read only database (since it is still being generated by MS Access based software. I am currently using a vanilla version of SQLite3 version 3.7.16.1 and have not set any option defines. In my test, I am simply calling step for each row and not wasting time retrieving the column's values. The slowest query is a view which is a series of inner joins and is 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)) which generates about 16000 rows. The row counts and primary keys for the referenced tables are: Objects = 5495, Object_ID; DTC_Statuses = 5234, DTC_Status_ID; Object_TextTables = 15718, Object_Transactions = 5747, Object_Transaction_ID; Equations = 43, Equation_ID; Equation_Types = 10, Equation_Type_ID; Object_Types = 5, Object_Type_ID; DisplayFormats = 7, DisplayFormat_ID. The primary keys were simply carried over from the MS Access DB. And the query is a predefined view that our software uses. Any suggestions? Vance ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help speed up query
On 4/15/2013 1:07 PM, ven...@intouchmi.com wrote: I've been trying to convince my boss that SQLite should be faster than MS Access. I wrote a conversion program that reads a Access DB and generates an SQL statement files and then sends the table creation and insert statements to SQLite. I also wrote a C++ wrapper that I can call from VB6 in order to access the SQLite DB. It is a little faster except in some cases where it seems quite a bit slower. This is a read only database (since it is still being generated by MS Access based software. I am currently using a vanilla version of SQLite3 version 3.7.16.1 and have not set any option defines. In my test, I am simply calling step for each row and not wasting time retrieving the column's values. The slowest query is a view which is a series of inner joins and is 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)) Do you have any indexes? This query should benefit greatly from indexes on DTC_Statuses(Object_ID), Object_TextTables(Object_ID), Object_Transactions(Object_ID). I bet your Access database has them, perhaps indirectly in the form of foreign keys or something like that. Without these indexes, you force SQLite to examine some 2.5*10^15 ( 5495*5234*15718*5747 ) combinations of rows. An index on Objects(Object_Type_ID) may also help, probably to a lesser degree. In sqlite3 command line console, run your query with EXPLAIN QUERY PLAN prepended. Show the output of that. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help speed up query
That's odd, I had listed the tables and keys in the original message. Here is that section again: which generates about 16000 rows. The row counts and primary keys for the referenced tables are: Objects = 5495, Object_ID; DTC_Statuses = 5234, DTC_Status_ID; Object_TextTables = 15718, Object_Transactions = 5747, Object_Transaction_ID; Equations = 43, Equation_ID; Equation_Types = 10, Equation_Type_ID; Object_Types = 5, Object_Type_ID; DisplayFormats = 7, DisplayFormat_ID. I assumed that primary keys are indexes. If not, then that is probably my problem. I do know that this is a huge join, I tried to get a total count of the orthogonal results but it took way too long. I'll work on getting the explain results. Vance on Apr 15, 2013, Igor Tandetnik i...@tandetnik.org wrote: On 4/15/2013 1:07 PM, ven...@intouchmi.com wrote: I've been trying to convince my boss that SQLite should be faster than MS Access. I wrote a conversion program that reads a Access DB and generates an SQL statement files and then sends the table creation and insert statements to SQLite. I also wrote a C++ wrapper that I can call from VB6 in order to access the SQLite DB. It is a little faster except in some cases where it seems quite a bit slower. This is a read only database (since it is still being generated by MS Access based software. I am currently using a vanilla version of SQLite3 version 3.7.16.1 and have not set any option defines. In my test, I am simply calling step for each row and not wasting time retrieving the column's values. The slowest query is a view which is a series of inner joins and is 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)) Do you have any indexes? This query should benefit greatly from indexes on DTC_Statuses(Object_ID), Object_TextTables(Object_ID), Object_Transactions(Object_ID). I bet your Access database has them, perhaps indirectly in the form of foreign keys or something like that. Without these indexes, you force SQLite to examine some 2.5*10^15 ( 5495*5234*15718*5747 ) combinations of rows. An index on Objects(Object_Type_ID) may also help, probably to a lesser degree. In sqlite3 command line console, run your query with EXPLAIN QUERY PLAN prepended. Show the output of that. -- Igor Tandetnik ___ 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
Re: [sqlite] Help speed up query
here is the output from explain: 0|Trace|0|0|0||00| 1|String8|0|1|0|DTC|00| 2|Goto|0|80|0||00| 3|OpenRead|1|17|0|8|00| 4|OpenRead|0|1027|0|10|00| 5|OpenRead|6|1025|0|2|00| 6|OpenRead|7|16|0|2|00| 7|OpenRead|2|170|0|4|00| 8|OpenRead|8|171|0|keyinfo(1,BINARY)|00| 9|OpenRead|3|731|0|6|00| 10|OpenRead|9|733|0|keyinfo(1,BINARY)|00| 11|OpenRead|4|139|0|7|00| 12|OpenRead|5|138|0|2|00| 13|Rewind|1|69|0||00| 14|Column|1|1|2|0|00| 15|MustBeInt|2|68|0||00| 16|NotExists|0|68|2||00| 17|Column|0|2|3|1|00| 18|MustBeInt|3|68|0||00| 19|NotExists|6|68|3||00| 20|Column|6|1|4||00| 21|Ne|1|68|4|collseq(BINARY)|69| 22|Column|0|9|5|1|00| 23|MustBeInt|5|68|0||00| 24|NotExists|7|68|5||00| 25|IsNull|2|68|0||00| 26|SeekGe|8|68|2|1|00| 27|IdxGE|8|68|2|1|01| 28|IdxRowid|8|6|0||00| 29|Seek|2|6|0||00| 30|IsNull|2|67|0||00| 31|SeekGe|9|67|2|1|00| 32|IdxGE|9|67|2|1|01| 33|IdxRowid|9|7|0||00| 34|Seek|3|7|0||00| 35|Column|3|4|8|0|00| 36|MustBeInt|8|66|0||00| 37|NotExists|4|66|8||00| 38|Column|3|5|9|2|00| 39|MustBeInt|9|66|0||00| 40|NotExists|5|66|9||00| 41|Rowid|0|10|0||00| 42|Column|2|2|11|0|00| 43|Column|2|3|12|0|00| 44|Column|2|1|13|0|00| 45|Rowid|1|14|0||00| 46|Column|1|4|15|0|00| 47|Column|1|5|16|0|00| 48|Column|1|6|17|0|00| 49|Column|1|2|18|0|00| 50|Column|1|3|19|0|00| 51|Column|1|7|20|0|00| 52|Column|3|3|21|5|00| 53|Column|5|1|22||00| 54|Column|4|3|23|65535|00| 55|RealAffinity|23|0|0||00| 56|Column|4|4|24|0|00| 57|Column|4|5|25|0|00| 58|Column|4|6|26|0|00| 59|Column|4|0|27||00| 60|Column|0|3|28|1|00| 61|Column|0|4|29|0|00| 62|Column|0|6|30|0|00| 63|Column|6|1|31||00| 64|Column|7|1|32||00| 65|ResultRow|10|23|0||00| 66|Next|9|32|0||00| 67|Next|8|27|0||00| 68|Next|1|14|0||01| 69|Close|1|0|0||00| 70|Close|0|0|0||00| 71|Close|6|0|0||00| 72|Close|7|0|0||00| 73|Close|2|0|0||00| 74|Close|8|0|0||00| 75|Close|3|0|0||00| 76|Close|9|0|0||00| 77|Close|4|0|0||00| 78|Close|5|0|0||00| 79|Halt|0|0|0||00| 80|Transaction|0|0|0||00| 81|VerifyCookie|0|174|0||00| 82|TableLock|0|17|0|DTC_Statuses|00| 83|TableLock|0|1027|0|Objects|00| 84|TableLock|0|1025|0|Object_Types|00| 85|TableLock|0|16|0|DisplayFormats|00| 86|TableLock|0|170|0|Object_TextTables|00| 87|TableLock|0|731|0|Object_Transactions|00| 88|TableLock|0|139|0|Equations|00| 89|TableLock|0|138|0|Equation_Types|00| 90|Goto|0|3|0||00| Thanks for your help on this! Vance on Apr 15, 2013, ven...@intouchmi.com wrote: That's odd, I had listed the tables and keys in the original message. Here is that section again: which generates about 16000 rows. The row counts and primary keys for the referenced tables are: Objects = 5495, Object_ID; DTC_Statuses = 5234, DTC_Status_ID; Object_TextTables = 15718, Object_Transactions = 5747, Object_Transaction_ID; Equations = 43, Equation_ID; Equation_Types = 10, Equation_Type_ID; Object_Types = 5, Object_Type_ID; DisplayFormats = 7, DisplayFormat_ID. I assumed that primary keys are indexes. If not, then that is probably my problem. I do know that this is a huge join, I tried to get a total count of the orthogonal results but it took way too long. I'll work on getting the explain results. Vance on Apr 15, 2013, Igor Tandetnik i...@tandetnik.org wrote: On 4/15/2013 1:07 PM, ven...@intouchmi.com wrote: I've been trying to convince my boss that SQLite should be faster than MS Access. I wrote a conversion program that reads a Access DB and generates an SQL statement files and then sends the table creation and insert statements to SQLite. I also wrote a C++ wrapper that I can call from VB6 in order to access the SQLite DB. It is a little faster except in some cases where it seems quite a bit slower. This is a read only database (since it is still being generated by MS Access based software. I am currently using a vanilla version of SQLite3 version 3.7.16.1 and have not set any option defines. In my test, I am simply calling step for each row and not wasting time retrieving the column's values. The slowest query is a view which is a series of inner joins and is 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
Re: [sqlite] Help speed up query
On 4/15/2013 2:12 PM, ven...@intouchmi.com wrote: That's odd, I had listed the tables and keys in the original message. Here is that section again: which generates about 16000 rows. The row counts and primary keys for the referenced tables are: Objects = 5495, Object_ID; DTC_Statuses = 5234, DTC_Status_ID; Object_TextTables = 15718, Object_Transactions = 5747, Object_Transaction_ID; Equations = 43, Equation_ID; Equation_Types = 10, Equation_Type_ID; Object_Types = 5, Object_Type_ID; DisplayFormats = 7, DisplayFormat_ID. I assumed that primary keys are indexes. Yes, primary keys are backed by indexes. But I'm asking about indexes on fields other than primary key - e.g. DTC_Statuses(Object_ID), not DTC_Statuses(DTC_Status_ID). What good is an index on DTC_Status_ID when no joins mention it? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help speed up query
On 4/15/2013 2:20 PM, ven...@intouchmi.com wrote: here is the output from explain: I asked about EXPLAIN QUERY PLAN, not EXPLAIN. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help speed up query
Do you have appropriate indexes on the tables. See: http://stackoverflow.com/questions/1676448/using-sqlite-how-do-i-index-colum ns-in-a-create-table-statement -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of ven...@intouchmi.com Sent: Monday, April 15, 2013 12:08 PM To: sqlite-users@sqlite.org Subject: [sqlite] Help speed up query Hi, I've been trying to convince my boss that SQLite should be faster than MS Access. I wrote a conversion program that reads a Access DB and generates an SQL statement files and then sends the table creation and insert statements to SQLite. I also wrote a C++ wrapper that I can call from VB6 in order to access the SQLite DB. It is a little faster except in some cases where it seems quite a bit slower. This is a read only database (since it is still being generated by MS Access based software. I am currently using a vanilla version of SQLite3 version 3.7.16.1 and have not set any option defines. In my test, I am simply calling step for each row and not wasting time retrieving the column's values. The slowest query is a view which is a series of inner joins and is 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)) which generates about 16000 rows. The row counts and primary keys for the referenced tables are: Objects = 5495, Object_ID; DTC_Statuses = 5234, DTC_Status_ID; Object_TextTables = 15718, Object_Transactions = 5747, Object_Transaction_ID; Equations = 43, Equation_ID; Equation_Types = 10, Equation_Type_ID; Object_Types = 5, Object_Type_ID; DisplayFormats = 7, DisplayFormat_ID. The primary keys were simply carried over from the MS Access DB. And the query is a predefined view that our software uses. Any suggestions? Vance ___ 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
Re: [sqlite] Help speed up query
Thanks Igor! I did create indexes for the SQLite DB version based on the indexes in the MS Access DB. Object_Texttables does have an index on Object_ID and Object_Transactions also has an index on - Object_ID. Just to double check, my index declarations are: CREATE INDEX IF NOT EXISTS Object_TextTables_1_index ON Object_TextTables (Object_ID); and CREATE INDEX IF NOT EXISTS Object_Transactions_2_index ON Object_Transactions (Object_ID); Here is the result of the explain query plan: 0|0|1|SCAN TABLE DTC_Statuses (~100 rows) 0|1|0|SEARCH TABLE Objects USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) 0|2|6|SEARCH TABLE Object_Types 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) I appreciate the time you spending on this! Vance on Apr 15, 2013, Igor Tandetnik i...@tandetnik.org wrote: On 4/15/2013 2:20 PM, ven...@intouchmi.com wrote: here is the output from explain: I asked about EXPLAIN QUERY PLAN, not EXPLAIN. -- Igor Tandetnik ___ 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
Re: [sqlite] Help speed up query
Igor, As a follow up, I added the other two indexes with no real inprovement. 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|1|SCAN TABLE DTC_Statuses (~100 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: Thanks Igor! I did create indexes for the SQLite DB version based on the indexes in the MS Access DB. Object_Texttables does have an index on Object_ID and Object_Transactions also has an index on - Object_ID. Just to double check, my index declarations are: CREATE INDEX IF NOT EXISTS Object_TextTables_1_index ON Object_TextTables (Object_ID); and CREATE INDEX IF NOT EXISTS Object_Transactions_2_index ON Object_Transactions (Object_ID); Here is the result of the explain query plan: 0|0|1|SCAN TABLE DTC_Statuses (~100 rows) 0|1|0|SEARCH TABLE Objects USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) 0|2|6|SEARCH TABLE Object_Types 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) I appreciate the time you spending on this! Vance on Apr 15, 2013, Igor Tandetnik i...@tandetnik.org wrote: On 4/15/2013 2:20 PM, ven...@intouchmi.com wrote: here is the output from explain: I asked about EXPLAIN QUERY PLAN, not EXPLAIN. -- Igor Tandetnik ___ 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
Re: [sqlite] Help speed up query
On 4/15/2013 3:27 PM, ven...@intouchmi.com wrote: Here is the result of the explain query plan: 0|0|1|SCAN TABLE DTC_Statuses (~100 rows) 0|1|0|SEARCH TABLE Objects USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) 0|2|6|SEARCH TABLE Object_Types 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) Try adding an index on DTC_Statuses(Object_ID). You want the SCAN to be on Object_Types (because it's small and further limited by the WHERE clause), and everything else done via SEARCH (which requires a primary key or a suitable index). The only missing link is a way to search DTC_Statuses given an Object_ID. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help speed up query
On 4/15/2013 3:45 PM, ven...@intouchmi.com wrote: As a follow up, I added the other two indexes with no real inprovement. Which two indexes? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help speed up query
DTC_Statuses(Object_ID) and Objects(Object_Type_ID) Vance on Apr 15, 2013, Igor Tandetnik i...@tandetnik.org wrote: On 4/15/2013 3:45 PM, ven...@intouchmi.com wrote: As a follow up, I added the other two indexes with no real inprovement. Which two indexes? -- Igor Tandetnik ___ 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
Re: [sqlite] Help speed up query
On Mon, Apr 15, 2013 at 9:45 PM, ven...@intouchmi.com wrote: Igor, As a follow up, I added the other two indexes with no real inprovement. 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|1|SCAN TABLE DTC_Statuses (~100 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) No clue if it matters, but maybe you could gather stats on the tables, and see if that helps? --DD http://www.sqlite.org/lang_analyze.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help speed up query
Are you sure? The query plan looks like Objects(Object_Type_ID) was added but DTC_Statuses(Object_ID) was not. Igor Tandetnik On 4/15/2013 3:57 PM, ven...@intouchmi.com wrote: DTC_Statuses(Object_ID) and Objects(Object_Type_ID) Vance on Apr 15, 2013, Igor Tandetnik i...@tandetnik.org wrote: On 4/15/2013 3:45 PM, ven...@intouchmi.com wrote: As a follow up, I added the other two indexes with no real inprovement. Which two indexes? -- Igor Tandetnik ___ 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
Re: [sqlite] Help speed up query
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. Vance on Apr 15, 2013, Igor Tandetnik i...@tandetnik.org wrote: Are you sure? The query plan looks like Objects(Object_Type_ID) was added but DTC_Statuses(Object_ID) was not. Igor Tandetnik On 4/15/2013 3:57 PM, ven...@intouchmi.com wrote: DTC_Statuses(Object_ID) and Objects(Object_Type_ID) Vance on Apr 15, 2013, Igor Tandetnik i...@tandetnik.org wrote: On 4/15/2013 3:45 PM, ven...@intouchmi.com wrote: As a follow up, I added the other two indexes with no real inprovement. Which two indexes? -- Igor Tandetnik ___ 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
Re: [sqlite] Help speed up query
On Mon, Apr 15, 2013 at 10:04 PM, Dominique Devienne ddevie...@gmail.comwrote: On Mon, Apr 15, 2013 at 9:45 PM, ven...@intouchmi.com wrote: Igor, As a follow up, I added the other two indexes with no real inprovement. 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|1|SCAN TABLE DTC_Statuses (~100 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) No clue if it matters, but maybe you could gather stats on the tables, and see if that helps? --DD http://www.sqlite.org/lang_analyze.html The end of 5.2 in http://www.sqlite.org/optoverview.html suggests it can make a difference. Especially since the QUERY PLAN shows 1,000,000, and you wrote there are 5234 rows only. As 7.0 states, you could also use covering indexes (an index will all the columns your query is accessing indexed) for your small lookup tables, to avoid an indirection back from the index to the table. 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_TextTablesON [Objects].[Object_ID]=[Object_TextTables].[Object_ID]) INNER JOIN Object_Transactions ON [Objects].[Object_ID]=[Object_Transactions].[Object_ID]) INNER JOIN EquationsON [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)) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help speed up query
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
Re: [sqlite] Help speed up query
On 4/15/2013 4:31 PM, Simon Slavin wrote: I also note this: WHERE ((([Object_Types].[Object_Type])=DTC)) This is technically incorrect. It should use single quotes around DTC, not double quotes. Actually - does any of the tables participating in the statement have a column named DTC, by any chance? Perhaps DTC_Status? If so, then your WHERE clause doesn't mean what you think it means: it's not a filter, but another join. That would explain the query plan. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help speed up query
On 15 Apr 2013, at 9:40pm, Igor Tandetnik i...@tandetnik.org wrote: On 4/15/2013 4:31 PM, Simon Slavin wrote: I also note this: WHERE ((([Object_Types].[Object_Type])=DTC)) This is technically incorrect. It should use single quotes around DTC, not double quotes. Actually - does any of the tables participating in the statement have a column named DTC, by any chance? Perhaps DTC_Status? If so, then your WHERE clause doesn't mean what you think it means: it's not a filter, but another join. That would explain the query plan. Ahhhahahaha ! I made an assumption. Bad Simon. No cookie. Thanks, Igor. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help speed up query
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
Re: [sqlite] Help speed up query
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 (~100 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
Re: [sqlite] Help speed up query
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 (~100 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
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 100 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 (~100 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
