Re: [sqlite] Performance Issue on Large Table

[Olaf Schmidt]

Am 23.02.2020 um 20:23 schrieb Richard Damon:


An amount of 140 tables in such a "BibleVersions.db" is not
uncommon and can be managed by SQLite in a good performance.


I'm not sure that form of division would be good. One basic rule of 
database normalization is that you don't break-up data based on the 
value of one of the fields ...


Sure, but FTS-"Tables" are in a "special category" (IMO)...

At least, I'd separate them "by language", because it does
not really make sense to me, to stuff e.g. the 31102 verses
of a japanese Bible-version into the same FTS-index,
where already one (or more) english versions reside.

Between japanese and english that's obvious already at the
"Unicode(Point)-level" - but even among "Latin-versions"
(e.g. english and german ones) there might be different
stemmer-algos to consider, to give optimal search-results.

Olaf

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance Issue on Large Table

[Olaf Schmidt]

Am 21.02.2020 um 02:24 schrieb Chip Beaulieu:
I have a table with 4.5 million records with full text indexing. 
 > Reads are very fast, but deleting / inserting / updating
takes on average about 50 seconds per record. 
I often do batches of 30,000 deletes / inserts at a time. 
The last batch took 10 hours to complete.


These 30,000 deletes+inserts indicates that these were the
verse-records of a complete Bible-Content, right?

And your 4.5Mio records total indicate, that you have about
140 different Bible-versions in your DB?


I suspect it’s got something to do with the triggers more than the indexes.

Could be - but my guess is, that your primary DB-changes are caused
by "deleting or inserting verses of whole bible-contents".

So why not handle (hold) each bible in a separate table?

An amount of 140 tables in such a "BibleVersions.db" is not
uncommon and can be managed by SQLite in a good performance.

Inserting "your next Bible-Version" into it would be fast,
because you're creating separate Tables for that job.
Same thing for "deleting Bible-versions" (just 2 "Drop Table"-calls).

You might argue, that the perfomance will suffer - especially
when you're trying to find "differences between two versions"...

But that is only a matter of a Join (via VerseID) on
the two Tables you want to "get a Verse-Diff for".

I've played that through with two different "Bibles":
- Bible12.txt and Bible13.txt (each one about 5MB unzipped)
- downloadable here: http://www.gutenberg.org/files/30/old/

The 31,102 "verse-records" each of the two text-files contains,
can be parsed and inserted into separate FTS-Tables in about:
- 0.5sec each (incl. text-parsing)

So, the total import-time for both (Bible12 and Bible13) was ~ 1sec.

That's far below your timings for "inserting a single verse-record".

FWIW, here is the SQLite-DB, my imports have produced (about 7MB)
http://vbRichClient.com/Downloads/BibleVersions.zip

It contains 4 Tables now (after importing the 2 text-files):
- Books_Bible12 (66 records) + FTS_Bible12 (31,102 records)
- Books_Bible13 (66 records) + FTS_Bible13 (31,102 records)

The schema-defs for those "Books_xxx" and "FTS_xxx" tables are:
"Create Table  (BookID Integer Primary Key, Title Text)"
"Create Virtual Table  Using FTS5(Scripture, tokenize=porter)"

No extra-indexes are needed... (IMO)

So, whilst the Books-Table was defined "quite normally" -
the FTS-table only contains a single Field ("Scripture")...
which raises the question, how to efficiently join 2 FTS-tables
(e.g. to find "differences in the Scripture-fields").

Well, in the recent Bible-TextFiles (from project Gutenberg),
theres already a "verse- or scripture-id" which is unique:
01:001:001 (two digits for BookNr, 3 for ChapterNr and 3 for VerseNr)

This Text-based (unique) ID can be easily transformed
to an Int-Value of the form: 01001001 - and that's what
I did whilst inserting into the FTS-Tables (filling their RowID-fields)

So, a later Join, to find scripture-differences between:
- FTS_Bible12 and FTS_Bible13 contents
becomes possible - e.g. this way:

Select T1.RowID, T1.Scripture, T2.Scripture
From FTS_Bible12 T1 Join FTS_Bible13 T2 On T1.RowID=T2.RowID
Where T1.Scripture <> T2.Scripture

It will (on the given example-DB above) find 8 records,
where scripture-content differs (after about 50msec or so).


For those interested, below is the import-Code I've used - which
will run as either VBScript - but also within VBA or VB5/6...
(it reads like "pseudo-code", so porting to other languages is easy):

'**depends on the COM-wrapper for SQLite (available at vbRichClient.com)
Function ImportInto(oMemDB, sTblNameBooks, sTblNameFTS, sBibleContent)
  On Error Resume Next

  With oMemDB
.BeginTrans
.Exec "Create Table " & sTblNameBooks & " (BookID Integer Primary 
Key, Title Text)"
.Exec "Create Virtual Table " & sTblNameFTS & " Using 
FTS5(Scripture, tokenize=porter)"


Dim sCmdB: sCmdB = "Insert Into " & sTblNameBooks & " (BookID, 
Title) Values(?,?)"
Dim sCmdV: sCmdV = "Insert Into " & sTblNameFTS & "(RowID, 
Scripture) Values(?,?)"


Dim L, VID, S
For Each L In Split(sBibleContent, vbCrLf) 'enumerate the Lines (in L)

  Select Case InStr(L, " ") 'check the Pos of the first Space-Char in L

Case 1  'we are probably still in a verse-block...
  If Len(VID) Then S = S & Mid(L, 11) 'Ok, concat that Verse-Part

Case 11 'a potential Verse-Idx-Line
  If InStr(L, ":") = 3 Then VID = Left(L, 10): S = Mid(L, 12)

Case 0  'probably an empty Line (as in the end of a Verse-Block)
  If Len(VID) Then
.ExecCmd CStr(sCmdV), CLng(Replace(VID, ":", "")), S 'RowID 
+ Scripture

 VID = "" 'reset the VID for the next verse
  End If

Case 5 'it's a potential Book-Line
  If Left(L, 4) = "Book" And Len(L) < 40 Then
 .ExecCmd CStr(sCmdB), CLng(Mid(L, 6, 2)), Trim(Mid(L, 10)) 
'BookID + Title

  End If
  End 

[sqlite] SQLITE_HAS_CODEC

[Olaf Schmidt]

Have just seen (in https://www.sqlite.org/src/timeline),
that this compiletime-option was removed (2020-02-07).

Speaking as a wrapper-author...

Are there alternatives in sight, to further support:
- a "ReKey"-feature in a relatively compatible manner,
  without breaking existing (user-)code, which currently does
  make use of "already existing, encrypted DBs" out there?

Background:
I've implemented this in the COM-wrapper for SQLite over
a decade ago - in a compatible way to what was once used
in the .NET-wrapper (at a time, where Robert Simpson was
still developing and maintaining it).

We had kind of an agreement, to not "torpedo" the income-
generating official crypto-module of SQLite, by sticking
to the weaker RC4-Stream-Cipher (being "decent enough"
for "home-usage and non-critical-businesses", IMO).

IIRC - 2 or 3 years ago, I've re-checked sources of the now official
.NET-wrapper - and it seemed that the compatibility (regarding that
"weaker" crypto-support) between the two wrappers was still there
(not sure, if that is still the case with the most recent .NET-wrapper).

In case the feature is still in the .NET wrapper - what is planned
(regarding the "ReKey-feature", and further support for already
existing encrypted DBs, which were encoded with the feature)?

Is there a chance, that the SQLITE_HAS_CODEC compile-switch
can be "re-activated" (to be able to update our wrappers
to newer SQLite-versions without larger efforts)?

If not, could a new-written VFS-module with such "weaker crypto-
support" be imlemented in a way, that it will not break existing
DBs out there?

Kind Regards,

Olaf

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] New word to replace "serverless"

[Olaf Schmidt]

Am 28.01.2020 um 12:18 schrieb Richard Hipp:

On 1/28/20, Howard Chu  wrote:


Wait, really? 
AFAICS embedded means in-process, no IPC required to operate.


Things like MySQL-embedded and H2 run a "server" as a thread instead
of as a separate process.  Clients then use Inter-Thread Communication
rather than Inter-Process Communication to send their queries to, and
get their results from, the database thread.  So this is really the
same thing as a server using IPC except that the server runs in the
same address space as the client.  The point of using the term
"serverless" is to indicate that SQLite does not work that way.


I've always found "serverless" kinda "misleading" (technically).

Technically, SQLite is "embeddable InProcess".

And thus such a Process (similar to what was described for MySQL-
embedded above), could "very well be a DB-Server itself".

Your own Fossil-engine would be a good example for such a Server-App,
since it allows (very server-like):
- concurrent access of multiple Users via sockets...
- to access and manage data, which is stored in an SQLite-DB-File

I guess it boils down to "what amount of config- or coding-efforts"
are needed, to produce/compile a true ServerProcess with the help
of an embeddable "InProcess-Library".

E.g. my COM-wrapper for SQLite is implemented as a Dll as well
(and thus "embeddable InProcess") - but it allows with only two
lines of UserCode (just starting a COM-Lib-internal, threaded socket-
listener), to transform "any Host-Process" into an SQLite-Resultset-
read/write-supporting AppServer (which in many concurrent scenarios
outperforms MySQL and also the MS-SQLServer).

With the WAL-extension SQLite is usable in many different (concurrent)
Server-scenarios (and "Server-Processes") ...with a relative small
amount of "extra-code".

So in that sense, the word "serverless" kinda suggests that "special
usecase only" - which is not (only), what SQLite is used for in reality.


Olaf

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Standard deviation last x entries

[Olaf Schmidt]

Am 18.10.2019 um 19:45 schrieb Bart Smissaert:


Regarding:

With r(s2, s, i) As (Select 2, 1, 1 Union All
Select s2, (s2>0)*(s+s2/s)*.5, i+1 From r Where Abs(s2-s*s)>1e-12
Limit 32
) Select s From r Order By i Desc Limit 1

How would this work if I wanted to update all the values in a table column
to have the square root?


Well, as with any other (single-value-returning) Sub-Select
(which was enclosed in parentheses)...

E.g.
 "Update MyTable Set MyCol = (Select 1)"
would update MyCol with the value 1 across the whole table...

Same thing basically (only "a bit larger") with a CTE-based
"Single-Value-SubSelect"...

BTW, I've updated and tuned the thing a bit (which should now
offer more precision, and ~20% more performance as well):

With  r (x, y, i) As (
  Select ?, 1, 1  Union All
  Select x,(x>0)*(y+x/y)/2 yi,i+1 From r Where Abs(yi-y)>1e-12 Limit 32
) Select y From r Order By i Desc Limit 1

Note the question-marked "Parameter" for the "squared Input-Value"
(in the first Select Statement of the CTEs triple).

Integrated into an Update-Query it could look this way:

Update MyTable Set MySquareRoot = ( -- SubSelect-Opening-Paren...
 With  r (x, y, i) As (
   Select MySquaredValue, 1, 1  Union All
   Select x,(x>0)*(y+x/y)/2 yi,i+1 From r Where Abs(yi-y)>1e-12 Limit 32
 ) Select y From r Order By i Desc Limit 1
) -- SubSelect-Closing-Paren...

HTH

Olaf

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Standard deviation last x entries

[Olaf Schmidt]

Am 12.10.2019 um 16:47 schrieb Bart Smissaert:

Sorry, I forgot to tell that. It is date column with an integer number.

ID xValue xDate

1   130  40123
1   120  41232
1   140  40582
1   100  40888
1   110  42541
2   140  41225
2   130  41589
2   150  40872


Because SQLite on Android has at least CTEs available,
(since Android 5 or so), I thought I make an attempt using them...

For your above sample-set I've created a table Test this way
(with slightly changed Column-Names + different IDs with "gaps"
+ a few more sample-records, to cover especially n=1 sets):

Create Table Test(ID, xVal, xDat);

Insert Into Test Values(3, 130, 40123);
Insert Into Test Values(3, 120, 41232);
Insert Into Test Values(3, 140, 40582);
Insert Into Test Values(3, 100, 40888);
Insert Into Test Values(3, 110, 42541);

Insert Into Test Values(5, 140, 41225);
Insert Into Test Values(5, 130, 41589);
Insert Into Test Values(5, 150, 40872);

Insert Into Test Values(6, 110, 41225);
Insert Into Test Values(6, 115, 41227);

Insert Into Test Values(9, 105, 41225);

After the above Table-Creation + Inserts, one should
define a "Base-View" first, which is then able to select
appropriate aggregates (respecting your "max-4" condition):

Create View vw_StdDev_Max4_Aggregates As
Select ID, Avg(xVal) a1, Avg(xVal*xVal) a2, Count(*) n From Test T
Where xDat In (Select xDat From Test Where T.ID=ID Order By xDat Desc 
Limit 4)

Group By ID;

Only this view above has to be adjusted, according to your
real table- and column-names, but the following two "derived views",
will work after these changes as well, as long as you leave the
above view-name - and the field-names: (ID, a1, a2, n) intact.

So what remains, is two more view-defs (one for the population-StdDev -
and one with a few slight changes, to cover the sample-StdDev as well.

Create View vw_StdDev_Samples As
Select ID, n n_SampleSize, (a2-a1*a1)*n/(n-1) "s^2", (
  With r(s2, s, i) As (Select (a2-a1*a1)*n/(n-1), 1, 1 Union All
Select s2, (s2>0)*(s+s2/s)*.5, i+1 From r Where Abs(s2-s*s)>1e-12 
Limit 32

  ) Select s From r Order By i Desc Limit 1
) s From vw_StdDev_Max4_Aggregates Where n>1
Union All
Select ID, n, Null, Null From vw_StdDev_Max4_Aggregates Where n=1;

Create View vw_StdDev_Populations As
Select ID, n n_PopulationSize, (a2-a1*a1) "sigma^2", (
  With r(s2, s, i) As (Select (a2-a1*a1), 1, 1 Union All
Select s2, (s2>0)*(s+s2/s)*.5, i+1 From r Where Abs(s2-s*s)>1e-12 
Limit 32

  ) Select s From r Order By i Desc Limit 1
) sigma From vw_StdDev_Max4_Aggregates Where n>1
Union All
Select ID, n, 0, 0 From vw_StdDev_Max4_Aggregates Where n=1;


Ok, here the result I get on my test-table, when the 3 views
are in place, and the following gets executed:

Select ID, n_PopulationSize, sigma From vw_StdDev_Populations;
ID  n   sigma

3   4   14.7901994577491
5   3   8.16496580927733
6   2   2.5
9   1   0


Select ID, n_SampleSize, s From vw_StdDev_Samples;
ID  n   s

3   4   17.0782512765993
5   3   10.1
6   2   3.53553390593274
9   1   null

Please note the returned null (vs. the real 0) in the last result
(done, because "unbiased estimates" are undefined for sample-size == 1)

Finally for those interested, the CTE-based "iterated Square-Root",
cut out for an isolated calculation of sqrt(2).

With r(s2, s, i) As (Select 2, 1, 1 Union All
  Select s2, (s2>0)*(s+s2/s)*.5, i+1 From r Where Abs(s2-s*s)>1e-12 
Limit 32

) Select s From r Order By i Desc Limit 1

The max. iterations are limited to 32 - though for input-values
in a "typical range", the iteration will usually exit earlier...
In case of the example above -> sqrt(2), the iteration-counter
i reached only 6 (as can be seen, when you include i alongside s
in the final Select.

With r(s2, s, i) As (Select 2, 1, 1 Union All
  Select s2, (s2>0)*(s+s2/s)*.5, i+1 From r Where Abs(s2-s*s)>1e-12 
Limit 32

) Select i, s From r Order By i Desc Limit 1

HTH

Olaf



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Table was deleted on macOS

[Olaf Schmidt]

Am 15.10.2019 um 23:53 schrieb Simon Slavin:

... There is no reason for a table to disappear.


But sometimes intent... ;-)

Maybe one of the App-Users is an xkcd-fan...
https://xkcd.com/327/

@the OP
Don't tell us now, that the table in question
was indeed named "Students"... 

Olaf

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Windows app to read SQLite DB, and launch web browser when dbl-clicking a record?

[Olaf Schmidt]

Am 19.10.2018 um 16:02 schrieb Winfried:

I have a bunch of hyperlinks in an SQLite database, and need to read 
each page for validation before deleting the record.
To make it easier, the DB manager should launch the default web browser 
when I double click on a column that contains a hyperlink.


Before I build a GUI, is there a Windows SQLite database manager 
that can do this?


Am a bit late here - but perhaps it's interesting for others as well...

Since you need it for Windows - why not write a little tool for that -
using VBScript (*.vbs Files), which are supported on all current Win-OS.

VBScript interacts nicely with COM-libraries (less so with "flat-Dlls"), 
via CreateObject("Lib.Class") - so your question basically boils down to:

- is there a COM-lib for SQLite
- is there a COM-lib which also supports GUI-development
- does this GUI-support include DataGrid-Bindings to SQLite-ResultSets
(to keep the efforts low)...

Well, yes there is: http://vbRichClient.com/#/en/Downloads.htm
(a COM-framework, which wraps SQLite - as well as cairo for its GUI-
and drawing-stuff).

Whilst the above Link is targetting VBA and VB6-developers primarily,
an adaption with decent support for VBScript-development is here:
http://vbRichClient.com/Downloads/ScriptGUI5.zip

The above Zip contains several *.vbs examples in its Root-Folder -
and already includes all the needed libs in a \Bin\-SubDirectory.

The tool allows after unzipping, a development "right-out-of-the-box"
(it does not require any Setup when running on Win-Systems > XP) -
and the frameworks size is comparably small (about 3MB zipped).

I've included a solution to your problem already in two versions:
(both demos are using a dynamically created "Urls"-DB and -Table)
- SQLite_DataGrid_Simple.vbs
- SQLite_DataGrid.vbs

Here is a ScreenShot of the complete Code for the simple version:
http://vbRichClient.com/Downloads/ScriptGUI5.png

And here one of the running *.vbs-App from the "enhanced version"
(which has only a dozen more lines of code than the simple one):
http://vbRichClient.com/Downloads/SQLiteDataGrid.png

Help-Files which describe the interfaces of the Framework-
Classes are contained as *.chm in the Zips \Doc\ Subdirectory.

HTH

Olaf

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how into insert row into middle of table with integer primary key

[Olaf Schmidt]

Am 22.11.2017 um 01:29 schrieb Jens Alfke:


When I’ve run into this before, the requirement has been to support lists with 
customizable ordering, like an outliner where the user can freely drag the rows 
up and down.


Yep.
And therefore such cases should be handled at the App-Level IMO...

There's a lot of ways to approach that - one that comes to mind
(since JSON is in the meantime standard in App-development),
is to store such "orderable Groups" in their own JSON-Blob-DBFields
(as simple Text - serialized into JSON-Array-format for example).

E.g. when we assume that any given "fruit-salad" is stored as
a single record (a single Blob) in a table "recipes", then
this could look like the following VB-Code...

(which interested users could paste e.g. into an Excel-VBA-Module,
after installing and referencing the vbRichClient5-COM-wrapper
for SQLite):

Private Cnn As cMemDB, SQL As String

Sub Main()
  Set Cnn = New_c.MemDB 'create an SQLite InMemory-DB-Instance
  Cnn.Exec "Create Table Recipes(ID Integer Primary Key, R Text)"

  InsertNewRecipe MakeRecipe("apple", "pear", "kiwi") 'insert 1st record

  Dim R As cCollection  'at App-Level, a Recipe is a Collection
  Set R = GetRecipeByID(1)  'retr. the above inserted Record by ID
  R.Add "banana", Before:=1 'add banana before Index 1 (pear)
  UpdateRecipe 1, R 'write the new content of R back into the DB (ID 1)

  'check, whether the DB-update was successful, retr. a Collection by ID
  Debug.Print GetRecipeByID(1).SerializeToJSONString

  'search-queries against the JSON-content are possible per Like...
  SQL = "Select R From Recipes Where R Like '%banana%'"
  Debug.Print Cnn.GetRs(SQL)(0)

  'or when the SQLite-JSONExtension is available, it will allow
  'to query the contents of JSON-fields more specifically...
  SQL = "Select R From Recipes Where json_extract(R,'$[1]')='banana'"
  Debug.Print Cnn.GetRs(SQL)(0)
End Sub

The above prints out (the same thing from all 3 Debug-Statements):
["apple","banana","pear","kiwi"]
["apple","banana","pear","kiwi"]
["apple","banana","pear","kiwi"]

The critical line in the above main-code (which makes handling
the issue per SQL obsolete) is: -> R.Add "banana", Before:=1
(most Array-, List- or Collection-Objects allow such Inserts inbetween,
 no matter which programming-language).


'-- the needed Helper-Functions for the above Main-Routine --
Function MakeRecipe(ParamArray PA()) As cCollection
  'returntype of a new Recipe is a JSON-Array-(in a cCollection)
  Set MakeRecipe = New_c.JSONArray
  Dim P: For Each P In PA: MakeRecipe.Add P: Next 'copy-over-loop
End Function

Sub InsertNewRecipe(R As cCollection)
  Cnn.ExecCmd "Insert Into Recipes(R) Values(?)", _
   R.SerializeToJSONString
End Sub

Function GetRecipeByID(ByVal ID As Long) As cCollection
  Dim sJSON As String 'first retrieve the JSON-String by ID
  sJSON = Cnn.GetSingleVal("Select R From Recipes Where ID=" & ID)
  'deserialize sJSON into a cCollection
  Set GetRecipeByID = New_c.JSONDecodeToCollection(sJSON)
End Function

Sub UpdateRecipe(ByVal ID As Long, R As cCollection)
  Cnn.ExecCmd "Update Recipes Set R=? Where ID=?",_
   R.SerializeToJSONString, ID
End Sub


Olaf

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Small Performance Regression (3.21.0)

[Olaf Schmidt]

Am 28.10.2017 um 23:00 schrieb Richard Hipp:

On 10/27/17, Olaf Schmidt <n...@vbrichclient.com> wrote:

The new CoRoutine-approach seems to slow down certain
ViewDefinitions (in comparison to running a Query directly).


Can you please download and try the latest "Prerelease Snapshot" from
https://sqlite.org/download.html and let me know whether or not it
clears your issue.  Thanks.


Yep - the issue is solved (just compiled the amalgamation-snapshot).
Query-times are now again identical (at 10msec) for the Invoices-View
and its SQL-representation.

Thank you for reacting that fast...

Kind Regards,

Olaf

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Small Performance Regression (3.21.0)

[Olaf Schmidt]

Am 28.10.2017 um 19:22 schrieb Bart Smissaert:

This is interesting, not so much for the performance regression, but for
the fact that the posted Northwind database has
spaces in table names and view names. I wasn't aware this is allowed 
and it caused a lot of errors in my app. I have this nearly fixed now 
(by adding the square brackets),...


More SQL-Standard-like would be DoubleQuote-chars instead of the
SquareBrackets, but thankfully SQLite allows for [...] (as well
as for SingleQuotes around column-names).


...I am left with one problem. This is the fact that for example the view
Product Sales for 1997 has a UDF in the view definition: DatePart.
Obviously this is causing an error:
no such function: DatePart


Yep, sorry about that - the SQLite-COM-wrapper (vbRichClient5.dll)
opens an SQLite-DB with nearly the whole set of MS-JET-engine compatible
Functions (DatePart, DateDiff, Left$, Right$, Mid$, Format$, Instr etc.)
by default (though there is a Parameter in the OpenDB-calls which allows
to switch that off).

The Northwind-DB (introduced by MS as a Demo-DB with MS-Access a
few decades ago) was used deliberately, to show the quite painless
migration from the JET-engine to the SQLite-COM-wrapper in some
Demo-Apps (including nearly compatible View-Defs, along with
compatible Jet-SQL-Functions).

I know there is the compile option SQLITE_ENABLE_UNKNOWN_SQL_FUNCTION, 
but will only avoid the error for

EXPLAIN and EXPLAIN QUERY PLAN.

Is there a way to handle this with a compile option or otherwise, so that
instead of an error a Null will be produced or just the
original value?


I for my part would *insist* on SQLite throwing an error, when
it encounters unknown (userdefined) functions or collations
in a Database, which a currently given wrapper-extension does
not support.

How else would you have been able, to stumble over the (for your
wrapper-lib) non-working view-definition?

The question it boils down to (using "VB-speak") is:
"Do we really want an 'On Error Resume Next'-behaviour in SQLite?"...
;-)

Olaf



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Small Performance Regression (3.21.0)

[Olaf Schmidt]

Am 27.10.2017 um 21:11 schrieb Richard Hipp:

Thanks for the report.

Do you have any other interesting, complex, or slow queries 
using your database that you can send me for testing purposes?


With regards to the NWind.db, the Invoices-View is the
one with the largest processing-time.
NWind.db is a direct Import from an (MS-JET-based),
NWind.mdb - and the View-Defs are nearly identical.

The MS-JET-Engine needs about 35msec for the Invoices-View BTW.
whereas SQLite 3.17 (for directly executed SQL, as well as the View):
- 14msec (before "Analyze" -> on a fresh created DB)
- 10msec (after  "Analyze" ran once against the DB-Connection)

and as said, with the new 3.21 I got:
- 24msec (before "Analyze", running the View)
- 15msec (before "Analyze", running direct SQL)
- 19msec (after  "Analyze", running the View)
- 10msec (after  "Analyze", running direct SQL)

Other (larger) File-DBs I use, are only large due to
FTS4/FTS5-Virtual-Tables - and there everything is
"fast enough" (typically I get my resultsets from
those in under 3msec).

Our main-usage of SQLite in Applications is local settings-storage
and InMemory-DBs (for "parking" larger resultsets App-internally,
which came in from https-WebRequests or over ADO->MS-SQLServer).

Kind Regards,

Olaf


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Small Performance Regression (3.21.0)

[Olaf Schmidt]

Am 27.10.2017 um 21:59 schrieb David Raymond:

Also getting 31ms for both...

Thanks for testing that guys...

I was able to get similar timings (about 30msec), when the
(quite large) resultset-output was delegated into a file...

To avoid doing expensive File-IO in that test, I'd recommend
to run it again with the two small Files (SQL1.txt and
SQL2.txt), which I've now included in the ZipFile...
Here again the Link: http://vbRichClient.com/Downloads/NWind.zip

With these two Files, I get the following results (on a Win8.1 machine):

sqlite> .read SQL1.txt
Run Time: real 0.000 user 0.00 sys 0.00
Run Time: real 0.010 user 0.00 sys 0.00
Run Time: real 0.000 user 0.00 sys 0.00
sqlite>

sqlite> .read SQL2.txt
Run Time: real 0.000 user 0.00 sys 0.00
Run Time: real 0.006 user 0.00 sys 0.00
Run Time: real 0.000 user 0.00 sys 0.00
sqlite>

