Re: [sqlite] Size of the SQLite library

I have to agree with Bob! We have considered SQLITE for our project. Going over 500Kbytes puts it just beyond the size of our Flash - the current Firmware. Vance On 2018-05-31 11:04, Bob Friesenhahn wrote: > On Thu, 31 May 2018, R Smith wrote: > >> Nice idea, but to be honest, I can't

Re: [sqlite] Best way to implement Find commands

I should have also noted that in most cases the original query is a complicated multiple join statement, not a simple table query so there is no rowid that I can rely on unless I do generate a temp table. Vance on May 24, 2013, ven...@intouchmi.com wrote: > >Thanks to James, Keith and Michael

Re: [sqlite] Best way to implement Find commands

Thanks to James, Keith and Michael for your input! I don't have any control over the original query. It may or may not include an ORDER BY clause. Ideally it would, which makes the question about repeated result order moot. I was hoping that by making the exact same query to build a temporary

[sqlite] Best way to implement Find commands

I've got a prepared statement from a original query that may have been stepped x times to row X. I want to implement the functions: FindFirst - find the first row (between row 1 and the last available row inclusive) that satisifies a new query. FindLast - find the last row (between row 1 and

Re: [sqlite] Is there a way to select a precision?

You could always store the precision info in another column or two. Vance on May 06, 2013, Paul van Helden wrote: > >> A delared type of NUMBER(10,2) has NUMERIC affinity, which means that >> SQLite will attempt to store (string) values as integers first and floats >>

Re: [sqlite] Help speed up query

Simon and everyone who has thrown ideas into the mix, I very much appreciate the effort that you folks have put into this! If nothing else, I am learning from this exorcise. I set the page_size to 4096 and cache_size to 1024. (I picked the next binary size above the 2976 KB size of the DB under

Re: [sqlite] Help speed up query

I tried transfering the database to an in memory copy and running the queries. It is actually a little slower but not much. I'm sure there are tables that are not being accessed and so loading them into memory would add to the time. Vance on Apr 16, 2013, ven...@intouchmi.com wrote: > >Yes,

Re: [sqlite] Help speed up query

Yes, the DBs are on my local disk. The quoted times are after the first run so mostly in cache. It takes about twice the time the first run for both SQLite and MS Access. Vance on Apr 16, 2013, Simon Slavin wrote: > > >On 16 Apr 2013, at 2:32pm, ven...@intouchmi.com

Re: [sqlite] Help speed up query

removing the parenthesises (what is the plural?) made no difference to the query plan which is: 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 Objects USING INTEGER

Re: [sqlite] Help speed up query

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

Re: [sqlite] Help speed up query

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=?)

Re: [sqlite] Help speed up query

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

Re: [sqlite] Help speed up query

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

Re: [sqlite] Help speed up query

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 >

Re: [sqlite] Help speed up query

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

Re: [sqlite] Help speed up query

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

Re: [sqlite] Help speed up query

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|

Re: [sqlite] Help speed up query

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,

[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

Re: [sqlite] When is column type valid?

Yes, it does work after step and before get column value. Thanks for your help in this. A quick follow up: The call to sqlite3_col_decltype returns a char * Should I issue a sqlite3_free on it when I am done with the string? Vance on Apr 10, 2013, Igor Tandetnik wrote: >

Re: [sqlite] When is column type valid?

Sorry, I did mean sqlite3_column_decltype. I repeated my test with sqlite3_column_type and doing the call after a successful call to sqlite3_column_text still always yields SQLITE_NULL. When changing the call to sqlite3_column_decltype, I do get the column declarations. So, all I need to do is

Re: [sqlite] When is column type valid?

Igor, Yes, I called sqlite3_column_type before and after each sqlite3_step in order to determine when it might work and it always came back as SQLITE_NULL (5). Incidentally, the SQLite version is "3.7.16.1". Vance on Apr 09, 2013, Igor Tandetnik wrote: > >On 4/9/2013 8:01

Re: [sqlite] When is column type valid?

I am using sqlite3_column_type. I'l try sqlite3_column_dectype tomorrow. I'm hoping that the column type follows the intermediate result set from joins and views. It looks like I have to be sure SQLITE_ENABLE_COLUMN_METADATA is set. Vance on Apr 09, 2013, Simon Slavin

[sqlite] When is column type valid?

Hello, I've done a query via a view. The column names that I retrieve are good. But, the column types are not. They are always set to SQLITE_NULL (5) whereas the values are either integers, or strings and no null values. Is there a way to obtain data type? Vance

Re: [sqlite] create view doesn't work

Simon, The first select was the one that I retrieved from the MS Access DB. That one does NOT work in SQLite. Thanks for explaining why it wasn't acceptable by SQLite. It was the second select, which I rewrote, that I was asking about. That one is acceptable by SQLite. My question was if

Re: [sqlite] create view doesn't work

As a follow up, what I meant to ask is why it doesn't work. I rewrote the original select: SELECT "Categories"."Category_Name", "Strings"."Long_Text", "Units"."Long_Units", "Limits"."Lower_Limit", "Limits"."Upper_Limit" FROM Objects INNER JOIN (Categories INNER JOIN Object_Categories ON

Re: [sqlite] create view doesn't work

Dominique, Thanks for your response and for breaking down the select into manageable pieces! According to the limits info, there should be no problem with the # of terms in that select statement (< 500). There are hundreds of these predefined queries that I am extracting out of the MS access

[sqlite] create view doesn't work

Hello, Using sqlite3, I entered this create view statement: CREATE VIEW IF NOT EXISTS "Annies Form" AS SELECT Categories.Category_Name, Strings.Long_Text, Units.Long_Units, Limits.Lower_Limit, Limits.Upper_Limit FROM Objects INNER JOIN (Categories INNER JOIN Object_Categories ON

Re: [sqlite] Bullzip ms Access to MySql

Simon, Thanks! Vance on Mar 07, 2013, Simon Slavin wrote: > > >On 7 Mar 2013, at 5:51pm, ven...@intouchmi.com wrote: > >> Simon, >> >> Thanks for the detailed response! > >You're welcome. > >> Can I say: >> Create INDEX c on myTable(c); > >Well, technically yes you can.

Re: [sqlite] Bullzip ms Access to MySql

Simon, Thanks for the detailed response! Can I say: Create INDEX c on myTable(c); Vance on Mar 07, 2013, Simon Slavin wrote: > > >On 7 Mar 2013, at 2:51pm, ven...@intouchmi.com wrote: > >> I haven't dealt with indexes in SQLite in the past, so a couple of follow up >>

Re: [sqlite] Bullzip ms Access to MySql

It is more than that. I tried removing your suggestion, then removed AUTOINCREMENT. Then it complained about the INDEX clause. I'm working on a program to rearrange things like moving the Primary key declaration to the field constraint clause, moving the index declarations to index creation

[sqlite] Bullzip ms Access to MySql

Hi, I have been trying to use Bullzip's Access to MySql converter to generate a SQL file that I can import into SQLite. I'm using SQLite Manager under FireFox to import the resultant SQL file. First, has anyone successfully done this. Is there a better way. A couple of my problems involve