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
