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