Note: SQL1-Text is copying into an InMemory-Temp-Table
from the Invoices-View - whilst SQL2 is doing the same
from "directly given SQL" (the same as used for the ViewDef).

The timings in both cases vary about +-1msec -
the values as posted represent the median-values.
Not really factor 2 (as encountered when copying into an
inmemory-resultsets of my wrapper-library) - but nearly so.

Though looking at the explain output it looks for the 
view/subroutine/subquery version it's spending like extra machine 
steps copying every result row from one set of registers to another.


Yep, that Extra-copying of the RowValues seems to be the culprit
(the Select has a decent Field-Count - and these efforts
seem to sum-up over the scanned Records then).

Olaf

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Small Performance Regression (3.21.0)

[Olaf Schmidt]

The new CoRoutine-approach seems to slow down certain
ViewDefinitions (in comparison to running a Query directly).

FWIW, here's a download-link to an NorthWind-SQLite-DB, which
already contains certain view-definitions:
http://vbRichClient.com/Downloads/NWind.zip
(an "Analyze"-command was already run against that DB).

The one view-def (where the difference is most apparent) is [Invoices]:

Here a: Select * From Invoices
needs about 20msec.

Whereas running the SQL of the View-definition directly,
needs only about 10msec.

A simpler view-definition in the above zipped NWind.db is:
[Order Details Extended]

Which is defined with the following SQL:
SELECT * FROM Products JOIN [Order Details] Using(ProductID)
Order By OrderID

When run directly, it comes up with the result after ~4msec,
whereas: Select * From [Order Details Extended] needs ~6msec.

Using Explain, it showed that in case we run against the
ViewNames, the CoRoutine-approach was present, whereas when
running the SQL directly (and faster), the CoRoutine-preparation
was absent from the Explain-Output...

No biggie here so far - but reporting the behaviour early seemed
like a good idea to me...

Kind Regards,

Olaf


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bulk load strategy

[Olaf Schmidt]

Am 17.05.2017 um 19:08 schrieb David Raymond:


The unique index on DistinguishedName though is what gets used for that sub 
query of the insert, so most definitely keep that one index for the whole load. 
(The others can be left out until the end though)



I once had a similar scenario, and solved it with good speed -
by following Simons suggestion to Drop all indexes first -
and then I've "manually ensured Uniqueness" over a DB-
independent, normal HashList for fast "Exists-lookups".

After the import went through (with about 30 records/sec),
I've freed the HashList and recreated the Indexes on the DB.

Not sure though, how many unique "DistinguishedNames" Joseph
has to manage in his scenario (risking out-of-memory on the
Hash-Container).

Olaf

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to use sqlite from classic ASP with no installation...

[Olaf Schmidt]

Am 29.06.2016 um 07:52 schrieb Olaf Schmidt:

Sorry, the line below (of the Test-Script) does not match with the
little "folder-layout" I've mentioned in my previous post -
so it needs to be adapted to the concrete (absolute or relative)
path, where the manifest (along with the 3 Binaries) will be located.


SxS.Manifest = Server.MapPath("/Downloads/RC5/vbRichClient5.manifest")


...according to the Example-Folder-Layout (here it is again):

wwwroot/asp/
   TestSQlite.asp
wwwroot/asp/RC5/
   vbRichClient5.manifest
   vbRichClient5.dll
   vb_cairo_sqlite.dll
   DirectCOM.dll

the line above should have been:
SxS.Manifest = Server.MapPath("/asp/RC5/vbRichClient5.manifest")

Olaf

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to use sqlite from classic ASP with no installation...

[Olaf Schmidt]

Am 24.06.2016 um 10:48 schrieb Dominique Lagree:


I have a classic ASP application running on Windows / IIS to provide that would 
use an sqlite database.
...
How can we access to sqlite through an interface (oledb ?) that could be called 
from
classic ASP vbscript and could run just from a copy to the application forder 
or a standard
default driver or provider that would be available on a windows server 2003 ?



You cannot call any "Standard-dll" from *.asp or VBScript directly
(not without a COM-wrapper around that Dll).

And although ADO is available (as creatable COM-based Environment),
you will not be able to bring any ADO->ODBC-Connectionstring to
work (e.g. with Werners ODBC-driver), as long as this ODBC-driver
is not properly registered in the registry (to be recognizable
and loadable "by ConnectionString" with the ADODB.Connection-Object).

However, there might be a way out, since at least on IIS7 to IIS8 a:
<%
Response.Write CreateObject("Microsoft.Windows.ActCtx") Is Nothing
%>

... will report a nice "False" back into the Browser.

Not sure, which version of IIS comes on Win-2003-Server - and
whether Win-2003 already has support for the above ProgID of the
"Side-by-Side regfree-helper" -> "Microsoft.Windows.ActCtx"

So, you might want to check this out first on the Server in question
(another necessity is, that the IIS works in 32Bit-mode... on the
newer IIS-versions one can switch either the Default- or the
ASP-ApplicationPool into 32Bit-mode, no matter if the system is
a 64Bit one - but maybe the machine in question has already a
"native 32Bit Server-version" installed...

Well, if the Server supports the SxS-ProgId (is able to create an
Object), and the ASP-environment runs in a 32Bit-Pool - you could
then place the vbRichClient5-COM-wrapper for SQLite e.g. in a
SubFolder of your *.asp-Scripts - "XCopy-like" (without need for 
registry-writing or setups)... example of the folder-structure:


wwwroot/asp/
   TestSQlite.asp
wwwroot/asp/RC5/
   vbRichClient5.manifest
   vbRichClient5.dll
   vb_cairo_sqlite.dll
   DirectCOM.dll

Here's the download-page: http://vbRichClient.com/#/en/Downloads.htm

The TestSQLite.asp Script could contain for a short test:

<%
Response.Write "Simple SQLite-Demo: "

On Error Resume Next
'try to create the SxS-Object - and provide it with the RC5-manifest-file
Dim SxS: Set SxS = CreateObject("Microsoft.Windows.ActCtx")
SxS.Manifest = Server.MapPath("/Downloads/RC5/vbRichClient5.manifest")

'now create the "RC5-entry-point-object" first (a Constructor-ClassInstance)
Dim New_c: Set New_c = SxS.CreateObject("vbRichClient5.cConstructor")

'Ok, now we can create an SQLite-Connection-Object (deriving it from the 
New_c constructor)

Dim Cnn: Set Cnn = New_c.Connection
Cnn.CreateNewDB ":memory:" 'here we go with a Mem-DB for demonstration
'...an existing file-db would be opened per: Cnn.OpenDB 
Server.MapPath(".../your.db")


Cnn.Execute "Create Table T1(ID Integer Primary Key, Txt Text)"

Dim Rs: Set Rs = Cnn.OpenRecordset("Select * From T1 Where 1=0") 'open 
an empty Rs on T1

Rs.AddNew: Rs("Txt") = "foo"
Rs.AddNew: Rs("Txt") = "bar"
Rs.UpdateBatch 'commit the two inserts into the DB


Set Rs = Cnn.OpenRecordset("Select * From T1") 'now we select 
the whole table

Do Until Rs.EOF
   Response.Write Rs("ID") & ",  " & Rs("Txt") & ""
   Rs.MoveNext
Loop

If Err Then Response.Write Err.Description & "": Err.Clear
%>

HTH

Olaf

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Multiple in-memory database table query

[Olaf Schmidt]

Am 19.04.2016 um 20:34 schrieb Jarred Ford:
> Is it possible to create multiple in-memory databases and be able to access 
> tables with a single query between them?  For example, select * from 
> db1.dbo.table1 db1 join db2.dbo.table1 db2 on db1.x = db2.x.

Yes, that's possible over appropriate FileURIs:
https://www.sqlite.org/inmemorydb.html

Olaf

[sqlite] sqlite in vba7.1 and windows 8.1

[Olaf Schmidt]

Am 19.02.2016 um 14:31 schrieb Montes C?mara, Victor:

> I've been using sqlite in vba6 for some years
 > using RichClient3 framework of Olaf Schmidt (www.datenhaus.de)

The new site (which offers a new version of the RichClient,
as well as new versions of __stdcall-compiled SQLite... as
e.g. version 3.11 currently) is now hosted here:
http://vbRichClient.com/#/en/Downloads.htm

> Now I'm migrating my application to 64 bits using vba7
 > in Windows 8.1.
> The problem I'm facing is with the LoadLibrary function
 > to load dynamically the DLL DirectCOM.dll

DirectCOM.dll (as well as vb_cairo_sqlite.dll - and dito the
COM-wrapper vbRichClient5.dll) - are all 32Bit-PE-Binaries.

And 32Bit-Dlls cannot be loaded into a 64Bit-Process (in
your case the 64Bit-version of your Excel-installation).

This has (BTW) nothing to do with the 64Bitness of the OS,
which can run both kind of *Processes* (32Bit and 64Bit) -
it's simply the restriction that 32Bit-Dlls are loadable
only by 32Bit-Processes - and the same thing holds true
for the pairing of 64Bit-Dlls and 64Bit-Processes.

That's also the reason, why basically two (nearly identical)
sets of system-dlls exist - one in \System32 (for the 64-
Bit-Dlls) - and the other set in \SysWow64 (32Bit-Dlls).

As for "using 32Bit-Dlls in a 64Bit-Excel-Process" - that's
(indirectly) possible, when you will use a kind of "64Bit-to-
32Bit-Broker-mechanism" (by creating a 32Bit-Process, which
in turn then loads and instantiates the 32Bit-COM-Dll(s) -
and then allows cross-process-communication between 64Bit-
Excel and the 32Bit-Process (named e.g. MyAXHost32Bit.exe).

