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
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
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,
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|
On 4/15/2013 2:12 PM, 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,
On 4/15/2013 2:20 PM, ven...@intouchmi.com wrote:
here is the output from explain:
I asked about EXPLAIN QUERY PLAN, not EXPLAIN.
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
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
Thanks Igor!
I did create indexes for the SQLite DB version based on the indexes in the
MS Access DB.
Object_Texttables does have an index on Object_ID
and Object_Transactions also has an index on - Object_ID.
Just to double check, my index declarations are:
CREATE INDEX IF NOT EXISTS
Igor,
As a follow up, I added the other two indexes with no real inprovement.
Here is the new explain query plan:
0|0|6|SEARCH TABLE Object_Types USING COVERING INDEX
sqlite_autoindex_Object_Types_1 (Object_Type=?) (~1 rows)
0|1|1|SCAN TABLE DTC_Statuses (~100 rows)
0|2|0|SEARCH TABLE
On 4/15/2013 3:27 PM, ven...@intouchmi.com wrote:
Here is the result of the explain query plan:
0|0|1|SCAN TABLE DTC_Statuses (~100 rows)
0|1|0|SEARCH TABLE Objects USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
0|2|6|SEARCH TABLE Object_Types USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
On 4/15/2013 3:45 PM, ven...@intouchmi.com wrote:
As a follow up, I added the other two indexes with no real inprovement.
Which two indexes?
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
DTC_Statuses(Object_ID) and Objects(Object_Type_ID)
Vance
on Apr 15, 2013, Igor Tandetnik wrote:
>
>On 4/15/2013 3:45 PM, ven...@intouchmi.com wrote:
>> As a follow up, I added the other two indexes with no real inprovement.
>
>Which two indexes?
>--
>Igor Tandetnik
>
On Mon, Apr 15, 2013 at 9:45 PM, wrote:
> Igor,
>
> As a follow up, I added the other two indexes with no real inprovement.
> Here is the new explain query plan:
> 0|0|6|SEARCH TABLE Object_Types USING COVERING INDEX
> sqlite_autoindex_Object_Types_1 (Object_Type=?) (~1
Are you sure? The query plan looks like Objects(Object_Type_ID) was
added but DTC_Statuses(Object_ID) was not.
Igor Tandetnik
On 4/15/2013 3:57 PM, ven...@intouchmi.com wrote:
DTC_Statuses(Object_ID) and Objects(Object_Type_ID)
Vance
on Apr 15, 2013, Igor Tandetnik
This is the create index statement:
CREATE INDEX IF NOT EXISTS
"DTC_Statuses_1_index" ON
"DTC_Statuses" ("Object_ID");
For some reason, when I execute analyze, my software locks up somewhere
when running its test queries. I'll delve further into this.
Vance
on Apr 15, 2013, Igor Tandetnik
On Mon, Apr 15, 2013 at 10:04 PM, Dominique Devienne wrote:
> On Mon, Apr 15, 2013 at 9:45 PM, wrote:
>
>> Igor,
>>
>> As a follow up, I added the other two indexes with no real inprovement.
>> Here is the new explain query plan:
>> 0|0|6|SEARCH TABLE
On 15 Apr 2013, at 9:22pm, ven...@intouchmi.com wrote:
> This is the create index statement:
> CREATE INDEX IF NOT EXISTS
> "DTC_Statuses_1_index" ON
> "DTC_Statuses" ("Object_ID");
>
> For some reason, when I execute analyze, my software locks up somewhere
> when running its test queries.
On 4/15/2013 4:31 PM, Simon Slavin wrote:
I also note this:
WHERE ((([Object_Types].[Object_Type])="DTC"))
This is technically incorrect. It should use single quotes around DTC, not
double quotes.
Actually - does any of the tables participating in the statement have a
column named DTC,
On 15 Apr 2013, at 9:40pm, Igor Tandetnik wrote:
> On 4/15/2013 4:31 PM, Simon Slavin wrote:
>> I also note this:
>>
>>> WHERE ((([Object_Types].[Object_Type])="DTC"))
>>
>> This is technically incorrect. It should use single quotes around DTC, not
>> double quotes.
>
>
The integrity check came back with OK.
My software was not locked up totally. It did finally come back. For some
reason executing analyze slow other queries way down.
Here is the new explain query plan:
0|0|6|SEARCH TABLE Object_Types USING COVERING INDEX
sqlite_autoindex_Object_Types_1
I corrected the "DTC" to 'DTC' and undid the analyze since that seemed to be
slowing things down quite a bit.
Unfortunately, the speed did not improve.
Here is the latest explain query plan:
0|0|6|SEARCH TABLE Object_Types USING COVERING INDEX
sqlite_autoindex_Object_Types_1 (Object_Type=?)
On 15 Apr 2013, at 9:47pm, ven...@intouchmi.com wrote:
> The integrity check came back with OK.
Good.
> My software was not locked up totally. It did finally come back. For some
> reason executing analyze slow other queries way down.
I am surprised by anyone reporting that ANALYZE has
SQLite was much slower on many queries AFTER analyze was executed.
here are the referenced tables, # of rows, primary key, and additional indexes:
Objects = 5495 rows, Primary key=Object_ID, other indexes on Address_ID,
Dimension_ID, DisplayFormat_ID, Limit_ID, and Object_ID;
DTC_Statuses = 5234
Thank you for clarification. I'll forward this to the original
reporter and add a note about this to our library.
Regards,
Kenichi Ishigaki
2013/4/14 Jay A. Kreibich :
> On Sat, Apr 13, 2013 at 11:25:41PM +0700, Dan Kennedy scratched on the wall:
>> On 04/13/2013 11:22 PM,
24 matches
Mail list logo