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 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