This is possible over COM (not only across processes, but
also across machines) by (auto-created) 64Bit-COM-Proxy-
instances in the 64bit-Process - which will then "marshal"
all the Method-Calls between the 64Bit-Proxy-Class and the
real Class-instance (the 32Bit-one, which is hosted by the
Broker-Process (your self-compiled AXHost32Bit.exe).

VB6 allows the creation of such a 32Bit ActiveX-Executable
quite easily - and it can be implemented even generically,
by just this little code in e.g. a Public cConstruct-Class:

Public Function GetInstanceByProgID(ProgID As String) As Object
   Set GetInstanceByProgID = CreateObject(ProgID)
End Function

As said, that's all the code in the little AXHost32Bit.exe
that is necessary.

With that you can create and access any 32Bit-COM-Dll(Class)
which is registered on the System from within a 64Bit(-VBA)-Process.

You can even use Early-Binding within 64Bit-VBA7 by selecting
the (32Bit-)lib over the VBA7-"References"-dialogue (e.g.
the one for the newer COM-SQLite-wrapper 'vbRichClient5')
This will *not* load the 32Bit-COM-Dll itself - but restricts
itself to a lookup in the Resource-Section of said Dll-File, to
read only the TypeLib-Information (Classes and their Method-Defs).

With these Typelib-Infos Excel-64Bit is then capable to
(auto-)instantiate the appropriate, interface-compatible
(64Bit-)Proxy-Class within Excel (which then does the auto-
marshalling of method-calls to the real Class-instance which
runs within AXHost32Bit.exe.

Sorry for the somewhat longer post, which is only indirectly
related to SQLite(-usage).
To get more details you might want to ask further questions
in a VB6-NewsGroup or on vbForums.com...

Olaf

Re: [sqlite] Feature Request: Binding Arrays

[Olaf Schmidt]

Am 31.10.2013 14:09, schrieb Dominique Devienne:

[Userdefined functions in conjunction with fast Exists-checks
in "Userland" - vs. SQLites built-in indexing in case of In (List)]


I'm not convinced by this. The "real table" can be quite large, several
100's to 100,000's rows (up to 1+ million rows) and col can be the primary
key, or a non-unique "parent" key where many parent keys have about 10 rows
each, and a few have in the 1000's, while the in-list could very small
(down to just 1 element) or quite large (several thousands).

With a function based approach, you are *always* full-scanning the whole
"real" table, no matter the cardinality of the InList operand, and even
with a very fast InList function, this is not going to beat getting 10 PK
rows, or 10 "parent" key rows (e.g. 100 rows to 10,000 rows) via indexes,
especially since these are virtual tables with Boost.MultiIndex unique or
non-unique indexes (i.e. 5x to 10x faster than SQLite's paged B-tree
indexes). It might well beat it if the InList operand cardinality is high,
as in your 40K and 60K testing in a 100K rows table, because an InList
that's 40% or 60% of the whole table is close enough to a full scan that
using a native code set or map test similarly outperforms SQLite's generic
paged B-tree indexes like our Boost.MultiIndex-based indexes.

Of course that's speculation on my part, versus your timed experimentation,
so could well be that I'm wrong. And I'll need to look into this eventually.



You're not wrong - although the UDF-timings in my previous post are
correct - it is true that they will remain (relatively) constant -
even in case we reduce the Count in the CompareList from 4 to
1000 or 100.

All timings with 10 records in the "real" table - FullTable-scan
due to using an UDF with a sorting-Dictionary-instance:
36msec (100 items in the compare-list)
43msec (1000 items in the compare-list)
48msec (1 items in the compare-list)
52msec (4 items in the compare-list)

The above was no surprise to me, because I'd expected that due to the
FullTable-scans in case of the UDF-approach... what came as a surprise
was the kind of "inverse-lookup" the SQLite-optimizer apparently
performs, when an index exists on the "real" table which provides
the Column-value to compare against the "In"-list.

In my large compare-lists (4 and 6) this behaviour didn't
become obvious in the timings whilst with 100 and 1000 items in the
compare-lists there was clearly a difference.

Again, all timings with 10 records in the "real" table -
the compare-list created beforehand in a tmp-table -
the table- and index-creation not included in the timings
SQL: Select Count(*) from T Where Col in Tmp

No indexes in the whole setup (not on the "real" table T and also
not on the Tmp-Table):
37msec (100 items in the compare-list)
47msec (1000 items in the compare-list)
84msec (1 items in the compare-list)
136msec (4 items in the compare-list)

With only an index on the Tmp-Table-Column:
37msec (100 items in the compare-list)
56msec (1000 items in the compare-list)..triple-checked, not an outlier
65msec (1 items in the compare-list)
77msec (4 items in the compare-list)

With only an index on the real table (on the compare-value-column):
0.4msec (100 items in the compare-list)
1.9msec (1000 items in the compare-list)
26msec (1 items in the compare-list)
116msec (4 items in the compare-list)

With both indexes (on the real table and the tmp-table):
Identical timings to the case above - apparently the index on
the real table was choosen in favour of the tmp-table-index -
which is the correct choice of the optimizer for all compare-list-counts 
below 3 or so (since with 4 the index

on the tmp-table performs clearly faster already).

So, my mistake was to choose too large compare-list-counts in
my first test-setup - otherwise it would have become obvious
that indexes on the original "real" table are indeed worthwhile.

This holds true for compare-listcounts smaller than about
a third of the total records in the original table.
An index on the Tmp-Table which holds the compare-list is
apparently only worthwhile above this compare-count.

The timings against a 10-records-table in a fulltable-
scan with the UDF (here again - this was on a intel i5 2.8GHz):
36msec (100 items in the compare-list)
43msec (1000 items in the compare-list)
48msec (1 items in the compare-list)
52msec (4 items in the compare-list)

are not that bad - and I'd guess (since the COM-SQLite-wrapper
I've used has some more overhead due to the Interface-delegation)
that there's perhaps 5msec to subtract compared with C/C++ UDFs -
and I can also imagine, that a nice Boost-Object can also out-
perform the SortingDictionary I've used (perhaps by 20-40% or so).

So, in a C/C++ setup I'd expect these values for a UDF
with a Boost-object for the exists-checks (rough estimate):
21msec (100 items in the compare-list)
26msec (1000 items in the 

Re: [sqlite] Feature Request: Binding Arrays

[Olaf Schmidt]

Am 29.10.2013 13:19, schrieb Dominique Devienne:


So, after those functions are in place - where's the problem with:

select * from table where InMySmallUnsortedArrayExists(some_column)
select * from table where InMyLargerSortedArrayExists(some_column)
select * from table where InMyHashListExists(some_column)
...
etc. for trees or whatever you want to use to speed-up the exists-check.

Or more generically with an additional Param:
select * from table where InList(@ListTypeEnmValue, some_column)



First off, when you use functions like this, you basically rule
out index use, even if some_column is indexed. That's not good.
<< WHERE col IN list>> OTOH, might use an index.


Might - yes, and *if* an index is used for the In-Checks, then
you're perhaps "wasting it" - or it could be the wrong index
which is choosen by the query-optimizer.

The better index (in case you use Tmp-Tables) is not the index
on col of the "real table", but the index on the Tmp-Table-Col.

However, in any case we have an exists-check to perform here,
(for every sqlite3_step) and the only question is, can your own
function perform this check faster than the built-in mechanism
of SQLite (when SQLite performs at its best, using an index which
was defined on the comparelist).

In my tests SQLite cannot outperform a well-implemented
"sorting Dictionary-Class", no matter if an index is in use
or not.

So, I would not dismiss the usage of UDFs in your special case
that fast - the UDF-implementation is dead-easy, more flexible
and with the Dictionary I was using, about 50% faster than
SQLite with pre-indexed Tmp-Tables (about factor 3.5 faster
than what you're currently using with the dynamic List-Joins).



Second, as I mentioned, it's the UI that's SQL-driven. You can have N lists
or tables or combos in various dialogs, all looking at the same underlying
(virtual) table but each will have its own selection, so it's N different
lists that need to be used, where N is not fixed (some dialogs can be
popped up several times too, different instances of the *same* dialog). So
creating a function or a temp table for each just isn't great either, and
forces to name what is inherently "anonymous" IMHO.



For exactly this "anonymous case" I've already proposed:
select * from table where InList(@ListTypeEnmValue, some_column)

Meaning, that you only need this single function instead of the
"more specialized ones" - at the "cost" of setting one additional
parameter - and reacting to that param within your UDF-callback.

You can precompile such kind of statement and use binding-calls,
to replace the @ListTypeEnmValue Param-Slot with an Integer-
value (or even a Pointer-Value) of your choice.

Below is my complete Testcode (sorry, no C-code - but I think you
will get the idea - and will deduce that the CommandObjects are
simply encapsulating the SQLite-Binding-Calls).

Here's the timing-values this Demo puts out (10 values, split
into two lists: [1 to 4] and the other one [40001 to 10]

UDF and Dictionary   103msec 4 6
Tmp-Tbls without Index   301msec 4 6
Tmp-Tbls with Index  151msec 4 6
Joined comma-sep-Lists   358msec 4 6

VB6-code:

Option Explicit

Implements IFunction

Enum enmDictType
  DictTypeNone
  Dict40K
  Dict60K
  '...
  DictTypeMax
End Enum

Private Cnn As cConnection, DictArr(DictTypeMax) As cSortedDictionary

Private Sub Form_Click()
Dim i As Long, Res1&, Res2&, Arr1$(1 To 4), Arr2$(40001 To 10)
Cls

  Set Cnn = New_c.Connection(, DBCreateInMemory) 'create a new InMem-DB
  Cnn.AddUserDefinedFunction Me 'add the Implementer of the new func
  Cnn.Execute "Create Table T(Col Integer)"

  With Cnn.CreateCommand("Insert Into T Values(?)")
   Cnn.BeginTrans
  For i = 1 To 10 'add values into T (fast, per Binding-API)
.SetInt32 1, i: .Execute
  Next
   Cnn.CommitTrans
  End With

  'two Temp-Tables (one for 40K, the other for 60K records)
  Cnn.Execute "Create Temp Table Tmp1(Col Integer)"
  Cnn.Execute "Insert Into Tmp1 Select * From T Where Col<=4"
  Cnn.Execute "Create Temp Table Tmp2(Col Integer)"
  Cnn.Execute "Insert Into Tmp2 Select * From T Where Col >4"

  'same preparations for our alternatives to the Tmp-Tables
  Set DictArr(Dict40K) = New cSortedDictionary
  Set DictArr(Dict60K) = New cSortedDictionary
  For i = 1 To 4
DictArr(Dict40K).Add i
Arr1(i) = i
  Next
  For i = 40001 To 10
DictArr(Dict60K).Add i
Arr2(i) = i
  Next

  'use a static precompiled query with a UDF (just different Params)
  New_c.Timing True
Res1 = DoQuery(Dict40K)
Res2 = DoQuery(Dict60K)
  Print "UDF and Dictionary", New_c.Timing, Res1, Res2

  New_c.Timing True
Res1 = GetCount("Select Count(*) from T Where Col in Tmp1")
Res2 = GetCount("Select Count(*) from T Where Col in Tmp2")
  Print "Tmp-Tbls without Index", New_c.Timing, Res1, Res2

  Cnn.Execute "Create 

Re: [sqlite] vbscript and sqlltie

[Olaf Schmidt]

Am 27.10.2013 08:41, schrieb Joe Fuller:


I’m trying t access sqllite database via vbscript.
I don’t want to use odbc just ado.net.


I'm pretty sure vbScript doesn't support .NET class-instancing directly,
vbScript wants COM-(Dlls, to instantiate classes from).


This is the sort of thing I’ve tried.

Set objConn = CreateObject("ADODB.Connection")
objConn.Open = "Provider=System.Data.SQLite; Data 
Source=C:\DATA\SQLLite\mydb.db3"

I get the error “Provider cannot be found. It may not be properly installed”


With your above: CreateObject("ADODB.Connection")
you create a "classic ADO" (COM) Connection-instance.

Well, what I do know for sure is, that such an ADO.COM instance can work
with the ODBC-driver you find here: http://www.ch-werner.de/sqliteodbc/
(proper connection-string: "DRIVER=SQLite3 ODBC Driver;Database=test.db"

Not sure, if the .NET-wrapper you mentioned can be accessed as a COM-
Object because it was compiled with the "COM Accessible" options -
and then in turn properly registered - but I'd doubt it.

Also not sure, if this wrapper possibly comes with an OleDB-Driver you
could use with classic ADO and the connection-string you've shown above:
("Provider=System.Data.SQLite; Data Source=C:\DATA\SQLLite\mydb.db3")
But I'd doubt that too (your error-message already hints at that).

Olaf

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Feature Request: Binding Arrays

[Olaf Schmidt]

Am 16.10.2013 10:40, schrieb Dominique Devienne:


If I somehow missed a better work-around to this lack of array-binding, I'm
also interested of course, but obviously I'd prefer real array binding.


Maybe I'm missing something - but if I'd have a lot of "InApp-
MemAllocations" in the form of different kinds (or "types")
of lists, constantly changing their content as well as their
listcount - but not their "type" - then I'd use a simple set
of userdefined functions (not sure though, how "trigger-happy"
those are with regards to your hooking-worries, when you add
them into the engine - but this needs to happen only once,
at startup).

So, after those functions are in place - where's the problem with:

select * from table where InMySmallUnsortedArrayExists(some_column)
select * from table where InMyLargerSortedArrayExists(some_column)
select * from table where InMyHashListExists(some_column)
...
etc. for trees or whatever you want to use to speed-up the exists-check.

Or more generically with an additional Param:
select * from table where InList(@ListTypeEnmValue, some_column)

Olaf

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to return the row number? (NOT the rowid)

[Olaf Schmidt]

Am 06.07.2013 18:58, schrieb j.merrill-


There are reasons to want what is in other SQL implementations
implemented with "row_number() OVER (ORDER BY ...)" but
"disconnected Recordsets" or for data "passed across thread-
or machine-boundaries" are definitely NOT valid reasons.


Since they are self-describing containers, which can be created
directly from an SQL-string, they allow for a sort of genericity
whilst designing applications, which just saves a lot of lines of
code - and a row_number(OptionalOffsetToStartFrom)-function *is*
much easier to notate in a View-Definition-script, than to implement
with dedicated lines of code in your programming-language of choice.

Perhaps you think "loops" when you think about retrieving (or
visualizing) DB-Data - (and in this case it is easy to ensure
such a counter of course, if the loop is (or needs to be) always
already there in the first place).

I think: "returned Set-Objects, based on a View-Name and some Params":
- and one line of code, to retrieve them by Command-Object or SQL-string

then I'm free to decide, what to do with that Set-Object...
e.g. direct visualizing in a Grid:
- also one line of code: Grid.DataSource = Recordset

Or I decide to serialize it into a Byte-Array, to be able to
pass it into another thread - or pump it out over sockets, to
satisfy an RPC.
- also one line of code: ByteArray = Recordset.Content

So far I *was* living without that row_number()-feature of course -
no need to explain to me, what can be done at the application-level ...
I've perhaps forgotten more about efficient Application-programming,
than... - arrghh - no, let's not go there... ;-)


The "row number" value as commonly described in this thread is

> completely useless except in the context of one specific
> execution of a particular SQL statement.

In the same spirit as you just wrote above, I could come up with:
"a Round()-function is completely useless, except in the context of..."
or
"multiplying a Field-Value with a constant literal is completely
 useless..."
or ...

I can only repeat, what I already wrote in my reply to Keith - it's
only about (just another) convenience-feature - nothing more.


You would need to use the table's primary key value to do any updates
to the original table...

> ...to avoid updating rows that had been changed by other sessions
> users after the initial SELECT.

What does this necessity have to do with my nice, new "per SQL included 
RowNumbering"-field (I'd otherwise would have to ensure with dedicated

"non SQL-code")?


You would definitely not want to relate one recordset to another using the
> "row number" value, because executing the exact same SQL statement 1 
second
> later could return a completely different "row number" value for 
every primary key.


Who said, that those who are asking for the inclusion of a row_number-
feature, are complete DB-newbies, who have to be thaught something as 
primitive as the above? ;-)



Olaf


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to return the row number? (NOT the rowid)

[Olaf Schmidt]

Am 06.07.2013 19:25, schrieb Keith Medcalf:

Date: Fri, 05 Jul 2013 02:04:04 +0200

From: Olaf Schmidt <n...@vbrichclient.com>
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Is there a way to return the row number? (NOT
the rowid)
Message-ID: <kr52ig$e71$1...@ger.gmane.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed

Am 03.07.2013 14:50, schrieb Keith Medcalf:



Given all that, I will NEVER use the pure sql (if I can use any
other solution).


given that the "ordinal in the result set" is a fallacious
concept being created for the convenience of an application
program which cannot deal with sets properly ...


Oh, so convenience is now bad suddenly?
[...]
Because there's a lot of things one can use that for - especially
when you consider the concept of disconnected Recordsets, which
can be passed around in an application, or across thread- or
machine-boundaries - generic container-classes, which can be
bound to grids - or used as the datasource for parts in a Report
... in any of those cases such a "directly contained info" can
be useful, when it's already "there in the returned set-object
as a calculated column-value".
[...]


This is utterly fallacious.


No, it isn't - instead your reply is immature (may I ask, how old
you are)?


When you have a result set stored in a data structure, the "row
number" is inherent in that structure (whether it is a list, array,
or some dotSnot crud which wraps a simple array structure in hugely
overcomplicated layers of smega).


Tell me news...


You always access the first row as entry 0 of the structure.


Oh, do I?
I'd say, your index 0 is only one of the possible lower-bound-values,
a data-constainer-structure can be accessed with (depending on the
implementation of that container - or its settings)...

But I'm glad, you discovered already lists and arrays... well -
I can ensure you, that there's "much more out there" (not only
the "dotSnot crud", you so far successfully avoided, I'm sure).

>   Correspondingly the 47th row can be accessed
> by indexing into entry 46 of the structure.  Encoding the index
> (ordinal) within the array row data serves no useful purpose
> whatsoever.

Glad you brought that up - but again your leap is not far enough,
because there's sorting ...
Sorting, some of the more advanced Container-structures (you're
aware, that I'm not talking about Lists Or Arrays anymore, do you?)
will support "at the clientside" (no DB-connection, no SQL-engine
in sight there) - some of them in a pretty advanced way, allowing
basically the same (multi-column) functionality as the SQL
Order By Clause.

How would your "inherent index" behave now - or better: "how would
you *like* it to behave"? (after a Sorting took place - and you're
about to re-render your Grid- or Report-Content).

What, if you indeed want it, to be treated (by the client-app)
as a sort of "initial ranking" (because in the resultset, the
original Sort-Columns or criteria, which led to that "simple
ranking" are not contained)?



Doing so will just come back to bite the wattage - challenged in
the ass -- just like datetime values do.


I'm very sorry, but the above sentence does not make any sense
to me (in the given context, and given what you said earlier).


If you think you need to encode the index into the data, then
you should not be designing databases or applications, you do
not have the necessary skills to be doing so.



Now, *that* I understand better - but you don't seem to read
carefully enough (this may explain your somewhat rude tone -
well, I always try to allow for a certain degree of "youthful
arrogance" - but seriously, this is just an utterly idiotic
statement to made there.

It's about a "pure-convenience-feature" in an already nice
OpenSource-lib, ... which has still a pretty small footprint -
but the feature is really cheap to implement, not adding much
to the codebase.

Nothing more - no attacks on OpenSource-ideals, no attacks with
regards to "undermining relational rules in db-design" - the
feature is entirely in the category of e.g. the Round()-function -
a pure convenience-thingy, to "pretty up resulting views
already at the SQL-level" (and yes, I'm aware, that Round() can
be used in other scenarios too, but that's its main-purpose).

Olaf


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to return the row number? (NOT the rowid)

[Olaf Schmidt]

Am 04.07.2013 20:36, schrieb James K. Lowden:


Yes, and there's your O(N log N): N for the SCAN and log(N) for the
SEARCH. To process 1,000,000 rows takes 1,000,000 accesses.  To produce
the rank requires roughly 20 searches per row (given an appropriate
index), or 20,000,000 total accesses. Plus some work, depending on
memoization, to count the nodes beneath the found one.

Inefficient? It's the theoretical *minimum* for the general case.


>... snip ...
>

Did I miss something, or have I answered your efficiency concerns?


What you missed, is apparently *testing* your own suggestions...
You know, those "practical things" some of us do from time to time...
;-)

Well, I just did so (using a small Table with an index on ID
for your self-join-suggestion):

Create Table T (ID Integer Primary Key, Item Text)

Select Count(Lesser.ID), T.ID, T.Item From T As T
   Left Outer Join T As Lesser
   On T.ID > Lesser.ID
   Group By T.ID
   Order By T.ID

I don't know, what I'm doing wrong - but my timing-trend
comes out like this:

RecordCount in T  msec for the above query

 1003msec
 1000 186msec
 1  17857msec

And so, after waiting already for about 18 seconds for the query
to complete with only 1 records in T, I will of course not
try it with the "1,000,000 rows" you were mentioning above. 


Olaf

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to return the row number? (NOT the rowid)

[Olaf Schmidt]

Am 03.07.2013 14:50, schrieb Keith Medcalf:



Given all that, I will NEVER use the pure sql (if I can use any
other solution).


given that the "ordinal in the result set" is a fallacious
concept being created for the convenience of an application

> program which cannot deal with sets properly ...

Oh, so convenience is now bad suddenly?
Why do you think, libraries like SQLite exist at all?
They're there to offer *convenience* - to save other
developers some time (or headaches)... thanks to the
SQLite-team BTW at this occasion - you've developed a
really convenient "time-saver" here over the years for
a lot "of us"...

But given your logic, should the usage of e.g. literals in SQL-
Field-expressions be forbidden now too? ... because:
  "this is stuff, which should be handled at the application-
  level, otherwise the developer is to blame, for abusing
  the concept of relational databases..."

What kind of theorizing is that here... there's a whole lot
of stuff in DBEngines, libraries (and in SQL as well), which
is just there for convenience.

Having a Counter-Number conveniently available (without creating
a clumsy performance-hog as James K. Lowden was suggesting) is
a reasonable request from a *practical* point of view and pretty
cheap to implement.

So, I'm voting +1 for that...

Because there's a lot of things one can use that for - especially
when you consider the concept of disconnected Recordsets, which
can be passed around in an application, or across thread- or
machine-boundaries - generic container-classes, which can be
bound to grids - or used as the datasource for parts in a Report
... in any of those cases such a "directly contained info" can
be useful, when it's already "there in the returned set-object
as a calculated column-value".
It would allow to write more "generic code" at the app-level -
when for e.g. the visualizing of Line-Numbers (or to remember
or visualize an initial-Sort-Order) in a Grid for example, one
could just "hang the Recordset-Datasource in 'as is'" - without
having to resort to "manually managing the filling of a spare-
column" within that Grid in question, because the information
was not contained as a Field in the Recordset-Container-Class.

If SQL allows to create (from a Double-typed-Field as the
Input-source) a convenient, directly displayable visual output,
handing out "calculated Percent-Strings, nicely formatted to
two decimal-places after the dot" over an SQL-expression... -
well, then I don't understand what all the fuss is about here,
when there's only the simple request for a *little* bit
"more of the same" in this regard...

Olaf



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Large Database Windows vs Linux

[Olaf Schmidt]

Am 28.06.2013 14:54, schrieb Christopher W. Steenwyk:


I have a rather large database (11 GB) that has two tables (one with
approximately 500,000 rows and another with approximately 50,000,000 rows).
  In this database I am performing a query that joins these two tables to
produce approximately 4.4 billion rows and then uses a GROUP BY and COUNT
to return some values to me.

I struggled with the slowness of this query for a while in linux and
through covering indexes, ANALYZE, and a SSD I was able to get the query to
run in about 15 minutes which I am OK with.


... speaking from a pure technical (or better, hardware-)
perspective - the problem should not take that long.

It is IO-Bound apparently (especially on Windows) - but even on
Linux your 15 minutes (which is roughly 1000 seconds for ease of
calculation) seem way too much.
The sustained Disk-Read-Throughput of a modern SSD is between 300
and 550MB per second. Let's take 300MB/sec - with that rate, your
11GB should be "spooled-through" (passing your own "Software-Filter"
along the way) in roughly 1GB any 3 seconds - or say: about 30-40
seconds for the whole 11GB.

Most (or at least "many") of the problems, which cause such huge
amounts of data, are pretty "narrow" - and if that ist the case,
then one doesn't always need to tackle them with a "generic, all-
purpose SQL-engine".

If you end up, having only 2 or 3 "typical Query- or Filter-criteria"
to perform against those 11GB - then why not "hand-code" your filters,
and store your data in dedicated binary files?

E.g. your smaller set of 500,000 Rows looks like (at least for the
Join-Operation) it could be stored separately from the other data -
and before you perform the Grouping-Join, could be loaded into memory
completely.
Done so, you could perform your grouping-filter then, shoveling only
your larger set of data (in binary-format, directly from Disk with
about 300MB/sec) into memory - and then compare it there against
your already cached, smaller "Join-and-Group-criteria-set".


Olaf


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Implementing "Save As..." functionality for Application File Format usecase

[Olaf Schmidt]

Am 03.04.2013 23:11, schrieb Tiago Rodrigues:


I'm writing a small simulation app and for it I would like to use SQLite3
as an application file format,  ...

> ...

For that, the simplest idea would be to use the online backup family of
functions, calling sqlite3_backup_init() and sqlite3_backup_step() on the
database, calling COMMIT on the backup and ROLLBACK on the original.
Naturally, that doesn't work, as you can't back up a database in the middle
of a transaction -- sqlite3_backup_step() returns SQLITE_BUSY.

That being said, has anyone on the list encountered (and hopefully solved)
this particular problem?


Don't know why you want to hold "a session open" on your original
(initial) DB-File for that long...

One can open ones own "Application-Format-File" from a FileDB-
Template - or from any (already written, existing) "Document-File"
on Disk - by just "touching" those FileDBs (those need to be open
only for a very short moment)...

Meaning, "your current-working-set" (the DB you work against)
could always be an InMemory-DB - but you can write the current
content of this MemDB at any time (to any FileName on Disk),
over the Backup-API.

And as said, in the opposite direction, you can use the Backup-API
as well, to open from any File(Template) already existing on Disk.

Read-direction:
- Open FileDB-Connection
- use the Backup-API to create a copy as a MemDB-Connection
- Close the FileDB-Connection immediately after that...

Time needed for that "Copy-Over" ... (just tested here against
an about 1.3MB large File-DB -> NWind.db - with 15 Tables or so):
about 10msec.

Write-Direction has about the same Performance.

Since the Backup-API works Page-based, you can expect
the Read/Write-throughput of the Disk - and for smaller
Files in the range of about 50kB to 2MB, this just makes
"blink" (allowing even "naive" Undo/Redo-scenarios (where
you read/write the whole DB to and from Memory) ...with a
depth of e.g. 15-30, when your "AppDB" is not that large
(there's a lot of App-Data which fits into DBs, smaller
than 1MB).

Olaf







___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple network connections

[Olaf Schmidt]

Am 25.12.2012 22:18, schrieb Çağlar Orhan:


But every client has MS *.hta files and connecting to sqlite
over ODBC driver with vbscript.


What you rely on in this case, has nothing to do with
the WebServer (IIS) ... meaning the WebServer is not
used as a decoupling layer (over http).

What you do instead is FileLevel-Access over a (Network-)
Share (SMB/CIFS).
In this mode, the client-machines are opening the DB separately -
and the DBFiles Pages are transferred to *each* client independently:

[.hta->vbscript->ADO(ODBC)->]...network...[<-SMB/CIFS]



That means 5-10 or few larger client counts make
connections to that unique sqlite file on the IIS server.

As said, these connections have nothing to do with the
IIS (as a Service on your ServerHost).

Your ServerHost-*Machine* was simply set up, to provide
(beside the IIS) also the SMB/CIFS-Service, to be able to
act as a (Network-)FileServer (which then interacts with
your clientside ADO/ODBC-Driver at the Network-FileLevel).

In this mode, SQLite-concurrency is handled over (NetworkLayer-)
FileLocks - and these FileLocks do make problems sometimes.

The more stable mode would be, to use your IIS as a real decoupling
layer, and access the DB-Files at the server-end *locally* (e.g.
behind an *.asp-File, which can hold VBScript too - but delivers
disconnected Rs to your Clients, after retrieving them over a
local DB-Connection against a Server-local SQLite-File):

[.hta->vbscript->XMLhttp-Obj]...network...[<-IIS<-.asp<-ADO(disc. Rs)]

Your current (CIFS-)access-mode would also work, when you completely
disable the IIS-WebService on your Server-Machine - and open your
ADO(ODBC)-connections directly within VBScript (without any .hta).

If you have very stable Network-Connections (no WLAN involved,
very stable ClientMachines (no Notebooks, which may power down
"on an open connection" due to longer inactivity), then the SMB/CIFS-
Mode can work relatively reliable, especially when you really
only have 5-15 clients "to serve" in that mode.

But in case there's more clients - or when you have a certain
potential for "client-machines, disconnecting unexpectedly" -
then it's better to work over IIS/.asp instead of CIFS, to do
"server-local" DBFile-Access over http.

But also that "Server-local-mode" could cause certain concurrency-
problems (but then more performance-related ones) ...not as many
as when working with Network-Filelocks as the only possible
"concurrency-signalling-mechanism" available to SQLite.


Olaf


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Subject: Re: Simple SQLite-based spreadsheet?

[Olaf Schmidt]

Am 09.12.2012 12:40, schrieb Simon Slavin:


On 9 Dec 2012, at 11:13am, Gilles Ganault  wrote:


OTOH, whoever writes that application could always provide two
version: Basic (datagrid) and Pro (spreadsheet).


If you think you can make money by providing a basic app, write one and market 
it.

People don't want one.  If they have already paid for and learned to use Excel,
they'll use that.  If they haven't, they'll use anything that's free.
They don't care whether the data engine is SQLite or anything else.



Yep - and for those who want "Excel-like Editing-Comfort
for simple Data-Lists" - there's always the option,
to work with Excel or OpenOffice-Calc on *.csv-Files
(*.csv usually opens without problems in a SpreadSheet-
 Application).

@Gilles
The only thing remaining for a decent workflow, which in
the end is based on SQLite-storage, would then be a small
batch-program or -script, which ensures the SQLite-To-CSV
conversion (the sqlite-commandline-tool could do that) -
accompanied by a second batch-file which ensures the
back-conversion from CSV to SQLite-DBTable (and maybe
also already the direct upload to a given WebServer).

Along with a tool or script which is "Watching for Folder-Changes",
one could automate that entirely, including a potential
WebServer-Synchronizing.

Olaf

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Subject: Re: Simple SQLite-based spreadsheet?

[Olaf Schmidt]

Am 08.12.2012 22:09, schrieb Gilles Ganault:


Ok, but where are the large number of tools that would do that?

Features:
- very fast, very basic spreadsheet (not based on Excel or
  Libre/OpenOffice)


I think you will have to be more specific with regards
to "spreadsheet-like functionality".

Do you need Formulas in individual cells (difficult
with a DB-Table-Backend) or just for "entire columns"
(a bit easier then with a DB-Table-based-"DocFormat")...

Do you need individual "Formats" (BackGround-Colors, or
Font- Types/Colors/Sizes/Weights as well as "Format-Strings
to render Double- or Date-Values" appropriately) ...
at cell-level ... or just for entire columns...?

If no such special Formatting is needed, then the
term "DataGrid" is the more common one, since
"real SpreadSheet functionality" is usually associated
with the extended requirements (at individual cell-level)
I've listed above.


- saves data in SQLite

Even with "all the extended formatting", just mentioned
above, you could of course save everything which makes
up such a "real spread-sheet-document" against a single
SQLite-DB-File as your choice of "Document-storage".

But the result would then be in its own kind of special
"DB-Document-Format" - involving more than one table
per document-page (in case you want to use efficient
storage with a kind of "applied normalization").

To store more than just the "Raw-Column-Data" in only
a *single* table would require a lot of "sparsely
populated, 'kind of hidden' extra-Columns"...

And since in your first posting you wrote:
   "I need to enter a bunch of items into a table
that I can later read from a web app."

I wonder, whether you expect the webapp to visualize
your (then retrieved at the [Web]serverside I assume)
SQLite-Data with all the "pretty spreadsheet-like
formatting" - or just "datagrid-like column-based
Text-Formatting"?

If it is "Rich-Offline-Formatting of Table-like views"
to finally feed a WebApp (after uploading such an
Offline-created Doc.) - then there's either the HTML-
export-Option of OpenOffice for example - or you can
use the Browser- or HTML/JS-based stuff out there,
which helps to create such rich formatted table-views
directly (for example HTML/JS-based WYSIWYG-Editors
with a good Table-Plugin - or e.g. the Google-Docs-
Spreadsheet).
...just to mention some "Web"-options, which don't involve
storing the Document in an SQLite-File.

If you want more DataGrid-like Web-Editing in your
Browser, then there's also a lot of "DataGrid-like"
JS-Components (e.g. direct jQuery-based ones - or
those included in the larger JS-based Toolkits, like
in the Ext-Framework for example).
Those would require a serverside serialization of
(SQLite)-DataContent into XML or JSON usually -
to be more easily "bindable" to the JS-based Grids.


- very easy to create new tables + columns
- lets the user edit rows/columns as easily as in Excel
- data can be sorted by any column
- access to SQL a plus, but must not be required, as the app is meant
to non-techies


This last block of your requirements is already addressed
(more or less) by many of the already existing "SQLite-Admin-Apps"
out there, a few of them also offering "In-Table-Cell-Editing
of raw Column-Data" - but as said - this would be what is known
as "DataGrid-Mode", it's not "real SpreadSheet-Functionality".

But as others have already mentioned - if your requirements are
"somewhat special" (outside the Standard-usecase) - and the
Browserbased JS-Frameworks or -components are not "your thing" -
then use some existing Grid- or Spreadsheet-components for
your language of choice - there's real spreadsheet-components
out there - as well as all kind of different DataGrid-components
(for the Windows-World these would come as Dlls, OCXes or
Delphi-VCLs or .NET or Java-based components).

The normal DataGrid-components can be bound with only a few
lines of code, to work directly also against SQLite-Backends...
The SpreadSheet-Components usually support DB-Backend-Binding
as well (on Windows usually over ADO or ADO.NET) - but as
soon as you want to use their full formatting-options, you
will have to use their proprietary SpreadsheetDoc-Format -
though most of them also offer "Excel-Export".

Olaf


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Remote connection to SQLite db

[Olaf Schmidt]

Am 18.10.2012 15:41, schrieb Abhinav:


Does SQL lite have an option of processes connecting to it from remote hosts 
with a port number?


There's nothing directly built-in in the sqlite-library.

In case you need that for Windows (also depends on your development-
environment a bit) my SQLite-COM-wrapper supports an "AppServer-Mode",
which is able to transfer resultsets (serialized Recordsets)
from the serverside to the clientside, supporting compression
as well as encryption... (there's dedicated RPC-Listener-Classes
to easily implement the serverside with a few lines of code -
and also dedicated Client-RPC-Classes which can communicate
with the server-end over TCP/IP.
The write-direction (Inserts, Updates, Deletes) is supported
too - also based on serialized Recordsets, which are able
to serialize to only "the diff, created on the clientside in
a user-session" back to the serverside - and then these
"accumulated changes" of such an backtransported "Diff-Recordset"
can be applied in a single transaction on the server-end.

Though, as said, the wrapper is a COM-library - usable only
on Windows ... (although it is tested and known to work also
on Linux/Wine) ... so, it's usage is restricted to languages,
which can load and access COM-libs (preferrably from VB5/VB6,
or "Office-VBA" - but also from Delphi, C++, Powerbasic, etc. -
but a bit less comfortable then).

There's other "free networking-implementations" as well, which
are known to be "alive and kickin"... as e.g.

http://www.sqlitening.com/support/index.php
(also for windows, implemented in PowerBasic)

ALso check out Marco Bambinis efforts here:
http://www.sqlabs.com/blog/
(not sure, if these new products strictly require MacOS-Hosts -
 the former Real-Server was SQLite-based and ran on more systems
 than just MacOS AFAIK, maybe Marco can help out himself here)

There's also a page on the SQLite-wiki - but not sure which
of the listed projects is actively supported or maintained yet.
http://www.sqlite.org/cvstrac/wiki?p=SqliteNetwork

But what finally remains are of course Webserver-(http-) based
solutions, where you can transfer back and forth with e.g.
JSON-based RPCs or with XMLhttp-Requests (with http-GZ-compression
when needed, or SSL for encryption).


Olaf


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Subtract times hh:mm:ss

[Olaf Schmidt]

Am 07.10.2012 23:43, schrieb Bart Smissaert:


I take it these files are not ready yet to put in a commercial app?


Not yet, but in 2-3 weeks the COM-interfaces of the new added
Classes (as for eaxmple the new convenience-class cMemDB) should
be "stabilized" and contain their final method-signatures (binary 
compatibility is not yet switched on for vbRichClient5.dll).


So if you (or others who are using the RichClient-COM-libs) have some
suggestions for "more convenience-stuff or otherwise useful additions",
now is the time to speak-up (not here in the group, just drop me an
E-Mail with your "wish-list" and I see what I can include into RC5).

Olaf

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Subtract times hh:mm:ss

[Olaf Schmidt]

Am 06.10.2012 19:38, schrieb Bart Smissaert:

Times I get (65000 records, subtracting 2 fields defined as text in
the same table)
gives me following times:
method with julianday 0.4 secs
method with unixepoch 0.6 secs
using ctime etc. via VB wrapper 1.2 secs


What? A VB-implemented User-Defined-Function slower than
a built-in C-function? Now, there's a challenge... ;-)

Since I'm in the last stages for a new wrapper-
version (RichClient5) - I've tried to speed these
two functions up already in the new Binary (FWIW).

Wasn't all that difficult, because the former VB.Runtime-
function (Format$,... yes, I was lazy), which internally
was playing a major role in these conversions, is not
the fastest and leaves enough room for improvement.

So, after optimization the UDFs CTime/CDbl are now about
twice as fast as the time/julianday-functions.

If you want to try it out, here's a download of the
current snapshot of the new wrapper:
www.datenhaus.de/Downloads/vbRC5BaseDlls.zip

After registering you should be able to run the following
testcode (TestTable contains 86400 "increasing seconds").

Tried to avoid the selection of too many records in the
test-query (only a single one is returned), so that the
test can run more or less completely inside SQLites VDBE.

The printed results, after scanning over 86400 records are:
Count: 1  Timing VB-UDF: 0,049s
Count: 1  Timing SQLite: 0,090s

--- used table-layout and VB-testcode ---

'*Into a Form, then click the Form
Option Explicit

Private MemDB As New cMemDB, Rs As cRecordset, T!

Private Sub Form_Load()
  With MemDB.NewFieldDefs
.Add "ID Integer Primary Key"
.Add "T1 Text"
.Add "T2 Text"
MemDB.CreateTable "T"
  End With

  With MemDB.CreateCommand("Insert Into T Values(@ID,@T1,@T2)")
MemDB.BeginTrans
  Dim i As Long
  For i = 0 To 86400 - 1 '<-one day (increasing seconds in T2)
.SetNull !ID
.SetText !T1, "00:00:00"
.SetText !T2, Format$(i / 86400, "hh:mm:ss")
.Execute
  Next i
MemDB.CommitTrans
  End With
End Sub

Private Sub Form_Click()
Const Where1 = "CTime(CDbl(T2) - CDbl(T1)) >= '23:59:59'"
Const Where2 = "time(julianday(T2) - julianday(T1) - .5) >= '23:59:59'"

  T = Timer
Set Rs = MemDB.GetTable("T", Where1)
  T = Timer - T
  Print "Count:"; Rs.RecordCount, "Timing VB-UDF: "; Format(T, "0.000s")

  T = Timer
Set Rs = MemDB.GetTable("T", Where2)
  T = Timer - T
  Print "Count:"; Rs.RecordCount, "Timing SQLite: "; Format(T, "0.000s")

  Print
End Sub

Olaf

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Subtract times hh:mm:ss

[Olaf Schmidt]

Am 02.10.2012 21:23, schrieb Bart Smissaert:

Nice one, thanks for that.


Just in case you use the COM-Wrapper - and this operation takes
place in a plain Recordset-Select (and isn't stored somewhere
in the DB itself, e.g. in a Trigger), then you can reduce the
amount of function-calls a bit, when you use something like that:

Define the Table-Field with the wrapper-supported Time-FieldType,
which ends up as 'hh:mm:ss' Text in the SQLite-DB-Field - but is
correctly translated back into a Date-Type in the receiving cRecordset.

To safe a few CPU-Cycles in the Query, you can directly place
the Double-representation of a VB(A)-Date in the Query-String
(done in the Example per ? Placeholder in a cSelectCommand).

The Diff-expression in the Select then looks this way:
CTime(? - CDbl(HMS))

CDbl, to convert the TextContent of the HMS-Field into the
Double-representation of a VB-Date - and CTime to
convert the difference back into a 'hh:mm:ss' String.

Not sure, if that is faster than SQLites built-in Date/Time-Functions,
but worth a try...


' Into a Form (clicking the Form gives the Delta to its "LoadTime")
' Output then i.e.:   HMS  04:21:27  True  DTS  00:00:01  True
Option Explicit

Private Cnn As New cConnection, GetDeltaCmd As cSelectCommand

Private Sub Form_Load()
  Cnn.CreateNewDB '<- InMemory

  'the wrappers Time-FieldType ensures 'hh:mm:ss' TextFormat in the DB
  Cnn.Execute "Create Table T(HMS Time)"

  With Cnn.CreateCommand("Insert Into T Values(?)")
.SetTime 1, Now()
.Execute
  End With

Const GetDeltaSQL = "Select HMS, CTime(? - CDbl(HMS)) As DTS From T"
  Set GetDeltaCmd = Cnn.CreateSelectCommand(GetDeltaSQL)
End Sub

Private Sub Form_Click()
  GetDeltaCmd.SetDouble 1, Now() 'we place the Param directly as Double

  With GetDeltaCmd.Execute 'returns a cRecordset
Debug.Print !HMS.Name, !HMS.Value, VarType(!HMS.Value) = vbDate,
Debug.Print !DTS.Name, !DTS.Value, VarType(!DTS.Value) = vbString
  End With
End Sub

Olaf

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Store error messages in thread local memory

[Olaf Schmidt]

Am 24.09.2012 11:26, schrieb Sebastian Krysmanski:

Ok, I tried that. It definitely improves performance when using a lot
threads (15+)...


So I take it, that your last posted result here was using a
shared cache (in case of the multiple connections):


--
SELECT_COUNT: 133,333
THREAD_COUNT: 15
Testing with one connection (ReadWrite) and filled table...
Elapsed: 12.3 s  (162,647.6 stmt/sec)
Testing with multiple connections (ReadWrite) and filled table...
Elapsed: 12.7 s  (157,324.1 stmt/sec)


Whilst the following result (from an earlier of your replies),
was not using a shared cache (instead using separate caches
per thread in case of the multiple connections):

> --
> SELECT_COUNT: 133,333
> THREAD_COUNT: 15
> Testing with one connections (ReadWrite) and filled table...
> Elapsed: 9.5 s
> Testing with multiple connections (ReadWrite) and filled table...
> Elapsed: 51.2 s

Possible things which could explain that (because it differs from
my results considerably) are potentially:
1. the setting for: PRAGMA read_uncommitted
2. the implementation of the Busy-Handler

Ok, just looking what my settings were for case 1...
and 'PRAGMA read_uncommitted' was at Zero, so this case
can be dumped I think.

So I suspect the latter case ...
Which Busy-Handler do you use, the built-in one?
called up per: sqlite3_busy_timeout(sqlite3*, int ms)

Or your own implementation?

In my own BusyHandler (I'm dealing only with Windows here)
my first (few) "wait-fast" calls in the Busy-WaitLoop are always:
Sleep 0

which according to the MSDN:
> 
http://msdn.microsoft.com/en-us/library/windows/desktop/ms686298(v=vs.85).aspx

"relinquishs the remainder of the threads timeslice", but
will try to return as soon as possible in this case.
So, if you implement your own Handler (for Windows),
calling Sleep 0 first (a few times) may worth a try,
so that you give the pausing thread the chance, to
take up its work much earlier, in case the cause for
the blocking was only a really short one (on some
other thread).

Also worth noting in this regard is, that the TimeSlice for
low values as e.g.
Sleep 1
doesn't (always) send the thread sleeping for "exactly" 1 msec -
the time until the thread resumes its work depends in this
case (for such low values) on the settings which were
(only potentially) performed earlier with timeSetEvent...

By default Windows has a "Tick-Interval" of about 12-15msec
(as the minimum-sleep-time for millisec-values > 0).
But this "Tick-Interval-granularity" is explained also in
the 'Remarks'-section of the MSDN-page for Sleep().

Olaf



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Store error messages in thread local memory

[Olaf Schmidt]

Am 21.09.2012 19:28, schrieb Keith Medcalf:



So one needs to take care that the engine operates in the properly
matching modes for the two approaches when used in threads:

- shared Cache (single dbHdl over all threads) => serialized
(SQLITE_THREADSAFE=1)

- separate Caches (a dedicated dbHdl per thread) => multi-thread
(SQLITE_THREADSAFE=2)



You descriptions are incorrect.  No matter how you set
SQLITE_THREADSAFE,you have one cache per connection --
unless you open the connections in"shared cache" mode,
in which case the connections so opened share a single cache.


Not really "incorrect"... maybe "incomplete" matches better.
By default SQLite compiles (IMO) with disabled shared cache.
And that means, that a single DBHandle (shared among threads)
works against a single Cache of course - and multiple DBHandles
(one per Thread) work (by default) against separated Caches.

And for the latter case (one Connection-Hdl per Thread) the
enabling of the shared cache can make a difference of course.


SQLITE_THREADSAFE=2 means that the SQLite library DOES NOT
enforce single entrance per connection with a mutex for you.
It is how you tell SQLite that the application will enforce
those serialization requirements, and that the SQLite library
need not bother itself with enforcement, and should instead
just explode/dump core/fault/corrupt whatever it wants if you
are careless or fail to adhere to the serialization requirements.


My understanding so far was, that SQLITE_THREADSAFE=2 is only
"unsafe", when a single DB-Connection is shared among threads.
When each thread gets its own connection-handle, then this
mode should work a bit faster than with SQLITE_THREADSAFE=1
(and my tests, I've done just now, prove that).


For the multiple-connection-approach (each Cnn having its own cache)
you will probably see better results, when you work against an
sqlite-binary, which was compiled (or set to) multi-thread
(-DSQLITE_THREADSAFE=2).


If and only if you adhere to the serialization requirements.
If you fail to do so, then hell on earth is likely to ensue.


Don't know what you mean by that, because I rely on what's
stated in the SQLite-Docs - so, implementing my own serializing
seems not to be required, as long as SQLite is working with either
SQLITE_THREADSAFE=2 (and my app-code does not share Connection-
Handles among threads) or SQLITE_THREADSAFE=1 (sharing of Cnn-
Handles among threads is allowed).

The only thing I've implemented myself (with regards to potential
"locking issues") is my own SQLite_Busy-Retry-handling (I don't
use the built-in BusyHandler).



My DB-Sizes are not that huge, so I can effort the independent
DB-Connection-caches on each thread in my server-threadpool.


And that is probably why connection-per-thread works for you.


Sure - I've compiled my wrapper-lib with SQLITE_THREADSAFE=2 -
because a single Connection (shared among threads) can never
happen in my threaded scenarios (my COM-Wrapper is bound to be
used only in COM-STAs, which have isolated memory per "thread-
class-instance").


You have not yet hit the point where the supervisors'effort
of memory management for duplicated cache data exceeds
benefit from multiprogramming,...


In case the concurrently accessed DB-Files get that large, that
the benefit of "more or less redundant caches per thread" will
become questionable (degrades the over-all-performance), then
there's always the option to enable SQLites shared-cache-mode
on the Server-Instance (or the multi-threaded Process).

Further below comes a performance-comparison for you... just
wrote a test, to make sure my results are "still as before"
(against newest SQLite-version 3.7.14, compiled with MSVC-
 2008 - my last tests in this regard were done already some
 years ago, but the latest Dll-version still delivers the
 same results which led to my decision in the past, to compile
 my "wrapped SQLite-binary" with SQLITE_THREADSAFE=2).

Ok, this was done on Win7 (no VM) on a "real QuadCore"
(an Intel-i5, 2.8 GHz) having no "virtual cores".

ThreadPool-Count was set to 4, matching the CPU-cores -
and the "Count of total Selects to perform" was set to
8000 - so in multi-thread-mode, each of the 4 threads
was performing 2000 Selects in a loop (each Select in
addition also copying the incoming data over into dedicated
recordset-structures within the SQLite-step-loop).

All the Selects were working against a smaller DB, fitting
entirely into each threads page-cache - and the table
addressed by the Selects contains hierarchical Tree-Data
with about 12000 table-records...

1. A pure throughput-oriented Select (RecordCount as per Limit-Clause)
   SQL = "Select * From Tree Limit 3000"

   Single-threaded performance (8000 Rs-Selects in a Loop): 19.5s

   And using 4 threads (2000 Selects on each thread)
   SQLITE_THREADSAFE=2: 5.4s vs 34.2s <-with enabled shared cache
   SQLITE_THREADSAFE=1: 6.1s vs 38.6s <-with enabled shared cache

The above scenario (more throughput-related) is 

Re: [sqlite] Store error messages in thread local memory

[Olaf Schmidt]

Am 21.09.2012 18:03, schrieb Olaf Schmidt:

...so I can effort the independent...


...arrgh, 'afford' of course, sorry for the noise...

Olaf


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Store error messages in thread local memory

[Olaf Schmidt]

Am 20.09.2012 17:40, schrieb Sebastian Krysmanski:

>> What's your threading mode?
>> http://www.sqlite.org/threadsafe.html


Serialized


This could explain, why you get these performance-results, you
reported before (comparing multiple connections vs. a shared one):

  > I tested with a database containing one table with 50,000 entries.
  > I then ran "SELECT *" on this table from 100 concurrent threads
  > where each thread randomly selected 20,000 table entries.

  > The results are:
  > * using a single connection for all threads: 11 seconds
  > * using one connection per thread: 59,3 seconds

For the multiple-connection-approach (each Cnn having its own cache)
you will probably see better results, when you work against an
sqlite-binary, which was compiled (or set to) multi-thread
(-DSQLITE_THREADSAFE=2).

So one needs to take care that the engine operates in the properly
matching modes for the two approaches when used in threads:

- shared Cache (single dbHdl over all threads) => serialized
  (SQLITE_THREADSAFE=1)

- separate Caches (a dedicated dbHdl per thread) => multi-thread
  (SQLITE_THREADSAFE=2)

The last one does well for me at least on the Win-OS'
(not tested on Linux here).
My DB-Sizes are not that huge, so I can effort the independent
DB-Connection-caches on each thread in my server-threadpool.

Olaf

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] instr function or equivalent

[Olaf Schmidt]

Am 10.09.2012 17:17, schrieb Bart Smissaert:

Ah, OK. I have a feeling that needs to be done either in your
application code or with a user defined SQLite function.
Somebody may prove me wrong.


Hi Bart,

since I know you're using my COM-Wrapper, a larger set
of Functions (in "VBA-Style", similar to the JET-Engine) are
already built-in there (including Instr, Left$, Right$, Mid$,
DateDiff, DatePart, ... etc.).

For example Sébastiens requirement could be handled this way:

Select Mid$(TheField, Instr(TheField,'[') From Tbl

Olaf

BTW, a new version including newest SQLite 3.7.14 is out now
since yesterday: www.datenhaus.de/Downloads/vbRC4BaseDlls.zip

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Finding rows with MIN(MTIME) for all IDs

[Olaf Schmidt]

Am 05.09.2012 16:57, schrieb Richard Hipp:

On Wed, Sep 5, 2012 at 10:53 AM, Olaf Schmidt <s...@online.de> wrote:


Am 05.09.2012 15:58, schrieb Igor Tandetnik:


  Well, you could do something like this:


SELECT id, a, b, ..., mtime FROM tab t1
where mtime = (select min(mtime) from tab t2 where t2.id=t1.id)
ORDER BY mtime DESC;



Ah, nice ... this solves the problem of the potential
"non-uniqueness" of mtime... (was fiddeling along with
something like that too, but so far without a result).

Just for completeness (in case mtime *is* unique):


SELECT id, a, b, ..., mtime FROM tab
WHERE mtime IN (SELECT Min(mtime) FROM tab GROUP BY id)
ORDER BY mtime DESC



In the actual use case that inspired this question (and for which, after
seeing the alternatives, I think I'll stick with SQLite's magical
processing of min())


Sure, I've always seen this special Grouping-behaviour of SQLite
as a feature (when no "SQL-portability" needed to be considered).


the mtime value is a floating point number, and we all
know the hazards of comparing floating point numbers for equality, right?


Hmm, out of interest, considering that in this case - where the
query doesn't contain any Float/String-conversion and no dynamic
calculations (just plain Byte-by-Byte comparison of the stored
SQLite.Values?) - would it really be that risky?


Olaf

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Finding rows with MIN(MTIME) for all IDs

[Olaf Schmidt]

Am 05.09.2012 15:58, schrieb Igor Tandetnik:


Well, you could do something like this:

SELECT id, a, b, ..., mtime FROM tab t1
where mtime = (select min(mtime) from tab t2 where t2.id=t1.id)
ORDER BY mtime DESC;


Ah, nice ... this solves the problem of the potential
"non-uniqueness" of mtime... (was fiddeling along with
something like that too, but so far without a result).

Just for completeness (in case mtime *is* unique):

SELECT id, a, b, ..., mtime FROM tab
WHERE mtime IN (SELECT Min(mtime) FROM tab GROUP BY id)
ORDER BY mtime DESC

Olaf

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using WAL?

[Olaf Schmidt]

"Sachin Gupta" 
schrieb im Newsbeitrag
news:51b335651c9a0c4ea520d47f23a580b414f5baf...@sinnodmbx001.techmahindra.com...

> Following are the setup details that we are presuming that our
> application will be put through test:
>
> 1. One writer thread (To avoid multiple Writers. We don't
> actually need multiple writers)
Good so far... because what you want, is only *one*
SQLite-thread when working against InMemory-DBs... ;-)

> 2. Multiple (around 5-8 Reader Threads)
Don't know, what you need these Reader-Threads for -
certainly not to achieve better Read-Performance against
SQLite when used with an InMemory-DB.

They could be helpful, if you use them, to host e.g. multiple
socket-connections, which place incoming Select-Strings
in a "queue" - and they could also be used, to send *copies*
of your resultsets back over the same TCP-connection without
"disturbing your SQLite-InMemory-Thread further".

Please shade some more light on that "necessitiy".

> 3. One Record size of ~2K (slightly on the higher Side)
> 4. Insertion rate (MAX) = 1500 per sec
That would sum-up to about 3MB per sec, which is
an insertion-data-rate, SQLite should have no problem
with at all (in InMemory-Mode).

The more interesting question is, in what chunks do these
~1500 records per second come in?
150 records any 100msec - or is it 15records any 10msec -
or...?

In either case I would predict, that the inserts (when using
the Binding-interfaces) would take only a tenth of these
"incoming-chunks-interval" (remember, that SQLite can
achieve sustained insertion-data-rates of more than
15 records per second, as Eric already wrote and as
is also my experience, especially against InMemory-DBs
and if no "heavy indexing" was defined on the table in
question).

What is more interesting (since you want to work against
Memory), is "where this all ends"?
With ~3MB per second, after around 20minutes (1200secs)
you would consume already ~3.5GByte of memory,
having around 1.8Mio records in your table(s).

So, what is your usecase ... do you want to work with
something like a ringbuffer? Are Deletes involved - and
after what time do they begin "to kick in"?


> 5. One main table and some helper Tables.
>
> We are presuming that the write operations would be pretty
> heavy but the read operations will be somewhat lighter.
I assume you mean with "lighter"...:
"Not with the same rate as the insertion-frequency."

But are they really light?
What kind of Selects are these - what's the indexing-scheme
to keep your queries fast (assuming your memory will grow
into the Gigabyte-Range).

> And of course there will be instances where the read and
> write are happening simultaneously.
No there will be no such instances, since the InMemory-
DB-Connection is used on only one thread - but that
isn't really a problem, if you "do it right"...

Let's assume, your 1500 records per second come in in chunks of
15 (from a device for example, which you could handle in a
separate device-thread, so that "waiting for the next data-chunk
of the device" will not disturb your SQLite-InMemory-Thread).

So the data of such an 15-record-chunk comes in at roughly
10msec each. SQLite in InMemory-Mode and without
any heavy indexes on the tables in question will be finished
with the inserts of these 15 records in about 1-2msec max.

After that the SQLite-Thread would have 8msec of "Idle-Time"
(until the next "15-record-chunk" comes in) - and it could
use these 8msecs to perform "Select-Strings" which
were queued within your "5-8 reader threads" - it would
be your responsibility, to perform only that many of
these queued selects, that you stay within your 8-10msec-
limit Idle-Time (because the next chunk of "write-data"
is coming soon) - but if you design your indexes carefully
(preferrably occupying only the RowID-Field) - then you
could mayhap perform 4 Selects (2msec each) in these
8msec, placing a copy of the read-data from each sqlite_step
directly in a memory-area of the current "reader thread
you serve".

This way you could answer 4 reader-selects in each
10msec-interval, with a "data-actuality" of max.
"10msec behind schedule" - but you already told us,
that your readers "select-frequency" is not that high,
so you will probably have to perform only one of these
Selects in the 8msec "Idle-Time-between-chunks"

So what are really the "real-time-requirements" in your
scenario - do you need to work even more "near
to your incoming write-data"? Then maybe shorten the
Insert-Intervals to e.g. "5 Records any 3msec" and
only one allowed Select in such an interval.
Your single SQLite-Thread will perhaps handle that
happily too - you will only need to ensure proper
queuing in your "satellite-threads around this SQLite-
worker" (as are your device-thread, assuming this is
the datasource of your insert-records ... and also in
your accompanying reader-threads, which would only
need to be responsible for queueing Select-Strings
and also to queue 

Re: [sqlite] SQLite server

[Olaf Schmidt]

"Doug"  schrieb im
Newsbeitrag news:043201cba22a$f023df40$d06b9d...@poweradmin.com...

> This is where I think the problems will crop up.
> Can you imagine making a
> network round trip for each row fetch, and then for each
> column fetch of each row (sqlite3_column_type,
> sqlite3_column_double, etc).
>
> To avoid that, you would need to fetch a lot (buffer) of data and
> bring it back to the client.  And if the response is large, will
> you page, or keep the transaction open on the server thus
> blocking all other clients?
>
> The devil is always in the details :)

In this case, not really...
One can perfectly work without the DBConnection
(SQLites DB-Hdl) on the clientside.

Read-Mode:
The only thing the SQLite-API needs in addition to support
an "RPC-AppServer-mode" better (but also worthwhile for
normal, "connected mode", e.g. for back-forward-navigation),
is a new to introduce resultset-container-object (or struct).
Just a "convenience-addition" in analogy to:
  sqlite3_get_table
  sqlite3_free_table
But instead of delivering an Row/Col-array of "sidewards allocated"
UTF8-string-pointers as the 2D-set - it could work in a way
similar to what for example my wrapper implements in its
cRecordset-Class.

I've done (already years ago) my performance-tests for that,
and by far the best (fastest) approach was, to allocate memory
vertically!, meaning in "stripes per column" (respecting the
expected storage-class of the column in question) - and *not*
a recordwise (horizontally) allocated chunk for each row.
For each column I reallocate (growing by a factor of ~1.5,
to avoid reallocs in each and every sqlite-step) usually two
different memory-areas ...:
e.g. for a "double-storage-class-column":
   an ubyte array for Null- and "unexpected storageclass"-Flags
   a double array which holds the non-null values
or for the blobs- and string-storage-class:
   an ubyte array for Null- and "unexpected storageclass"-Flags
   an "UTF8-stream-array", which contains concatenated Field-Values
   a ulong array for the start-offsets within the UTF8-stream
for the integer-storage-class, I assume signed int8 first,
   until the first value appears, which does not fit  into
   signed int8 anymore - in this case an "elevation" of
   the allocated array-area is performed (moving over all
   the previous values in a small cast-loop).
   The stages in this elevation-mechanism are int8, int32, int64 -
   this way especially the boolean-flag-columns (fitting into int8)
   don't take up that much memory.

As mentioned above, there's already a flagging-mechanism
for unexpected storage-class-values, which could either
be "forced" into the expected storage-class (according to
a mapper-set of column-type-descriptions) - or just
converted to string and stored in a "sparse-matrix-area"
of  the resultset, to be able to deliver such values later
on anyways (over a Binary-Lookup on a RowIdx/ColIdx-
key).

So in addition to:
  sqlite3_get_table
  sqlite3_free_table
We could use an accompanying and fast resultset-api as:
  sqlite3_get_resultset
 sqlite3_resultset_get_recordcount
 sqlite3_resultset_get_columncount
 sqlite3_resultset_get_columninfo(hRs, ColIdx, colInfoEnum)
 sqlite3_resultset_get_value(hRs, RowIdx, ColIdx, strgTypeEnum)
  sqlite3_free_resultset

Coming back to the RPC-stuff again:
The idea to have an encapsulation of vertically allocated
memory-stripes per column in such an resultset is not
only fast in normal Desktop-Mode (in delivering a "typed
copy of a Select" - together with a bunch of useful column-
infos, retrievable e.g. over: "Select * From T Where 0"),
the main-advantage of this kind of mem-alloc-scheme
comes into play within additional functions like that:
sqlite3_resultset_serialize(hRs, *pBytes)
sqlite3_resultset_deserialize(*pBytes, *hRs)
Instead of looping over single fields (or records), to build up
a resulting bytestream-serialization, it is now much faster, to loop
over the columnwise allocated chunks and concatenate these
in the resulting serialized byte-stream - and the allocation for
the resulting bytestream can now be done *once* (without
any needed reallocs) since all the column-stripe-sizes are
known beforehand.

As said, all this is not made "out of thin air" - this is
(basically) the implementation I'm working with in my
wrapper-classes in dhRichClient3.dll, which contains
classes for a DCOM-like AppServer-Mode too.
Able to deliver thousands of resultsets per second
over worker-objects, hosted in a threadpool.

What my serializable ResultSet-implementation supports
additionally, is a "CreateTableFromContent" method,
which I use often, to "beam up" a freshly received
serialized resultset-stream directly into a clientside
(usually InMemory-DB-) table.
sqlite3_resultset_deserialize_to_table(*pBytes, DbHdl, tblName)

So, for a reliable (and simple) servermode, Sylvains
suggestion is (from my practical experience) perfect for
SQLite.
Keep the sqlite-api "as is" - 

Re: [sqlite] Stored procedures

[Olaf Schmidt]

"BareFeetWare"   schrieb
> On 13/11/2010, at 10:33 AM, Olaf Schmidt wrote:

> >> If you have code (in either environment) that is
> >> looping or grabbing one result and sticking it in a
> >> second query, then your approach is probably flawed.
> >
> > As you say: "probably" ... because "it depends" ...
> > (maybe your application wants to show "details"
> > in a delayed fashion - e.g. when a Node in a
> > Treeview gets "expanded").
>
> Good example. At the point where the user requests expanded
> details (eg by clicking on a button), that obviously kicks
> off application code. As far as its interaction with the
> database, simplistically, it would have to do something
> like get the key values in the current row, check whether
> it exists in the details set, get the detail data. What I'm
> saying is that this should be done as one SQL call, which
> most likely doesn't require any branching logic within that call.
>
> So it should look something like this, with just one SQL call:
>
> array filteredDetailResults  = execQuery(
> select "Product Code", "Product Name", Quantity, Price
> from "Invoice Items"
> join "Invoice List" on "Invoice Items"."Invoice Number" = "Invoice
List"."Invoice Number"
> left join "Item List" on "Invoice Items"."Product Code" = "Item
List"."Product Code"
> where "GUI Selection Flag" = 1
> )
> show filteredDetailResults

Nah ... come on - Joins are common things - don't
know which DB-Application-developer (who worth
its salt) is not making use of it (instead of falling back
to loops).

And in my App I would have put the above SQL
into a View-Definition beforehand (that's another
common thing, most DB-Engines share and which
is well-portable).

Ending up with something like that...

SQL = "Select * From InvoiceDetailView Where CustomerID = " & _
CurrentNode.ID

' get a new Recordset-Instance, containing the set, defined above
Set Rs = Cnn.OpenRecordset(SQL)

' GUI-Refresh over DataBinding against the Rs-Instance
Set MyGUIWidget.DataSource = Rs

--
And in case I'd have put the above (already View-based)
SQL-Code into a CommandObject first, then I would only
need to set the Command-Parameter (typed, and without
fiddling around with String-Concats).

InvoicesCmd.SetInteger !CustomerID, CurrentNode.ID
Set MyGUIWidget.DataSource = InvoicesCmd.Execute


Olaf



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about SQLite features.

[Olaf Schmidt]

"Chris Wolf" schrieb

[Nested Recordsets/Resultsets as an alternative to Joins,
 to "shape-off" redundancy in "hierarchical requests"...
 as a native DB-Feature usable over Sybase StoredProcs...
 ...and the ADO-ShapeProvider as an example for an
 alternative to use "these things" in a DBEngine-independent
 way (at least on the Win-Platform)]

> > If you request the construction of such a nested
> > Recordset at the serverside (over the Shape-
> > Provider), then there are no network-roundtrips
> > involved, in case the DB-Server and the
> > AppServer do run on the same machine.
> >
> > But we digress ... ;-)
> >
> In your scenario, here, even though the middle-tier
> ("business-layer-code") is collocated with
> the database, it looks to me like ADO is still a client-server
> technology (I'm relatively unfamiliar with it) you still need
> a "Connection" object to connect with the database, ...

You're basically right - ADO-Resultsets are the "storage-
container" (with an integrated Cursor-Engine), that
"looks always the same" to your Application-Code -
and the "ADO-Connection-Object" connects (involving
the OLEDB-driver) to the DBEngine in question.

And in case the DBengine runs on the same machine as
the OLEDB-Driver, then faster IPC-mechanisms
(than sockets) are usually involved.

> ...so I assume there's still a protocol stack, through which,
> the client-based "SHAPE" mechanism must make multiple
> (local) round trips.
The "protocoll-stack" is thin in this (local) case - but if
you want to put it this way, then yes - the SHAPE-Provider
will probably perform "multiple Requests" - how efficient
it does this (compared with "native Sybase") would need
to be tested.
At least I could imagine some Optimizations (working
over Joins, which include only the Parents Primary Key
in the Joined result, to avoid overboarding redundancy,
before the final Resultset is constructed and delivered
into the "hierarchical ADO-Recordset-container".

> Even though the ADO Connection is not as heavy-weight
> as HTTP, or even TCP/IP (I'm assuming for local connections
> it may be via named pipes) ...
...or Shared Memory...

> There's still serialization/deserialization of the client-server
> protocol stack.
No, would not think so - since most OLEDB-providers
(as well as the ADO-Recordsets) also support so
called "serverside cursors" - the "looping over incoming
records" can be done efficiently "in place", building the
hierarchy "on the fly" then (no larger buffer-conversion
or -swapping going on IMO).


> With stored procedures, the multiple open cursors to different
> tables are right there, running in the same process/thread
> space of that stored proc - no connection, no protocol
> stack, so it's going to be "much" faster.
I would not bet on your "much"-attribute that much... ;-)
After all there is an overhead of course - but the DB-engine
itself will need a larger amount of time internally, to perform
the "joining" (the key-lookups on the dependent tables).
How much faster native Sybase is, compared with the more
generic "ADO-Shaper" ... only a test can tell (in case you're
running on windows, this should be relative easy to do).

But the general point you made is understood I think -
Stored procedures, which do make use of builtin
specialities can (or better: usually) offer a better performance -
the question is, "how badly" an application is in need
of that advantage (or if it is large enough, to be noticable
at all).


Olaf



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Stored procedures (was: Question about SQLite features.)

[Olaf Schmidt]

"BareFeetWare"  schrieb
> On 12/11/2010, at 6:30 AM, Olaf Schmidt wrote:
>
> > "jeff archer"  schrieb
> >> From: "Olaf Schmidt"
> >> Wednesday, November 10, 2010 9:07:19 AM
> >>
> >>> [Stored procedures in SQLite]
> >>>
> >>> IMO stored procedure-support only makes
> >>> sense in "Server-Instances" which run on their own...
> >
> >> I disagree. The overall design and structure of applications
> >> using SQLite and therefor SQLite itself would benefit
> >> from SQLite supporting stored procedures.
> >> This would allow all code necessary for enforcing
> >> the business rules of the data to be stored in the
> >> database itself. This is just a good basic design principal.
> >
> > There was a somewhat similar sounding post (from BareFeetWare,
> > sent on 20.Oct to this list) who also encouraged, to include
> > "more logic" into the SQLite-Files itself, to reach more "portability".
>
> That post of mine is archived here:
>
http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2010-October/025096.html

Yep, that was it.

> For the record, I (for BareFeetWare) was advocating implementing
> database logic in constraints and triggers, rather than in
> application code. I was not actually talking about stored
> procedures.
> In order of preference, I would implement logic in:
>
> 1. Constraints
> 2. Triggers
> 9. Pure SQL (eg stored procedures)
> 10. Application code (ie an external language that
> calls SQL snippets)

After reading your posting again, it seems I had a
"wrong image" in the back of my mind, regarding
your post, sorry.

Since you are talking about *database-logic* -
our opinions do not differ that much I'd say...
>From my posting you were replying to ... I wrote:
  "...use "DB-internal mechanisms" only in a
   range, which is common among different
   DB-engines (Triggers for example,
   to ensure referential integrity at least)."

And constraints, which I left out, belong into the
same category, since they are "common enough"
too (among different DB-engines).

Maybe I also misunderstood Jeff Archer in this
regard, but I think he meant a different thing with:
  "...code necessary for enforcing the business rules..."

If he meant "business-logic" or "business layer", then
that's something, what in my opinion belongs into
a shareable Component (usually a Dll), written
in a normal language - but I think I already made
my points, let's not repeat them again - and it's
after all only a personal opinion.

> IMO, if you're implementing database logic (ie constraints
> and triggers) in application code, then you're reinventing
> the wheel, ...
As said, that was not what I was "complaining about".

[layout of a handmade "stored procedure mechanism for the poor"
 in SQLite ;-)]

> But my question is: why?
>
> Why would we want to perform stored procedures?
> Or why would we want to perform application code,
> if you're on that side of the "war"? ;-)

Ehhmm, because a "bunch of well-organized and consistent
data-records" is not (yet) an Application? ;-)

> In most cases, I suggest that you should be implementing your
> database logic in constraints and triggers, not in procedural
> code (ie not in SQL store procedures and not in application code).
Here you go again... ;-)
Nothing wrong with Triggers and Constraints (the
"database logic", as you call it). Things which ensure
(force) consistency on your Data belong into the DB.
They are usually well portable between different
Engines (good DB-Import-Tools can recognize and
understand most of these - where importing vendor-
specific Stored-Procedures into another Backend
can be adventurous at least, if we talk about larger
or complex procedures).

> If you have code (in either environment) that is looping or
> grabbing one result and sticking it in a second query,
> then your approach is probably flawed.
As you say: "probably" ... because "it depends" ...
(maybe your application wants to show "details"
 in a delayed fashion - e.g. when a Node in a
 Treeview gets "expanded").

Anyways, hope my position is more clear to you now,
and - (since you made this request) - you feel
"enlightened enough" in the meantime .

Olaf



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about SQLite features.

[Olaf Schmidt]

"Chris Wolf"  schrieb

> I can't resist adding my little opinion to yet another
> "business logic in stored procs vs. app layer" holy war...

... yeah, seems this thread is evolving nicely in this
regard ...

> I usually prefer keeping the business logic in the application
> layer and leaving the DB tier to be just a data provider.
> In actual practice, this is not always practical.
>
> Let's say you implement a service where the client can
> retrieve a set of top-level records,
> each has an id; then for each id, you get additional
> detail records from numerous detail/line-item tables.
> If you implement this as a collection of fine-grained services,
> i.e. each piece is a round trip from client, through web
> services layer, through to db layer; and for each top-level id
> in the result set - the performance will be abysmal.
>
> With Sybase stored procs, you can stack multiple result
> sets in one call, so in the above scenario, you invoke the
> lookup proc for each top-level id and the proc performs
> all the secondary detail queries and stacks it all together
> in a multiple-results  result-set, such that there's only one
> round-trip through the tiers for each top-level id in the set.

But that is a common problem, which can be solved
directly in the business-layer-code at the serverside
as well, also achieving a "stacked serialisation
of resultsets" in one roundtrip.
One can either code such a thing (a stacked serialization)
"by hand" (based on XML for example, which is well-nestable) -
or on a given platform (e.g. on Windows) one can avoid
these hand-coded parts by making use of one of the
already mentioned "DB-abstraction-helpers".
E.g. ADO does support so called "shaped, hierarchical
Recordsets" for a long time now ... over the "DataShape-
Provider" which is part of ADO (and plays together with
a lot of OLEDB-providers from different vendors...
not sure if the Sybase-Provider is one of those, which is
"Shape-Provider-capable").
http://support.microsoft.com/kb/189657

If you request the construction of such a nested
Recordset at the serverside (over the Shape-
Provider), then there are no network-roundtrips
involved, in case the DB-Server and the
AppServer do run on the same machine.

But we digress ... ;-)

> I don't see how this is pertinent to SQLite, since it's
> generally not used in multi-user client-server configurations.


I'm using it here in (smaller to midsized) multi-user scenarios -
my COM-wrapper (Dll) contains fast serialization-mechanisms
and a built-in AppServer for that "mode".

And I know that some Readers of this list also use
SQLite behind Servers (mostly over http then)
(the Fossil-repository is just another example).

These solutions are certainly not "large enterprise-stuff" -
but reliable and fast enough for ones own "special
applications".

Olaf



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about SQLite features.

[Olaf Schmidt]

"Petite Abeille" schrieb
>
> On Nov 11, 2010, at 8:30 PM, Olaf Schmidt wrote:
>
> > If such an "encapsulation of business-rules" is sitting in the
> > DB itself - written in a proprietary "DB-dialect", then you
> > cannot call such a thing a "business-layer" anymore.
>
> Nonsense :))

