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
