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

Reply via email to