Of course... ;-)

Nah, seriously... you know, how I meant that and what
the context was ... *if* somebody decides to handle
DB-Interaction (and his "set of business-rules") *not*
directly "in the client" (or alternatively "in the DB-Server") -
then he obviously does so, to decouple the Client-Application
from the DB(-Backend) - allowing then (if done right),
to "connect" this intermediate layer to different clientside
Implementations (GUIs) - as well as different DB-Backends.

Encapsulated in a Dll with the right interfaces, one can
use it either serverside (e.g. behind a WebServer, to
talk to Browser-Clients - delivering JSON- or XML-
serialized Resultset-Content) - or behind a "real AppServer",
to talk to "Fat Clients" (delivering Resultset-Content
in a serialized "Object-Container" - as for example
disconnected ADO-Recordsets on Windows, which
are then understood by a large Set of languages, easily
bindable with mostly only one line of code to a
DataGrid or whatever GUI-Widget).
Heck, you can put such a layer-Dll even at the clientside,
to support a standalone App or alternatively a more
Client/Server-like approach, capable to work against
a different set of DB-engines even then (in the standalone
App for example, against SQLite).


["Helsinki Declaration(s)"...]
Cannot disagree more with these articles, sorry.
>From my experience his main-assumption is just
not true, that it is more difficult to develop such
a layer with "modern languages or environments" -
compared with a proprietary DB-dialect and
some DB-specific enhancements or features.

Perhaps you should give an example of a certain
stored procedure (not too complex, to keep
things more simple), describe what it does - and
then compare it with the implementaion-code,
done in a "normal language", which does use
ODBC/JDBC/ADO or whatever and is using
only "common SQL-statements", to achieve
the same thing in a DB-engine-independent way?


Olaf



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about SQLite features.

[Olaf Schmidt]

"jeff archer"  schrieb
>From: "Olaf Schmidt"
>Wednesday, November 10, 2010 9:07:19 AM
>
>>[Stored procedures in SQLite]
>>
>>IMO stored procedure-support only makes sense in
>> "Server-Instances" which run on their own...

> I disagree. The overall design and structure of
> applications using SQLite and therefor SQLite itself
> would benefit from SQLite supporting stored procedures.
> This would allow all code necessary for enforcing the
> business rules of the data to be stored in the database itself.
> This is just a good basic design principal.

There was a somewhat similar sounding post
(from BareFeetWare, sent on 20.Oct to this list)
who also encouraged, to include "more logic" into
the SQLite-Files itself, to reach more "portability".

All nice and well, but the problem is two-fold IMO.
Portability with regards to Application-Code
(exchangeability of "The DB" among different languages)
and on the other hand, portability of the Data (the DB,
the "Backend").

>From my experience one should use "DB-internal
mechanisms" only in a range, which is common
among different DB-engines (Triggers for example,
to ensure referential integrity at least).

But a "Stored-procedure-language" which is
more or less proprietary to a certain DB, does
not work out that well, if your Application (or
your Business-Layer with your "business-rules")
needs to be able, to talk to a different backend
(since your new customer "just made the wish"...;-)

Then backend-portability is required - and the less
voluminous and complex your "special code" in your
current DB-backend is, the faster will be your switch
to a new backend (using the App-language for the
business-rules then, supported by some sort of
"DB-Abstraction-Helper" of course - as e.g. JDBC,
ODBC - or ADO/OLEDB in the Windows-world).

And if you want to ensure (aside from easy backend-
portability), that your "business-rules" (your business-
layer) survives "App-language-switches" as well, then you
should encapsulate it in a component (a Dll for example) -
containing a public reachable API, which is usable/callable
from different languages then.

If such an "encapsulation of business-rules" is sitting in the
DB itself - written in a proprietary "DB-dialect", then you
cannot call such a thing a "business-layer" anymore.

A layer is a thing, sitting "in-between" (to abstract
from two sides, in this case the App-language(s) *and*
the Storage-Engine(s)) ... and not a thing "sitting inside
somewhere".

Just my 2cts...


Olaf



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about SQLite features.

[Olaf Schmidt]

"Tran Van Hoc"  schrieb im
Newsbeitrag news:43a0ef604a674b3fb80d1447b41f8...@isbvietnam.com...

[Stored procedures in SQLite]

IMO stored procedure-support only makes
sense in "Server-Instances" which run
on their own (and communicate over
different IPC-mechanisms, mainly sockets,
with their "Clients").

But SQLite is not such "a Server" - it's a
*library* and as such it offers its API
directly to the hosting Process.

If you choose, to implement a small Server-
Host (talking over sockets with "DB-Clients"),
then you can write your stored Procedures
in any language you want - either "in a static way"
which would be implemented in the language you
choose to write the Server-Host-Application with
(in case this language has no scripting-capabilities).
But if you choose TCL/Perl/Ruby/Lua/Python/etc.
as your scripting-language, you're free to do
so too - then your scripts could even be stored
within "normal DB-Tables" in your serverside
SQLite-DB and executed dynamically on request.
No need to learn "special Stored-Procedure-syntax" -
just talk in your scripting-language of choice with
SQLite - using everything the SQLite-API has
to offer (depending a bit on the wrapper-bindings
of the scripting-language in question).

If you do not want to implement such a hosting
Server-instance yourself, you could always choose
a WebServer-environment as "the Server-layer".
These Hosts can "talk http" - and on the serverside
you have support for all sorts of scripting-languages,
as the ones just listed above.

Olaf



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] New to SQLite and I have a question

[Olaf Schmidt]

"Bob Keeland"  schrieb

> I am new to using SQLite but think that it may be good
> for a project that I'm working on. I do my programming
> in Visual Basic and don't know any C\C++.
> Is there any problem with connecting with SQLite from
> Visual Basic?
No.
Though the links Simon gave you, are for wrappers which
"connect" you to the (VB).NET world.
If it is "classic VB" you're using (up to VB-Version 5/6) -
then you can also take a look at the COM-Wrapper-
section in the SQLite-wiki:
http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers

>...
>
> My main question - Does this sound like SQLite would
> be appropriate?
Yes, from what you wrote, there should be no problems -
does not seem like a "heavy scenario" - and could even
fit into memory completely (at least on Desktop-Systems),
and SQLite supports "InMemory-Mode".

As to expanding your solution to Handheld-Devices ...
SQLite is working fine there (although many Devs
prefer working at the lower "C-language-level" then) -
but as far as I know, Robert Simpsons .NET-wrapper
(http://sqlite.phxsoftware.com) should work there
as well... as long as you're targetting devices, which
support the .NET-mobile framework (then you
could work further with your language-binding as
long as it is the VB.NET-basic-dialect and not
VB-Classic).

A broader approach, to bring your "search-services"
to these devices would be a "Web-hosted one",
since most of the newer Smartphones come with
a decent Browser (and often with permanent Internet-
connection nowadays).

> I've been using Access as my database up to now...
As said, if VB.NET, then http://sqlite.phxsoftware.com
is a good recommendation (working over ADO.NET)...
and in case we're talking about VB-Classic and
existing experience with "normal (COM-) ADO" - then
my wrapper at: http://www.thecommon.net/2.html
is probably the one with the greatest resemblance
to ADO-behaviour - not much to learn anew.

But if your GUI (for the Desktop-Version) is not too
complex, I'd develop a "unified solution" (for both,
Desktop and HandHeld) as a WebApp ...


Olaf



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to search for asterix character?

[Olaf Schmidt]

"Bart Smissaert" 
schrieb im Newsbeitrag
news:aanlktikivzcbz81hqs28dtptoy8h6hc6nbukesmth...@mail.gmail.com...

> > "...Where SomeColumnContent Like '%someother[*]part%'
>
> Thanks Olaf, that works fine.
> As my customers won't get this I think I might let my
> code take care of this.
Perhaps a good idea. ;-)

> How would it work with glob?
Umm, not a glob expert  don't use it here (yet).
my first thought would be, to precede the char in question
with an escape-char (as the backslash)... testing...

No, it apparently works in the same way as my overridden
like per:
"...Where SomeColumnContent glob '*someother[*]part*'

But possibly not case-insensitive (not tested, that) -
and not "unicode-aware" of course.


Olaf



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to search for asterix character?

[Olaf Schmidt]

"Bart Smissaert" <bart.smissa...@gmail.com>
schrieb im Newsbeitrag
news:aanlktil5lha-3-l6x8umwv8e3pyrda6h0ln3dcoyh...@mail.gmail.com...

> Yes, it must be either my code or the wrapper to blame.
> Thanks for confirming.

It's a wrapper-"problem", but a perhaps a coding problem
as well... ;-)

The wrapper overrides sqlites internal Like-Function,
to achieve (Unicode-)BSTR mapping.

Internally VBs normal Like-Function then does "all the rest".

The wrapper (due to compatibility reasons with regards
to JET *.mdbs) also understands their older "like-syntax":
"...Where SomeColumnContent Like '*somepart*'
(in this regards behaving somewhat similar to SQLites glob,
which BTW was _not_ overriden)...

But it supports the "default-like-syntax" as well:
"...Where SomeColumnContent Like '%somepart%'

To specify single chars, which are equal to the "comparison-
descriptors" you have to "escape them" within brackets:
"...Where SomeColumnContent Like '%someother[*]part%'

If the above should not work, then this would be considered
a "real wrapper-bug". ;-)


HTH

Olaf Schmidt



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [Windows] Application to let end-users handle records?

[Olaf Schmidt]

"Gilles Ganault" <gilles.gana...@free.fr> schrieb im
Newsbeitrag news:uk03v5h4ovo8un5hq68ldbotuf1fujr...@4ax.com...
> On Mon, 17 May 2010 18:59:36 +0200, "Olaf Schmidt"
> <s...@online.de> wrote:
> >I'd say, what you're looking for (in case a "softer" migration
> >of Excel-VBA-Devs is planned) is more a library IMO -
> >and not an "Application".
>
> Thanks, I'll check it out. Ideally, I was looking for a tool that
> would read data entry forms from an external file and allow
> users to create/edit records through a GUI without my writing
> an app specifically for them.

Well, in that case it boils down to: how complex these
"data entry-forms" may be (if I assume, that the *user*
needs to be able, to edit these "external files" with a
simple TextEditor, to define the "forms behaviour"
and the "recordwise scrollable Edit-Fields" which
need to be contained there) - otherwise I do not see,
why the user should not use the (VB-Macro) IDEs of
either Excel or OpenOffice-Calc directly.

Hmm, thinking about that requirement (defining an entry-
forms content + behaviour per written text) - there are
some people, who'd indeed call that "programming". ;-)

No, seriously - if the requirement is, that the user
should only need to enter something like that into
these "external data-entry-form-textfiles":

TableName: SomeDBTable
ColumnList: All
Filter: SomeDblColumn > 1.00
Filter: SomeDateColumn Between 'Now' and 'Then'
Writable: Yes
...
And then the application (when directed per Directory-List-
Click to such an entry), would automatically create the
needed Form-Mask, containing all the pre-defined editable-
Fields, matching the SQL-Column-Type automatically
with the proper "edit-behaviour" and all the Fields get
correct "labeling" - then such Definition-Files would be
of course doable (and editable) by virtually anybody
(and would help to cover a lot of  simpler "edit-cases").

Dunno if a "lean App" which covers that, already exists (for a
windows-os) - but writing such a dynamic form-creation-engine
for the special purposes of your users shouldn't be that hard,
if you have a bit of experience.

Other "text-based form definition-files" are *.html of
course - or *.xml or *.xaml (in case you want to do that
per .NET) - but any solution would (IMO) involve at
least a bit of "preparation" first, by a developer who
adapts this "dynamic form-creation-approach" to the
needs of the users in question.

But in case your users could live with the ability, to
edit their table-records over a Grid-Visualization, then
Henks suggestion, to use one of the many DBManagers
out there would be an option too ... don't know if some
of them also contain a User-editable "non-Grid-View"
with dedicated Entry-Fields in vertical arrangement
(to fullfill your "recordwise" navigation-requirement).

Olaf Schmidt




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [Windows] Application to let end-users handle records?

[Olaf Schmidt]

"Gilles Ganault" <gilles.gana...@free.fr> schrieb im
Newsbeitrag news:tmr1v5lfsa26lcbooq20sh0q4358jdf...@4ax.com...

> I was wondering: To help people move from Excel
> to a DB to handle data, I'd like to show them a really
> good Windows application that will enable them to
> create/read/update/delete records, and should present
> users with forms to handle records (not tables, as this is too
> abstract for users).
>
> Ideally, it should also a somewhat-protected admin
> section where I could perform tasks such as managing tables,
> creating/deleting indexes, etc., but I can do without.
>
> Is there a open- or closed-source Windows application
> that you would recommend for end-users?

I'd say, what you're looking for (in case a "softer" migration
of Excel-VBA-Devs is planned) is more a library IMO -
and not an "Application".

And Excel-VBA experienced Users/Devs usually have some
"Record-" or Recordset-experience per ADO - which is
a COM-layer - and COM is (yet) the preferred component-
library-mechanism in VBA (as well as in the VB5/6 world).

So, I'd suggest, that you take a look at my (free binary) COM-
based SQLite-wrapper (dhRichClient3.dll) which you can
download here: www.thecommon.net/3.html

This wrapper allows an ADO-like usage of Connection-,
Command- and Recordset-Objects - so, "record-oriented"
working with specified SQL-Selects is no problem - also
directly within Excel-VBA-code - or if you want to present
them a "standalone Windows-App", then a VB5- or VB6-
compiler would be sufficient, to create such a thing with ease
(there's a VB6-code Demo-package too on the site above,
 which implements (in the SQLite-SubFolder) a pretty
 complete Demo-App around NWind.db, which includes
 also "Grid-Visualization" with only a few lines of code per
 Form).

And I know, that the wrapper is already used directly within
Excel-VBA too, by many "Office-Devs" - and I've already
included some Excel-convenience-functions into it, to e.g.
support putting the contents of a query (buffered on the
return of a query within a cRecordset first) from such an Rs
into the XL-cells directly with one or two lines of code per either:
XLRange = Rs.GetRows
or
XLRange = Rs.GetRowsWithHeaders
alternatively one can use:
XLRange.CopyFromRecordset Rs.GetADORsFromContent
or
XLRange.CopyFromRecordset Rs.DataSource

And with regards to DDL - the wrapper supports that currently
only at the "lower-level" per Cnn.Execute "DDL-statement" - but
in case you want to do these Table- and Index-Creations in a "more
comfortable objectoriented" way, you could look at Jason Peter
Browns website, who wrote some wrapper-classes for that:
http://www.jasonpeterbrown.com/jpbdbfactory.html

And whilst the above is with regards to objectoriented schema-
*creation* - what's already directly built-in is, to enumerate
and readout an already existing schema (e.g. for visualizing in
a TreeView) per:
For Each Db In Cnn.Databases
For Each Tbl In DB.Tables
For Each Clm In Tbl.Columns
'...
Next Clm
For Each Idx In Tbl.Indexes
'...
Next Idx
Next Tbl
Next Db


Olaf Schmidt





___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE3 in multi-thread server

[Olaf Schmidt]

"HLS" <sant9...@gmail.com> schrieb im
Newsbeitrag
news:9cf5931e1003160705v38335b5g47a3d91193c28...@mail.gmail.com...

> ...we have a ISAM/BTREE database under our full
> reader/writer and exclusive locking controls...

>From your posted code-snippet I don't see any
"heavy SQL-Query-usage" - so, first the question,
why would you want to change your current system?

If you need only something like a "fast entry" into
some of your DataFile-Chunks (per FileNameAreaKey)
and then enumerate (record-)content in these
Data-Chunks, respecting some "sort-order" - why choose
a system which is based on SQL-query-processing?

There are other engines, which fit better for such
scenarios - berkeley-db comes to mind, which in
its latest incarnations should be usable threadsafe too
(not sure about concurrency and locking in that engine).
Or just "write your own thing", if the goal is only,
to achieve fast "ordered enumeration" of more or less
simple records-structs, hosted in files.

If SQL-based querying is (becoming) something which
would be "nice to have", then maybe consider other engines,
which work "better over sockets" (since this mode is
built-in) and have not that much "locking-restrictions"
as SQLite in concurrent scenarios (and also support
record-level-locking directly).

If it has to be SQLite, because it is nice, small, fast and
easy to deploy - then you should consider a completely
different approach with regards to your current client-
side locking-handling.

We also use an RPC-server, with the SQLite-engine
as the serverside backend - but we "completely isolate"
the serverside-cursors from the clientside - meaning, we
perform our SQL-query at the serverside - and serialize
(copy) all the records, according to the "set the SQL-string
describes" into a "transferrable container-object" (a Recordset,
or ResultSet) first.  This container-objects content is then
compressed and transferred over sockets to the clientside
(after all the sqlite-handles for the query in question were
freed).

This works fast and robust, no sqlite-handles are kept open
between requests - in fact this works faster than "serializing
and transferring each record separately over the sockets,
whilst keeping a bunch of serverside SQLite-cursors alive,
which can only be moved forward".

At the clientside, the transferred container (the Recordset)
is freely navigatable (back and forth) - supports its own
Find-, Sort- methods - contains all the Field-Type-
descriptions and so on...

If we need Record-Level-locking at the clientside, we
just update a (serverside) LockedRecords-Table with
the currently locked RecordIDs (+UserID - and some
extra-fields for some "timeout-handling") - and based on
queries against these "locking-tables", we can "colorize"
the currently locked Records appropriately in our GUI.

I understand, that you "don't want to rewrite, what you
currently have" - but (at least for an *SQLite*-engine in the
backend) you will have to IMO.

Nonetheless, such a "disconnected approach" is in the
meantime something like "common-practise" - given all
the Browser-based clients which work against
"RPC-Servers, or Application-Servers - or let's call them
*WebServers* ;-) ...over sockets (e.g. receiving JSON-
serialized Resultsets over http) - ... no matter what SQL-
engine is working in the backend of such a WebSite ...
"clientside-controlled Servercursors" are "a thing of the
past" IMO, since they "introduce more problems than
they solve".

Olaf Schmidt



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fastest concurrent read-only performance (+ threads vsprocesses) [BUG?]

[Olaf Schmidt]

"Marcus Grimm"  schrieb im
Newsbeitrag news:4b9a01e8.2060...@medcom-online.de...

> Increasing the number of threads does affect the overall
> read performance slightly, example:
> 1 Thread: 11.2 sec
> 16 Threads: 8.2 sec
> Single Process: 11sec.
>
> I still would expect a better scaling, ...

IMO, what Lukes timings have clearly shown is,
that if you would enhance your "single-process-test"
to a "multiple, parallel-working-processes-test",
that the scaling would work nearly perfectly (as
it should).

Processes (only running their single "Default-Thread")
when working in parallel, would not profit from an
enabled Shared-Cache (no matter what the compile-
settings or the open-settings are).

Nearly the same should be expectable (naively thought) from
multiple, parallel threads within one single process. If Shared
Cache is "Off" in this mode, then the behaviour should be the
same as with multiple-running-processes (each process
only running one single thread).

In both cases the mem-consumption is higher, since
each "worker-thread" or "worker-process" runs its
own, separate cache, but the benefits in performance
(when all the caches are hot after a while) should not
only occur in the multiple-process-scenario.

So the question really is, what is different, when we
compare the multiple-process-based mode with the
multiple-threads mode (Shared-Cache = "Off").
Both modes want to trade memory versus performance,
but only the multiple-processes-mode gets the bargain
from this exchange currently.

One obvious thing (which very well could be the only
cause) is, that in process-mode, we have isolated
memory - and all the (cross-thread-)Locking-requests are
meaningless (don't wait, or cost much performance), because
there's only one single thread in each running process. Only
the "cross-process-capable locks" near the file-level take
effect and ensure correct concurrent behaviour between
multiple *processes*.
This should work Ok for multiple processes, even when
SQLite was compiled in "plain singlethreaded mode".

Now, using a similar, singlethreaded (no mutexes, no locking)
compiled sqlite3-library should result in the same performance as
with multiple-processes, when used from multiple-threads
(No Shared Cache).

If my assumption is right, that running such a multithreaded
scenario against a singlethreaded compiled library performs
at the same level as the multiple-processes-scenario,
then the question remains, how one could make this mode
"secure and usable" in the same way as with the "naturally isolated"
process-memory. Speaking only for the Win-platform, thread-
local storage comes to mind, or maybe it is enough (when all the
larger, important SQLite-object-types are created from the heap -
or at least can be forced to...), that the Heap-handle gets created
with the appropriate Creation-Flags for secure, threadsafe
allocations from this Heap-Handle later on.

Olaf



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fastest concurrent read-only performance (+ threads vsprocesses)

[Olaf Schmidt]

"Pavel Ivanov"  schrieb im
Newsbeitrag
news:f3d9d2131003051131k23c7b61cueda0bcc72e6aa...@mail.gmail.com...

Oops, pressed send unintentionally...

> > Long story short - I suspect the open-call, to be the "blocker"
> > in your "SharedCache-Mode=Off"-scenario.

> If database file is pretty large, query reads a lot of data while
> executing and all data read fit into database cache configured
>  then I think I/O will be the main difference between "with-shared-
> cache" and "without-shared-cache" scenarios.

The rest of your reply was probably meant for Luke,
but the statement above is from my post, so ...

>From his timing-results Luke clearly reports blocking
behaviour with Shared-Cache=Off (although the blocking
should only be reasonable with Shared-Cache=On) ...
So, since everybody seems to agree about, that without
Shared-Cache the blocking on sqlite3_step() should
*not* happen - I suspect the open-calls (in each threaded
request) to be the culprit.

So, I don't mean my post with regards to the additional
overhead from the open-call, doing its "real work" (schema-
info-parsing and stuff) - instead from the results Luke has posted,
I'd think, that there's an active mutex in the open-sequence-
actions of SQLite, which prevents the parallelism.

Olaf



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fastest concurrent read-only performance (+ threads vsprocesses)

[Olaf Schmidt]

"Pavel Ivanov" <paiva...@gmail.com> schrieb im
Newsbeitrag
news:f3d9d2131003051131k23c7b61cueda0bcc72e6aa...@mail.gmail.com...
> Long story short - I suspect the open-call, to be the "blocker"
> in your "SharedCache-Mode=Off"-scenario.

If database file is pretty large, query reads a lot of data while
executing and all data read fit into database cache configured then I
think I/O will be the main difference between "with-shared-cache" and
"without-shared-cache" scenarios.

But how did you understand when queries performed more in "serial" way
and when more in "parallel" way? I've re-read all your messages and
didn't find how you did that. And what's the running time when you
tried to run queries in several processes?

And one more probably the main question: what threading library are
you using? Is it kernel-space or user-space threads? Maybe this
library serializes execution of your threads when processes can be
parallelized over CPU cores.


Pavel

On Fri, Mar 5, 2010 at 2:09 PM, Olaf Schmidt
<s...@online.de> wrote:
>
> "Luke Evans" <luk...@me.com> schrieb im Newsbeitrag
>
news:5d6df6e4-c817-4788-a2a2-87dc5e32f...@me.com...
>> Thanks very much for your notes Olaf.
>>
>> I've done as you suggest... I already had SQLITE_THREADSAFE=2
>> defined, but didn't have the SQLITE_OMIT_SHARED_CACHE
>> asserted (assuming private cache to be the default).
>
> Ok, don't know the (compile-)default of SQLites
> SharedCache-mode - this was only to make sure,
> nothing went wrong with regards to your ...sqlite3_open_v2() call.
>
> And BTW (since you asked that below) ... with the term:
> "dynamic access-mode-switches" I meant these switches
> in the open-call - which you mentioned in your first post:
>
> "despite having SQLITE_CONFIG_MULTITHREAD set,
> SQLLITE_CONFIG_MEMSTATUS off, with
> SQLITE_OPEN_SHAREDCACHE and SQLITE_OPEN_NOMUTEX
> used on open."
>
> Reading your posted code-snippet (good you've included that) -
> it seems, you're handling such a "threaded read-request"
> *including* the open-call in each these "ReadOut-QueryActions".
>
> How does the whole thing perform, if you throw out
> the Open-call from your threaded request-handling?
>
> For that you could change your threading-model to a "longer-
> living one", spanning the workerthreads with a "fixed thread-
> pool-count", each thread then opening your DB on startup.
> And then let all these worker-threads in the pool enter an
> efficient wait-loop, waiting for a "process-request" message...
> (Not sure about the threading-model on OS-X - I'm more
> a Windows-guy).
>
> Then (in case of an incoming query on your main-thread)
> you could perform a loop on your central "threadpool-
> handler-object" (which knows all its spanned worker-threads)
> for a "free-slot" (a shared memory-area), then place the
> SQL-string there and inform the found worker-thread
> with a "Messaging-Event-mechanism of your choice",
> that it has to process the query and place your resultset-object
> in its thread-slot.
>
> Long story short - I suspect the open-call, to be the "blocker"
> in your "SharedCache-Mode=Off"-scenario.
>
> Would be interested, how the whole thing performs (and
> compares to your 4.5 seconds in SharedCache-mode),
> if you ensure the DBHandle-values beforehand within your
> threads (soon after thread-startup) - and then perform only
> the "statement- and step-actions" in your threaded timings -
> and maybe try the performance-test more than once in your
> test-session, to get also the timings for "hot SQLite-caches"
> (on each of your separate "Thread-DBHandles").
>
> Olaf
>
>
>
> ___
> 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



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fastest concurrent read-only performance (+ threads vs processes)

[Olaf Schmidt]

"Luke Evans"  schrieb im Newsbeitrag
news:5d6df6e4-c817-4788-a2a2-87dc5e32f...@me.com...
> Thanks very much for your notes Olaf.
>
> I've done as you suggest... I already had SQLITE_THREADSAFE=2
> defined, but didn't have the SQLITE_OMIT_SHARED_CACHE
> asserted (assuming private cache to be the default).

Ok, don't know the (compile-)default of SQLites
SharedCache-mode - this was only to make sure,
nothing went wrong with regards to your ...sqlite3_open_v2() call.

And BTW (since you asked that below) ... with the term:
"dynamic access-mode-switches" I meant these switches
in the open-call - which you mentioned in your first post:

  "despite having SQLITE_CONFIG_MULTITHREAD set,
   SQLLITE_CONFIG_MEMSTATUS off, with
   SQLITE_OPEN_SHAREDCACHE and SQLITE_OPEN_NOMUTEX
   used on open."

Reading your posted code-snippet (good you've included that) -
it seems, you're handling such a "threaded read-request"
*including* the open-call in each these "ReadOut-QueryActions".

How does the whole thing perform, if you throw out
the Open-call from your threaded request-handling?

For that you could change your threading-model to a "longer-
living one", spanning the workerthreads with a "fixed thread-
pool-count", each thread then opening your DB on startup.
And then let all these worker-threads in the pool enter an
efficient wait-loop, waiting for a "process-request" message...
(Not sure about the threading-model on OS-X - I'm more
 a Windows-guy).

Then (in case of an incoming query on your main-thread)
you could perform a loop on your central "threadpool-
handler-object" (which knows all its spanned worker-threads)
for a "free-slot" (a shared memory-area), then place the
SQL-string there and inform the found worker-thread
with a "Messaging-Event-mechanism of your choice",
that it has to process the query and place your resultset-object
in its thread-slot.

Long story short - I suspect the open-call, to be the "blocker"
in your "SharedCache-Mode=Off"-scenario.

Would be interested, how the whole thing performs (and
compares to your 4.5 seconds in SharedCache-mode),
if you ensure the DBHandle-values beforehand within your
threads (soon after thread-startup) - and then perform only
the "statement- and step-actions" in your threaded timings -
and maybe try the performance-test more than once in your
test-session, to get also the timings for "hot SQLite-caches"
(on each of your separate "Thread-DBHandles").

Olaf



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fastest concurrent read-only performance (+ threads vs processes)

[Olaf Schmidt]

"Luke Evans"  schrieb im Newsbeitrag
news:3be16206-d0c6-4041-a3a6-ca3c069ee...@me.com...

> It's Objective-C, but all the SQLite interfacing bits are pure C
> directly driving the SQLite API compiled into the program
> (3.6.22 amalgamation).
Just to make sure ... do you really create different DB-Handles
(over dedicated DBOpen-Calls) in each of your threads?

Aside from that, could you recompile the SQLite-library
on your system explicitely with:
SQLITE_THREADSAFE=2
and
SQLITE_OMIT_SHARED_CACHE

And then just open the DBHandles without giving
explicit "sqlite_open_v2()  Flags"?

Maybe (if that solves these "serialized Read-access-issues")
it could indicate, that the "dynamic access-mode-switches"
in sqlite_open_v2() do not work as expected in threaded
mode (at least on your system-OS).

Olaf







___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Mozilla's method

[Olaf Schmidt]

"Artur Reilin" <sql...@yuedream.de> schrieb im
Newsbeitrag news:op.u5vno6hp1pq...@rear...

> If you are using something like an log system it
> would be better in this way, but in apps like an
> shop what wouldn't be so great. (thinking about
> ebay with the bets and such..)

Of course, but I think I made that already clear,
that the approach should not to be misunderstood as a
"general recommendation" - it really should be used only within
smaller Apps, which don't need e.g. "stacked transactions",
or "complex transactions which could fail" ... Apps which
also only work singlethreaded within a single process ...
...the timer-based transaction-syncing then only an
"easier applicable workaround" in environments which
cannot - (or don't want to) make use of the more efficient
working async-writer-thread implementation of the SQLite-engine).

Olaf Schmidt




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Mozilla's method

[Olaf Schmidt]

"Artur Reilin" <sql...@yuedream.de> schrieb im
Newsbeitrag news:op.u5vlqcps1pq...@rear...

> But that means, if there is a power off or an system crash,
> your data which you send at this moment, goes nirvana.

Yep, as I wrote at the end of my post:
  "...in case of an unexpected Close of the App (due to
   whatever reason), you will lose only the new data which
   was gathered within the last timer-interval."

The Timer-interval in question should therefore not be
too large - also with regards to "palpable App-Blocking"
in the continously (timer-triggered) "syncing Events" ... but also
not too small, to achieve the expected "performance effect" -
so, at least "more than only one single new log-record" should
be gathered (on average) within the interval, to work with a
somewhat better write-efficiency.

Would require some experimenting first, which timer-interval
works best (depends somewhat on the frequency and size of
the incoming new data-records, but also on the underlying
storage-media, the DB is hosted on - be it flash-based media,
as USB-sticks for example - or "real Hard-Disks").

Olaf Schmidt



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Mozilla's method

[Olaf Schmidt]

"Bert Nelsen" <bert.nel...@googlemail.com> schrieb
im Newsbeitrag
news:a5ffd531001010911r3de60ec1o44e2c14bce7a7...@mail.gmail.com...

> So SQLite looks at both the database on the disk
> and in memory?
> Wouldn't that be difficult???

Of course... ;-)
And what's so amazing with the SQLite-engine -
is, that all that comes in such a small package.

Regarding "aggregated writes" and transactions again...

What SQLites async-writer thread does, is to
implement something like a "delayed write" at
the DB-engine-level (instead to rely on such a
feature to be implemented at the filesystem-level).

Delayed writes can ensure better performance, since
the new gathered (to be written) data can be grouped
(sometimes also reordered) into larger chunks,
to let the "real disk actions" happen within a more
optimal (more efficient) "operation-window", so to say.

In case of your (smaller) VB-based application you can
achieve something like that also with an "always opened"
transaction, gathering the new to be written data (records)
using SQLites internal transaction-cache-mechanisms -
and then writing it out (syncing it) to disk in a somewhat more
"relaxed" fashion (with better efficiency) from within a timer-
event - thereby avoiding the usage of threads.

That said, I'd recommend the following "approach" only
for smaller Apps/Tools which "own the DB exclusively" -
and are not expected to grow much over time (implementing
smaller logging-scenarios for example - as in your case for
the incoming GPS-data).

At App-startup (e.g. in Form_Load of the VB-App):

Private Sub Form_Load()
  InstantiateAndOpenTheConnection
  Cnn.BeginTrans 'ensure an opened transaction
  '... other init-stuff
End Sub

'in a Timer on that Form (e.g. set to 200-500msec)
Private Sub tmrDelayedWrite_Timer()
If Cnn.TransactionStackCounter = 0 Then
Cnn.BeginTrans 'just in case... (we're not expecting to reach here)
Else
Cnn.CommitTrans 'sync the currently "cached" content
Cnn.BeginTrans 'and reopen a new transaction after that
End If
End Sub

And on App-ShutDown just ensure, that "all the rest" is written too
Private Sub Form_Unload(Cancel As Integer)
tmrDelayedWrite.Enabled = False 'disable the timer first

If Cnn.TransactionStackCounter Then
Cnn.CommitTrans 'write the remaining stuff
End If
End Sub

As said, in that mode you should work only with "proven
commands" which are expected to "never fail" (with regards
to an then unnecessary Cnn.RollBackTrans, which could "mess
up" the whole simplified or "globalized" approach above).
And you should *not* work with the wrappers
Recordset.UpdateBatch-feature in that mode, since this
method performs an implicit transaction on the (Rs-internally)
gathered data (from Rs.AddNew, Rs.Delete or  Rs-FieldChanges).

So, to be entirely sure, you could open all the Recordsets
ReadOnly (using the Optional Flag in the OpenRecordset-
method) - and then work only with Cnn.Executes or better
the Command-Objects in write-direction.

This way the "real writes" - the syncing - only happens within
the timer-event - you will risk no DB-corruption this way -
and in case of an unexpected Close of the App (due to whatever
reason), you will lose only the new data which was gathered
within the last timer-interval.

Olaf Schmidt



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Mozilla's method

[Olaf Schmidt]

"Bert Nelsen" <bert.nel...@googlemail.com> schrieb
im Newsbeitrag
news:a5ffd530912311004p26a7cc5k1f1bf6f671bef...@mail.gmail.com...

> Your .Sychronous = False property does everything
> as fast as I want, and I am not afraid of losing some
> user data (it's not a critical application) but
> I am very much afraid of having a corrupted db.
> Can anybody please confirm
> that there is no chance of getting my db corrupted?

Ah, I see now, where the "confusion" came from.
The wrappers Synchronous-Property has nothing to do
with the (relative new) async-writer-feature of SQLite -
instead it maps to SQLites Synchronous PRAGMA
(as a "convenience property").

You can set all the Pragmas alternatively also per
Cnn.Execute "PRAGMA pragma_name ..."

or read out a current Pragma-Value with:
Cnn.OpenRecordset("PRAGMA pragma_name")(0).Value

Please read about SQLite-Pragmas here:
http://www.sqlite.org/pragma.html
... and what's written there about the Synchronous-Pragma-
Settings. With the Synchronous-Pragma at 'Off' or '0', you're
risking DB-corruption.

So, I would not touch the Synchronous-Property in your
case (leaving it at its default FULL(2)) - instead you should
wrap larger insert- or update-actions within a transaction -
that works fast as well.
Also consider using the binding-support of the wrapper
(the Command-Objects), to achieve faster (and more
typesafe) operations in "write direction".

Olaf Schmidt




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Mozilla's method

[Olaf Schmidt]

"Bert Nelsen" <bert.nel...@googlemail.com> schrieb
im Newsbeitrag
news:a5ffd530912310853t7024d908tefbf1ef40df47...@mail.gmail.com...

> I would like to make writes to my SQLite db faster.
In what regard (under which circumstances) is it slow
currently?

What's your typical recordcount per transaction?
Do you use transactions at all?
How many indexes are defined on the "slow tables"?
Are you using Command-Objects (the sqlite-bind API)?
In what language is the App-Host written (regarding
easy "thread-control")?
Do you work through a wrapper yet, or with the SQLite-lib
directly?

> I was thinking about the Async method, but I think I
> remember reading somewhere that it may cause database
> corruption.
IMO the async-feature was not risky with regards to data-
corruption, only with regards to durability - an (uncorrupted)
DB could contain "lesser data" (not the last "version", which
your successfully reported transactions suggested earlier) -
in case of e.g. a powerfailure.
But your "mozilla-comment-snippet" already mentioned that too.

I remember, that at some point in time you were using my
COM-wrapper, to work with SQLite. And I was thinking
about "handing out" the async-functionality over an appropriate
method, as the feature came up - but then stayed away from
it, for the sake of higher stability. The COM-wrapper is
mostly used in conjunction with VB5/6 - and there you have
not that much control over the teardown-process of an App
like in other, more "bare to the metal" languages - imagine
the async SQLite-WriterThread, having "yet some stuff
in the pipe" - and a closing VB-App, which only "knows"
the COM-Object - and not the SQLite-lib behind it, which
"owns" the async thread.

So, just in case you're using the dhRichClient3-wrapper-lib,
there's currently no plan (and no time), to make that feature
available in a reliable and stable working way (playing well
with VB5/6) over the COM-interface.

Olaf Schmidt



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ICU collation

[Olaf Schmidt]

"Igor Tandetnik"  schrieb im
Newsbeitrag news:hgom0b$u0...@ger.gmane.org...
Sylvain Pointeau
 wrote:
> > How does sqlite handle ICU, I don't understand?
> > is it used only for the sort order?

> No, the collation is used both for sorting and for equality check.
> However, ICU doesn't actually treat 'ä' as equal to 'ae'. You can see for
yourself here:

>
http://demo.icu-project.org/icu-bin/locexp?_=de_DE_=en=col=phonebook
Nice link, thanks...

> Try sorting
>
> ae
> áe
> ä

> You'll see they actually get sorted in that order.
>  'ä' and 'ae' can't possibly be considered equal when there
> exist strings that sort between them.

As I see it - one has different (fine-tuning) options for
ICU, which work "on top" of a choosen "base-setting".
If I set the first entry (all others remain at their defaults) to:
L1 = Base Letters

then ICU behaves IMO in the way the OP wants it to.
ae
áe
ä
are treated the same then...

Not sure, if the current ICU-implementation in SQLite (as it is)
allows to set these advanced flags "from outside" per SQL ...


Olaf



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite with HTA(vbscript) Date Format Problem

[Olaf Schmidt]

"Çaðlar Orhan" <cagla...@gmail.com> schrieb im
Newsbeitrag
news:677fef480912130642u5ed971b2r2c9cbec17771e...@mail.gmail.com...
> I am using SQLite for my little HTA application with
> vbscript.
> Everything is ok but in my sql query date format gets
> wrong records. SQLite uses YY-MM-DD
Normally it should be: -MM-DD

> i am querying with DD-MM-YY what should i do?
You are aware of the VBScript Format(...) function?
Just get your input right, before you feed it to the
SQLite-engine in your concatenated SQL-command-
string...

Dim D
D = CDate(Now) 'ensure a Variant-DateType within 'D'

MsgBox Format(D, "'\-mm\-dd'")
will give you the content of the Date-Variable as:
'-MM-DD'

and if your SQLite-Table really contains the dates in YY-MM-DD,
then:
MsgBox Format(D, "'yy\-mm\-dd'")
will give you:
'YY-MM-DD'
appropriately (in both cases already including the quote-signs).

Then your SQL-string-construct could look like this for example:
(assuming D1 and D2 represent Date-Variables and define an interval).
SQL = "Select * From Table Where DCol Between " & _
 Format(D1, "'yy\-mm\-dd'") & " AND " & _
 Format(D2, "'yy\-mm\-dd'")

Or define a Const for the above shown Format-Def-
Stringliteral for the second Parameter of the Format-Function.

Nonetheless better to use a command-object for that task, in
case your current COM-wrapper has built-in support for that.

Olaf Schmidt



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Convert Access sql to SQLite sql

[Olaf Schmidt]

"Pavel Ivanov" <paiva...@gmail.com> schrieb im
Newsbeitrag
news:f3d9d2130912020440r1777d1ado6d9938755f80...@mail.gmail.com...

> Your Access query doesn't have good equivalent in SQLite. Your options
are:
> 1. Execute SELECT separately and then for each row in the result issue
> an UPDATE with necessary values (UPDATE can be prepared and you can
> just bind all values).
>
> 2. Insert results of SELECT into some temporary table and then issue
> one UPDATE statement like this:
>
> UPDATE EvAtemp SET
> EvAtemp.EstimateDate = (select Date from TempTable tt where tt.Code =
> EvAtemp.Code and tt.Function = EvAtemp.Function),
> EvAtemp.CodeEst = (select Code from TempTable tt where tt.Code =
> EvAtemp.Code and tt.Function = EvAtemp.Function),
> ...
>
> This will work much-much slower than first approach.
>
> 3. Modify your SELECT so that it joins with EvAtemp too to get from
> there rowid of the row which should be updated, insert results into
> temporary table and issue UPDATE like this:
>
> UPDATE EvAtemp SET
> EvAtemp.EstimateDate = (select Date from TempTable tt where
> tt.EvAtempROWID = EvAtemp.rowid),
> EvAtemp.CodeEst = (select Code from TempTable tt where tt.
> EvAtempROWID = EvAtemp.rowid),
> ...
>
> This will work faster than 2nd approach but I believe it's still
> slower than the 1st.

Yep, good hints - and when googling, one can find also messages
which suggest an:
4. Insert Or Replace Into ... Select ... approach.
But doing so would (although working) involve a
Delete/Recreate instead of an Update of records in many cases.

What I fiddled out in the meantime is something, which
maybe could be included into the core possibly, since
it involves "stuff which is already there" in SQLite.

A working Demo-Source is posted here (using the VB-language):
http://groups.google.de/group/microsoft.public.vb.database/msg/20bc947f3d5bdea4

In short again...
5. Alternative to an Update Tbl Set  From (Select ...)  As Qry Where
Cond

- define a (temporary) View for all columns of [Tbl], which are
   involed in the Update-Stmt above (the ones defined in the Set-list,
   as well as the ones, used within the final "Update-Where-Condition"

- define a second (temporary) View for the SubSelect [Qry], so
   that we are able later on, to rearrange the Column-Order in
   a way, that it matches with the order of the Columns in the
   first created view above (to meet the "Set-Pairs" and eventual
   compair-pairs of the Where-Condition).

- define an Instead Of Trigger for the first View...
   assuming the first view was named [v_Tbl] having columns A,B,C:
   Create Temp Trigger tr_upd_Tbl Instead Of Insert On v_Tbl
Begin
  Update Tbl Set A = new.A, B = new.B
   Where  C = new.C;
End;
...note, that the new.xxx-names in the expressions can (should)
match the lefthandside naming directly, which eases programmatic
construction of that trigger-definition.

- finally the "triggering":
   Insert Into v_Tbl Select  From v_Qry

   ...if we take care in the needed fieldlist-Def for the SubSelect-
   View [v_Qry], that the "Set-, and expression-pair righthandsides"
   match with the Column-Order, defined for [v_Tbl], then we can
   execute the whole thing - and the performance is not bad,
   already tested that...

- oh, and dont forget the "cleanup-drops" for the temporary
   views and the temporary trigger.

Don't know, if one does work "out of specification", if an
Update is performed inside an "Instead Of Insert" trigger,
but the above steps perform really good + it can be "formalized"
nicely, to construct the statements also programmatically
(maybe at some "PreProcessor-level", at least in the wrappers,
 if something like that is not planned to integrate into the
 core-engine).


Olaf Schmidt



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why does LIKE operator affect order of query plan?

[Olaf Schmidt]

"Tim Romano"  schrieb im
Newsbeitrag news:4b017343.2040...@yahoo.com...

> I've added a column to my table:
>
> ALTER TABLE WORDS
> ADD COLUMN spell varchar COLLATE NOCASE
>
> and have then copied the contents of a 100% pure ASCII column
> into column SPELL.
>
> explain query plan
> select * from WORDS where spell like 'foo%'
>
> shows that SQLite is still doing  full table scan.
Did you create an Index on column "spell" beforehand?
(In that case, since you already gave the Collation in the
 Column-Def, you would not need to give the NoCase-
 Collation explicitely again in the Create Index-Statement).

> QUESTION:
> Is it possible to confirm that the column actually has been created
> with NOCASE collation?
Why not just test, what a simple Select gives as a result of e.g.:
  Select spell From Words Where spell = 'abc' Limit 1
In case column spell contains a "known word" as 'Abc' for
example, you should get back a resulting row - if NoCase
wouldn't have been recognized correctly, you shouldn't get
back a string-result (a resulting row) from that query (in such
a case-insensitivity-provocing Comparison-Attempt).

Olaf




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why does LIKE operator affect order of query plan?

[Olaf Schmidt]

"Tim Romano"  schrieb im
Newsbeitrag news:4b0149c9.8000...@yahoo.com...
> ...
> My query with the LIKE operator worked instantaneously
> in MS-Access,  BTW, where I originally had the database.
Since Access *.mdbs are often used with(in) VB- or
VBA-based applications - are you by any chance
using my COM-wrapper (dhRichClient3.dll) to work with
SQLite?
If that is the case, the wrapper overrides the original
Like-function of the sqlite-engine, to perform Unicode-
aware comparisons on Windows-WStrings - and it also
tries its own "optimizations" in case you've passed an
"indexable" Like-comparison-string (with a '...%' at the
right-hand-side) in your Select.

Your index on the text-column in question would have to be
created (and "attributed") with ... Collate NoCase, to make
the "indexed Like optimizations" work as expected.

Olaf




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reverse Referencing Rows

[Olaf Schmidt]

"Rick Ratchford" 
schrieb im Newsbeitrag news:dbfb2606d0c1448b930064474496a...@dolphin...
> A while back, Igor gave me some help on pulling out mm/dd ranges (sets)
from
> my table.
>
> This is the code that does that.
>
> sSQL = "SELECT Date, Year, Month, Day, Open, High, Low, Close FROM ["
&
> gsTableName & "] " & _
> "WHERE ((Month - " & lngStartMth & ")*100 + (Day - " &
> lngStartDay & ") + 1300) % 1300 <= ((" & _
> lngEndMth - lngStartMth & ") * 100 + (" & lngEndDay -
> lngStartDay & ") + 1300) % 1300 " & _
> "ORDER BY Year, ((Month - " & lngStartMth & ")*100 + (Day - "
&
> lngStartDay & ") + 1300) % 1300"
>
> The only problem I have is that there are some stray rows that are not
> wanted.

Did you already tried the small function I've posted
into the VB-database-group (which was based on
Igors original suggestion)?

This also shows, how to work with a CommandObject -
(code is included again at the end of this post).

It enhances the *Where* clause (not the Order By),
to get rid of your "stray-rows" in the very first year
of a "year-crossing-set").

Function GetMonthDayRange(TableName As String, _
  ByVal MStart&, ByVal DStart&, _
  ByVal MEnd&, ByVal DEnd&, _
  ByVal InclYearSort As Boolean) As cRecordset
Dim SQL As String

  'a Cmd-SQL does *not* contain any direct "VarConcats", only @Placeholders
  SQL = "SELECT Date,Month,Day,Open,High,Low,Close FROM @TblName" & _
" WHERE (((mon...@mstart)*100 +  (d...@dstart)+ 1300)% 1300" & _
" <=(( @me...@mstart)*100 +(@de...@dstart)+ 1300)% 1300)" & _
" AND (Year > (Select Min(Year) From @TblName) " & _
" OR (Month*100+Day >= @mstart*1...@dstart)) "

  'enhance the above SQL about the appropriate, different Order By Clauses
  If InclYearSort Then
SQL = SQL & "ORDER BY Year, Month, Day"
  Else
SQL = SQL & "ORDER BY ((mon...@mstart)*100+(d...@dstart)+ 1300)% 1300"
  End If


  With Cnn.CreateSelectCommand(SQL) 'create a Select-Command-Object
'now we replace the @PlaceHolders with the current Var-Content,
'which we've got passed in our Function-Parameters
.ReplColumnOrTableName !TblName, TableName
.SetInt32 !MStart, MStart
.SetInt32 !DStart, DStart
.SetInt32 !MEnd, MEnd
.SetInt32 !DEnd, DEnd

Set GetMonthDayRange = .Execute 'the Cmd.Execute returns a Recordset
  End With
End Function

Olaf



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite server for Windows/Linux?

[Olaf Schmidt]

"David Morris"  schrieb im
Newsbeitrag news:26044356.p...@talk.nabble.com...
>
> Have a peek at : http://www.sqlitening.com/support/index.php
> Different language and could run under Wine on *nix

Ah - yes of course, I left it out here, since it currently is "win only"
(written in PowerBasic). Already played around with it some months
ago - not bad. And IIRC, there was also a C-port planned regarding
the sources - which would allow to compile and run it natively
on more target-platforms.

But yes, the Wine-Layer runs very well these days, especially
if only service-like apps are hosted (with a minimal GUI). The
translation of the winsock-api into the *nix-socket-stack works
very reliable over long uptime-periods, as well as the mappings
and translations into the *nix-filesystems (and all that with much
better performance, compared with VM-based approaches ...
which would also require appropriate Win-OS-licenses per VM).

So, hosting such "IO-related services", originally developed for
win (mainly because "also the clientside has to run on that platform")
on *nix-servers (with a little help of the wine-layer) is now possible
without much hazzle - we do it this way for a few years now,
running our win-compiled service-binaries/layers on debian/wine-
machines.

Olaf




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite server for Windows/Linux?

[Olaf Schmidt]

"Gilles Ganault"  schrieb im
Newsbeitrag news:10h3e55s5ml6kto9rkvasvv1ejc0ao2...@4ax.com...

> Maybe someone will come up with a commercial alternative.
> I for one would gladly pay for a single-EXE, no-brainer solution
> that can run on Windows and *nix.

Hmm in this case, what about:
http://www.realsoftware.com/realsqlserver/

But then it depends on your used (preferred) "client-access-
technology" (how easy you can bind it into your client-app).

On the above site they state, that its usage from the clientside is
possible either over a C/C++ SDK - or directly over RealBasic-
SQLite-bindings (dunno if they also deliver an ODBC-driver for
their Server, to access it over a "standard-interface" from other
languages too).

So, on what platform(s) do your client-apps need to work?
In what (main)language do you develop your client-app?

Olaf



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite in-memory database far too slow in my use case

[Olaf Schmidt]

"Ron Arts"  schrieb im
Newsbeitrag news:4adac5c1.5010...@arts-betel.org...

> Then my program opens a socket, and starts accepting connections,
> those connections are long lasting, and send messages that need
> a fast reply. Many of the messages result in messages being send
> to all other clients. The messages require on average 10 lookups
> in the memory db, each by oid.

Is the "socket-listener-thread" already decoupled from the
thread which hosts your sqlite-connection-handle?

If not done already, you should try it (that will not speedup
the sqlite-performance, but the overall-performance of your
"broadcasting-dispatcher-app").
Additionally you should decouple the "sqlite-thread" also from
the "reply-sender-threads" (placing the sqlite-query-results
in some structures, where the sender-threads are able to find
them).

That would ensure, that the sqlite-engine can always run
fullspeed, not waiting for potentially "slow, or blocking
socket-transfers".

In such a design you could also try another thing, which
maybe speeds up your selects - meaning, maybe "oID-
aggregation" can help.

If you receive in your socket-listener-thread  approx.
5 requests per second (and nothing will intermit this
receiver-thread now, since sqlite-queries run elsewhere) ...
then we talk about 50 incoming messages per milisecond.
Now, since the sqlite-thread is running elsewhere already
... why not aggregate the incoming oIDs in a comma-
separated list (in a simple charbuffer, shared with the
sqlite-thread - and flagged with a "next-job-descriptor").

Each 1 msec (to keep the latency low), you should end
gathering oIDs in such a "next-job" charbuffer and set
the finalized-flag in the job-descriptor-structure (after
that you could start gathering oIDs in your listener-thread
on a different charbuf-allocation immediately).

The sqlite-thread should look for new, flagged as "ready to
proceed" charbuffers on its own, and start its work in a more
"aggregated fashion" then - and maybe the engine-overhead
gets a bit reduced, if sqlite now performs *one* (larger)
select (only each 1 msec), but returning more than only
one single record in its step-loop then.
i.e. per:
Select * from Table Where oID In YourGathered_IDList

Just an idea - I've not yet tested here, if the throughput
would be better this way instead of performing single-record-
selects only ... you probably lose the advantage of the
precompiled "single-record-statement", but could gain
over all, as soon as you reach the step-loop, which does
then more than just one record with probably less overhead
overall.

Maybe that worth' a try.

Olaf



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INTERSECT?

[Olaf Schmidt]

"Igor Tandetnik" <itandet...@mvps.org> schrieb im
Newsbeitrag news:hbfjnu$v...@ger.gmane.org...
> Olaf Schmidt wrote:
> > Just to add another one to the pile, any flaws I overlooked here...?
> >
> > select *, count(b) c_b from foo
> > where b in (...)
> > group by a
> > having c_b = length_of_b_list
>
> The OP apparently wanted actual (a, b) pairs for those a's
> that satisfy the condition, not just a list of a's.

Oops, I had only the OPs:

"I want the values of 'a' for which 'b' = 3 AND 'b' = 4"

in mind - and forgot about the additional "b-column-listing"
(which nonetheless should be somehow "redundant", since all
 the possible values of b were already explicitely specified as
 the filter-condition).

maybe that small enhancement does it, to deliver the
given condition back to the caller, contained within the resultset:

select a, group_concat(b) from foo where b in (3, 4)
group by a having count(b) = 2

as said, returning the b-values would be redundant in this
special case - maybe more interesting for "app-usage" would
be this slightly changed variant:

select a, group_concat(RowID) from foo where b in (3, 4)
group by a having count(b) = 2

Olaf



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INTERSECT?

[Olaf Schmidt]

"Pavel Ivanov"  schrieb im
Newsbeitrag
news:f3d9d2130910170753k6e680ecdtcb892f05b21cc...@mail.gmail.com...

> > select * from foo f1 where
> > (select count(*) from (select distinct b from foo f2 where f1.a = f2.a
and f2.b in (...) )) =
> >length_of_b_list
> > and b in (...);

> Shouldn't this be simplified like this?
>
> select * from foo f1 where
> (select count(distinct b) from foo f2 where f1.a = f2.a and f2.b in
(...) ) =
>length_of_b_list
> and b in (...);

Just to add another one to the pile, any flaws I overlooked here...?

select *, count(b) c_b from foo
where b in (...)
group by a
having c_b = length_of_b_list

Olaf



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] group_concat optimization

[Olaf Schmidt]

"Pavel Ivanov" <paiva...@gmail.com> schrieb im
Newsbeitrag
news:f3d9d2130910150647k5e28d8aan81d60fad8e71e...@mail.gmail.com...
> > Would the authors be kind to implement such optimization?
>
> I'm not author but I believe the answer to this question is No.
> Because this fix is good enough for you but may be not good for
> others. Your fix gets more memory than is really needed and it
> can be a problem for embedded devices.

But that would end up with only twice the allocated Bufferspace
than before (for the GroupConcat-String(s)) - and that only in the
worst-case.
Maybe a factor 1.5 (instead of the 2) would be a better
compromise between "wasted buffer-memory" and significantly
reduced realloc-calls in these typical "growing-append-space"
scenarios.


So, as long as sqlite3StrAccumAppend() is *not* used
"pretty much everywhere, blowing up *total* mem-usage
of a running SQLite-engine-instance by on average 25%",
I'd vote for the change... ;-)

Olaf Schmidt




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite in-memory database far too slow in my use case (new benchmark inside)

[Olaf Schmidt]

"Ron Arts"  schrieb im
Newsbeitrag news:4ad19195.2060...@arts-betel.org...

> I tried it, and indeed, this speeds up inserts tremendously as well,
> but in fact I'm not at all concernced about insert speed, but much more
about
> select speed. I use the following queries:
>
>CREATE TABLE company(id INTEGER PRIMARY KEY ASC, name)

Not sure, if an explicit "text-storage-class-hint" for your name
column will help to speed things up a bit more, but try:
CREATE TABLE company(id INTEGER PRIMARY KEY, name TEXT)

> Then I insert 50 records like this:
>
>INSERT INTO company (id, name) VALUES ('1', 'Company name number 1')

You should prepare the statement like this:
INSERT INTO company (id, name) VALUES (?, ?)

And then treat the (now RowID-mapped) Integer ID as an
Integer, not as a String.
Just use the correctly "typed" binding-calls.

And then wrap the Insert-Loop within a transaction.

> But I'm still looking to speed up selects.
With regards to Selects (searching for random "single IDs"), you
probably already reached the maximum (in case you've not done
any mistakes with the Type-Binding).

Olaf



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite in-memory database far too slow in my use case (new benchmark inside)

[Olaf Schmidt]

"Ron Arts" <r...@arts-betel.org> schrieb im
Newsbeitrag news:4ad10a9e.3040...@arts-betel.org...

> Here's my new benchmark output:
>
> sqlite3 insert 50 records time: 17.19 secs
> sqlite3 select 50 records time: 18.57 secs
> sqlite3 prepared select 50 records time: 3.27 secs
> glib2 hash tables insert 50 records time: 0.38 secs
> glib2 hash tables lookup 50 records time: 0.24 secs
>
> The prepared select indeed speeds up things tremendously,
> a 5-fold increase.

Now do the same thing (prepared commands) for
the sqlite3 - inserts too ... wrapped in a transaction.

Against an InMemory-Table I reach here about
12 Inserts per second (filling up and inserting
"mixed values" against a 8-column-table).
That's on a 1.8GHz  Intel-(mobile)CPU.

As long as your benchmarked-tabledef (your insert) has not
much more columns than my above mentioned '8', then
you should see something like "factor 5" there too.

With a small two-column-table (a prepared two-column-insert-Cmd)
I see about 36 inserts per second, somewhat depending
on the used datatypes (Integers and Doubles work a bit faster of
course than inserting the same "amount of Columns" as Text-Values).

Another reason for your bad insert-performance could of
course be, that you already defined an index on the table
in question (or in case your ID-Field is defined as
INTEGER PRIMARY KEY *and* you're filling up
new IDs in non-consecutive order).
In that case your current results seem a bit more reasonable.

If you don't have an index created yet (on your "HashKey-
ID-Column" ... or if you don't have mapped your ID-Field
to SQLites RowID yet (per INTEGER PRIMARY KEY)
then you maybe should try to create one -  if possible, after
your "main-amount" of fillups was done - that adds some
additional time to your overall-data-preparation efforts of your
"table-list" - but will be of benefit for your single-record-lookups,
based on your "... Where ID = ? ".


Olaf Schmidt



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS and postfix search

[Olaf Schmidt]

"Hugh Sasse"  schrieb im
Newsbeitrag
news:alpine.lfd.2.00.0908061712390.30...@trueman.cs.cse.dmu.ac.uk...

> I wonder if the idea of suffix arrays would belp.
> http://www.cs.dartmouth.edu/~doug/sarray/

I was about suggesting basically the same thing...

In case of Lukas' topics-table, one would have to
create an additional (search)table 'topics_suffixes',
where each word from within 'topics' is "expanded",
resulting in multiple record-entries.

In case the table 'topics' contains the word
'Motor' (with an ID of e.g. 12345) - the 'topics_suffixes'-
table should get the following insertions:
topic_id   |   word_suffixes
  12345   |   motor
  12345   |   otor
  12345   |   tor
  12345   |   or

In the above sequence the listing is stopped at a "maximum-
two-chars"-suffix (sparing us the storage or the last, singlechar),
to safe a bit of space, since single-char Like-queries, formulated
in "contains-format" (as e.g. ... word_suffixes Like '%t%' ...)
are probably not that interesting regarding their usual larger
recordcount-output (from the users point of view in such
"live-search-scenarios").

But all these "contains-queries", searching for wordparts,
larger than one single char can now be performed with
larger speed against the (agreed much larger) 'topics_suffixes'
table using:
Select Distinct topic_id Where word_suffixes Like 'somepart%'
(with a proper index on word_suffixes) - maybe combined in
a Join to table topics, depending on the Apps implementation.

The size of the topics_suffixes-table (and its index on
word_suffixes) dependent on the average-wordlenght
in topics of course - it's the usual tradeoff between
"used space" and speed.

Olaf




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Date Cutoff Statement

[Olaf Schmidt]

"Igor Tandetnik" <itandet...@mvps.org> schrieb im
Newsbeitrag news:h584q5$jo...@ger.gmane.org...

> You've truncated the last group short, so a different row from that
> "incomplete" group accidentally happened to be chosen.
Yep - therefore the recommendation in the VB-newsgroup,
to better rely on the Having-clause (performance is not that
much an issue in Ricks case).

> Try
>
> SELECT count(*), max(Date) FROM MyTable GROUP BY Year, Week

Ah yes - good catch (the Max(Date) term).

Although the SQLite-engine behaves relative "stable",
regarding "which value is filled into a Column that is part
of an Group By-construct", when such a column is specified
without an aggregate-expression.
Currently! (yes, nothing to rely on) it is the last value, that
"reaches the group", probably because the (temporarily used)
sorter, which stores the incoming Rows "sorting whilst adding"
(according to the Group By-clause), shows a "stable" sort-
behaviour, not changing the "first-level, incoming order" which
is determined by the RowID of the underlying table itself, as
I see it.
And Ricks table is built with increasing (auto) RowIDs,
"in sync" to the (already sorted) incoming Trading-
days/dates - and their "derived" Year, Month, Week, etc.
Columns.

So I think Rick will not see a difference regarding the reported
Date between:
SELECT count(*), max(Date) As Date
FROM MyTable
GROUP BY Year, Week

and

SELECT count(*), Date
FROM MyTable
GROUP BY Year, Week

or for better comparison:
SELECT count(*), max(Date) As MaxDate, Date
FROM MyTable
GROUP BY Year, Week

But without doubt, he should change his current query to
the Max(Date) aggregate to be on the safe side.

That leads me to a different (somewhat OT-question in
the context of *this* thread)...

I recently noticed this entry in the SQLite-tktview:
http://www.sqlite.org/cvstrac/tktview?tn=3979

And want to implement a fast sorter for SQLite, to become
more familiar with the C-language.

My question to you experts (including David, who already
looked after that ticket) - is there already "work in progress"
or were there already attempts, to write at least a "temporary
only sorter", which handles all these "throw-away after delivery"
Distinct - and Group By, ... etc. cases?

Is it possible at all, to write such a thing without tight integration
into SQLites VDBE?
If yes, is there already an interface-spec available, to handle
such temporary sorts with different (pluggable) sort-modules,
in a similar way as the interface for e.g. the vfs' was designed
and works?

If no such interface-spec exists, would it be possible to
design such a thing (at least "roughly", by an sqlite-expert) and
publish it here or on the sqlite-site, before I start working on that,
or do you say: "just be creative!".

Any input on that is appreciated - links to already existing
attempts or code-snippets too (not meaning the sorting-
approach itself, more regarding the integration into SQLite).

Regards,

Olaf Schmidt



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Date Comparisons SQL

[Olaf Schmidt]

"Rich Shepard"  schrieb im
Newsbeitrag news:alpine.lnx.2.00.0908031516300.3...@salmo.appl-ecosys.com...

> > It was set as String actually.
>
> Rick,
>
>That's the storage class; well, TEXT is the storage class.
Yep.

> > I believe this is a WRAPPER thing though.
Exactly.
The COM-wrapper handles Date-Column-Definitions
which are "declared" in the Create Table Statement
as "..., MyDate Date, ..." or "..., MyShortDate ShortDate, ..."
with something like an "auto-mapping" to the appropriate
vbDate Variable-Type (which at binary-level, in the language
is a Floatingpoint-Type with Double-precision - counting the
days since 1899-12-30, with the "percentage of a day"
encoded in the fractional part of this floatingpoint-number).

So, it's the wrapper who maps vbDate-Types to the SQLite-engines
Text-storage-class (using the standard-SQL "textdate-format",
'-mm-dd' for ShortDates and '-mm-dd hh:mm:ss'
for "full dates". The SQLite-engine itself does not know about
these "VB-Double-DateTypes "the wrapper hands out to the
application.

Just to shade some light on the topic... ;-)

Olaf



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite from ASP?

[Olaf Schmidt]

"Paul Claessen"  schrieb im
Newsbeitrag news:005601ca0d3f$af8748e0$0e95da...@com...

> Does anyone have any (simple) examples on how
> to use sqlite from .ASP scripts for me?

Since you have CreateObject() available in these scripts,
any COM-based SQLite wrapper should do...

Olaf



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [Columns] Keeping internal + beautified names?

[Olaf Schmidt]

"Gilles Ganault"  schrieb im
Newsbeitrag news:0sap559atknanmiv8g83pdj6f83e8ve...@4ax.com...
> On Sat, 11 Jul 2009 09:38:27 -0700, Jim Dodgen
>  wrote:
> >I would just use:
> >
> >SELECT id AS Identification FROM foobar
>
> Thanks. This is what I already use for displaying the results, but
> then I'm stuck when I need to perform INSERT/UPDATES because I need to
> get the actual columns names :-/
>
> It'd be cool if each column in a table could have an internal name and
> an external name.
>
> Is there a better way than either using the very first row in each
> table or keeping a table for this purpose?

There are SQLite-APIs for that purpose, which hand out
the original columnname to you:
sqlite3_column_origin_name

or the original tablename (useful in Joins):
sqlite3_column_table_name

or the original database-name:
sqlite3_column_database_name

All based on the current statement-handle of your Select.
You need a library-compile, with enabled:
SQLITE_ENABLE_COLUMN_METADATA

If that switch is "on", you can also make use of:
sqlite3_table_column_metadata
in addition, to retrieve even more infos about the
current column in question (if it is a "NotNull"-column,
or a Primary-Key-Column, etc.)

You can do all that (looping over the appropriate column-
count of your select) before entering the sqlite-step-loop -
and return these additional "header-infos" in appropriate
structures (together with your result-data-set).

The overhead, to retrieve all these additional infos is not
all that large - barely noticeable - the Recordset-Objects
of my wrapper always work in that mode (delivering all of
the data, but also these additional Field-infos) - and it does
not affect the overall-performance - much more time is spent
in the step-loop, which retrieves the real recorddata-content.

Olaf



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it Possible in SQL...

[Olaf Schmidt]

"Rick Ratchford" 
schrieb im Newsbeitrag news:152a3111f1ab4a9891b9158580cb7...@dolphin...

> Maybe I misunderstood, but the impression I got was
> that I should solve this problem using my VB language
> rather than dealing with the DB.
I don't understand Simons reply that way (to either use one
or the other)...
He clearly states:
  "Keeping the previous value of a field in a
   variable does not take lots of programming "

That means, he suggests basically the same thing, which
I tried to cover in my small example-routine (note, that
he's mentioning "values of fields").
Do use the DB (retrieving your records, containing
your fields) - but the initial query should be a "simpler"
one, which does not stress the SQL-engine all that much
with special requirements which are difficult to formulate
or to perform - just use the programming-language too
where it has obvious advantages ... against  intermediate
Rs-results.

> As you already noted, my "original plan" was to just create
> a recordset of my table with the additional column, then
> 'loop' through it and fill it in programmically.
Nothing wrong with that - especially for *these* kind of
queries (where you have to "park" some record-values for
a later calculation).

> I was hoping that all this could have been done internally
> and the resultant recordset returned already having the
> new column filled out. Why not, if it can be done without
> adding overhead?
Also nothing wrong with that - especially if you ask in that group
here, where you never know, what the SQL-experts are able to
come up with... :-)

> #>your current "direction-field" calculation presumably being one
> #>of the simpler requirements in that category - but if these kind
> #>of queries need to calculate e.g. sliding-averages over larger
> #>"window-sizes", you (or "the user") will probably already
> #>"feel" the speed-differences compared with dedicated looping.
>
> Is it any slower than having to loop through the recordset,
> comparing one record with another and then directly writing
> back into the recordset the result?
In that case I'd say yes, definitely - within an Rs-Loop you can
simply buffer the previous record-value(s) - and do your
calculations then on these temporary variable-structures,
holding the previous data, easy to combine "some lines later"
with the current record-values.

The needed SubSelects in the pure SQL-alternatives
have to be performed for each "next record" the SQL-
engine "collates" (in your concrete case here:
Recordcount times)

> As you noted, perhaps not in my case since the amount
> of data is not that extensive to begin with.
Yep, that "hides" the performance-issues, since even these
slower performing queries will probably return below the
50msec-interval a user is able to "note".

> When I read Simon's reply, I did not get the sense
> that he was suggesting I do a Rs-Loop. It appeared
> to me, and I could be mistaken of course, that he
> was referring to pure programming in by language (VB).
I'd say no - he was basically suggesting the same thing
IMO - to just use the right mix.

> So if the advice is not to abandon the SQL/SQLite
> approach, but to stick to simple SQL ...
"Simple" is relative - as you write yourself - your App already
performs faster using SQL for the right things - and that
don't have to be only "simple queries" - what you already
do with all these nice Group By queries - directly delivering
weekly or monthly stock-data, derived from your daily-based
records-table - with all these Min, Max, Avg, etc...aggregated-
values ... - this is the area, where SQL shines.

> and use VB code to loop through the recordset result,
> that works just fine. I prefer referencing recordsets over
> arrays when there isn't a speed cost.
Yep - compared with Arrays, these Objects are simply
the more dynamic and flexible container, which you can
pass around over your routines as a 4-Byte-ObjectPointer.
Since interfaces were already mentioned - here you have
already a very flexible "generic-base-container-interface"
as your workhorse - able to access the content in an array-
like way - or per FieldName and Rs-Cursor-shifting ...
you can do Rs-local sorting in different ways (without touching
the DB-engine again with these sorts, Find-Methods, etc.).

[Demo-routine]
> Oh, I agree that there is not much difference in the two
> code sets above. And I am certainly much more comfortable
> with the VB style since I understand it and am still having
> trouble getting a grip on SQL structure.
>
> As already mentioned, it as my 'original plan', and the
> 'how-to' as well, to create a basic recordset and then
> loop through it. I was trying to find out if it was the
> BEST WAY, or if there was a way to do it all via SQL.
As already said - you never know in that group...
And it is always something like a sportive challenge, to
make the "seemingly impossible possible" with plain SQL
for the Gurus here, just for fun, to stay fit... ;-)

> I 

Re: [sqlite] Is it Possible in SQL...

[Olaf Schmidt]

"Rick Ratchford" 
schrieb im Newsbeitrag news:c9ce387e92004e7b9c16ddaa2bd36...@dolphin...

> So modifying TmpTable, which will still be needed for
> other procedures, is not preferred. It would be great if
> a recordset could be derived from it instead that contains
> the DIRECTION results. Once the procedure exits, the
> recordset would just go away.
>
> My original plan was to create the recordset from TmpTable,
> with the added DIRECTION column.
>
> Ex: 0 as Direction FROM TmpTable
>
> Then, either loop through the recordset doing the comparisons
> and filling in DIRECTION, ...
That "original plan" is the fastest way, you can achieve
your desired result.
So I'm basically with Simons suggestion - don't "overuse" the
SQL-engine - just find a good mix between "pure SQL" and
additional (explicit) Rs-iterations.
The returned recordsets of my wrapper are writable - and
as long as you don't perform an Rs.UpdateBatch on them,
these Fieldchanges never reach the underlying table(s), the
Recordset was originally derived from - and the changes on
such an Rs are "forgotten" as soon as that Recordset goes
out of scope (terminates).

But as you already stated in your reply to Simon - SQL-queries,
working against your InMemory-DB-Tables are definitely
useful for many (most) of the tasks in your rewrite/update-
process whilst dealing with your stock-data ... but *this* kind
of query (dealing with previous, or following records) is
relative costly, since solutions usually depend on appropriate
Sub-Selects.

You don't need that much VB-Code, to perform the same
task much faster ("much faster" of course relative - the
recordsize in your tables is currently in a range, where
even the Sub-Select-based approach will perform "fast
enough" IMO - but you should be aware, that such queries
are more expensive than explicit Rs-loops - and if you
can avoid them with some lines of "non-SQL-code", you
should do so - your current "direction-field" calculation
presumably being one of the simpler requirements in that
category - but if these kind of queries need to calculate
e.g. sliding-averages over larger "window-sizes", you (or
"the user") will probably already "feel" the speed-differences
compared with dedicated looping.

'here a simple function, which delivers an appropriately filled Rs...

Function GetRsWithDirection(Cnn as cConnection) as cRecordset
Dim Rs as cRecordset, SQL as String
Dim LastTax As Double, CurTax As Double
SQL = "Select *, 0 As Direction From TmpTable Order By ID"
Set Rs = Cnn.OpenRecordset(SQL)

LastTax = Rs!Tax
Rs.MoveNext 'leave the first Record (Direction remains at 0)
Do Until Rs.EOF
CurTax = Rs!Tax
Rs!Direction = IIF(CurTax > LastTax, 1, -1)
LastTax = CurTax
Rs.MoveNext
Loop
Set GetRsWithDirection = Rs 'return the Rs-Result
End Function

If you compare the code-volume you'd have "to type"
with that of a similar routine that does it with a larger SQL-
string, you will not find all that much of a difference:

e.g. "borrowed" from Wes' reply (and not yet wrapped in a
Rs-delivering function):
select *,
(select
   case when b.tax < MarketTable .tax
   then "Up"
   when b.tax>=MarketTable .tax
   then "Down"
   else null
   end
 from MarketTable b
 where b.rowid=MarketTable.rowid-1) from MarketTable

And as said, the explicit loop, changing some Rs-Values
temporarily, should perform much faster, since esp. the line:
LastTax = CurTax 'copying only the content of a Double-Var
is barely measurable, compared with the time for the
needed SubSelect in a "plain SQL"-solution based on:
"where b.rowid=MarketTable.rowid-1"

So, yeah - "horses for courses" (at least in these special
cases, where you have to deal with previous or following
record-values)... ;-)


Olaf



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Pros and cons of various online code sharing sites

[Olaf Schmidt]

"D. Richard Hipp" <d...@hwaci.com> schrieb im
Newsbeitrag news:64f6bda6-2a04-4d46-aa08-901a6138c...@hwaci.com...

> See http://www.fossil-scm.org/ for more information on
> fossil.  Fossil is self-hosting, btw.

It's a real nice (and small!) SCM and I consider to use it for
the planned LGPL-opening of my COM-based sqlite-wrapper-
and -RPC-library.

That planned opening will be hosted on a dedicated servermachine,
which will support both - a (readonly) WebBrowser-Client
over Port 80 (more or less directly served by the Fossil-engine) -
but also a dedicated RichClient-Application (a "project-client"),
which works against the same online-host over the usual RPC-
Port my library is using.

My question is related to the GPL-license the Fossil-engine
is based on - regarding compatibility with my own stuff, which
will (needs to) be LGPL-based.

At the serverside I see no larger problems - two services,
running side-by-side - the RPC-service with no "direct linking"
to the Fossil-service.

But at the clientside I'd like to interact more closely with the
Fossil-engine (the ideal way would be a library-based Fossil-
engine which runs InProcess within the RichClient-App).

That RichClient-App licensed under LGPL - making use
of the new LGPL-licensed sqlitewrapper- and communication-
library - so, do you see any chance, how I can achieve
such a tighter integration without the need to "lower" my
planned LGPL-license to GPL?

What I would like to achieve is, that my solution is
usable within commercial contexts - and the LGPL-
license is not that restrictive in that regard, also in
terms of (re)distribution.

I'm not (yet) that "fluent" with regards to LGPL/GPL
compatibility - so, "how to interface" in the best way
with fossil with regards to easy (re)distribution and
usage of a "developer-package" (which later on
wants become a full-blown IDE with integrated SCM-
support - used also in commercial contexts)?

I'd be willing to also contribute to the fossil-project
(e.g. a COM-based fossil-interface-wrapper, released
 under GPL - if that is of any help - but as I see it - that
 would shift the GPL/LGPL "interfacing-issues" only one
 layer "away").


Olaf Schmidt



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about searches

[Olaf Schmidt]

"Christophe Leske"  schrieb im
Newsbeitrag news:4a37b811.4030...@multimedial.de...

>> You write your own comparison function that would consider
>> these two strings equal. See sqlite3_create_function,
>> sqlite3_create_collation.
>
> this problem pertains not only to Zürich, but to 24000 other
> entries, so
> ...
> How do you educate SQlite to return me
> "Sào Paulo" if only "Sao Paulo" is being entered?

As already advised above, the best thing is, to write a small
collation-extension for sqlite, which can be registered then
dynamically, before you start working with your data.

An implementation of such a small collation-routine is
pretty easy, if you use the right system-api-call, to compare
your strings - and under windows that is CompareStringW.
http://msdn.microsoft.com/en-us/library/ms647476.aspx

Before passing WStringPointers to that function, you will
have to convert your UTF8 into UTF16 beforehand -
either *after* entering your Collation-Callback with UTF8-
Data using the "officiall" MultiByteToWideChar-API,
using codepage 65001.

Alternatively simply define the correct SQLite-constant
whilst registering your new collation within the engine,
what your string-parameters (passed into your Callback)
are expected to be ... - for Win-WChars use:
SQLITE_UTF16
or
SQLITE_UTF16LE

After that you should be able, to implement this collation-
callback with only a few lines of code.

CompareStringW offers some nice Flags, which will be
useful for you as I see it:
E.g. with a combination of:
NORM_IGNORENONSPACE | NORM_IGNORESYMBOLS |
NORM_IGNORECASE

...and an LCID of 1033 (us-en, which should be available on each system)

...the following comparisons are all evaluated to be identical:
"a" = "Ä"
"Sao Paulo" = "Sào Paulo"
"Cote d azur" = "Côte d'azur"

and with LCID 1031 you will additionally get:
"SS" = "ß"
"ae" = "Ä"

Not that much control as with a selfdefined mapping
of course, but a whole lot more "tolerant" than what
you currently have.

Olaf




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SPHiveDB: A server for sqlite database.

[Olaf Schmidt]

"lau stephen" <stephen@gmail.com> schrieb
im Newsbeitrag
news:c26a05160906150443h44b8f434s46e6ab4215f61...@mail.gmail.com...
2009/6/14 Olaf Schmidt <s...@online.de>:

>> Each client could do (with a 1:10 relation of writes and reads)
>>
>> Loop 1000 times
>> With LoopCounter Modulo 7 (case-switch)
>> case 0: Insert one single record
>> case 1: Insert 10 records
>> case 2: Insert 100 records
>> case 3: Update one single Record
>>case 4: Update 10 affected Records
>> case 5: Update 100 affected Record
>> case 6: Delete one single Record
>> End of WritePart
>>
>> Followed by 10 different Read-Jobs,
>> each requesting a completely delivered
>> resultset, based on different where-clauses, etc.
>> reporting only the recordcount to the console or
>> into a file, together with the current timing
>>
>> (maybe fill up two different tables - and also include
>> some Joins for the read-direction-jobs)
>> End loop
>>

>Did you mean to do this test for one user?
>
>The request info:
>
>{
>"method" : "execute",
>"params" : [
>{
>"dbfile" : 0,
>"user" : "foobar",
>"dbname" : "addrbook",
>"sql" : [
>"insert into addrbook values ( 1,
>\"f...@bar.com\" )",
>"select * from addrbook"
>]
>}
>],
>"id" : "foobar"
>}
>
>The server will lock the user ( foobar ) while processing this request.
>If the server receives two requests for one user at the same time,
>it will process this two requests serial.

No, I meant a test, which does not perform "multi-requests"
at the serverside (thereby saving roundtrips), but instead a
load of single requests from the clientside, to measure the
Requests per Second in the end, the server is able to deliver
in total (under concurrency-stress) for the given "single-job-
volume".

The whole thing (as in the loop I've posted) performed by 8
parallel working Clientscripts - maybe split over two client-
machines, running then 4 parallel script-loops each, and all
doing the same (only having a difference in the username,
ranging from "user1" to "user8" for example).

So, what I posted was only the clientside "stress-loop"-
definition (roughly).

I was only trying, to formulate something like a simple
RPC-(or DB-)Server "client-stress-schema" that is not all
that complicated to implement in different languages (for
the clientside) - and could also be performed in more or
less the same way against PostgreSQL for example, since
the JSON-serialized job-definitions contain only SQL-
Statements - and deliver (JSON-serialized) resultsets
in case of incoming Select-Statements for the DB-Read-
direction.

Each of the clients (clientscripts) should have performed
the following (after finishing the outer main-loop):

assuming the outer loop-count was 1000...

- 1000 different write-jobs (ranging from Inserts, over
  Updates to simple Deletes) - delivering a "success"
  or "no success" as the result of the RPC only.

- 1 different Read-Jobs (Selects, delivering a resultset
   in the RPC-Result)

So, the server has to process finally an Request-Count of
11000 single, DB-related RPC-jobs for each Client ...
suming up to 88000 processed DB-Requests over all the
8 concurrently working scripts (with a Writes vs. Reads-
ratio of 1:10).

The time of the last returning client-script is then setting
the total-time, the server was busy in performing these
88000 concurrent RPC-requests (or DB-requests).
And that result could be used as an indicator, how such
an approach compares with a "real" DB-Server (although
such servers usually don't deliver their resultsets in a
JSON-format over the sockets, so there's probably a
measurable serialization-overhead for the JSON-based
requests, depending also a bit, if these JSON-jobs are
triggered from within Javascript - or from e.g. a simple
C-Client, that makes use of a faster JSON-helper-lib.

To "blend-out" the clientside overhead a bit in that
testloop, the incoming resultsets from the performed
"DB-Select-RPCs" shouldn't be processed further at the
clientside - maybe defining only the requirement, to
perform just the deserialization, to have these returned
resultsets in a "usable format" available within the client
(meaning, only up to the deserialization into an array - and
then forget this job - and the array, perform the next one).

An eventual 

Re: [sqlite] SPHiveDB: A server for sqlite database.

[Olaf Schmidt]

"Olaf Schmidt" <s...@online.de> schrieb im
Newsbeitrag news:h131eu$m0...@ger.gmane.org...

> Loop 1000 times
> With LoopCounter Modulo 7 (case-switch)
> ...
> ...
> Ending up with 11 new records in the DB

 Ok - make the loop-counter 7000 to reach the
result of 11 new records.

Sorry.

Olaf



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SPHiveDB: A server for sqlite database.

[Olaf Schmidt]

"stephen liu"  schrieb
im Newsbeitrag
news:e5a33d590906140609h4f3f35fah9083be3560f5d...@mail.gmail.com...
http://code.google.com/p/sphivedb/

> SPHiveDB is a server for sqlite database. It use JSON-RPC over
> HTTP to expose a network interface to use SQLite database.
> It supports combining multiple SQLite databases into one file.
> It also supports the use of multiple files ( through Tokyo Cabinet ).
> It is designed for the extreme sharding schema -- one SQLite
> database per user. It supports to Automatic synchronization
> of database structure depending on a create table statement.

Interesting approach - (such a mem-vfs offers some nice
options at the serverside, once it is done).

Did you already tested, how the whole approach behaves
in concurrency-scenarios with e.g. only 4-8 concurrent clients?
Using your schema, especially the concurrent writes should get
a nice boost.

I imagine a simple script, which is executed by each client-
process and could fill-up a fresh DB to a certain amount of
records in a single table maybe (to keep the test-scenario simple).

Each client could do (with a 1:10 relation of writes and reads)

Loop 1000 times
With LoopCounter Modulo 7 (case-switch)
case 0: Insert one single record
case 1: Insert 10 records
case 2: Insert 100 records
case 3: Update one single Record
case 4: Update 10 affected Records
case 5: Update 100 affected Record
case 6: Delete one single Record
End of WritePart

Followed by 10 different Read-Jobs,
each requesting a completely delivered
resultset, based on different where-clauses, etc.
reporting only the recordcount to the console or
into a file, together with the current timing

(maybe fill up two different tables - and also include
 some Joins for the read-direction-jobs)
End loop

Ending up with 11 new records in the DB
(or just redefine the loop-counter for more).

Final Job for each client then a good matching single-
record-select with appropriate aggregates on the
currently contained data in the DB(-tables).

The last returning client should then always deliver the
same (correct) results - the timing then counted on this
last returning client.

Something like that is of course a very simplified
concurrency-scenario(-test) - a result-comparison would
be interesting nonetheless - and such a small schema
shouldn't be that difficult to implement over different
languages (working against different backends).

Maybe there's already something simple available,
that defines easy to follow stresstest-requirements,
one is able to implement without too much effort
(maybe for PostgreSQL something like that is already
 online).

Would be interested in such a simple(r) concurrency-
test-definition, in case there already is one ... does
anybody have a link for me?

Olaf



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slow Transaction Speed?

[Olaf Schmidt]

hon
(though against an older sqlite-version in that case).

Hmm - now since the first results from windows-binaries in a
VM were matching the results on a "native XP", I tried
my windows-sqlite-binaries (sqlite-version 3.6.14.1) again
in the VM - this time against a fresh (and small) VM-disk(file),
hosted on the SSD and visible inside the VM as a second
Harddisk.
Now the relations were switched again - the SSD now
performing somewhat like a real 7200rpm-disk on a real XP-
machine, giving for example ca. 110 TPS for 4K-pages and
sync = full (2) - where the XP-system-VM-diskfile, running
on the larger 5400rpm "host-disk" (as already posted above)
achieved ca. 86 TPS.


Really have no clue, how to interpret all that (especially the
Linux-results, be it the python ones, or the ones measured over
the Wine-layer), but the values I've posted are really checked
twice, no mistakes there.

Are these high values just caused by modern (disk-internal)
writeback-caches, where the vendors of these disks made
sure, that even in case of a power-loss the internal disk-cache
can be safely flushed?

Also reading the following articles was not helping much:

Possible data-loss in ext4:
http://www.heise.de/english/newsticker/news/134483
At the bottom of this article there's ext3-behaviour mentioned,
which was interesting to read.

Also interesting with regards to current ext3-behaviour was
the following one:

Kernel developers squabble over Ext3 and Ext4
http://www.heise.de/english/newsticker/news/135446

And finally some new default-ext3-settings in the new kernel?

Solving the ext3 latency problem:
http://lwn.net/Articles/328363/

Is fsync() somehow "messed up" on linux currently?


Olaf Schmidt


P.S.
the following was my test-code:

Const DBPath As String = "c:\transact_test.db"
'Const DBPath As String = "d:\transact_test.db"
Dim Cnn As cConnection, Cmd As cCommand, T As Single, i As Long

  Set Cnn = New cConnection
  Cnn.CreateNewDB DBPath
  Cnn.Execute "pragma page_size=4096"
  'Cnn.Execute "pragma page_size=1024"

  Cnn.Execute "pragma synchronous=2"
  'Cnn.Execute "pragma synchronous=1"

  Cnn.Execute "Create Table T(Int32 Integer)"

  T = Timer
Set Cmd = Cnn.CreateCommand("Insert Into T Values(?)")

For i = 1 To 1000
  Cnn.BeginTrans
Cmd.SetInt32 1, i
Cmd.Execute
  Cnn.CommitTrans
Next i

  T = Timer - T
  Print Format(T, "#.00msec"), Format(1000 / T, "#TPS")

  Set Cmd = Nothing
  Set Cnn = Nothing
  Kill DBPath




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Re-try logic on SQLITE_BUSY/deadlocked?

[Olaf Schmidt]

 schrieb im Newsbeitrag
news:634ea812687a6d75a1ddf17adce883fc.squir...@webmail.ipac.caltech.edu...


> >> My primary concern now is to prevent a dead-lock.
> > That seems to make sense now (I assume you're working
> > "near a deadlock" with your multipe-client-requests, not
> > going to sleep properly before the next retry).
>
> Still makes no sense to me, how the absence of re-try code,
> while very silly - yes;), can explain the vast difference in
> performance I see between the "local-disk" scenario and
> the over-NFS scenario?

Such things can be tested - just start the same Job you performed
earlier locally, now against NFS (with only one single writer-instance).
And that shouldn't be all that much slower than against the local disk.
If it already is (whilst using the DB over NFS exclusively), well - then
something is probably wrong with your NFS (only Samba-Shares
here - but in case of single Writer against the share, it does Ok -
not as fast as against the local disk - but "fast enough" (just what
one expects due to the involved sockets under the hood).
Then, if you find nothing wrong with the single-writer-performance,
increase the writers-count step-by-step, to take a look at the
additional locking-overhead.

Same thing in the other way round (which would be my first test)...
do your concurrency-tests against the local disk - I mean, you
already have many CPU-cores apparently on your Client-machine -
let this machine act somewhat like an AppServer then (without
a "socket-interface"), local processes/threads working concurrently
against the local disk. And there you have the "pure effects" -
easy to see then, if there's something wrong with your busy-
handling - or not - and maybe check your threading-settings
which you use in the sqlite-binary (we use '2' here, and no shared
cache, each thread having its own connection-handle).

Olaf



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Re-try logic on SQLITE_BUSY/deadlocked?

[Olaf Schmidt]

"Rosemary Alles"  schrieb im
Newsbeitrag news:57c55bd7-8d56-4913-adce-cbb2978dd...@ipac.caltech.edu...

> > What do you mean with "cores"?
> > Are these multiple client *machines* - or do we talk about
> > a sinlge client-machine with multiple (cpu-)cores here?
>
> Multiple machines with multiple cpus.
Ah Ok, that clears things up a bit.

> > In case you mean only "several cores" on a single client-machine -
> > why is your DB "behind" NFS at all (and not on a local disk)?
>
> N/A - see above - several machines.
So what you really want is a DB-Server implementation
in that case - and that's what SQLite does not support
"out of the box".

Working in "faked server-mode" against a share is in no
way satisfying under heavy "write-load", because neither
the smb-protocol nor NFS are well suited for DB-typical
transfers.

Nonetheless one can wrap the SQLite-engine behind a
dedicated AppServer-instance (which works always
locally against its SQLite-DB-Files, "visible" directly
only to that Server-Instance or -Host).

And if the communication against this Server-instance
is then done over sockets again, but with better suiting
protocols (transferring "disconnected" Resultsets - and
splitting up your larger writejobs against the Server-instance
into smaller pieces), then SQLite performes very well -
as I already posted in an earlier reply to you.

> We achieve neither, they are both (read and write - not done
> simultaneously, i.e. never read when writing and vice versa) ...
That's normal in SQLite. If there's a "Writer-lock" currently, then
also the Readers (Reader-Threads or -Processes) are blocked.
But in case you are working locally against your DB-File,
these Writes (when done in smaller chunks) usually block
the DB (and eventually waiting Readers) only for milliseconds.

And in your case (using NFS or SMB) the whole locking -
and also the release of these locks is very costly.
So I'm not that sure, if the main-culprit here is your
current busy-handling (although you could probably speed
up the whole thing a bit, when you find a better-working
"wait-strategy" in case of sqlite_busy-errors) - but first you
need to get rid of these protocols - and use SQLite locally,
from inside a (multithreaded) Server-instance on a dedicated
Host.

> > That does not mean, that your Inserts will be slow (if only
> > one process/thread or core will handle them) - after all you
> > perform your DB-writes against a single resource (your disk).
> > And whilst working against a local disk, SQLite can achieve
> > ca. 5-20 inserts per second, depending of course
> > on the Column-Count and if the underlying table has indexes
> > defined on its columns. In my tests I can achieve ca.
> > 12 inserts per second on a table with 8 "mixed-type"
> > Columns (having no indexes defined on it - normal 7200rpm
> > SATA-HardDisk).
>
> Obviously, we are doing something weird, or our NFS
> configuration is strange or whatever.
I'd say, what you currently see is "normal" (more or less).

> The disk is obviously not local
Yep - I was aware of that - what I wanted to show with
the timings above was, what timeouts you should expect
in case you do smaller Write-Jobs against your DB in
a locally working Server-instance (against a local Disk).
Insert 1000 new Records? - will block the Readers for
ca. 10-20msec - then reading can be done again in
parallel by the reader-threads.
And the usual relation between read- and write-requests
is more 90/10 in a typical "business-DB"-scenario (if not
less) - and also these just mentioned "1000 Inserts at once"
don't come up that often.

> The insert speeds you specify are more than likely
> heavily dependent on configuration/ setup.
> Are you also working on multiple machines with multiple
> cpus over NFS?
No - this is (more or less) the "out-of-the-box" performance
of the SQLite-Engine, running on a "desktop-OS", working
against a normal SATA-Drive - wrapping the Inserts in
a Transaction - and using the bind-APIs to fill in the
"Param-Slots" of the next "Insert-Record".

As said - that's what you can expect, if you find a
good AppServer-implementation which wraps the
engine and works locally against the HardDisks.

> In this case, on a local disk, much less than a second
> per update.
See - there you are... ;-)

> However, scaled over the previously described
> "concurrent" scenario with several identical copies
> of the process (program) attempting to access the
> database over NFS from several computers with
> several cpus  - up to a minute per update - It's ridiculous.
Yep - as said - either find a good SQLite-(App-)Server-
wrapper for unixoid systems (maybe Real-SQLServer
or John Stantons implementation - or the Network-
engines which are mentioned in the SQLite-Wiki) -
or just switch to MySQL or PostgreSQL.

But reduce your expectations when working over
SMB or NFS with sqlite.

> Don't have those numbers handy, but will soon. The total
> size of current DB is up to 70mb.
Oh 

Re: [sqlite] Re-try logic on SQLITE_BUSY/deadlocked?

[Olaf Schmidt]

"Rosemary Alles" <al...@ipac.caltech.edu> schrieb im
Newsbeitrag news:f113017d-8851-476d-8e36-56b2c4165...@ipac.caltech.edu...

> I have a database (simple schema) with two tables on which I
> perform  "concurrent" udpates over NFS ...
> ...
> Large updates, distributed over several cores over NFS -
> supposedly  concurrent "but not really"?
Could you give some more detailed background-info about
your current scenario?

What do you mean with "cores"?
Are these multiple client *machines* - or do we talk about
a sinlge client-machine with multiple (cpu-)cores here?

In case you mean only "several cores" on a single client-machine -
why is your DB "behind" NFS at all (and not on a local disk)?

In either case (be it multiple client-machines which talk to your
DB - or just multiple cores on a (capable but) single client-
machine - you will not achieve faster inserts against your DB
by working concurrently (regarding the write-direction to the
SQLite-DB).
SQLite can profit from multiple cores (threads) only in the
Read-Direction.

That does not mean, that your Inserts will be slow (if only
one process/thread or core will handle them) - after all you
perform your DB-writes against a single resource (your disk).
And whilst working against a local disk, SQLite can achieve
ca. 5-20 inserts per second, depending of course
on the Column-Count and if the underlying table has indexes
defined on its columns. In my tests I can achieve ca.
12 inserts per second on a table with 8 "mixed-type"
Columns (having no indexes defined on it - normal 7200rpm
SATA-HardDisk).

So, what timings do you currently get, in case you perform
your updates only running on one single client (or core)?
And could you please check the DB-Size before and after
such a typical "update-job" (so that we get an impression
about the transferred byte-volume - maybe you could also
give the count of new records in case of insert-jobs)?

And do you work over GBit-Ethernet (or an even faster
"NFS-channel")?

Regards,

Olaf Schmidt



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Optimizing concurrency with sql query - locks?

[Olaf Schmidt]

"Rosemary Alles" <al...@ipac.caltech.edu> schrieb im
Newsbeitrag news:af79a266-b697-4924-b304-2b1feccba...@ipac.caltech.edu...

> Run on a single processor, the following query is quite fast:
> ...snip...
> When concurrency is introduced (simply running the query on several
> processors against the same database - say 300 instances of it) ...
Normally it does not make sense, to start more instances than
available CPU-Cores (as long as a spanned process/thread
gets the chance, to do some real work - and does not have to
"idle around" in a waitstate).

On our Appserver we typically use only a worker-threadpool of
ca. twice the CPU-cores - the worker-pool is then provided with
new jobs from a central server-queue (where incoming requests
are "parked" first).

> ...a massive slow down with significant fluctuations in time between
> instances. Only reads are done (i.e. queries) - no writes. How does
> one optimize concurrency in sqlite3

As others already commented, if your Queries cannot be run within
the SQLite-Cache ("locally visible to one CPU-core" - because
your underlying table was too large to fit in), then the single resource
(your disk) comes into the game and SQLite does not scale well.

In case of smaller DBs or tables (or increased cache-settings for SQLite)
this little engine scales surprisingly well on parallel Read-Requests
(speaking for the mode with a separate connection on each thread,
 so the shared-cache-mode is currently not used on our Appserver).

Here come some values (tested against a quad-core here, hosting
only a small NWind.db, which entirely fits into the cache) - each
client running an endless stress-loop with always the same query
("Select * from Invoices" - which stresses the engine a bit,
because that View contains some Joins already, resultset-size
2155 records on 26 columns). No resultset-caching was done
on the Appserver-end - so the query was always performed
against the sqlite-engine directly (so no tricks here).

Server-Responses per second (disconnected and serialized Rs):
1 client: ca. 21
2 clients: ca. 37
3 clients: ca. 51
4 clients: ca. 62
5 clients: ca. 71
6 clients: ca. 80

The reason, that I was not able, to get close to the 80 Responses
per second already with 4 clients was, that the clientside also had
some stress (visualizing the retrieved Resultsets in a DataGrid, before
starting a new request) - and 4 of them were started on a DualCore
XP-machine first (causing enough stress already on that XP-machine) -
the 5th and 6th client were additionally started then on another client-
machine (running on Linux then using the Wine-layer - in the same way
as the RPC-Server was doing on the quad-machine).

Here's a screenshot of this running scenario (6 clients causing stress,
the screenshot was taken on the XP-machine, which was running 4
of the 6 clients - the server is visible in a VNC-session-window).
http://www.datenhaus.de/Downloads/Quad-Stress.png

And as you can see on the KDE4-systemmonitor - the 4 CPU-cores
are pretty equally involved in that scenario (one core a bit more -
due to the WineServer, which apparently was handling the IO-translation
into the Linux-socket-stack "on its own" - also interesting to see,
that Wine apparently has something like a "cutting-point" at ca. 80%
CPU-usage - probably to "play fair" with the underlying linux-OS
or something like that... (at least on that debian-machine here).

As as side-note (becoming somewhat more offtopic now in this
thread) - the network-coverage was at ca. 17MB/sec on eth0 in
this small stress-test (1GBit-network) - the RPC-transfers were
running FastLZ-compressed over the wire - if I would
have switched that off, then we would have seen a ca. 50%
coverage on the GBit-channel (because usually circa factor 3 is
achievable with compression on typical DB-resultsets).
And it's not that easy, to reach ca. 50% GBit-coverage with
"typical DB-Server-Engines" - measured on the serverside under
concurrency-stress (e.g. against Postgres or MySQL or
MS-SQLServer). That's mainly due to the fact, that Appservers
usually don't (have to) support serverside cursors - so typically
larger packets (completely prepared and serialized resultsets)
can be send over the wire (not that much ping-pong necessary
on the protocol).

So for our purposes (having more or less smaller DBs here),
the current sqlite-implementation scales good enough in
these typical, more ReadDirection-related-scenarios -
the currently implemented locking-scheme seems not all
that restrictive (if working against non-shared SQLite-caches) -
so I hope, the sqlite-devs (although they often recommend,
to work singlethreaded wherever possible ) keep up their
good work on that front. :-)

Regards,

Olaf Schmidt



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sporadic freezes of sqlite

[Olaf Schmidt]

"D. Richard Hipp" <d...@hwaci.com> schrieb im
Newsbeitrag news:fb9c47d5-1b72-4574-bb44-4f0d6685f...@hwaci.com...

> FWIW, we are in the process of "productizing" the
> test_async.c  asynchronous VFS for SQLite.
> The new async VFS might be available as a compile-time
> option or as a loadable extension on both windows and
> unix in 3.6.14.

> The async VFS does all disk writes in a background thread...
> ...
> The async VFS also uses more memory, since the data
> waiting to be written to disk has to be stored somewhere.
Not that much of a problem nowadays (at least if not running
on an embedded device)...

> It might use a lot more memory if you are committing changes
> to the database faster than the disk and the background writer
> thread can handle them.
That's understandable - and Ok - but leads to some questions:
(assuming we are running on windows as in my case)

How's the "read-uncommitted"-case handled then in the
main-thread of the hosting process - is there an "automatic
cross-thread-linking" to the "yet to write to the disk"-pages and
the Selects already get (merge) their data from these pages?

And how would we have to handle the case, if the user
simply is closing the Application-process gracefully
(for example by closing the Main-Window) - but your
writerqueue yet contains outstanding writejobs?
Is there some "state-info" which we could check - and
set the process "on-hold" by ourselfs until all writes are
finished?

Is there only one vfs-WriterQueue-Thread per process -
or is it per Connection (DB-Handle) - I assume per process,
but just to make sure...

In case there is just one WriterQueue-Thread per process -
is there any chance, that the current backup-implementation
could make use of that central writer-thread?
I mean, in case of the more granular working backup-mode,
which automatically retriggers itself in case of some writes on
another DB-Handle (Connection) - we can currently not
use this "low-prio"-backupmode on our AppServer, which
holds "per-thread" DB-Connections in its WorkerThread-Pool.
Currently, if we start an additional "low-prio" Backup-
Thread - this thread will probably never finish - in case the
other WorkerThreads perform writes against their (separate)
SQLite-DB-ConnectionHandles.

So, what we had planned for our AppServer was, to direct
all DB-Write-Operations from the WorkerThreads to a
dedicated DBWriterThread - which holds a separate
DB-Handle too - but in case of a backup would pass its
very same DB-Handle to the low-prio backupthread
(thereby avoiding the retriggering).
Do you see any chance (since all writes are already
"centralized" if one uses the new async-writer-approach),
that in case of a running "granular-backup" we could
avoid our planned "workaround"?

In either case - that will be a nice addition to the engine -
thank you.

Olaf Schmidt



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users