[sqlite] Find non-numeric character in text field

2009-11-04 Thread RB Smissaert
I would like to do a where on a text field and check if the values have
non-numeric characters,
which is in this case is anything other than 1,2,3,4,5,6,7,8,9,0 or a space
character.
Is this possible without using a UDF or a very long OR construction?

RBS


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


Re: [sqlite] Need Help SQL

2009-10-12 Thread RB Smissaert
> I'm using Olaf Schmidt's VB SQLite binder.

That does use parameterized statements.
Look at the methods and properties of the cCommand object in the object
browser. Also look at the demo code that comes with dhRichClient3.

RBS


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rick Ratchford
Sent: 12 October 2009 20:16
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Need Help SQL

#>Doesn't your VB SQLite binding support parameterized statements? If so,
#>I'd suggest you dump it and find a better one.
#>
#>Igor Tandetnik

I'm not up on the terminology. I'm using Olaf Schmidt's VB SQLite binder.
He'd probably be better to answer this question than I.

:-)
Rick



___
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] Sqlite profiler

2009-09-23 Thread RB Smissaert
OK, thanks.
In that case I do that in my application code.

RBS


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Christian Schwarz
Sent: 23 September 2009 14:33
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Sqlite profiler

> Maybe, what is it?

http://en.wikipedia.org/wiki/Profiling_(computer_programming)

Cheers, Christian
___
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] Sqlite profiler

2009-09-23 Thread RB Smissaert
Maybe, what is it?

RBS


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of mcnamaragio
Sent: 23 September 2009 14:16
To: sqlite-users@sqlite.org
Subject: [sqlite] Sqlite profiler


Hello,

Would anyone be interested in sqlite profiler? If yes what features would
you expect from it?

Thank you. 
-- 
View this message in context:
http://www.nabble.com/Sqlite-profiler-tp25531129p25531129.html
Sent from the SQLite mailing list archive at Nabble.com.

___
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] Any way to speed up this SQL?

2009-09-12 Thread RB Smissaert
Thanks, that works and is a lot faster.
I got this down to under 0.5 sec now.
I took '¬' for the upper limit character and that should always be fine.

RBS



-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
Sent: 12 September 2009 14:56
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Any way to speed up this SQL?

RB Smissaert wrote:
> Have 2 tables with both one text field called term and need to run a
> SQL like this, to count the records in table1 where the start of term
> in table1 equals a term in table2:
>
> select
> count(a.rowid)
> from table1 a inner join table2 b on
> (lower(b.term) = lower(substr(a.term,1,length(b.term

Try writing the condition as

a.term collate nocase between b.term and b.term || 'Z'

Replace 'Z' with a character that compares above any characters that may 
appear in your strings. If in doubt, try using CAST(X'EFBFBF' as text) 
(this is U+ represented in UTF-8, and should compare greater than 
anything valid). Make sure there is an index with collate nocase on 
a.term (an index on b.term won't be helpful).

Igor Tandetnik



___
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] Any way to speed up this SQL?

2009-09-12 Thread RB Smissaert
Thanks for the tip and will have a look at that.
I have in the meantime made this a lot faster by making the data in both
tables upper case and making the small table smaller by taking out invalid
records. I can then run a simpler join with glob, although I noticed it
still doesn't use the index. Sounds like your solution will be faster still.

RBS


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of marbex
Sent: 12 September 2009 12:25
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Any way to speed up this SQL?


I had a similar issue. I wanted to find strings that started as another
string in the same table and field. My solution was to create a temp table
that consisted of the id, the string and the first word of the string which
I then indexed. The table had 30 000 records and the total processing time
went down from 15-20 minutes to 15 seconds!

Applying that solution to your case, not knowing the nature of your data (I
had names), I guess you can do something like this:

- Get the length of the shortest string in table2.term. Lets say it's 3.

- Create a tmptable of table1
Create temp tmptable1 as
select term, lower(substr(term,1,3)) shortest
from table1

- Create a tmptable of table2
Create temp tmptable2 as
select term, lower(substr(term,1,3)) shortest
from table2

- Index the shortest fields
Create index idx_tmptable1_shortest on tmptable1(shortest)
Create index idx_tmptable2_shortest on tmptable2(shortest)

-Run this sql
select
count(a.rowid)
from tmptable1 a inner join tmptable2 b on a.shortest=b.shortest
where (lower(b.term) = lower(substr(a.term,1,length(b.term



RB Smissaert wrote:
> 
> Have 2 tables with both one text field called term and need to run a SQL
> like this, to count the records in table1 where the start of term in
> table1
> equals a term in table2:
> 
> select
> count(a.rowid)
> from table1 a inner join table2 b on
> (lower(b.term) = lower(substr(a.term,1,length(b.term
> 
> term is indexed in both tables, but not surprisingly, this query runs very
> slow, taking some 40 minutes. Table1 is large, maybe some 1 million rows
> and
> table2 is small, maybe some 30.000 rows. All rows in table2 are unique,
> but
> table1 has many duplicates.
> 
> Any suggestions to speed this up?
> I could also tackle this in code rather than in SQL.
> 
> RBS
> 
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context:
http://www.nabble.com/Any-way-to-speed-up-this-SQL--tp25412299p25413614.html
Sent from the SQLite mailing list archive at Nabble.com.

___
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] Any way to speed up this SQL?

2009-09-12 Thread RB Smissaert
Have 2 tables with both one text field called term and need to run a SQL
like this, to count the records in table1 where the start of term in table1
equals a term in table2:

select
count(a.rowid)
from table1 a inner join table2 b on
(lower(b.term) = lower(substr(a.term,1,length(b.term

term is indexed in both tables, but not surprisingly, this query runs very
slow, taking some 40 minutes. Table1 is large, maybe some 1 million rows and
table2 is small, maybe some 30.000 rows. All rows in table2 are unique, but
table1 has many duplicates.

Any suggestions to speed this up?
I could also tackle this in code rather than in SQL.

RBS




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


Re: [sqlite] Does SQLite have an Instr function?

2009-09-11 Thread RB Smissaert
In my particular case it is simple and I have solved the problem by adding a
function via the VB wrapper.

RBS


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Darren Duncan
Sent: 11 September 2009 22:52
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Does SQLite have an Instr function?

RB Smissaert wrote:
> Does SQLite have a string function that produces the first position of a
> string within another string?
> For example select Instr('abcd', 'c') would produce 3
> Looked in the documentation and the forum, but couldn't see it.

This also isn't a simple problem since the answer would vary depending on
your 
abstraction level in dealing with characters; for example, is a character a 
language-dependent grapheme, a language-independent grapheme, a Unicode 
codepoint, a byte, an integer, etc.  If asked the length of a character
string 
that has an accented letter, say, the answer would vary depending on which
of 
the above abstractions they want the answer in, and for less abstracted
answers, 
it is affected by what codepoints or bytes are used for the character, etc. 
Similarly, asking "at what position does the substring match" is not simple.
In 
practice, it is simpler to deal with strings than characters, and asking
simply 
*if* a string is a substring of another, is a much simpler question, and
LIKE 
does that already. -- Darren Duncan
___
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] Does SQLite have an Instr function?

2009-09-11 Thread RB Smissaert
OK, thanks, saves me looking further.
I can add this function via the VB wrapper (Olaf Schmidt's dhRichClient3),
but thought it might be faster if there was a pure SQLite implementation.

RBS


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
Sent: 11 September 2009 22:40
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Does SQLite have an Instr function?

RB Smissaert <bartsmissa...@blueyonder.co.uk>
wrote:
> Does SQLite have a string function that produces the first position
> of a string within another string?
> For example select Instr('abcd', 'c') would produce 3

Unfortunately, no. Of course, SQLite does provide a way for you to add 
your own custom functions.

Igor Tandetnik



___
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] Does SQLite have an Instr function?

2009-09-11 Thread RB Smissaert
Does SQLite have a string function that produces the first position of a
string within another string?
For example select Instr('abcd', 'c') would produce 3
Looked in the documentation and the forum, but couldn't see it.

RBS



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


Re: [sqlite] tool to browse a sqlite database

2009-03-11 Thread RB Smissaert
This is now all sorted and it was indeed a simple bug in the wrapper.
When parsing out the create table statement it hadn't anticipated the double
quotes surrounding the tables and fields.
I understand that this is in fact the standard/recommended way, although I
don't do it myself and prefer: CREATE TABLE Table1([Field1] Integer etc.

The wrapper has been updated and (for VB/VBA users) can be downloaded here:
www.datenhaus.de/Downloads/dhRichClient3.zip
www.datenhaus.de/Downloads/dhRichClient3-Demo.zip

RBS



-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Ralf Junker
Sent: 08 March 2009 14:38
To: General Discussion of SQLite Database
Subject: Re: [sqlite] tool to browse a sqlite database

RB Smissaert wrote:

>What SQLite version produced the file World.db3?

I am not 100% sure about the exact SQLite version which I used to create the
original World.db3, but I am VACUUMing it regularly to bring it up to date
with recent versions. So I expect it should be some version after 3.6.8.

>I ask as my wrapper doesn't pick correctly the fields of a table.
>This is Olaf Schmidt's VB wrapper dhRichClient with SQLite 3.6.11.

I just did a PRAGMA integrity_check; on World.db3 with the SQLite3.exe v.
3.6.11 and it reports 'ok'. Looks like a wrapper problem to me.

>BTW, SQLiteSpy looks a very nice GUI tool.

Thanks!

Ralf 

___
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] tool to browse a sqlite database

2009-03-08 Thread RB Smissaert
OK, thanks to clarify that.
There is no problem with the table SQLiteSpy.db3, so what difference in the
files Word.db3 and SQLiteSpy.db3 could possibly explain this problem?
I have reported this to Olaf and I am sure he will shed light on this.

RBS


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Ralf Junker
Sent: 08 March 2009 14:38
To: General Discussion of SQLite Database
Subject: Re: [sqlite] tool to browse a sqlite database

RB Smissaert wrote:

>What SQLite version produced the file World.db3?

I am not 100% sure about the exact SQLite version which I used to create the
original World.db3, but I am VACUUMing it regularly to bring it up to date
with recent versions. So I expect it should be some version after 3.6.8.

>I ask as my wrapper doesn't pick correctly the fields of a table.
>This is Olaf Schmidt's VB wrapper dhRichClient with SQLite 3.6.11.

I just did a PRAGMA integrity_check; on World.db3 with the SQLite3.exe v.
3.6.11 and it reports 'ok'. Looks like a wrapper problem to me.

>BTW, SQLiteSpy looks a very nice GUI tool.

Thanks!

Ralf 

___
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] tool to browse a sqlite database

2009-03-08 Thread RB Smissaert
What SQLite version produced the file World.db3?
I ask as my wrapper doesn't pick correctly the fields of a table.
This is Olaf Schmidt's VB wrapper dhRichClient with SQLite 3.6.11.
BTW, SQLiteSpy looks a very nice GUI tool.

RBS

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Ralf Junker
Sent: 08 March 2009 09:13
To: General Discussion of SQLite Database
Subject: Re: [sqlite] tool to browse a sqlite database

BareFeet wrote:

>See a comparison of several GUI SQLite tools here:
>http://www.tandb.com.au/sqlite/compare/?ml

SQLiteSpy is missing from the list. It is available from

  http://www.yunqa.de

SQLiteSpy is a Unicode SQLite3 database browser GUI for Win32. Features
include:

* Database at a Glance - The schema treeview displays all items contained in
a database, including tables, columns, indexes and triggers. Press F5 to
update the schema tree, double-click a table or view to display its data,
use the context menu for frequently used commands.

* Grid Cell Editing - Table cells are editable in the grid: Display a table
via the schema treeview, select a cell and press F2 to invoke the editor.
Then modify and confirm to write your changes back to the table.

* Data Type Display - The native SQL data types are displayed with different
background colors to help detect type errors. Type errors can cause
performance degradation or wrong SELECT result sets if NULL values are
confused with empty strings.

* Full Unicode - SQLiteSpy fully supports SQLite's Unicode capabilities.
Data display and entry is completely realized as Unicode, including SQL
commands.

* Multiple SQL Edits - Modern tabs are used to edit and display multiple SQL
queries for easy comparison of query statements and results. SQL queries are
executed by typing or loading them into the SQL edit. Then press F9 to run
the query, or CTRL+F9 to run the current line or selection only.

* Time Measurement - SQL execution time is automatically measured and
displayed to help optimize queries.

* Regular Expressions - The SQL keyword REGEXP is supported and adds the
complete regular expression syntax of Perl 5.10 to SQLiteSpy.

* Mathematical SQL Functions - The following mathematical SQL functions are
available in addition to the SQLite default: ACOS(), ASIN(), ATAN(), ATAN(),
ATAN2(), CEIL(), CEILING(), COS(), COT(), DEGREES(), EXP(), FLOOR(), LN(),
LOG(), LOG(), LOG2(), LOG10(), MOD(), PI(), POW(), RADIANS(), SIGN(), SIN(),
SQRT(), TAN(), TRUNCATE().

* Data Compression - The SQL functions COMPRESS() applies zlib's deflate to
any text or BLOB value. The raw deflate data stream is returned.
UNCOMPRESS() inflates this stream back to the original. Integers, Doubles,
and Nulls are returned unchanged.

* Compact Result Storage - The internal data storage mechanism uses SQLite's
native data types for optimal compatibility. As a result, SQLiteSpy uses far
less memory than other SQLite managers and handles large tables much more
efficiently.

* Built in SQLite Engine - SQLiteSpy comes as a single file executable with
the SQLite database engine already build into the application. There is no
need to distribute any DLLs, which makes SQLiteSpy easy to deploy with
customers.

* Easy Install & Uninstall - To run SQLiteSpy, just extract the
SQLiteSpy.exe file to any directory and execute the file. No installation is
needed - when first started, the program creates a single file SQLiteSpy.db3
(a SQLite3 database) to store options and settings. It does not write any
other files or to the registry. Uninstalling is as simple as deleting two
files only: The application's executable and its options database file.

* Freeware - SQLiteSpy is Freeware for personal and educational use. If you
are using SQLiteSpy commercially, your donation is welcome to promote the
ongoing development of this software.

Ralf  

___
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] Lock SQLite file by overwriting bytes and then un-lock?

2009-01-26 Thread RB Smissaert
Thanks; will do that.

RBS

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of MikeW
Sent: 26 January 2009 12:02
To: sqlite-users@sqlite.org
Subject: Re: [sqlite]Lock SQLite file by overwriting bytes and then un-lock?

RB Smissaert <bartsmissa...@...> writes:

> 
> Thanks for the tip, but does that work on a Windows Mobile device?
> 
> RBS
Don't know - also look at http://www.freeotfe.org/
(Google: encrypted files windows mobile)

Cheers,
MikeW

___
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] Lock SQLite file by overwriting bytes and then un-lock?

2009-01-26 Thread RB Smissaert
Thanks for the tip, but does that work on a Windows Mobile device?

RBS


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of MikeW
Sent: 26 January 2009 11:40
To: sqlite-users@sqlite.org
Subject: Re: [sqlite]Lock SQLite file by overwriting bytes and then un-lock?

RB Smissaert <bartsmissa...@...> writes:

> 
> For some reason this mail went to the junk mail folder.
> That sounds good and I would be happy to buy this, but I am not sure
about:
> > All you need to do is replace the DLL
> I have number of dll's:
> For the desktop: an ActiveX dll and a plain Windows dll. The ActiveX is
used
> by my VBA/VB6 app and that ActiveX uses the plain Windows dll. These files
> are compiled by Olaf Schmidt.
> On the WM6 device: A .net dll System.Data.SQLite.DLL and a little
Basic4PPC
> dll, SQLDevice.dll, which I think is also a .net dll. These files are
> compiled by Erel, the author of Basic4PPC.
> How would SEE fit in with all this?
> 
> RBS

Since you are running Windows, I would have the app and standard SQLite DB,
and all ancillary data, stored on an properly encrypted drive.

e.g. http://www.truecrypt.org/

You can't be too careful with sensitive data like this.

Regards,
MikeW




___
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] Lock SQLite file by overwriting bytes and then un-lock?

2009-01-25 Thread RB Smissaert
For some reason this mail went to the junk mail folder.
That sounds good and I would be happy to buy this, but I am not sure about:
> All you need to do is replace the DLL
I have number of dll's:
For the desktop: an ActiveX dll and a plain Windows dll. The ActiveX is used
by my VBA/VB6 app and that ActiveX uses the plain Windows dll. These files
are compiled by Olaf Schmidt.
On the WM6 device: A .net dll System.Data.SQLite.DLL and a little Basic4PPC
dll, SQLDevice.dll, which I think is also a .net dll. These files are
compiled by Erel, the author of Basic4PPC.
How would SEE fit in with all this?

RBS


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of D. Richard Hipp
Sent: 25 January 2009 19:40
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Lock SQLite file by overwriting bytes and then
un-lock?


On Jan 25, 2009, at 2:32 PM, RB Smissaert wrote:

> Yes, you are right there. It won't be much good for anything else  
> then a
> casual peek at the device. This is a clinical database, so it involves
> patients, diagnoses, medications etc. The ID data and the clinical  
> data are
> stored in different tables, but even then with a simple hex editor  
> it won't
> be that difficult to match the 2 up.
> So, I suppose the only solution is to encrypt the data. Problem is  
> that the
> SQLite on the device can't decrypt and even if it could it would be a
> different system than the one used on the desktop. The DB file is  
> made on
> the desktop and then copied to the device and on the desktop I use  
> Olaf
> Schmidt's VB wrapper dhRichClient and SQLite 3.6.1. The device app is
> developed with Basic4PPC and that is based on .Net and uses SQLite  
> 3.3.12.
> Not sure there is an easy solution there without affecting the speed  
> of the
> application.


http://www.hwaci.com/sw/sqlite/see.html is often used to meet HIPAA  
requirements.  SEE supports cross-platform databases.  The key can be  
entered using PRAGMAs so no new interfaces are needed in your  
wrapper.  All you need to do is replace the DLL.  And by purchasing an  
SEE license, you get the additional satisfaction of knowing that you  
are helping to support the ongoing development and maintenance of  
SQLite :-)


D. Richard Hipp
d...@hwaci.com



___
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] Lock SQLite file by overwriting bytes and then un-lock?

2009-01-25 Thread RB Smissaert
Thanks for the suggestion.
The problem is that I use 2 different wrappers, one for the desktop and one
for the device. Possibly, somehow it would be possible to add SSE to the
desktop wrapper, but it will be very difficult to get it added to the device
wrapper as I have no control over that.
Will bear this option in mind though.

RBS


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kees Nuyt
Sent: 25 January 2009 20:08
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Lock SQLite file by overwriting bytes and then
un-lock?

On Sun, 25 Jan 2009 18:29:28 -, "RB Smissaert"
<bartsmissa...@blueyonder.co.uk> wrote in General Discussion
of SQLite Database <sqlite-users@sqlite.org>:

>Would it be possible to make a SQLite file un-usable by overwriting bytes
in
>certain places and then (via an encrypted password) make the file usable
>again by putting the right bytes back in the right places?
>I use a VB wrapper that can encrypt the database, but I am using this
SQLite
>file on a Windows Mobile device and the SQLite wrapper used there can't do
>that.
>I can do this quite easy by picking some fixed bytes at the beginning of
the
>file, but it then is too easy to pick this up by comparing with a normal
>SQLite file. So, maybe I need to overwrite sqlite_master, but will it be
>possible to always find the start and end of that part of the file?
>Thanks for any ideas about this.
>
>RBS

If the security is important to you, it might be worth to
invest in SEE, the proprietary, licenced, SQLite Encryption
Extension:
http://www.sqlite.org/support.html

It makes sure only your application can access the database.
-- 
  (  Kees Nuyt
  )
c[_]
___
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] Lock SQLite file by overwriting bytes and then un-lock?

2009-01-25 Thread RB Smissaert
Yes, you are right there. It won't be much good for anything else then a
casual peek at the device. This is a clinical database, so it involves
patients, diagnoses, medications etc. The ID data and the clinical data are
stored in different tables, but even then with a simple hex editor it won't
be that difficult to match the 2 up.
So, I suppose the only solution is to encrypt the data. Problem is that the
SQLite on the device can't decrypt and even if it could it would be a
different system than the one used on the desktop. The DB file is made on
the desktop and then copied to the device and on the desktop I use Olaf
Schmidt's VB wrapper dhRichClient and SQLite 3.6.1. The device app is
developed with Basic4PPC and that is based on .Net and uses SQLite 3.3.12.
Not sure there is an easy solution there without affecting the speed of the
application.

RBS



-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jay A. Kreibich
Sent: 25 January 2009 18:48
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Lock SQLite file by overwriting bytes and then
un-lock?

On Sun, Jan 25, 2009 at 06:29:28PM -, RB Smissaert scratched on the
wall:
> Would it be possible to make a SQLite file un-usable by overwriting bytes
in
> certain places and then (via an encrypted password) make the file usable
> again by putting the right bytes back in the right places?
> I use a VB wrapper that can encrypt the database, but I am using this
SQLite
> file on a Windows Mobile device and the SQLite wrapper used there can't do
> that.
> I can do this quite easy by picking some fixed bytes at the beginning of
the
> file, but it then is too easy to pick this up by comparing with a normal
> SQLite file. So, maybe I need to overwrite sqlite_master, but will it be
> possible to always find the start and end of that part of the file?
> Thanks for any ideas about this.

  To what end?  What are you trying to protect against?

  If the database has sensitive information, especially strings, much
  of it can be recovered by simply dumping the database file with a
  hex-editor.  Simply making the file unreadable by the SQLite library
  provides only the bare minimum of protection.

  If all you want to do is prevent the casual user from browsing the
  file, then I suppose this will work.  Although your indication of
  scrambling the first few bytes as being "too easy" to fix seems to
  indicate you're after something stronger.

  If you really need to protect the data, you need to protect the data
  itself, not the access mechanism.  You either need to re-write the
  VFS to encrypt whole database pages, or you need to encrypt the data
  itself before it is stored into the database.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
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] Lock SQLite file by overwriting bytes and then un-lock?

2009-01-25 Thread RB Smissaert
Would it be possible to make a SQLite file un-usable by overwriting bytes in
certain places and then (via an encrypted password) make the file usable
again by putting the right bytes back in the right places?
I use a VB wrapper that can encrypt the database, but I am using this SQLite
file on a Windows Mobile device and the SQLite wrapper used there can't do
that.
I can do this quite easy by picking some fixed bytes at the beginning of the
file, but it then is too easy to pick this up by comparing with a normal
SQLite file. So, maybe I need to overwrite sqlite_master, but will it be
possible to always find the start and end of that part of the file?
Thanks for any ideas about this.

RBS


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


Re: [sqlite] Why the difference in these 2 SQLite files?

2009-01-10 Thread RB Smissaert
Some progress in this.
Doing a trivial file write (set Read-Only to True and then back to False)
gives me the same speed benefit. So at least this takes SQLite out of the 
equation in solving this problem.

RBS


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of RB Smissaert
Sent: 09 January 2009 22:15
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Why the difference in these 2 SQLite files?

OK, any DB write to this file on the desktop (not the mobile device)
will make the query go fast. A completely trivial update will do it, so
it looks somehow the db write on the desktop does makes it go fast.
I can't check now if the same applies when doing this db write on the first
PC and hopefully it does.
Maybe I need some app to compare the 2 db files to see what is going on
here. Baffling me.

RBS


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of RB Smissaert
Sent: 09 January 2009 20:54
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Why the difference in these 2 SQLite files?

Have now also looked at the full EXPLAIN and although understand that
it looks indentical to me for both databases:

Slow
-
addropcode  p1  p2  p3

0   Goto0   34  
1   MemLoad 0   0   
2   If  0   31  
3   MemInt  1   0   
4   MemInt  0   1   
5   Integer 1   0   
6   MustBeInt   0   0   
7   MemStore2   0   
8   IfMemZero   2   31  
9   IfMemPos2   13  
10  Pop 1   0   
11  MemInt  -1  3   
12  Goto0   14  
13  MemStore3   1   
14  Integer 0   0   
15  OpenRead1   15123   keyinfo(1,BINARY)
16  SetNumColumns   1   2   
17  Integer 823 0   
18  IsNull  -1  30  
19  MakeRecord  1   0   d
20  MemStore4   0   
21  MoveGe  1   30  
22  MemLoad 4   0   
23  IdxGE   1   30  +
24  Column  1   0   
25  MemInt  1   1   
26  Pop 1   0   
27  MemIncr -1  2   
28  IfMemZero   2   30  
29  Next1   22  
30  Close   1   0   
31  MemLoad 1   0   
32  Callback1   0   
33  Halt0   0   
34  Transaction 0   0   
35  VerifyCookie0   397 
36  Goto0   1   
37  Noop0   0   


Fast
-
addropcode  p1  p2

0   Goto0   34
1   MemLoad 0   0
2   If  0   31
3   MemInt  1   0
4   MemInt  0   1
5   Integer 1   0
6   MustBeInt   0   0
7   MemStore2   0
8   IfMemZero   2   31
9   IfMemPos2   13
10  Pop 1   0
11  MemInt  -1  3
12  Goto0   14
13  MemStore3   1
14  Integer 0   0
15  OpenRead1   15123
16  SetNumColumns   1   2
17  Integer 831 0
18  IsNull  -1  30
19  MakeRecord  1   0
20  MemStore4   0
21  MoveGe  1   30
22  MemLoad 4   0
23  IdxGE   1   30
24  Column  1   0
25  MemInt  1   1
26  Pop 1   0
27  MemIncr -1  2
28  IfMemZero   2   30
29  Next1   22
30  Close   1   0
31  MemLoad 1   0
32  Callback1   0
33  Halt0   0
34  Transaction 0   0
35  VerifyCookie0   400
36  Goto0   1
37  Noop0   0

So, what possibly could explain this? There can be no cache effect as I can
reverse it from fast to slow and vice versa by copying one or the other
database.


RBS


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of RB Smissaert
Sent: 09 January 2009 20:00
To: sqlite-users@sqlite.org
Subject: [sqlite] Why the difference in these 2 SQLite files?

Have a large (about half a Gb) SQLite db file, made with version 3.6.1.
I make this file on one PC (Windows XP) put the file on a USB stick, take it
home, copy it to the home PC (Win XP) local drive and then from there copy
the file to a Windows mobile (WM6) device, a Samsung Omnia.
On that device I access the db file with an application written with
Basic4PPC, which uses the .Net framework. SQLite version used by Basic4PPC
is 3.3.12.

Now the thing I don't understand.
I run the following query (on the mobile device):

SELECT EXISTS(
SELECT PATIENT_ID
FROM TABLE1
WHERE PATIENT_ID = 123

Re: [sqlite] newbie question regarding my sqlite code

2009-01-10 Thread RB Smissaert
Try this:

select
avg(age)
from acoda
union all
select
avg(durata)
from main
union all
select
sum(età)
from dipendenti


RBS


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of silvio grosso
Sent: 10 January 2009 10:41
To: sqlite-users@sqlite.org
Subject: [sqlite] newbie question regarding my sqlite code

Hello,

I am a new sqilte user and I am learning sqlite code in my spare time..
I have always used sql code with Microsoft Access and Base (openoffice).
Therefore, sorry to ask a question very simple .

When I write the very simple code:
select sum(età) from dipendenti
everything works fine and the result is 100.

When
I try a bit longer query the result for the sum regarding the column
età from the table dipendenti changes and it is wrong. That is 25200?
(instead of the right value 100!).

The query is:
select avg(age), avg(durata), sum(età) from acoda, main, dipendenti

In
the above query the avg results for the column age (table acoda) and
the column durata (table main) are right. The only value wrong is the
third, that it, sum (for the table dipendenti, column age, 25200 instead of
the right value 100).
The column age in the table dipendenti is not present in the other two
tables (acoda, main).

What's wrong with the second query?


Thanks in advance.

Best regards



  
___
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] Why the difference in these 2 SQLite files?

2009-01-09 Thread RB Smissaert
OK, any DB write to this file on the desktop (not the mobile device)
will make the query go fast. A completely trivial update will do it, so
it looks somehow the db write on the desktop does makes it go fast.
I can't check now if the same applies when doing this db write on the first
PC and hopefully it does.
Maybe I need some app to compare the 2 db files to see what is going on
here. Baffling me.

RBS


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of RB Smissaert
Sent: 09 January 2009 20:54
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Why the difference in these 2 SQLite files?

Have now also looked at the full EXPLAIN and although understand that
it looks indentical to me for both databases:

Slow
-
addropcode  p1  p2  p3

0   Goto0   34  
1   MemLoad 0   0   
2   If  0   31  
3   MemInt  1   0   
4   MemInt  0   1   
5   Integer 1   0   
6   MustBeInt   0   0   
7   MemStore2   0   
8   IfMemZero   2   31  
9   IfMemPos2   13  
10  Pop 1   0   
11  MemInt  -1  3   
12  Goto0   14  
13  MemStore3   1   
14  Integer 0   0   
15  OpenRead1   15123   keyinfo(1,BINARY)
16  SetNumColumns   1   2   
17  Integer 823 0   
18  IsNull  -1  30  
19  MakeRecord  1   0   d
20  MemStore4   0   
21  MoveGe  1   30  
22  MemLoad 4   0   
23  IdxGE   1   30  +
24  Column  1   0   
25  MemInt  1   1   
26  Pop 1   0   
27  MemIncr -1  2   
28  IfMemZero   2   30  
29  Next1   22  
30  Close   1   0   
31  MemLoad 1   0   
32  Callback1   0   
33  Halt0   0   
34  Transaction 0   0   
35  VerifyCookie0   397 
36  Goto0   1   
37  Noop0   0   


Fast
-
addropcode  p1  p2

0   Goto0   34
1   MemLoad 0   0
2   If  0   31
3   MemInt  1   0
4   MemInt  0   1
5   Integer 1   0
6   MustBeInt   0   0
7   MemStore2   0
8   IfMemZero   2   31
9   IfMemPos2   13
10  Pop 1   0
11  MemInt  -1  3
12  Goto0   14
13  MemStore3   1
14  Integer 0   0
15  OpenRead1   15123
16  SetNumColumns   1   2
17  Integer 831 0
18  IsNull  -1  30
19  MakeRecord  1   0
20  MemStore4   0
21  MoveGe  1   30
22  MemLoad 4   0
23  IdxGE   1   30
24  Column  1   0
25  MemInt  1   1
26  Pop 1   0
27  MemIncr -1  2
28  IfMemZero   2   30
29  Next1   22
30  Close   1   0
31  MemLoad 1   0
32  Callback1   0
33  Halt0   0
34  Transaction 0   0
35  VerifyCookie0   400
36  Goto0   1
37  Noop0   0

So, what possibly could explain this? There can be no cache effect as I can
reverse it from fast to slow and vice versa by copying one or the other
database.


RBS


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of RB Smissaert
Sent: 09 January 2009 20:00
To: sqlite-users@sqlite.org
Subject: [sqlite] Why the difference in these 2 SQLite files?

Have a large (about half a Gb) SQLite db file, made with version 3.6.1.
I make this file on one PC (Windows XP) put the file on a USB stick, take it
home, copy it to the home PC (Win XP) local drive and then from there copy
the file to a Windows mobile (WM6) device, a Samsung Omnia.
On that device I access the db file with an application written with
Basic4PPC, which uses the .Net framework. SQLite version used by Basic4PPC
is 3.3.12.

Now the thing I don't understand.
I run the following query (on the mobile device):

SELECT EXISTS(
SELECT PATIENT_ID
FROM TABLE1
WHERE PATIENT_ID = 123)

There is an index on field PATIENT_ID.

Takes query takes quite long, say about 1 second.

Now I do the following:
On the home PC I access the SQLite db file, again with 3.6.1 and I drop a
table in that database. This table is only small and completely irrelevant
In the above query. After doing that I copy that db file to the mobile
device, so overwriting the old file. Now when I run that same query it is a
multitude faster, say maybe 20 milli-seconds. I can achieve the same by
dropping

Re: [sqlite] Why the difference in these 2 SQLite files?

2009-01-09 Thread RB Smissaert
Have now also looked at the full EXPLAIN and although understand that
it looks indentical to me for both databases:

Slow
-
addropcode  p1  p2  p3

0   Goto0   34  
1   MemLoad 0   0   
2   If  0   31  
3   MemInt  1   0   
4   MemInt  0   1   
5   Integer 1   0   
6   MustBeInt   0   0   
7   MemStore2   0   
8   IfMemZero   2   31  
9   IfMemPos2   13  
10  Pop 1   0   
11  MemInt  -1  3   
12  Goto0   14  
13  MemStore3   1   
14  Integer 0   0   
15  OpenRead1   15123   keyinfo(1,BINARY)
16  SetNumColumns   1   2   
17  Integer 823 0   
18  IsNull  -1  30  
19  MakeRecord  1   0   d
20  MemStore4   0   
21  MoveGe  1   30  
22  MemLoad 4   0   
23  IdxGE   1   30  +
24  Column  1   0   
25  MemInt  1   1   
26  Pop 1   0   
27  MemIncr -1  2   
28  IfMemZero   2   30  
29  Next1   22  
30  Close   1   0   
31  MemLoad 1   0   
32  Callback1   0   
33  Halt0   0   
34  Transaction 0   0   
35  VerifyCookie0   397 
36  Goto0   1   
37  Noop0   0   


Fast
-
addropcode  p1  p2

0   Goto0   34
1   MemLoad 0   0
2   If  0   31
3   MemInt  1   0
4   MemInt  0   1
5   Integer 1   0
6   MustBeInt   0   0
7   MemStore2   0
8   IfMemZero   2   31
9   IfMemPos2   13
10  Pop 1   0
11  MemInt  -1  3
12  Goto0   14
13  MemStore3   1
14  Integer 0   0
15  OpenRead1   15123
16  SetNumColumns   1   2
17  Integer 831 0
18  IsNull  -1  30
19  MakeRecord  1   0
20  MemStore4   0
21  MoveGe  1   30
22  MemLoad 4   0
23  IdxGE   1   30
24  Column  1   0
25  MemInt  1   1
26  Pop 1   0
27  MemIncr -1  2
28  IfMemZero   2   30
29  Next1   22
30  Close   1   0
31  MemLoad 1   0
32  Callback1   0
33  Halt0   0
34  Transaction 0   0
35  VerifyCookie0   400
36  Goto0   1
37  Noop0   0

So, what possibly could explain this? There can be no cache effect as I can
reverse it from fast to slow and vice versa by copying one or the other
database.


RBS


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of RB Smissaert
Sent: 09 January 2009 20:00
To: sqlite-users@sqlite.org
Subject: [sqlite] Why the difference in these 2 SQLite files?

Have a large (about half a Gb) SQLite db file, made with version 3.6.1.
I make this file on one PC (Windows XP) put the file on a USB stick, take it
home, copy it to the home PC (Win XP) local drive and then from there copy
the file to a Windows mobile (WM6) device, a Samsung Omnia.
On that device I access the db file with an application written with
Basic4PPC, which uses the .Net framework. SQLite version used by Basic4PPC
is 3.3.12.

Now the thing I don't understand.
I run the following query (on the mobile device):

SELECT EXISTS(
SELECT PATIENT_ID
FROM TABLE1
WHERE PATIENT_ID = 123)

There is an index on field PATIENT_ID.

Takes query takes quite long, say about 1 second.

Now I do the following:
On the home PC I access the SQLite db file, again with 3.6.1 and I drop a
table in that database. This table is only small and completely irrelevant
In the above query. After doing that I copy that db file to the mobile
device, so overwriting the old file. Now when I run that same query it is a
multitude faster, say maybe 20 milli-seconds. I can achieve the same by
dropping an index, again in a small table, completely unrelated to the above
query and I see the same speed gain. I have compared the 2 different
database files, so stats, all the different pragma's etc. and I can't see
the difference. I have also compared the SQLite query plans and they are the
same (using the index) for both files. So what possibly could explain the
difference in speed?

I know it is a bit of a long-winded question, but maybe somebody has some
idea what is going on here and if so, very grateful for that as I can't see
it.


RBS





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

[sqlite] Why the difference in these 2 SQLite files?

2009-01-09 Thread RB Smissaert
Have a large (about half a Gb) SQLite db file, made with version 3.6.1.
I make this file on one PC (Windows XP) put the file on a USB stick, take it
home, copy it to the home PC (Win XP) local drive and then from there copy
the file to a Windows mobile (WM6) device, a Samsung Omnia.
On that device I access the db file with an application written with
Basic4PPC, which uses the .Net framework. SQLite version used by Basic4PPC
is 3.3.12.

Now the thing I don't understand.
I run the following query (on the mobile device):

SELECT EXISTS(
SELECT PATIENT_ID
FROM TABLE1
WHERE PATIENT_ID = 123)

There is an index on field PATIENT_ID.

Takes query takes quite long, say about 1 second.

Now I do the following:
On the home PC I access the SQLite db file, again with 3.6.1 and I drop a
table in that database. This table is only small and completely irrelevant
In the above query. After doing that I copy that db file to the mobile
device, so overwriting the old file. Now when I run that same query it is a
multitude faster, say maybe 20 milli-seconds. I can achieve the same by
dropping an index, again in a small table, completely unrelated to the above
query and I see the same speed gain. I have compared the 2 different
database files, so stats, all the different pragma's etc. and I can't see
the difference. I have also compared the SQLite query plans and they are the
same (using the index) for both files. So what possibly could explain the
difference in speed?

I know it is a bit of a long-winded question, but maybe somebody has some
idea what is going on here and if so, very grateful for that as I can't see
it.


RBS





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


Re: [sqlite] delete to leave x rows per group

2008-07-03 Thread RB Smissaert
Thanks, nice and simple.

I had come up with something that works as well, but probably more complex
than needed. This is with my actual data:

delete
from
sqlite_tablePa
where
rowid not in
(select
rowid 
from
sqlite_tablePa 
where
(select
count(*) 
from
sqlite_tablePa  as s   
where
s.patient_id = sqlite_tablePa.patient_id and
s.rowid < sqlite_tablePa.rowid) < 3)

I think I will stick with your solution as it is simpler and most likely
faster as well.


RBS



-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Igor Tandetnik
Sent: 03 July 2008 22:54
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] delete to leave x rows per group

RB Smissaert <[EMAIL PROTECTED]>
wrote:
> Can this be done in SQLite SQL?
>
> ID Value
> ---
> 1 A
> 1 B
> 1 C
> 1 D
> 1 E
> 2 A
> 2 B
> 2 C
> 2 D
> 2 E
> 2 F
>
> Delete rows to leave x rows per ID, say 3 rows, so we get:

delete from tableName where rowid not in (
select rowid from tableName t2
where t2.ID = tableName.ID
order by Value desc
limit 3
);

Igor Tandetnik 



___
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] delete to leave x rows per group

2008-07-03 Thread RB Smissaert
Can this be done in SQLite SQL?

ID  Value
---
1   A
1   B
1   C
1   D
1   E
2   A
2   B
2   C
2   D
2   E
2   F

Delete rows to leave x rows per ID, say 3 rows, so we get:

ID  Value
---
1   C
1   D
1   E
2   D
2   E
2   F

If there were less than 3 rows for a group then that group should be
ignored, so now rows should be deleted from that group.


RBS


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


[sqlite] PRAGMA journal_mode = OFF slower?

2008-05-20 Thread RB Smissaert
Done some testing with 3.5.9 with PRAGMA journal_mode = OFF and it seems
that strangely it makes DB writing queries slower. I use SQLite from VB/VBA
with the wrapper from Olaf Schmidt. Should it not be that PRAGMA
journal_mode = OFF should make inserts, create index etc. faster rather than
slower? Has anybody else seen the same? This is on Windows XP.

RBS


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


Re: [sqlite] sorting records in random order

2008-05-07 Thread RB Smissaert
I compared the speeds and found them to be the same.

RBS


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Dennis Cote
Sent: 07 May 2008 22:25
To: General Discussion of SQLite Database
Subject: Re: [sqlite] sorting records in random order

Samuel Neff wrote:
> This query runs slow:
> 
> SELECT id FROM data ORDER BY random();
> 
> but this equivalent query runs very fast:
> 
> SELECT id FROM (SELECT id, random() r FROM data) ORDER BY r;
> 

I couldn't see how these would be different so I fired up the explain 
command. As I expected, these two produce identical code (except for the 
  integer id assigned to the ephemeral table used for the sort). I don't 
think here will be any difference in speed between these two statements.

SQLite version 3.5.7
Enter ".help" for instructions
sqlite> create table t (id integer primary key, a text);
sqlite> .explain
sqlite> explain select id from t order by random();
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 0 explain select id from t order by 
random(
);  00
1 OpenEphemeral  1 3 0 keyinfo(1,BINARY)  00
2 Goto   0 26000
3 OpenRead   0 2 000
4 SetNumColumns  0 0 000
5 Rewind 0 14000
6 Rowid  0 1 000
7 MakeRecord 1 1 200
8 Function   0 0 3 random(-1) 00
9 Sequence   1 4 000
10Move   2 5 000
11MakeRecord 3 3 600
12IdxInsert  1 6 000
13Next   0 6 000
14Close  0 0 000
15OpenPseudo 2 0 000
16SetNumColumns  2 1 000
17Sort   1 24000
18Column 1 2 200
19Integer1 6 000
20Insert 2 2 600
21Column 2 0 100
22ResultRow  1 1 000
23Next   1 18000
24Close  2 0 000
25Halt   0 0 000
26Transaction0 0 000
27VerifyCookie   0 1 000
28TableLock  0 2 0 t  00
29Goto   0 3 000
sqlite> explain select id from (select id, random() r from t) order by r;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 0 explain select id from (select 
id, random
() r from t) order by r;  00
1 OpenEphemeral  2 3 0 keyinfo(1,BINARY)  00
2 Goto   0 26000
3 OpenRead   1 2 000
4 SetNumColumns  1 0 000
5 Rewind 1 14000
6 Rowid  1 1 000
7 MakeRecord 1 1 200
8 Function   0 0 3 random(-1) 00
9 Sequence   2 4 000
10Move   2 5 000
11MakeRecord 3 3 600
12IdxInsert  2 6 000
13Next   1 6 000
14Close  1 0 000
15OpenPseudo 3 0 000
16SetNumColumns  3 1 000
17Sort   2 24000
18Column 2 2 200
19Integer1 6 000
20Insert 3 2 600
21Column 3 0 100
22ResultRow  1 1 000
23Next   2 18000
24Close  3 0 000
25Halt   0 0 000
26Transaction0 0 000
27VerifyCookie   0 1 000
28TableLock  0 2 0 t  00
29Goto   0 3 000
sqlite>

Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org

Re: [sqlite] temp tables and PRAGMA temp_store

2008-04-30 Thread RB Smissaert
Hi Olaf,

Aaah, that is a funny one!
Will test today then with PRAGMA temp_store = FILE
and see if that makes it slower!

Bart 


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Olaf Schmidt
Sent: 30 April 2008 04:24
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] temp tables and PRAGMA temp_store

RB Smissaert <[EMAIL PROTECTED]> writes:

> 
> Using the latest SQLite and trying to speed up the making of some
> intermediate tables. I thought I could do that by doing
> PRAGMA temp_store = MEMORY and CREATE TEMP TABLE etc.
> I do run the PRAGMA directly after establishing the SQLite connection.
> So far I haven't seen any speed increase yet and I am wondering if maybe
> I am doing something wrong here.
> Does this PRAGMA only work on a newly created db file? Have tried that,
but
> again no difference.
> Would you expect a speed increase from doing the above?
> I am running this from VB with the wrapper from Olaf Schmidt.

Hi Bart,

the reason why you don't see any changes is,
that this is already the default in my version
of the SQLite-engine compile (sqlite35_engine.dll).
;-)

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] temp tables and PRAGMA temp_store

2008-04-29 Thread RB Smissaert
Using the latest SQLite and trying to speed up the making of some
intermediate tables. I thought I could do that by doing
PRAGMA temp_store = MEMORY and CREATE TEMP TABLE etc.
I do run the PRAGMA directly after establishing the SQLite connection.
So far I haven't seen any speed increase yet and I am wondering if maybe
I am doing something wrong here.
Does this PRAGMA only work on a newly created db file? Have tried that, but
again no difference.
Would you expect a speed increase from doing the above?
I am running this from VB with the wrapper from Olaf Schmidt.
Thanks for any advice.

RBS


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


Re: [sqlite] Implicit INDEX?

2008-04-14 Thread RB Smissaert
Have tested this now on a table of some 30 rows (no indexes at all)
and with 100 rows to find in the middle of the table, sorted asc on
time-stamp field.
It gave me a speed increase of about 25%.
If I looked for rows at the beginning of the table the speed increase was
more, some 50% faster.
If I looked for rows at the end (highest timestamp) then the simple select
was quite a lot faster than the one with limit etc.

Maybe not the kind of increase you were interested in, but still something
and with no extra overhead at all, just a different SQL. Probably only worth
it if looking for early times.

RBS


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of
[EMAIL PROTECTED]
Sent: 14 April 2008 19:11
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Implicit INDEX?

How about this:

select
*
from
table1
where
rowid >=
(select
rowid
from
table1
where
time_stamp = xxx
limit 1)
and
rowid <
(select
rowid
from
table1
where
time_stamp > xxx
limit 1)

RBS


> Donald,
>
>> To test this, I think you'd want to create a select for some of the most
>> recent data (i.e. data at the tail of the database), perhaps after
>> clearing cache.  I suspect this will take the full table scan time to
>> return any values.
>
>  I'd actually just thought of that.  All my test SELECTs happened
> to be using data close to the "front" of the file.  As suspected, if I try
> it on "later" data, I have to wait for the table scan, then I get the
> data.  I'd be erroneously assuming that SQLite was somehow searching for
> the first value quickly, then scanning the table.
>
>> Two thoughts:
>>
>>   1).  Easy.
>> How bad is the extra 0.8 GByte cost of the index?  At today's prices,
>> it's only about 20 cents on ordinary 5" drives.  (but maybe you're
>> programming a cellphone)
>
>  No, it's nothing terribly constrained.  I'm just trying to
> understand the mechanisms and do what I can to keep the size down where
> possible.  I was somewhat surprised to find that adding an index on a
> single INTEGER column nearly doubled the size of the database and wanted
> to figure out if there was a way around it, given that the column will
> always be sorted.  (And given my, perhaps erroneous understanding that
> creating an INDEX just makes sure that the column stays sorted so SQLite
> can search through it more intelligently)
>
>>   2).  Fancy.
>> You could create your own sparse index table mapping a ROWID to say,
>> every thousandth timestamp.  Then you could create upper and lower ROWID
>> bounds on any query based on timestamps.  Maybe you import the records
>> in batches already and can create the new table at the same time.
>
>  This is closer to what I'm probably going to do.  The data gets
> pulled in every 5 minutes, but between runs, very little actually changes.
> So the idea is to store only the changes along with a full dump say once
> or twice every day.  Then I can just query the values from  time> to  and compute the state of everything from
> that data.
>
>  Thanks,
>
>   Chris
> ___
> 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] Any way to disable journaling & rollback?

2008-04-11 Thread RB Smissaert
DRH,

I would be seriously interested in a PRAGMA to disable/avoid a
journal file as in my application I don't need it at all and it
only slows down my DB writes. Would it be possible to add this?
If so, thanks in advance.

RBS



-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Griggs, Donald
Sent: 11 April 2008 14:06
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Any way to disable journaling & rollback?

Regarding: " removing the call of FlushFileBuffers for each transaction
made my application run 20 times faster."

Since you don't need the integrity protection that transactions afford,
would you not get the same performance gain using the standard source
and setting SYNCHRONOUS to zero? 



This email and any attachments have been scanned for known viruses using
multiple scanners. We believe that this email and any attachments are virus
free, however the recipient must take full responsibility for virus
checking. 
This email message is intended for the named recipient only. It may be
privileged and/or confidential. If you are not the named recipient of this
email please notify us immediately and do not copy it or use it for any
purpose, nor disclose its contents to any other person.
___
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] PHP Code That Can Store and Retrieve Images

2008-03-12 Thread RB Smissaert
Kees,

Thanks for the interest in this and replied off-list.

Bart

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Kees Nuyt
Sent: 12 March 2008 22:06
To: General Discussion of SQLite Database
Subject: Re: [sqlite] PHP Code That Can Store and Retrieve Images

On Wed, 12 Mar 2008 18:44:36 -, Bart wrote:

>Kees,
>
>Would you be interested to do a project for me for a fee?

[..]

>Regards,   Bart Smissaert

Answered in private mail.
-- 
  (  Kees Nuyt
  )
c[_]
___
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] PHP Code That Can Store and Retrieve Images

2008-03-12 Thread RB Smissaert
Kees,

Would you be interested to do a project for me for a fee?
I need to upload/download data to/from a hosted SQLite 3 DB.
This has to be done from VBA or from a VB6 AX dll.
I have posted this to RAC, but there seems little interest/progress.
If interested then could you contact me off-list?
I could correspond in Dutch.

Regards,   Bart Smissaert


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Kees Nuyt
Sent: 12 March 2008 18:39
To: General Discussion of SQLite Database
Subject: Re: [sqlite] PHP Code That Can Store and Retrieve Images

On Wed, 12 Mar 2008 09:10:44 -0400, you wrote:

>Here is a link to the PHP code to generate the base64 string and to convert
>the string back to an image.
>
>http://fundisom.com/phparadise/php/image_handling/base64_image_encode
>

In PHP it might be better to serialize().
That works for every value type.
Just my 0.02
-- 
  (  Kees Nuyt
  )
c[_]
___
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] PHP Code That Can Store and Retrieve Images

2008-03-11 Thread RB Smissaert
Funny you ask that as just 2 days ago I posted a little project
on RAC to do exactly this. In my case it has to be called from
VBA or VB. Unfortunately and surprisingly no takers yet.

RBS


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Robert L Cochran
Sent: 11 March 2008 21:15
To: General Discussion of SQLite Database
Subject: [sqlite] PHP Code That Can Store and Retrieve Images

Is there open source PHP code (PHP 5.x compatible) that can store and
retrieve images from an SQLite 3.5.6 database?

For SQLite version 3.5.x, I need to use the PHP PDO functions if I am
using PHP 5.2.5, right?

I want to show a group of people about 45 photos which I would like to
store on an SQLite database and then retrieve.

Thanks

Bob Cochran
___
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] when to analyze?

2008-02-06 Thread RB Smissaert
Thanks for that explanation.
In my app tables are dropped and created and inserted with data
continuously, so the recommended use won't quite apply, but I understand
better now.

I noticed sqlite_stat1 doesn't get updated if a table is dropped or renamed
with ALTER TABLE, so I take it I will have to take care of this myself,
particularly with a table rename.

RBS


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED]
Sent: 06 February 2008 20:27
To: General Discussion of SQLite Database
Subject: Re: [sqlite] when to analyze?

"RB Smissaert" <[EMAIL PROTECTED]> wrote:
> Suppose we have a table with some 10 million rows and this table was
> analysed, so sqlite_stat1 has the stats of this table then is it worth it
to
> analyze again after adding say 1000 more rows? The indexing is still the
> same, so no indexes are dropped or created. Also the data of the added
rows
> won't be dramatically different from the existing rows.
> My guess it is not worth the time it will take and I could fine out by
> experimenting, but maybe somebody has some thoughts about this and could
> tell me.
> 

Briefly:  Your guess is correct

For additional background into why ANALYZE exists and what it
accomplishes for you, please see

http://www.sqlite.org/cvstrac/wiki?p=QueryPlans

SQLite rarely needs to do ANALYZE at all.  You can usually get
it to pick efficient query plans without having to ANALYZE.

The recommended use of ANALYZE, if you use it at all, is to
run it once during development on a dataset that is characteristic
of the kinds of data your application will store.  Retain the
results of this ANALYZE as they are found in the sqlite_stat1
table.  Then, when you deploy your application and create a new 
database, run ANALYZE once as soon as the schema is loaded but
before any data is added.  Such an ANALYZE will take almost no
time because your database is empty.  Then delete all of
the information from the newly created sqlite_stat1 table and
replace it with the data you saved from the ANALYZE you ran
during development on your sample dataset.

The result of this will be that SQLite will plan queries with
an eye toward optimizing databases that are typical for your
application.

--
D. Richard Hipp <[EMAIL PROTECTED]>

___
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] when to analyze?

2008-02-06 Thread RB Smissaert
Suppose we have a table with some 10 million rows and this table was
analysed, so sqlite_stat1 has the stats of this table then is it worth it to
analyze again after adding say 1000 more rows? The indexing is still the
same, so no indexes are dropped or created. Also the data of the added rows
won't be dramatically different from the existing rows.
My guess it is not worth the time it will take and I could fine out by
experimenting, but maybe somebody has some thoughts about this and could
tell me.

RBS



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


[sqlite] Duplicates in sqlite_stat1

2008-02-03 Thread RB Smissaert
Noticed that sqlite_stat1 can have duplicates on tbl, idx:

tbl idx stat
---
table1  idx190 2 1
table1  idx290 2
table1  idx12577 2 1
table1  idx22577 2

Is there any harm in this, so would SQLite know that it has to look at the
last added stats?

RBS


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


[sqlite] strange problem with DELETE

2008-02-03 Thread RB Smissaert
SQLite 3.5.4, Win XP, VBA with the wrapper dhRichClient

Running a query like this:

delete from sqlite_stat1
where
not tbl in
('table1', 'table2', 'table3')

The strange thing is that rows are deleted where tbl is one of the listed
tables.

Have tried all kind of alterations, such as making it case-insensitive,
leaving off single quotes or doing instead double-quotes, but I always get
deletes that should not happen. Must be overlooking something simple here,
but can't see it and thanks for any advice.

RBS



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


RE: [sqlite] Re: how to do this case when?

2008-01-31 Thread RB Smissaert
There isn't much in it, but it looks the one with IFNULL is the fastest.
Will stick to that one.

RBS

-Original Message-
From: RB Smissaert [mailto:[EMAIL PROTECTED] 
Sent: 31 January 2008 21:17
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Re: how to do this case when?

Thanks; I came up with number 3, but I like your number 1.
Any idea what could be the fastest or will it all be the same?

RBS


-Original Message-
From: Igor Tandetnik [mailto:[EMAIL PROTECTED] 
Sent: 31 January 2008 20:59
To: SQLite
Subject: [sqlite] Re: how to do this case when?

RB Smissaert <[EMAIL PROTECTED]>
wrote: 
> How do I alter this SQL, so that the original field remains the same
> when 
> there is no match? A case when else end should do it, but I can't get
> it 
> right.
> 
> UPDATE Table1 SET Field1 =
> (SELECT Field2 FROM Table2
> WHERE Table1.Field1 = Table2.Field1)

UPDATE Table1 SET Field1 =
IFNULL(
(SELECT Field2 FROM Table2
 WHERE Table1.Field1 = Table2.Field1),
Field1);

-- or

UPDATE Table1 SET Field1 =
(SELECT Field2 FROM Table2
 WHERE Table1.Field1 = Table2.Field1
  union all select Table1.Field1;
);

-- or

UPDATE Table1 SET Field1 =
(SELECT Field2 FROM Table2
 WHERE Table1.Field1 = Table2.Field1)
where exists
(SELECT Field2 FROM Table2
 WHERE Table1.Field1 = Table2.Field1)

Igor Tandetnik


-
To unsubscribe, send email to [EMAIL PROTECTED]

-





-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Re: how to do this case when?

2008-01-31 Thread RB Smissaert
Thanks; I came up with number 3, but I like your number 1.
Any idea what could be the fastest or will it all be the same?

RBS


-Original Message-
From: Igor Tandetnik [mailto:[EMAIL PROTECTED] 
Sent: 31 January 2008 20:59
To: SQLite
Subject: [sqlite] Re: how to do this case when?

RB Smissaert <[EMAIL PROTECTED]>
wrote: 
> How do I alter this SQL, so that the original field remains the same
> when 
> there is no match? A case when else end should do it, but I can't get
> it 
> right.
> 
> UPDATE Table1 SET Field1 =
> (SELECT Field2 FROM Table2
> WHERE Table1.Field1 = Table2.Field1)

UPDATE Table1 SET Field1 =
IFNULL(
(SELECT Field2 FROM Table2
 WHERE Table1.Field1 = Table2.Field1),
Field1);

-- or

UPDATE Table1 SET Field1 =
(SELECT Field2 FROM Table2
 WHERE Table1.Field1 = Table2.Field1
  union all select Table1.Field1;
);

-- or

UPDATE Table1 SET Field1 =
(SELECT Field2 FROM Table2
 WHERE Table1.Field1 = Table2.Field1)
where exists
(SELECT Field2 FROM Table2
 WHERE Table1.Field1 = Table2.Field1)

Igor Tandetnik


-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] how to do this case when?

2008-01-31 Thread RB Smissaert
Found this now:

UPDATE Table1 SET Field1 =
(case when Field1 IN (SELECT Field1 FROM Table2)
then
(SELECT Field2 FROM Table2 WHERE Table1.Field1 = Table2.Field1)
else
Table1.Field1 end)

Have a feeling though that this can be shorter.

RBS


-Original Message-
From: RB Smissaert [mailto:[EMAIL PROTECTED] 
Sent: 31 January 2008 20:15
To: sqlite-users@sqlite.org
Subject: [sqlite] how to do this case when?

How do I alter this SQL, so that the original field remains the same when
there is no match? A case when else end should do it, but I can't get it
right.

UPDATE Table1 SET Field1 =
(SELECT Field2 FROM Table2
WHERE Table1.Field1 = Table2.Field1)

Thanks for any advice.

RBS




-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-



[sqlite] how to do this case when?

2008-01-31 Thread RB Smissaert
How do I alter this SQL, so that the original field remains the same when
there is no match? A case when else end should do it, but I can't get it
right.

UPDATE Table1 SET Field1 =
(SELECT Field2 FROM Table2
WHERE Table1.Field1 = Table2.Field1)

Thanks for any advice.

RBS



-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Number of elements in IN clause

2008-01-28 Thread RB Smissaert
>SELECT * FROM maintable WHERE key IN stuff;

Thanks for that tip. Didn't know you could do that.
Can't remember this as standard SQL.

RBS



-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: 28 January 2008 12:40
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Number of elements in IN clause

Felix Radensky <[EMAIL PROTECTED]> wrote:
> Hi,
> 
> Is there any limitation on the number of elements in IN clause ?
> Can one have, e.g. thousands of elements ? Also, can having 
> to many elements become inefficient at some point and one
> has to use some other technique, i.e. comparing elements one
> by one in a loop ?
> 

You can create a table that contains the elements that you would
normally put in your IN clause:

   CREATE TEMP TABLE stuff(x);
   INSERT INTO stuff VALUES('one');
   INSERT INTO stuff VALUES('two');
   
   INSERT INTO stuff VALUES('one million');

Then run your query this way:

   SELECT * FROM maintable WHERE key IN stuff;

--
D. Richard Hipp <[EMAIL PROTECTED]>



-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-



[sqlite] Could this cause slow queries?

2008-01-24 Thread RB Smissaert
Latest SQLite version with the VB wrapper from Olaf Schmidt,
dhRichClient.dll. Running this in VBA Excel on Windows XP.

Have a suspicion that maybe you could get slow queries if a table repeatedly
gets a DELETE FROM TABLE followed by re-populating the table with inserts,
so cyling this repeatedly. It is the delete query that gets slow.
No logical explanation and no idea yet what exactly happens, but definitely
something strange going on. Maybe it somehow has to do with my application
or maybe the wrapper, but these are simple queries and there and the data is
all simple and nothing unusual there. I have dealt with this now by dropping
the table once in every Excel session and this seems to do the trick.

Is there anything in the SQLite code that could make this happen or should I
look at my app or the wrapper?

Thanks for any advice.

RBS



-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Slow query on one machine

2008-01-19 Thread RB Smissaert
I can't do anything like that as it is a machine of a customer and I have no
access to it. Just wondering what possibly could explain such a difference.
The relevant thing is that are load of queries coming after this and they
are all fine. So it specific to that particular table at that particular
point on that particular machine, all else behaves normal.

RBS

-Original Message-
From: Jay Sprenkle [mailto:[EMAIL PROTECTED] 
Sent: 19 January 2008 14:12
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Slow query on one machine

On Jan 18, 2008 3:32 PM, RB Smissaert <[EMAIL PROTECTED]>
wrote:
> The application
> that runs this is exactly the same on both machines. The slow machine is
> actually slightly slower specification wise, but that can't explain the
huge
> differences in timings.
>

Have you run spinrite ( a disk diagnostic/maintenance program ) on the
slow machine?


-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-



[sqlite] Slow query on one machine

2008-01-18 Thread RB Smissaert
Trying to figure out why (with one particular customer) some queries have
very different timings on one machine compared to another machine.
It has to do with updating one particular SQLite table with more recent data
from an Interbase table.

I give the database (S for SQLite and I for Interbase), SQL query and time
in seconds of slow machine and fast machine:

DB|Query|slow PC|fast PC
-
S|SELECT MAX(ENTRY_ID) FROM ENTRY_ATTRIBUTES|0.26|8.46
I|SELECT ENTRY_ID, ATTRIBUTE_TYPE, TYPE_SPECIFIC_INFO,
NUMERIC_VALUE FROM ENTRY_ATTRIBUTE WHERE ENTRY_ID > 15085882|0.05|0.07
S|INSERT OR IGNORE INTO ENTRY_ATTRIBUTES VALUES(?,?,?,?)|7.51|0.14
S|analyze ENTRY_ATTRIBUTES|431.96|0.03

All I can think of is that somehow there is something wrong with the SQLite
table ENTRY_ATTRIBUTES on the slow machine and I suggested dropping that
table and making a new one, but that made no difference. The application
that runs this is exactly the same on both machines. The slow machine is
actually slightly slower specification wise, but that can't explain the huge
differences in timings.

I think the only way for me to find is to get hold of that SQLite file, but
for now having some trouble getting hold of this file.
Any ideas?


RBS





-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Helping with table definition?

2007-12-25 Thread RB Smissaert
This is code I used a while ago. Don't use it anymore as I have a better
way to do this via my VB wrapper. There are some lines that deal with code
in other parts of my application, but I take it you can see that.
In case you didn't know this is VB(A).

Function GetSQLiteTableInfo2(strDB As String, _
 strTable As String, _
 Optional strSelect As String, _
 Optional strOmitFields As String, _
 Optional strAlias As String, _
 Optional strFields As String, _
 Optional strDataTypes As String, _
 Optional bCurrentConnection As Boolean) As
String()

 'will produce the table fields as an 0-based 1-D array
 'and make the strings:
 'field1,field2,field3 etc.
 'field1, field2, field3 etc.
 'datatype1,datatype2,datatype3 etc.
 '--
 Dim r As Long
 Dim c As Long
 Dim strSQL As String
 Dim arr
 Dim arr2
 Dim arr3
 Dim strAlias2 As String
 Dim arrOmitFields
 Dim bOmit As Boolean
 Dim bDoneFirst As Boolean
 Dim lRows As Long
 Dim strError As String
 Dim lDBHandle As Long

10   If Len(strAlias) > 0 Then
20  strAlias2 = strAlias & "."
30   End If

40   If Len(strOmitFields) > 0 Then
50  arrOmitFields = Split(strOmitFields, ",")
60  bOmit = True
70   End If

80   If bShowErrors Then
90  On Error GoTo 0
100  Else
110 On Error GoTo ERROROUT
120  End If

130  If bCurrentConnection = False Then
140 OpenDB strDB
150  End If

160  strSQL = "pragma table_info('" & strTable & "')"
170  arr = GetFromDB(strSQL, lRows, strError, strDB)

180  If lRows = -1 Then
190 GoTo ERROROUT
200  End If

210  If bOmit Then
220 For c = 0 To UBound(arr)
230If ValueIn1DArray(CStr(c + 1), arrOmitFields) = -1 Then
240   If bDoneFirst = False Then
250  strSelect = strAlias2 & arr(c, 1)
260  strFields = arr(c, 1)
270  strDataTypes = arr(c, 2)
280  bDoneFirst = True
290   Else
300  strSelect = strSelect & ", " & strAlias2 & arr(c, 1)
310  strFields = strFields & "," & arr(c, 1)
320  strDataTypes = strDataTypes & "," & arr(c, 2)
330   End If
340End If
350 Next
360  Else
370 For c = 0 To UBound(arr)
380If c = 0 Then
390   strFields = arr(c, 1)
400   strSelect = strAlias2 & arr(c, 1)
410   strDataTypes = arr(c, 2)
420Else
430   strFields = strFields & "," & arr(c, 1)
440   strSelect = strSelect & ", " & strAlias2 & arr(c, 1)
450   strDataTypes = strDataTypes & "," & arr(c, 2)
460End If
470 Next
480  End If

490  arr2 = Split(strFields, ",")

500  ReDim arr3(0 To UBound(arr2)) As String

510  For r = 0 To UBound(arr2)
520 arr3(r) = arr2(r)
530  Next

540  GetSQLiteTableInfo2 = arr3

550  Exit Function
ERROROUT:

560  ReDim arr2(0 To 6) As String
570  arr2(0) = "-1"
580  arr2(1) = CStr(Err.Number)
590  arr2(2) = Err.Description
600  arr2(3) = CStr(Erl)
610  arr2(4) = strTable
620  arr2(5) = strOmitFields
630  arr2(6) = strAlias

650  GetSQLiteTableInfo2 = arr2

End Function


Have a nice Christmas as well.


RBS


-Original Message-
From: Cesar D. Rodas [mailto:[EMAIL PROTECTED] 
Sent: 25 December 2007 01:35
To: sqlite-users@sqlite.org
Subject: [sqlite] Helping with table definition?

Hello,

Merry Christmas for every one!

I am wondering if someone did a function (the language doesn't care very
much) to get the table information and want to share him/her code.

I know that you can have the SQL definition of a table doing a "select *
from sqlite_master where type='table' ", but I need to parse SQL and
understand it.

Thanks in advance.

-- 
Best Regards

Cesar D. Rodas
http://www.cesarodas.com
http://www.thyphp.com
http://www.phpajax.org
Phone: +595-961-974165



-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] DeviceSQL

2007-12-12 Thread RB Smissaert
Couldn't find anywhere how much this costs.
Newsgroup search shows nil.
Has anybody downloaded and tried the demo?

RBS

-Original Message-
From: Joe Wilson [mailto:[EMAIL PROTECTED] 
Sent: 12 December 2007 17:10
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] DeviceSQL

Be careful about speculative comments.

For all anyone knows, said product could use SQLite internally with 
a couple of proprietary optimizations here and there that may make it
faster in specific cases. 

The sqlite public domain license would allow that sort of thing.


 


Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs


-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] DB managers that do searches?

2007-10-31 Thread RB Smissaert
> happy user of sqliteman

Thanks for the tip, it is quite nice.
Two things: Help doesn't launch from the interface and
it always seems to give Row(s) returned: 256 even when there are lot more.

Another nice one is SQL2006 Pro from OsenXPSuite.

RBS


-Original Message-
From: Bernie Cosell [mailto:[EMAIL PROTECTED] 
Sent: 31 October 2007 19:20
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] DB managers that do searches?

On 31 Oct 2007 at 11:37, James Dennett wrote:

> Bernie Cosell wrote:

> > I guess you've never used a [good] GUI-driven DB
> manager/administration
> > pgm. 
> 
> Your guess (luckily for me) is very wrong.  It's just that I call these
> GUIs, not "DB managers".

Ah... a terminology problem..  I'll just point out that the section in 
the wiki that has all of these pgms in it is called "Management Tools".

> ..  They're handy.  I have a number of them
> installed on the machine on which I write this, and I use them in
> addition to command line tools.

As I mentioned in another msg on this thread, we use phpMyAdmin for our 
MySQL databases at work and I can't remember the last time anyone at work 
needed (or wanted) to use the command line tool.  Different strokes...

> That's not a problem, is it?  Just a question of using a tool at the
> right level.  If you want to automate things, writing code is often a
> good way.  If you want to do ad hoc work, a visual tool can be much more
> convenient.

Just so.  Only difference between us here is that I have virtually no use 
for the command line tool: if I need to automate, I'll just write a 
little Perl/DBI pgm to do it (I have dozens of 'em..:o)) and I use the 
"visual tool" for everything else.  I'd rather write a small Perl program 
that try to cobble up a script to be read into the command line app.  As 
above, YMMV...

> So you're looking for a graphical tool to allow you to manually view and
> modify information in a SQLite3 database?

Yes, and I'm now the happy user of sqliteman, so my search is over..:o).  
It's "query manager" does *exactly* what I needed and works wonderfully.  
(and indeed, easily found the index conflict I was trying to sort out 
easily.)

  /Bernie\

-- 
Bernie Cosell Fantasy Farm Fibers
mailto:[EMAIL PROTECTED] Pearisburg, VA
-->  Too many people, too few sheep  <--   





-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Wrapper ADO like for VB6

2007-09-26 Thread RB Smissaert
No, haven't tried dhRPCServer as I only work with a local database
and single users.

RBS


-Original Message-
From: Giuliano [mailto:[EMAIL PROTECTED] 
Sent: 26 September 2007 13:58
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Wrapper ADO like for VB6

Thanks,

I have seen and it seams to be really interesting. I will try it for sure.
Did you also try the dhRPCServer that should act like a server/client?


- Original Message - 
From: "RB Smissaert" <[EMAIL PROTECTED]>
To: <sqlite-users@sqlite.org>
Sent: Wednesday, September 26, 2007 2:47 PM
Subject: RE: [sqlite] Wrapper ADO like for VB6


>I am using dhSQLite, which you can download from here:
> http://www.thecommon.net/2.html
>
> I have tried several wrappers for VB(A) (about 4 or 5) and this
> is the best one. Good support as well.
>
> RBS
>
>
> -Original Message-
> From: Giuliano [mailto:[EMAIL PROTECTED]
> Sent: 26 September 2007 13:35
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Wrapper ADO like for VB6
>
> Hello,
>
> I am porting a big program from SQLServ.. to SQLite and I have
> seen that there are many VB6 wrappers available. I would like to
> get some suggestion from people who already use one, to be sure
> to make the right decision (important: ADO like, stability, upgrades
> etc...)
>
> Many thanks in advance,
> Giuliano
>
>
>
>

-
> To unsubscribe, send email to [EMAIL PROTECTED]
>

-
>
> 



-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Wrapper ADO like for VB6

2007-09-26 Thread RB Smissaert
I am using dhSQLite, which you can download from here:
http://www.thecommon.net/2.html

I have tried several wrappers for VB(A) (about 4 or 5) and this
is the best one. Good support as well.

RBS


-Original Message-
From: Giuliano [mailto:[EMAIL PROTECTED] 
Sent: 26 September 2007 13:35
To: sqlite-users@sqlite.org
Subject: [sqlite] Wrapper ADO like for VB6

Hello,

I am porting a big program from SQLServ.. to SQLite and I have
seen that there are many VB6 wrappers available. I would like to
get some suggestion from people who already use one, to be sure
to make the right decision (important: ADO like, stability, upgrades
etc...)

Many thanks in advance,
Giuliano



-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] [Visual Basic] How do you work with SQLite?

2007-08-10 Thread RB Smissaert
Several reasons. Main one is that we won't be allowed as this is a third
party application clinical database. The other one is that it would cause
too much slow-down of the regular clinical front-end application.
This is reporting software and apart from some rare exceptions there is no
writing back to Interbase.

RBS

-Original Message-
From: mos [mailto:[EMAIL PROTECTED] 
Sent: 10 August 2007 02:58
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] [Visual Basic] How do you work with SQLite?

At 05:25 PM 8/9/2007, you wrote:
>I use it mainly to manipulate data obtained from an Interbase database.
>All the data will eventually be dumped to Excel.
>I use 2 ways to move data from Interbase to SQLite.
>One, via an ADO recordset after connecting to Interbase with ODBC. This
>recordset will then be dumped to SQLite via the free VB wrapper from Olaf
>Schmidt, dhSQLite. Very good and fast wrapper with a rich object model.
>Two, via a custom Delphi dll that moves data directly from Interbase to
>SQLite. This is maybe slightly faster than the first method, but not that
>much difference. So, no ODBC with this method and no ADO recordset.
>
>Moving data from SQLite to Excel is all done with dhSQLite. This wrapper
can
>be downloaed from: http://www.thecommon.net/2.html
>Very much recommended for anybody who works with VB or VBA.
>
>RBS

RBS,
 So why can't you manipulate the data in Interbase? It's reasonably 
fast.

Mike 


-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] [Visual Basic] How do you work with SQLite?

2007-08-09 Thread RB Smissaert
I use it mainly to manipulate data obtained from an Interbase database.
All the data will eventually be dumped to Excel.
I use 2 ways to move data from Interbase to SQLite.
One, via an ADO recordset after connecting to Interbase with ODBC. This
recordset will then be dumped to SQLite via the free VB wrapper from Olaf
Schmidt, dhSQLite. Very good and fast wrapper with a rich object model.
Two, via a custom Delphi dll that moves data directly from Interbase to
SQLite. This is maybe slightly faster than the first method, but not that
much difference. So, no ODBC with this method and no ADO recordset.

Moving data from SQLite to Excel is all done with dhSQLite. This wrapper can
be downloaed from: http://www.thecommon.net/2.html
Very much recommended for anybody who works with VB or VBA.

RBS


-Original Message-
From: Gilles Ganault [mailto:[EMAIL PROTECTED] 
Sent: 09 August 2007 22:46
To: sqlite-users@sqlite.org
Subject: [sqlite] [Visual Basic] How do you work with SQLite?

Hello

I was wondering: how do you VB developpers work with SQLite?

Currently, I use a variant array that I fill with data from SQLite, and use 
ComponentOne's grid object to display the data through its LoadArray() 
method; If/once the data is updated by the user, I write the array back to 
SQLite.

Do you use ADO et al? Other ways to connect VB and SQLite?

Thank you.



-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Multiple fields update

2007-08-06 Thread RB Smissaert
Yes, thanks, I just found out.
It can work without the WHERE clauses.

RBS


-Original Message-
From: Gerry Snyder [mailto:[EMAIL PROTECTED] 
Sent: 06 August 2007 21:59
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Multiple fields update

RB Smissaert wrote:
> I am sure this SQL used to be fine with SQLite:
>
> update table1
> set
> field1 = 0 where field1 = 2,
> field2 = 3 where field2 = 2
>
> Now however I get a syntax error near ,
>
> Has this changed?
>
>
> RBS
>   
Does the thread below ring a bell?  Does it help?

Gerry


---

Yes, thanks, that works indeed.

RBS

-Original Message-
From: Igor Tandetnik [mailto:[EMAIL PROTECTED] 
Sent: 11 March 2007 02:55
To: SQLite
Subject: [sqlite] Re: What is wrong with this UPDATE?

RB Smissaert <[EMAIL PROTECTED]>
wrote:

> > UPDATE
> > A3SQLADC_J
> > SET
> > ADDED_DATE = '' WHERE ADDED_DATE = 0 OR ADDED_DATE IS NULL,
> > START_DATE = '' WHERE START_DATE = 0 OR START_DATE IS NULL
> >
> > near ",": syntax error
>   

Make it

UPDATE
A3SQLADC_J
SET
ADDED_DATE = (case when ADDED_DATE = 0 OR ADDED_DATE IS NULL then '' 
else ADDED_DATE end),
START_DATE = (case when START_DATE = 0 OR START_DATE IS NULL then '' 
else START_DATE end);

Igor Tandetnik 





-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-



[sqlite] Multiple fields update

2007-08-06 Thread RB Smissaert
I am sure this SQL used to be fine with SQLite:

update table1
set
field1 = 0 where field1 = 2,
field2 = 3 where field2 = 2

Now however I get a syntax error near ,

Has this changed?


RBS




-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Re: Re: Re: Re: Re: Re: Re: How does SQLite choose the index?

2007-08-05 Thread RB Smissaert
Poor comparison in this case.
Are you going to make a mathematical model when you got a little stream to
cross and you have a few available planks to do it?

RBS


-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED] 
Sent: 05 August 2007 16:43
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Re: Re: Re: Re: Re: Re: Re: How does SQLite choose the
index?

We learn mathematics etc so that we can make numerical models which give 
us design information.  Imagine trying to build every combination of a 
bridge to settle on a design!

Make a mathematical model and get it close to optimal at the first attempt.

RB Smissaert wrote:
> Yes, I suppose you are right there.
> I will see if I can put together a report that runs all possible types of
> queries (sequentially) and then see if I have left anything out that would
> cause problems.
> 
> RBS
> 
> 
> -Original Message-
> From: Gerry Snyder [mailto:[EMAIL PROTECTED] 
> Sent: 05 August 2007 03:35
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Re: Re: Re: Re: Re: Re: Re: How does SQLite choose
the
> index?
> 
> RB Smissaert wrote:
> 
>> I think an application that
>>would produce all the needed indexes based on the table and all the
> 
> possible
> 
>>queries would be helpful. Anybody done such an app?
> 
> _All_ possible queries? Not practical for any significant number of 
> columns. N factorial gets big fast.
> 
> The indexes would be much larger than the data base itself.
> 
> I'm afraid you are going to have to settle for doing an intelligent 
> design of the data base.
> 
> 
> Gerry
> 
> 
>

> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>

> -
> 
> 
> 
> 
>

-
> To unsubscribe, send email to [EMAIL PROTECTED]
>

-
> 



-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Re: Re: Re: Re: Re: Re: Re: How does SQLite choose the index?

2007-08-05 Thread RB Smissaert
Yes, I suppose you are right there.
I will see if I can put together a report that runs all possible types of
queries (sequentially) and then see if I have left anything out that would
cause problems.

RBS


-Original Message-
From: Gerry Snyder [mailto:[EMAIL PROTECTED] 
Sent: 05 August 2007 03:35
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Re: Re: Re: Re: Re: Re: Re: How does SQLite choose the
index?

RB Smissaert wrote:
>  I think an application that
> would produce all the needed indexes based on the table and all the
possible
> queries would be helpful. Anybody done such an app?
_All_ possible queries? Not practical for any significant number of 
columns. N factorial gets big fast.

The indexes would be much larger than the data base itself.

I'm afraid you are going to have to settle for doing an intelligent 
design of the data base.


Gerry



-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-



[sqlite] Problem with glob '137*' ?

2007-08-05 Thread RB Smissaert
Queries where there is a glob comparison on a string that could be
interpreted as a number always seem a bit slower than when comparing to a
string that can't be compared to a number.

So for example:
select f from t where f glob '137*'
is slower than:
select f from t where f glob 'abc*'

Is this there any explanation for this and is there any way round it?
Or can SQLite not be to blame here and is it something in my wrapper?

RBS



-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Re: Re: Re: Re: Re: Re: Re: How does SQLite choose the index?

2007-08-04 Thread RB Smissaert
Ok, I guessed something like that were the case, but what I didn't get
was the purpose of the logN, without knowing the base for that. So why not
simply something like: if (N / m) > 2 then most likely index will be
helpful.

It sure is tricky to add these indexes as getting it wrong will seriously
affect performance. Trouble in this particular case is that I am dealing
with lots of different queries set by the user. I think an application that
would produce all the needed indexes based on the table and all the possible
queries would be helpful. Anybody done such an app?
I think though that I am getting close now to having it all covered and
thanks again for all the assistance.

RBS


-Original Message-
From: Igor Tandetnik [mailto:[EMAIL PROTECTED] 
Sent: 04 August 2007 23:45
To: SQLite
Subject: [sqlite] Re: Re: Re: Re: Re: Re: Re: How does SQLite choose the
index?

RB Smissaert <[EMAIL PROTECTED]>
wrote:
> But then if the base of the logarithm doesn't matter then
> how is this equation going to help you?
>
> m==N/logN
>
> So, basically it comes down to some experimenting?

Well, it tells you that if m is much smaller than N (say, by two orders 
of magnitude or more), it's a pretty safe bet that index will be useful. 
If m is close to N (say, within an order of magnitude), it is a pretty 
safe bet the index will be unhelpful. In the middle lies an area where 
it's more or less a wash.

Most real world problems tend to fall into the two well-defined areas. 
If you find your particular problem to fall into the gray area, then 
yes, you might want to experiment. But in this case, even if you find 
that an index helps, it is unlikely to help by much, so any advantage 
may be outweighed by additional space requirements and slowdown on 
inserts and updates.

Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Re: Re: Re: Re: Re: Re: How does SQLite choose the index?

2007-08-04 Thread RB Smissaert
But then if the base of the logarithm doesn't matter then
how is this equation going to help you?

m==N/logN

So, basically it comes down to some experimenting?

RBS



-Original Message-
From: Igor Tandetnik [mailto:[EMAIL PROTECTED] 
Sent: 04 August 2007 21:32
To: SQLite
Subject: [sqlite] Re: Re: Re: Re: Re: Re: How does SQLite choose the index?

RB Smissaert <[EMAIL PROTECTED]>
wrote:
> OK, will have a look at the wiki.
>
>> There's no "m" on the right hand side.
>> m equals N divided by logarithm of N.
>
> What is the base of that logarithm then?

Doesn't matter. All calulations shown are order of magnitude, only 
accurate modulo multiplication by some unknown constant. Choosing 
different base for the logarithm simply changes this constant.

Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Re: Re: Re: Re: Re: How does SQLite choose the index?

2007-08-04 Thread RB Smissaert
OK, will have a look at the wiki.

> There's no "m" on the right hand side.
> m equals N divided by logarithm of N.

What is the base of that logarithm then?

RBS


-Original Message-
From: Igor Tandetnik [mailto:[EMAIL PROTECTED] 
Sent: 04 August 2007 21:03
To: SQLite
Subject: [sqlite] Re: Re: Re: Re: Re: How does SQLite choose the index?

RB Smissaert <[EMAIL PROTECTED]>
wrote:
> Thanks; I have seen this O(N) etc. explanations a lot, but not sure
> what they exactly mean.

http://en.wikipedia.org/wiki/Big_O_notation

Roughly, we say that an algorithm has complexity O(N) (where N is the 
size of its input) when there exists some constant C such that the 
running time of an algorithm on this input is no more than C*N.

>> and for each entry would perform a logN
>
> Does the logN here mean m log N or something else?

Yes, logN is the same as log N or log(N) - a logarithm of N.

>> m==N/logN
>
> Ditto, does this mean break even point roughly when m equals N / (m
> log N) ?

There's no "m" on the right hand side. m equals N divided by logarithm 
of N.

Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Re: Re: Re: Re: How does SQLite choose the index?

2007-08-04 Thread RB Smissaert
Thanks; I have seen this O(N) etc. explanations a lot, but not sure
what they exactly mean.
Does it in this case simply mean O * N and O * (m log N) ?

> and for each entry would perform a logN

Does the logN here mean m log N or something else?

> m==N/logN

Ditto, does this mean break even point roughly when m equals N / (m log N) ?

Sorry, these might be basic questions, but would like to get this clear.


RBS

-Original Message-
From: Igor Tandetnik [mailto:[EMAIL PROTECTED] 
Sent: 04 August 2007 20:01
To: SQLite
Subject: [sqlite] Re: Re: Re: Re: How does SQLite choose the index?

RB Smissaert <[EMAIL PROTECTED]>
wrote:
> One thing I am not sure about yet is when an index would be helpful
> in the
> first place in relation to the data in the field.
> I understand an index is going to help little if the values in a
> particular
> field can only for example be 1 or 0, but roughly when does it become
> useful
> to add an index?

Suppose you have a table with N records. You run a query like "select * 
from t where f='x'; " which selects m records. Without an index on t(f), 
the query would run in O(N) time. With the index, it would be O(m log N) 
(it will scan m entries in the index, and for each entry would perform a 
logN lookup in the main table, by rowid).

Thus, when m is close to N (that is, the query selects almost all 
records), an index actually performs worse than a linear scan. The 
break-even point is somewhere on the order m==N/logN.

Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Re: Re: How does SQLite choose the index?

2007-08-04 Thread RB Smissaert
How does the field order in indexes work with joins?
So for example given the query:

select
t1.a,
t1.b,
t2.c
from
table1 t1 inner join table2 t2 on
(t1.id1 = t2.id2)
where
t1.a = 'abc'

would the index need to be
(a, id1)
or
(id1, a)


Does the field order in the tables have anything to do with this
or is it just the field order in the query and field order in the index
that matter?


RBS



-Original Message-
From: Igor Tandetnik [mailto:[EMAIL PROTECTED] 
Sent: 04 August 2007 15:49
To: SQLite
Subject: [sqlite] Re: Re: How does SQLite choose the index?

RB Smissaert <[EMAIL PROTECTED]>
wrote:
> So, basically it is best to make one large index (apart from the
> primary
> integer key?) that includes all fields that could be in a WHERE
> clause or a
> JOIN or a GROUP BY or a HAVING or an ORDER BY?

That depends on the queries you want to speed up. Index columns can only 
be used from left to right, with no skips, to satisfy the conditions of 
the query. For example, if you have an index on columns (a, b, c) and a 
query like

select * from t where a='x' and c='z';

then the index can be used to satisfy a='x' condition, but then a linear 
check of all records having a='x' is performed to satisfy c='z'. If you 
run such a query often and need it to run fast, you may want another 
index on (a, c), or perhaps (a, c, b).

Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Re: Re: How does SQLite choose the index?

2007-08-04 Thread RB Smissaert
> Index columns can only be used from left to right, with no skips,
> to satisfy the conditions of the query.

Ah, yes, I forgot about that one. So, I will need some more indexes.

RBS


-Original Message-
From: Igor Tandetnik [mailto:[EMAIL PROTECTED] 
Sent: 04 August 2007 15:49
To: SQLite
Subject: [sqlite] Re: Re: How does SQLite choose the index?

RB Smissaert <[EMAIL PROTECTED]>
wrote:
> So, basically it is best to make one large index (apart from the
> primary
> integer key?) that includes all fields that could be in a WHERE
> clause or a
> JOIN or a GROUP BY or a HAVING or an ORDER BY?

That depends on the queries you want to speed up. Index columns can only 
be used from left to right, with no skips, to satisfy the conditions of 
the query. For example, if you have an index on columns (a, b, c) and a 
query like

select * from t where a='x' and c='z';

then the index can be used to satisfy a='x' condition, but then a linear 
check of all records having a='x' is performed to satisfy c='z'. If you 
run such a query often and need it to run fast, you may want another 
index on (a, c), or perhaps (a, c, b).

Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Re: How does SQLite choose the index?

2007-08-04 Thread RB Smissaert
Thanks, that was very useful.
I didn't realize that table values could be obtained from the index.
I suppose it makes sense when you think about it.

So, basically it is best to make one large index (apart from the primary
integer key?) that includes all fields that could be in a WHERE clause or a
JOIN or a GROUP BY or a HAVING or an ORDER BY?

RBS


-Original Message-
From: Igor Tandetnik [mailto:[EMAIL PROTECTED] 
Sent: 04 August 2007 15:18
To: SQLite
Subject: [sqlite] Re: How does SQLite choose the index?

RB Smissaert <[EMAIL PROTECTED]>
wrote:
> I get this query plan (explain query plan):
>
> order from detail
> 
> 0   0  TABLE ENTRY AS E WITH INDEX
> IDX16$ENTRY$READ_CODE$ADDED_DATE$START_DATE$PROBLEM_ID
>
> My question is why doesn't it pick the index:
> IDX3$ENTRY$READ_CODE
>
> Not sure, but I would think that is more effective.

It's exactly the same in terms of efficiency. In fact, it is completely 
pointless to have two indexes where the column list of one is a strict 
prefix of the column list of another. The latter can be used, equally 
efficiently, everywhere the former can be used. In some cases the latter 
may even be more efficient. Consider:

create table t (a text, b text);
create index ta on t(a);
create index tab on t(a, b);

select a, b from t where a='xyz';

If SQLite chooses to use index ta, then it needs to perform a lookup in 
the table (by rowid) to retrieve the value of b. But if it uses index 
tab, then the value of b is stored in the index, and the table itself 
doesn't need to be consulted at all. So fewer pages to read from disk.

Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-



[sqlite] How does SQLite choose the index?

2007-08-04 Thread RB Smissaert
Given this table:

CREATE TABLE AMorb37F6_E
([PATIENT_ID] INTEGER,
[ENTRY_ID] INTEGER PRIMARY KEY,
[READ_CODE] TEXT,
[ADDED_DATE] TEXT,
[START_DATE] TEXT)

And these indexes:

IDX10$ENTRY$PATIENT_ID
IDX11$ENTRY$TERM_TEXT
IDX12$ENTRY$READ_CODE$ADDED_DATE
IDX13$ENTRY$READ_CODE$START_DATE
IDX14$ENTRY$READ_CODE$PROBLEM_ID
IDX15$ENTRY$READ_CODE$ADDED_DATE$PROBLEM_ID
IDX16$ENTRY$READ_CODE$ADDED_DATE$START_DATE$PROBLEM_ID
IDX2$ENTRY$ADDED_BY
IDX3$ENTRY$READ_CODE
IDX4$ENTRY$ENCOUNTER_ID
IDX5$ENTRY$ADDED_DATE
IDX6$ENTRY$UPDATED_DATE
IDX7$ENTRY$START_DATE
IDX8$ENTRY$PROBLEM_ID
IDX9$ENTRY$ENTRY_FLAGS

And this query:

SELECT
E.PATIENT_ID,
E.ENTRY_ID,
E.READ_CODE,
E.ADDED_DATE,
E.START_DATE
FROM
ENTRY E
WHERE
E.READ_CODE GLOB 'G2*' AND
(NOT E.DORMANT_FLAG = 1)
ORDER BY
E.PATIENT_ID ASC, E.ENTRY_ID ASC

I get this query plan (explain query plan):

order   fromdetail

0   0   TABLE ENTRY AS E WITH INDEX
IDX16$ENTRY$READ_CODE$ADDED_DATE$START_DATE$PROBLEM_ID

My question is why doesn't it pick the index:
IDX3$ENTRY$READ_CODE

Not sure, but I would think that is more effective.

What are the general rules as to how SQLites picks from the available
indexes?


RBS



-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] strategy adding indexes

2007-07-31 Thread RB Smissaert


Re: [sqlite] strategy adding indexes

drh
Tue, 31 Jul 2007 03:12:54 -0700

T <[EMAIL PROTECTED]> wrote:
> Hi RBS,
> 
> > - indexes that include all possible combinations of fields that may  
> > appear
> > in a WHERE clause.
> 
> As an aside, note that, AFAIK, indexes are only used:
> 
> 1. To get the first match of a query. If you ask for more than one  
> matching record, the second, third etc matches are found by searching,  
> not through the index.

No.

If an index is used to retrieve the first row in the result, then it
continues to be used for every row.

Depending on the query, it is often possible to not have to do
a full binary search of the index on the second and subsequent
rows.  Instead, the next matching index entry might be adjacent
to the previous one so finding the next value might be as
simple as moving one index entry to the left or to the right.
But whether or not you can do this depends on the query.  And
the index is still used for every row, regardless.

> 
> 2. From left to right in the same order as your index. So if you  
> create index MyIndex on MyTable ( Column1, Column2, Column3 ), then  
> you must test them in the same order, eg: where Column1 = Value1 and  
> Column2 = Value2 or Column3 = Value3. If you miss a column in the  
> sequence or place one out of order, the index won't be used from that  
> point in the test onwards.

The order of terms in a WHERE clause make no difference.  All
of the following work the same:

   WHERE column1=1 AND column2=2 AND column3=3
   WHERE 2=column2 AND column3=3 AND column1=1
   WHERE column2=2 AND 1=column1 AND column3=3
   WHERE 3=column3 AND 2=column2 and 1=column1
   WHERE column3=3 AND column1=1 AND 2=column2
   WHERE 1=column2 AND column3=3 AND column2=2

Notice also that A=B is the same as B=A.

But it is the case that you must cover some prefix of terms
from the index.  So if the index is on column1,column2,column3
in that order, and if you say:

   WHERE column1=1 AND column2!=7 AND column3=3

Then only the first term (column1) will be used by the index
because you cannot use an index on a != operator.  If you
say

   WHERE column1!=7 AND column2=2 AND column3=3

Then the index cannot be used at all.  If you say:

   WHERE column1=1 AND column2>3 AND column3=3

Then the first two terms of the index will be used.  Each
index can use at most one inequality operator and it must
be on the right-most column of the index that gets used.

> 
> 3. In equality tests, eg "=" (equals) and "in". If you use "like" for  
> comparison, the index isn't used. The last test (only) may be one or  
> two inequality tests, such as ">" or "<". And that last test must be  
> in sequence (ie rule 2).
> 

http://www.sqite.org/optoverview.html

--
D. Richard Hipp <[EMAIL PROTECTED]>



Thanks, that is very useful.
I wonder now if there would any mileage in writing an application (or does
it exist already?) that takes a table (or tables) and all the possible
queries and from those 2 produces a sensible plan to add the indexes.
I suppose to do that you need a good understanding of the SQLite optimizer
and if you have that then maybe you don't need such an application.

Just one thing I noticed:
When I run a query like for example this:
Select from tableX where read_code GLOB 'bd*' and entry_type = 8
It takes about 2 minutes
Now when after that I run this query:
Select from tableX where read_code GLOB 'bx*' and entry_type = 8
It will run in 10 seconds. Why is  this if the second query is not the same
as the first?

RBS





-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] strategy adding indexes

2007-07-30 Thread RB Smissaert
Hi Tom,

Thanks for that; useful to know.
Didn't know about point 1 and 2 and that will complicate matters a bit
further.

RBS

-Original Message-
From: T [mailto:[EMAIL PROTECTED] 
Sent: 31 July 2007 00:39
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] strategy adding indexes

Hi RBS,

> - indexes that include all possible combinations of fields that may  
> appear
> in a WHERE clause.

As an aside, note that, AFAIK, indexes are only used:

1. To get the first match of a query. If you ask for more than one  
matching record, the second, third etc matches are found by searching,  
not through the index.

2. From left to right in the same order as your index. So if you  
create index MyIndex on MyTable ( Column1, Column2, Column3 ), then  
you must test them in the same order, eg: where Column1 = Value1 and  
Column2 = Value2 or Column3 = Value3. If you miss a column in the  
sequence or place one out of order, the index won't be used from that  
point in the test onwards.

3. In equality tests, eg "=" (equals) and "in". If you use "like" for  
comparison, the index isn't used. The last test (only) may be one or  
two inequality tests, such as ">" or "<". And that last test must be  
in sequence (ie rule 2).

I hope this helps a bit. Some more learned SQLiters out there may care  
to correct or clarify.

Tom



-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-



[sqlite] strategy adding indexes

2007-07-30 Thread RB Smissaert
What would be a good strategy in adding indexes to the various tables?
I know SQLite can only use one index in simple (not intersect etc.) queries,
so is it usually best to make:
- indexes that include all possible combinations of fields that may appear
in a WHERE clause.
- make one very large index combining all fields that may appear in a WHERE
clause.

Or would it be better to make single field indexes and go with intersect
etc?

I understand that in the end it will come down to a lot of experimenting,
but maybe there are some useful guidelines/rules that speed up this large
task.

RBS

 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Interrupt SQLite

2007-07-18 Thread RB Smissaert
Thanks, that is a very useful tip!

RBS


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: 18 July 2007 00:56
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Interrupt SQLite

"RB Smissaert" <[EMAIL PROTECTED]> wrote:
> Is it somehow possible to interrupt an ongoing INSERT operation? I made a
> mistake in an index and now got into a very long process that I would like
> to stop. I am running this from VBA via the dll from Olaf Schmidt,
> dhSQLite.dll. I don't want to kill Excel as I would lose some work.
> Thanks for any advice.
> 

Try making the journal file read-only.  That should stop it.
And force a rollback.

--
D. Richard Hipp <[EMAIL PROTECTED]>



-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-



[sqlite] Interrupt SQLite

2007-07-17 Thread RB Smissaert
Is it somehow possible to interrupt an ongoing INSERT operation? I made a
mistake in an index and now got into a very long process that I would like
to stop. I am running this from VBA via the dll from Olaf Schmidt,
dhSQLite.dll. I don't want to kill Excel as I would lose some work.
Thanks for any advice.

RBS



-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Re: inner join

2007-07-16 Thread RB Smissaert
That is an interesting one.
Where could I find documentation about coalesce?

RBS


-Original Message-
From: Igor Tandetnik [mailto:[EMAIL PROTECTED] 
Sent: 16 July 2007 12:49
To: SQLite
Subject: [sqlite] Re: inner join

Andre du Plessis <[EMAIL PROTECTED]> wrote:
> I would like to be able to accomplish the following but don't see any
> support for inner joins on update queries.
> 
> update A
> set Id = B.Id
> from A inner join B
> on A.Field1 = B.Field_Temp
> where B.Price > 0

update A set Id = coalesce(
(select Id from B
 where A.Field1 = B.Field_Temp and B.Price > 0),
Id);

Igor Tandetnik


-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Re: How to store 128 bit values

2007-07-11 Thread RB Smissaert
> So no, you won't gain anything by trying to avoid this column -
> it is always there whether you declare it or not.

But I found that inserts were faster if I didn't create the table with
INTEGER PRIMARY KEY, so it looked I gained there, although I understand
I might lose out somewhere else.

RBS


-Original Message-
From: Igor Tandetnik [mailto:[EMAIL PROTECTED] 
Sent: 11 July 2007 20:18
To: SQLite
Subject: [sqlite] Re: How to store 128 bit values

RB Smissaert <[EMAIL PROTECTED]>
wrote:
> It seems if you do inserts on a table it is faster if you have no
> INTEGER
> PRIMARY KEY on that table

You _always_ have an INTEGER PRIMARY KEY on every table. It's part of 
SQLite storage mechanism.

If you don't explicitly declare one, it's still there under the name 
ROWID or OID (and a few other synonyms). By explicitly declaring it, you 
simply give the same column yet another name.

So no, you won't gain anything by trying to avoid this column - it is 
always there whether you declare it or not.

Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] How to store 128 bit values

2007-07-11 Thread RB Smissaert
> I have not measured it, but I'm guessing that the speed reduction 
> caused by inserting INTEGER PRIMARY KEY out of order is much less
> than the speed penalty of building a separate index.

Thanks.
I did measure that and thought that the speed penalty of creating the new
index was less than the penalty of inserting with INTEGER PRIMARY KEY, but I
will have a look at this again. I suppose it depends a lot on the data.
Now of course I also will need to look at the speed penalty in selecting and
joining when there is no INTEGER PRIMARY KEY.

RBS


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: 11 July 2007 20:17
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] How to store 128 bit values

"RB Smissaert" <[EMAIL PROTECTED]> wrote:
> > Looking up a record by INTEGER PRIMARY KEY is always twice as
> > fast as looking up the same record by any other key
> 
> Didn't realize that, but I have a question in connection with this.
> It seems if you do inserts on a table it is faster if you have no INTEGER
> PRIMARY KEY on that table and then later create an integer key on that
same
> field. But that would mean that selects and joins may be slower.
> Am I seeing this right or is there any way round this, so have fast
inserts
> and still have the INTEGER PRIMARY KEY?

The b-trees used for tables are optimized for inserting new
entries at the end of the table, because this is the common
case.  If you have an INTEGER PRIMARY KEY, then inserts will
therefore be fastest if you insert in primary key order.  If
you do not have an INTEGER PRIMARY KEY, then SQLite makes one
up for you automatically, and the made-up primary key (the
rowid) is almost always larger than all previous rowids so
the net effect is that you are always appending and always
going quickly.

I have not measured it, but I'm guessing that the speed
reduction caused by inserting INTEGER PRIMARY KEY out of
order is much less than the speed penalty of building a
separate index.

--
D. Richard Hipp <[EMAIL PROTECTED]>



-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] How to store 128 bit values

2007-07-11 Thread RB Smissaert
> Looking up a record by INTEGER PRIMARY KEY is always twice as
> fast as looking up the same record by any other key

Didn't realize that, but I have a question in connection with this.
It seems if you do inserts on a table it is faster if you have no INTEGER
PRIMARY KEY on that table and then later create an integer key on that same
field. But that would mean that selects and joins may be slower.
Am I seeing this right or is there any way round this, so have fast inserts
and still have the INTEGER PRIMARY KEY?
Thanks for any advice.

RBS


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: 11 July 2007 19:33
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] How to store 128 bit values

"Steve Krulewitz" <[EMAIL PROTECTED]> wrote:
> Hey all --
> 
> In the application I am working on (Songbird), we have a simple two
> table schema representing the tracks in your music collection and the
> properties on those tracks.  The keys used are all UUIDs (128 bit
> number) which we are currently storing in hex string form in a text
> column, so they literally appear in the database as
> "ee89b823-e709-40c5-bed7-dcb0b2b791a8".  We do lots of lookups and
> joins on these keys.
> 
> I was wondering if there is much to be gained by storing these 128 bit
> values in binary rather than as strings.

Storing the UUIDs as BLOBs rather than as hex-encoded strings
will requires 21 bytes per key instead of 43.  So your indices
will have about twice their current fanout.  That means that
you can expect to roughly double your lookup performance on
a cold cache.  (If the database, or large parts of it, is 
already in your OS cache, the difference will be much less
and will likely not be noticable.)

The downside of using BLOBs is that you cannot see them easily
during debugging when you do a "SELECT * FROM...".  You have
to use constructs like, "SELECT hex(uuid), ... FROM" which is
more typing.  You can fix that with a VIEW, I suppose.

The thing to consider is why you are using 128-bit UUIDs in
the first place?  Presumably you are doing this so that you
can sync and/or merge independently created databases without
having to worry about key collisions.  If you are not doing
syncs or merges or independently generated keys, then I can't
think of a good reason to use 128-bit UUIDs in the first
place.  Just use small integer autogenerated keys from SQLite.

Assuming you are doing syncing and merging, what I tend to
do in these kinds of situations is to create a mapping between
the universally unique ID (UUID) and a small integer ID that
is unique in the local database - call the latter the RID.
The RID is just an integer and can be your INTEGER PRIMARY KEY
for very fast lookups.  Looking up a record by INTEGER PRIMARY KEY
is always twice as fast as looking up the same record by any
other key, and because of high fanout can potentially be much
faster if you have a cold cache.  So I use the RID for joins
or other internal operations and only use the UUID for lookups 
from external data.

For example, your schema might look something like this:

   CREATE TABLE album(
 aid INTEGER PRIMARY KEY,  -- Internally unique album ID
 uuid TEXT UNIQUE, -- Universally unique album ID
 ...
   );
   CREATE TABLE track(
 tid INTEGER PRIMARY KEY,  -- Internally unique track ID
 uuid TEXT UNIQUE, -- Universally unique track ID
 aid INTEGER REFERENCES album, -- Album containing this track
 ...
   );
   CREATE INDEX track_album ON track(aid);

A typical query might be to find all tracks of an album:

   SELECT * FROM track 
   WHERE aid=(SELECT aid FROM album WHERE title=?)

And queries like this will run much faster using INTEGER
PRIMARY KEYS rather than UUIDs.

All that said, for even the largest music collection, your
database is unlikely to have more than a few thousand albums
and a few tens of thousands of tracks, and with such a small
database and running on a modern workstations, probably just
about anything you do is going to be fast enough.  The
optimizations described above are useful if you have tables
with millions of entries or if you are doing thousands
of queries per second or if you are running on some woefully 
underpowered portable music player.  But for a personal
music library running on a workstation at human-interaction
speed, use whatever schema makes it easiest for you to type 
in correct and working code.

--
D. Richard Hipp <[EMAIL PROTECTED]>




-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] error in round-function?

2007-06-10 Thread RB Smissaert
Just checked my code and luckily I don't round in SQLite.
I suppose an easy work-around for now would be to do something like:
Select round(field + 0.001, 1) as it will be unlikely
you are dealing with 0.949

RBS

-Original Message-
From: Olaf Schmidt [mailto:[EMAIL PROTECTED] 
Sent: 10 June 2007 21:34
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] error in round-function?


Thanks to RBS and Alberto for testing...

So it seems the problem is somehow Windows-related
(maybe the VC-Compiler).

Think I'll override the Round-Function in my wrapper.

Regards,

Olaf
-- 
View this message in context:
http://www.nabble.com/error-in-round-function--tf3897765.html#a11052069
Sent from the SQLite mailing list archive at Nabble.com.



-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Age calculation on literal

2007-06-03 Thread RB Smissaert
This function works and despite the convoluted construction it is pretty
fast, takes about 0.5 sec to convert one field in about 25000 rows.
I thought I can speed it up by replacing all the SQLite functions that
produce a literal by a VB variable. Indeed with this I can get it down to
about 0.3 secs. Does this mean that SQLite recalculates these functions
multiple times for every row? I suppose it does and in a way it makes sense
as 'now' changes over time.
In case any VB user is interested in this:

Function CurrentDateISO8601() As String

  Dim lCY As Long
  Dim lCM As Long
  Dim lCD As Long
  Dim strZeroMonth As String
  Dim strZeroDay As String

  lCY = Year(Date)
  lCM = Month(Date)
  lCD = Day(Date)

  If lCM < 10 Then
strZeroMonth = "0"
  End If

  If lCD < 10 Then
strZeroDay = "0"
  End If

  CurrentDateISO8601 = "'" & lCY & "-" & _
   strZeroMonth & lCM & "-" & _
   strZeroDay & lCD & "'"

End Function

Function ISO8601Date2AgeInMonths(strField As String, _
 Optional strAlias As String) As String

  Dim strAS As String
  Dim strCDate As String
  Dim lCM As Long
  Dim lCD As Long
  Dim strCY As String
  Dim strCM As String
  Dim strCD As String

  lCM = Month(Date)
  lCD = Day(Date)

  strCY = "'" & CStr(Year(Date)) & "'"

  If lCM < 10 Then
strCM = "'0" & CStr(lCM) & "'"
  Else
strCM = "'" & CStr(lCM) & "'"
  End If

  If lCD < 10 Then
strCD = "'0" & CStr(lCD) & "'"
  Else
strCD = "'" & CStr(lCD) & "'"
  End If

  strCDate = CurrentDateISO8601()

  If Len(strAlias) > 0 Then
strAS = " AS "
  End If

  ISO8601Date2AgeInMonths = _
  "case when " & strCDate & " >= " & _
  "date(" & strField & ", '+' || " & strCY & " - " & _
  "strftime('%Y', " & strField & ") || ' years') then " & _
  "case when " & strCD & " < strftime('%d', " & strField & ") then " & _
  "((" & strCY & " - strftime('%Y', " & strField & ")) * 12 + " & _
  "(" & strCM & " - strftime('%m', " & strField & "))) - 1 " & _
  "else " & _
  "((" & strCY & " - strftime('%Y', " & strField & ")) * 12 + " & _
  "(" & strCM & " - strftime('%m', " & strField & "))) - 0 " & _
  "end " & _
  "else " & _
  "case when " & strCD & " < strftime('%d', " & strField & ") then " & _
  "((" & strCY & " - strftime('%Y', " & strField & ") - 1) * 12 + " & _
  "(" & strCM & " + (12 - strftime('%m', " & strField & " - 1 " & _
  "else " & _
  "((" & strCY & " - strftime('%Y', " & strField & ") - 1) * 12 + " & _
  "(" & strCM & " + (12 - strftime('%m', " & strField & " - 0 " & _
  "End " & _
  "End" & strAS & strAlias

End Function


RBS


-Original Message-
From: RB Smissaert [mailto:[EMAIL PROTECTED] 
Sent: 01 June 2007 23:45
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Age calculation on literal

Got the syntax right, but not the logic.
I believe this (VB) function will now get the right SQL to get the age in
months:

Function ISO8601Date2AgeInMonths(strField As String, _
 Optional strAlias As String) As String

  Dim strAS As String

  If Len(strAlias) > 0 Then
strAS = " AS "
  End If

  ISO8601Date2AgeInMonths = "case when date('now') >= " & _
"date(" & strField & ", '+' || (strftime('%Y',
'now') - " & _
"strftime('%Y', " & strField & ")) || ' years')
then " & _
"case when strftime('%d', 'now') <
strftime('%d', " & strField & ") then " & _
"((strftime('%Y', 'now') - strftime('%Y', " &
strField & ")) * 12 + " & _
"(strftime('%m', 'now') - strftime('%m', " &
strField & "))) - 1 " & _
"else " & _
"((strftime('%Y', 'now') - strftime('%Y', " &
strField

RE: [sqlite] Age calculation on literal

2007-06-01 Thread RB Smissaert
Got the syntax right, but not the logic.
I believe this (VB) function will now get the right SQL to get the age in
months:

Function ISO8601Date2AgeInMonths(strField As String, _
 Optional strAlias As String) As String

  Dim strAS As String

  If Len(strAlias) > 0 Then
strAS = " AS "
  End If

  ISO8601Date2AgeInMonths = "case when date('now') >= " & _
"date(" & strField & ", '+' || (strftime('%Y',
'now') - " & _
"strftime('%Y', " & strField & ")) || ' years')
then " & _
"case when strftime('%d', 'now') <
strftime('%d', " & strField & ") then " & _
"((strftime('%Y', 'now') - strftime('%Y', " &
strField & ")) * 12 + " & _
"(strftime('%m', 'now') - strftime('%m', " &
strField & "))) - 1 " & _
"else " & _
"((strftime('%Y', 'now') - strftime('%Y', " &
strField & ")) * 12 + " & _
"(strftime('%m', 'now') - strftime('%m', " &
strField & "))) - 0 " & _
"end " & _
"else " & _
"case when " & _
"strftime('%d', 'now') < strftime('%d', " &
strField & ") " & _
"then " & _
"(strftime('%Y', 'now') - strftime('%Y', " &
strField & ") - 1) * 12 + " & _
"(strftime('%m', 'now') + (12 - strftime('%m', "
& strField & "))) - 1 " & _
"else " & _
"((strftime('%Y', 'now') - strftime('%Y', " &
strField & ") - 1) * 12 + " & _
"(strftime('%m', 'now') + (12 - strftime('%m', "
& strField & " - 0 " & _
"End " & _
"End" & strAS & strAlias

End Function


RBS


-Original Message-
From: RB Smissaert [mailto:[EMAIL PROTECTED] 
Sent: 01 June 2007 21:46
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Age calculation on literal

Got this now, after correcting the brackets:

SELECT
case when
date('2006-10-14', '+' || (strftime('%Y', 'now') - strftime('%Y', '2006-
10-14')) || ' years') <= date('now') then
case when
strftime('%d', 'now') > strftime('%d', '2006-10-14')
then
((strftime('%Y', 'now') - strftime('%Y', '2006-10-14')) * 12 +
(strftime('%m', 'now') - strftime('%m', '2006-10-14'))) - 1 
else 
(strftime('%Y', 'now') - strftime('%Y', '2006-10-14')) * 12 +
(strftime('%m', 'now') - strftime('%m', '2006-10-14'))
end 
else 
case when
strftime('%d', 'now') > strftime('%d', '2006-10-14')
then
(strftime('%Y', 'now') - strftime('%Y', '2006-10-14') - 1) * 12 +
(strftime('%m', 'now') +
(12 - strftime('%m', '2006-10-14'))) - 1 
else 
(strftime('%Y', 'now') - strftime('%Y', '2006-10-14') - 1) * 12 +
(strftime('%m', 'now') + (12 - strftime('%m', '2006-10-14')))
end
end


RBS


-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED] 
Sent: 01 June 2007 19:53
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Age calculation on literal

On 6/1/07, RB Smissaert <[EMAIL PROTECTED]> wrote:
>
> Got this nearly worked out now, but somehow I can't get the nested case
> when
> syntax right:
>
> SELECT
> case
> when
> date('2006-10-14', '+' || (strftime('%Y', 'now') - strftime('%Y',
> '2006-10-14')) || ' years') <= date('now')
> then
> case when
> strftime('%d', 'now') > strftime('%d', '2006-10-14')
> then
> ((strftime('%Y', 'now') - strftime('%Y', '2006-10-14')) * 12 +
> (strftime('%m', 'now') - strftime('%m', '2006-10-14')) - 1
> else
> (strftime('%Y', 'now') - strftime('%Y', '2006-10-14'))) * 12 +



You have an extra closing bracket in the line above.


(strftime('%m', 'now') - strftime('%m', '2006-10-14'))
> end
> else
> case when
> strftime('%d', 'now') > strftime('%d', '2006-10-14')
> then
> ((strftime('%Y', 'now') - strftime('%Y', '2006-10-14') -1)   * 12 +
> (strftime('%m', 'now') +
> (12 - strftime('%m', '2006-10-14' -1
> else
> (strftime('%Y', 'now') - strftime('%Y', '2006-10-14') -1)  * 12 +
> (strftime('%m', 'now') +
> (12 - strftime('%m', '2006-10-14')))
> end
> end

RE: [sqlite] Age calculation on literal

2007-06-01 Thread RB Smissaert
Got this now, after correcting the brackets:

SELECT
case when
date('2006-10-14', '+' || (strftime('%Y', 'now') - strftime('%Y', '2006-
10-14')) || ' years') <= date('now') then
case when
strftime('%d', 'now') > strftime('%d', '2006-10-14')
then
((strftime('%Y', 'now') - strftime('%Y', '2006-10-14')) * 12 +
(strftime('%m', 'now') - strftime('%m', '2006-10-14'))) - 1 
else 
(strftime('%Y', 'now') - strftime('%Y', '2006-10-14')) * 12 +
(strftime('%m', 'now') - strftime('%m', '2006-10-14'))
end 
else 
case when
strftime('%d', 'now') > strftime('%d', '2006-10-14')
then
(strftime('%Y', 'now') - strftime('%Y', '2006-10-14') - 1) * 12 +
(strftime('%m', 'now') +
(12 - strftime('%m', '2006-10-14'))) - 1 
else 
(strftime('%Y', 'now') - strftime('%Y', '2006-10-14') - 1) * 12 +
(strftime('%m', 'now') + (12 - strftime('%m', '2006-10-14')))
end
end


RBS


-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED] 
Sent: 01 June 2007 19:53
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Age calculation on literal

On 6/1/07, RB Smissaert <[EMAIL PROTECTED]> wrote:
>
> Got this nearly worked out now, but somehow I can't get the nested case
> when
> syntax right:
>
> SELECT
> case
> when
> date('2006-10-14', '+' || (strftime('%Y', 'now') - strftime('%Y',
> '2006-10-14')) || ' years') <= date('now')
> then
> case when
> strftime('%d', 'now') > strftime('%d', '2006-10-14')
> then
> ((strftime('%Y', 'now') - strftime('%Y', '2006-10-14')) * 12 +
> (strftime('%m', 'now') - strftime('%m', '2006-10-14')) - 1
> else
> (strftime('%Y', 'now') - strftime('%Y', '2006-10-14'))) * 12 +



You have an extra closing bracket in the line above.


(strftime('%m', 'now') - strftime('%m', '2006-10-14'))
> end
> else
> case when
> strftime('%d', 'now') > strftime('%d', '2006-10-14')
> then
> ((strftime('%Y', 'now') - strftime('%Y', '2006-10-14') -1)   * 12 +
> (strftime('%m', 'now') +
> (12 - strftime('%m', '2006-10-14' -1
> else
> (strftime('%Y', 'now') - strftime('%Y', '2006-10-14') -1)  * 12 +
> (strftime('%m', 'now') +
> (12 - strftime('%m', '2006-10-14')))
> end
> end
>
> It will give me an error (from my VB wrapper) syntax error near else.
> Any idea what is wrong here?


Try this instead, I find the extra indentation makes it easier to see what
you are doing.

SELECT
case when
date('2006-10-14', '+' || (strftime('%Y', 'now') - strftime('%Y',
'2006-10-14')) || ' years') <= date('now')
then
case when
strftime('%d', 'now') > strftime('%d', '2006-10-14')
then
((strftime('%Y', 'now') - strftime('%Y', '2006-10-14')) * 12 +
(strftime('%m', 'now') - strftime('%m', '2006-10-14')) - 1
else
(strftime('%Y', 'now') - strftime('%Y', '2006-10-14')) * 12 +
(strftime('%m', 'now') - strftime('%m', '2006-10-14'))
end
else
case when
strftime('%d', 'now') > strftime('%d', '2006-10-14')
then
(strftime('%Y', 'now') - strftime('%Y', '2006-10-14') - 1) * 12 +
(strftime('%m', 'now') +
(12 - strftime('%m', '2006-10-14' -1
else
(strftime('%Y', 'now') - strftime('%Y', '2006-10-14') - 1) * 12 +
(strftime('%m', 'now') + (12 - strftime('%m', '2006-10-14')))
end
end

HTH
Dennis Cote



-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Age calculation on literal

2007-06-01 Thread RB Smissaert
Yes, that looks better and thanks for that.
Still get the same error though.
I will keep fiddling with it.

RBS


-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED] 
Sent: 01 June 2007 19:53
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Age calculation on literal

On 6/1/07, RB Smissaert <[EMAIL PROTECTED]> wrote:
>
> Got this nearly worked out now, but somehow I can't get the nested case
> when
> syntax right:
>
> SELECT
> case
> when
> date('2006-10-14', '+' || (strftime('%Y', 'now') - strftime('%Y',
> '2006-10-14')) || ' years') <= date('now')
> then
> case when
> strftime('%d', 'now') > strftime('%d', '2006-10-14')
> then
> ((strftime('%Y', 'now') - strftime('%Y', '2006-10-14')) * 12 +
> (strftime('%m', 'now') - strftime('%m', '2006-10-14')) - 1
> else
> (strftime('%Y', 'now') - strftime('%Y', '2006-10-14'))) * 12 +



You have an extra closing bracket in the line above.


(strftime('%m', 'now') - strftime('%m', '2006-10-14'))
> end
> else
> case when
> strftime('%d', 'now') > strftime('%d', '2006-10-14')
> then
> ((strftime('%Y', 'now') - strftime('%Y', '2006-10-14') -1)   * 12 +
> (strftime('%m', 'now') +
> (12 - strftime('%m', '2006-10-14' -1
> else
> (strftime('%Y', 'now') - strftime('%Y', '2006-10-14') -1)  * 12 +
> (strftime('%m', 'now') +
> (12 - strftime('%m', '2006-10-14')))
> end
> end
>
> It will give me an error (from my VB wrapper) syntax error near else.
> Any idea what is wrong here?


Try this instead, I find the extra indentation makes it easier to see what
you are doing.

SELECT
case when
date('2006-10-14', '+' || (strftime('%Y', 'now') - strftime('%Y',
'2006-10-14')) || ' years') <= date('now')
then
case when
strftime('%d', 'now') > strftime('%d', '2006-10-14')
then
((strftime('%Y', 'now') - strftime('%Y', '2006-10-14')) * 12 +
(strftime('%m', 'now') - strftime('%m', '2006-10-14')) - 1
else
(strftime('%Y', 'now') - strftime('%Y', '2006-10-14')) * 12 +
(strftime('%m', 'now') - strftime('%m', '2006-10-14'))
end
else
case when
strftime('%d', 'now') > strftime('%d', '2006-10-14')
then
(strftime('%Y', 'now') - strftime('%Y', '2006-10-14') - 1) * 12 +
(strftime('%m', 'now') +
(12 - strftime('%m', '2006-10-14' -1
else
(strftime('%Y', 'now') - strftime('%Y', '2006-10-14') - 1) * 12 +
(strftime('%m', 'now') + (12 - strftime('%m', '2006-10-14')))
end
end

HTH
Dennis Cote



-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Age calculation on literal

2007-06-01 Thread RB Smissaert
Got this nearly worked out now, but somehow I can't get the nested case when
syntax right:

SELECT 
case 
when 
date('2006-10-14', '+' || (strftime('%Y', 'now') - strftime('%Y',
'2006-10-14')) || ' years') <= date('now') 
then 
case when 
strftime('%d', 'now') > strftime('%d', '2006-10-14') 
then 
((strftime('%Y', 'now') - strftime('%Y', '2006-10-14')) * 12 +
(strftime('%m', 'now') - strftime('%m', '2006-10-14')) - 1 
else 
(strftime('%Y', 'now') - strftime('%Y', '2006-10-14'))) * 12 +
(strftime('%m', 'now') - strftime('%m', '2006-10-14'))
end 
else 
case when 
strftime('%d', 'now') > strftime('%d', '2006-10-14') 
then 
((strftime('%Y', 'now') - strftime('%Y', '2006-10-14') -1)   * 12 +
(strftime('%m', 'now') + 
(12 - strftime('%m', '2006-10-14' -1 
else 
(strftime('%Y', 'now') - strftime('%Y', '2006-10-14') -1)  * 12 +
(strftime('%m', 'now') + 
(12 - strftime('%m', '2006-10-14')))
end 
end

It will give me an error (from my VB wrapper) syntax error near else.
Any idea what is wrong here?


RBS

-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED] 
Sent: 31 May 2007 22:17
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Age calculation on literal

RB Smissaert wrote:
> Thanks to Dennis Cote I got a nice way to get the age from the date in the
> form '-nmm-dd'. It works fine when I run it on a field, but when I run
> it on a literal date it gives me 100 too much:
>
> select
> case when 
> date('2002-01-01', '+' || (strftime('%Y', 'now') - 
> strftime('%Y', '2002-01-01')) || ' years') <= date('now') 
> then 
> strftime('%Y', 'now') - strftime('%Y', '2002-01-01') 
> else
> strftime('%Y', 'now') - strftime('%Y', '2002-01-01') - 1 
> end
>
> Why is this?
>
>   
This works for me in the sqlite shell as shown below:

C:\Documents and Settings\DennisC>sqlite3
SQLite version 3.3.15
Enter ".help" for instructions
sqlite>
sqlite> select
   ...> case when
   ...> date('2002-01-01', '+' || (strftime('%Y', 'now') -
   ...> strftime('%Y', '2002-01-01')) || ' years') <= date('now')
   ...> then
   ...> strftime('%Y', 'now') - strftime('%Y', '2002-01-01')
   ...> else
   ...> strftime('%Y', 'now') - strftime('%Y', '2002-01-01') - 1
   ...> end
   ...> ;
5

How are you running this query?

Dennis Cote


-
To unsubscribe, send email to [EMAIL PROTECTED]

-



-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Age calculation on literal

2007-05-31 Thread RB Smissaert
> How are you running this query?

I am running this from Excel VBA with a free wrapper from Olaf Schmidt,
dhSQLite, based on 3.3.17. I will check my code, but can't think of a way
why it should add 100 with literals and not on table fields.
Must admit I have been wrong before with these kind of things ...

RBS



-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED] 
Sent: 31 May 2007 22:17
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Age calculation on literal

RB Smissaert wrote:
> Thanks to Dennis Cote I got a nice way to get the age from the date in the
> form '-nmm-dd'. It works fine when I run it on a field, but when I run
> it on a literal date it gives me 100 too much:
>
> select
> case when 
> date('2002-01-01', '+' || (strftime('%Y', 'now') - 
> strftime('%Y', '2002-01-01')) || ' years') <= date('now') 
> then 
> strftime('%Y', 'now') - strftime('%Y', '2002-01-01') 
> else
> strftime('%Y', 'now') - strftime('%Y', '2002-01-01') - 1 
> end
>
> Why is this?
>
>   
This works for me in the sqlite shell as shown below:

C:\Documents and Settings\DennisC>sqlite3
SQLite version 3.3.15
Enter ".help" for instructions
sqlite>
sqlite> select
   ...> case when
   ...> date('2002-01-01', '+' || (strftime('%Y', 'now') -
   ...> strftime('%Y', '2002-01-01')) || ' years') <= date('now')
   ...> then
   ...> strftime('%Y', 'now') - strftime('%Y', '2002-01-01')
   ...> else
   ...> strftime('%Y', 'now') - strftime('%Y', '2002-01-01') - 1
   ...> end
   ...> ;
5

How are you running this query?

Dennis Cote


-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Age calculation on literal

2007-05-31 Thread RB Smissaert
Sorry, forget about this, it was something in the VBA code, so nil to do
with SQLite.

Could I ask you how I would get the age in months? I can see it will be
along similar lines, but maybe you have worked it out already.
I need it to be full calendar months, so, if current date is 2007-05-31 then
DOB  Age in months
--
2007-05-01   0
2007-04-30   1
2007-01-01   4
Etc.

RBS


-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED] 
Sent: 31 May 2007 22:17
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Age calculation on literal

RB Smissaert wrote:
> Thanks to Dennis Cote I got a nice way to get the age from the date in the
> form '-nmm-dd'. It works fine when I run it on a field, but when I run
> it on a literal date it gives me 100 too much:
>
> select
> case when 
> date('2002-01-01', '+' || (strftime('%Y', 'now') - 
> strftime('%Y', '2002-01-01')) || ' years') <= date('now') 
> then 
> strftime('%Y', 'now') - strftime('%Y', '2002-01-01') 
> else
> strftime('%Y', 'now') - strftime('%Y', '2002-01-01') - 1 
> end
>
> Why is this?
>
>   
This works for me in the sqlite shell as shown below:

C:\Documents and Settings\DennisC>sqlite3
SQLite version 3.3.15
Enter ".help" for instructions
sqlite>
sqlite> select
   ...> case when
   ...> date('2002-01-01', '+' || (strftime('%Y', 'now') -
   ...> strftime('%Y', '2002-01-01')) || ' years') <= date('now')
   ...> then
   ...> strftime('%Y', 'now') - strftime('%Y', '2002-01-01')
   ...> else
   ...> strftime('%Y', 'now') - strftime('%Y', '2002-01-01') - 1
   ...> end
   ...> ;
5

How are you running this query?

Dennis Cote


-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-



[sqlite] Age calculation on literal

2007-05-31 Thread RB Smissaert
Thanks to Dennis Cote I got a nice way to get the age from the date in the
form '-nmm-dd'. It works fine when I run it on a field, but when I run
it on a literal date it gives me 100 too much:

select
case when 
date('2002-01-01', '+' || (strftime('%Y', 'now') - 
strftime('%Y', '2002-01-01')) || ' years') <= date('now') 
then 
strftime('%Y', 'now') - strftime('%Y', '2002-01-01') 
else
strftime('%Y', 'now') - strftime('%Y', '2002-01-01') - 1 
end

Why is this?

RBS



-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Longest "real" SQL statement

2007-05-10 Thread RB Smissaert
This is one of my biggest and it is part of a number of queries to
transpose a table:

INSERT OR REPLACE INTO A3BP619_J(PATIENT_ID, ENTRY_ID_E1, START_DATE_E1,
ADDED_DATE_E1, SYST_E1, DIAST_E1, ENTRY_ID_E2, START_DATE_E2, ADDED_DATE_E2,
SYST_E2, DIAST_E2, ENTRY_ID_E3, START_DATE_E3, ADDED_DATE_E3, SYST_E3,
DIAST_E3, ENTRY_ID_E4, START_DATE_E4, ADDED_DATE_E4, SYST_E4, DIAST_E4,
ENTRY_ID_E5, START_DATE_E5, ADDED_DATE_E5, SYST_E5, DIAST_E5, ENTRY_ID_E6,
START_DATE_E6, ADDED_DATE_E6, SYST_E6, DIAST_E6, ENTRY_ID_E7, START_DATE_E7,
ADDED_DATE_E7, SYST_E7, DIAST_E7, ENTRY_ID_E8, START_DATE_E8, ADDED_DATE_E8,
SYST_E8, DIAST_E8, ENTRY_ID_E9, START_DATE_E9, ADDED_DATE_E9, SYST_E9,
DIAST_E9, ENTRY_ID_E10, START_DATE_E10, ADDED_DATE_E10, SYST_E10, DIAST_E10,
ENTRY_ID_E11, START_DATE_E11, ADDED_DATE_E11, SYST_E11, DIAST_E11,
ENTRY_ID_E12, START_DATE_E12, ADDED_DATE_E12, SYST_E12, DIAST_E12,
ENTRY_ID_E13, START_DATE_E13, ADDED_DATE_E13, SYST_E13, DIAST_E13,
ENTRY_ID_E14, START_DATE_E14, ADDED_DATE_E14, SYST_E14, DIAST_E14,
ENTRY_ID_E15, START_DATE_E15, ADDED_DATE_E15, SYST_E15, DIAST_E15,
ENTRY_ID_E16, START_DATE_E16, ADDED_DATE_E16, SYST_E16, DIAST_E16,
ENTRY_ID_E17, START_DATE_E17, ADDED_DATE_E17, SYST_E17, DIAST_E17,
ENTRY_ID_E18, START_DATE_E18, ADDED_DATE_E18, SYST_E18, DIAST_E18,
ENTRY_ID_E19, START_DATE_E19, ADDED_DATE_E19, SYST_E19, DIAST_E19,
ENTRY_ID_E20, START_DATE_E20, ADDED_DATE_E20, SYST_E20, DIAST_E20,
ENTRY_ID_E21, START_DATE_E21, ADDED_DATE_E21, SYST_E21, DIAST_E21,
ENTRY_ID_E22, START_DATE_E22, ADDED_DATE_E22, SYST_E22, DIAST_E22,
ENTRY_ID_E23, START_DATE_E23, ADDED_DATE_E23, SYST_E23, DIAST_E23,
ENTRY_ID_E24, START_DATE_E24, ADDED_DATE_E24, SYST_E24, DIAST_E24,
ENTRY_ID_E25, START_DATE_E25, ADDED_DATE_E25, SYST_E25, DIAST_E25,
ENTRY_ID_E26, START_DATE_ <<---etc.--->> T JOIN GROUP_39 g39 ON
(t1.PATIENT_ID = g39.PID) LEFT JOIN GROUP_40 g40 ON (t1.PATIENT_ID =
g40.PID)

It can be a lot longer even in Excel 2007 as that has many more available
columns.

RBS

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: 10 May 2007 00:33
To: sqlite-users@sqlite.org
Subject: [sqlite] Longest "real" SQL statement

I'm looking for an upper bound on how big legitimate 
SQL statements handed to SQLite get to be.  I'm not
interested in contrived examples.  I want to see
really big SQL statements that are actually used in
real programs.

"Big" can be defined in several ways:

*  Number of bytes of text in the SQL statement.
*  Number of tokens in the SQL statement
*  Number of result columns in a SELECT
*  Number of terms in an expression

If you are using really big SQL statements, please
tell me about them.  I'd like to see the actual
SQL text if possible.  But if your use is proprietary,
please at least tell me how big your query is in
bytes or tokens or columns or expression terms.

Thanks.
--
D. Richard Hipp <[EMAIL PROTECTED]>



-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] excel and sqlite

2007-05-07 Thread RB Smissaert
I have been using SQLite in Excel for the last half year now (in a
commercial application) and I think I will be able to help.
Currently I am using the wrapper written by Olaf Schmidt and this works very
well:
www.datenhaus.de/Downloads/dhSQLite-Demo.zip

If you are interested then I can send you a demo workbook.

RBS


-Original Message-
From: steve31415 [mailto:[EMAIL PROTECTED] 
Sent: 07 May 2007 18:03
To: sqlite-users@sqlite.org
Subject: [sqlite] excel and sqlite


Hi, I am developing a VBA app in excel 2003 and I would like to know how to
setup sqlite so that I can use it. I am a newbie when it comes to databases.
Currently I have installed the sqlite ODBC driver
(www.ch-werner.de/sqliteodbc/) and have created a database using
sqlitebrowser (http://sourceforge.net/projects/sqlitebrowser/). What should
I do next? 

Thanks for any suggestions
-- 
View this message in context:
http://www.nabble.com/excel-and-sqlite-tf3705051.html#a10361323
Sent from the SQLite mailing list archive at Nabble.com.



-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Search on Age, from DOB column

2007-05-03 Thread RB Smissaert
I am also working with a clinical application, using SQLite and VBA.
I use this function to produce the SQL to convert dates in the  ISO8601
format to an integer age. 

Function ISO8601Date2Age(strField, Optional strAlias As String) As String

   Dim strAS As String

   If Len(strAlias) > 0 Then
  strAS = " AS "
   End If

   ISO8601Date2Age = "case when date(" & strField & ", '+' || " & _
 "(strftime('%Y', 'now') - strftime('%Y', " & strField &
")) || " & _
 "' years') <= date('now') then " & _
 "strftime('%Y', 'now') - strftime('%Y', " & strField &
") " & _
 "else " & _
 "strftime('%Y', 'now') - strftime('%Y', " & strField &
") -1  End" & _
 strAS & strAlias

End Function


You may not be coding in VB, but you will get the idea.

RBS


-Original Message-
From: Allan, Mark [mailto:[EMAIL PROTECTED] 
Sent: 03 May 2007 11:57
To: sqlite-users@sqlite.org
Subject: [sqlite] Search on Age, from DOB column

Hi,

I need to be able offer the user the ability to search for patients in the
database based on age. i.e. age > 17 or age = 45 etc etc...

I only store the patient DOB in the database however, what is the SQL to
achive this? Can I subract todays date from the DOB and get the number of
years within an SQL string?

The patient table is similar to:-

Patients
{
INTEGER PrimaryKey;
TEXT Surname;
TEXT FirstName;
TIMESTAMP DOB;
...
...
...
}


Thanks in advance for your help.

Mark



DISCLAIMER:
This information and any attachments contained in this email message is
intended only for the use of the individual or entity to which it is
addressed and may contain information that is privileged, confidential, and
exempt from disclosure under applicable law.  If the reader of this message
is not the intended recipient, or the employee or agent responsible for
delivering the message to the intended recipient, you are hereby notified
that any dissemination, distribution, forwarding, or copying of this
communication is strictly prohibited.  If you have received this
communication in error, please notify the sender immediately by return
email, and delete the original message immediately.


-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Difference in these indices?

2007-03-28 Thread RB Smissaert
Ok, thanks.
A bit more work then to deal with all the indices.

Just one question; as I log nearly all my SQL statements to
a SQLite table, will this be OK with the double quotes added?

RBS


-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED] 
Sent: 28 March 2007 16:10
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Difference in these indices?

RB Smissaert wrote:
> Does this only apply to table and column names?
> I will never use double quote characters in my identifier 
> names, so there should be no problem there.
>
>   
It applies to all the identifiers: table, column, index, trigger, 
database (using attach as id), transactions (if named which they seldom 
are), collation, and view names.

HTH
Dennis Cote


-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Difference in these indices?

2007-03-28 Thread RB Smissaert
Dennis,

OK, you convinced me and I think I will alter this.
Does this only apply to table and column names?
I will never use double quote characters in my identifier 
names, so there should be no problem there.

RBS


-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED] 
Sent: 28 March 2007 15:41
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Difference in these indices?

RB Smissaert wrote:
> As to quotes etc.
> As my code works fine as it is I probably will leave this as the double
> quotes look ugly and it will be a reasonably big job to alter all this.
> Did I get you right that the only benefit of doing create "table1" etc.
> is compatibility with running sqlite with SQLite.exe?
>
>   
The benefit to using standard quoting for identifiers is portability. 
Your table definitions will almost certainly be rejected by almost any 
other database engine. Most don't support the same extended quoting 
rules that sqlite has added for compatibility with files coming from 
other sources.

If adding the escaped quotes to the SQL generation statements doesn't 
work for you, then you could create a simple function that adds the 
escaped quotes to your identifier variables. If you simplify the problem 
and assume you will never use double quote characters in your identifier 
names themselves this function is very simple;

Function Quote(id As String) As String
Quote = """" & id & """"
End Function

and your code becomes something like this.

strTable = "table1"
strColumn = "ID"
strSQL = "create " & Quote(strTable) & "(" & Quote(strColumn) & " 
INTEGER PRIMARY KEY)"

HTH
Dennis Cote


-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Difference in these indices?

2007-03-27 Thread RB Smissaert
Looks then that doing the table creation with INTEGER PRIMARY KEY
Is the way to go, but as always it will come down to a lot of testing.

As to quotes etc.
As my code works fine as it is I probably will leave this as the double
quotes look ugly and it will be a reasonably big job to alter all this.
Did I get you right that the only benefit of doing create "table1" etc.
is compatibility with running sqlite with SQLite.exe?

RBS

-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED] 
Sent: 27 March 2007 23:41
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Difference in these indices?

RB Smissaert wrote:
> Is it right that this won't affect the speed of any subsequent inserts or
> deletes?
>   
Well inserts will be done in id order. If you have predefined ids 
assigned by some outside source and specify them when you insert into 
sqlite, it will have to insert at random location in the btree. This 
will take longer than always appending at the end of the btree. If you 
let sqlite assign the ids, or the ids are in order, then this is not an 
issue. If you are always going to create the external index afterwards 
anyway, it will also probably not make much difference (you would have 
to test it each way).
> About the single quotes etc:
> This is VB code, so I can't do:
> Create table "table1"("ID" INTEGER PRIMARY KEY)
>
> I can do:
> Create table table1(ID INTEGER PRIMARY KEY)
>
> As the table and the columns are often variables it will be something
like:
>
> strTable = "table1"
> strColumn = "ID"
>
> strSQL = "create " & strTable & "(" & strColumn & " INTEGER PRIMARY KEY)"
>
>
>   
VB and SQL both use the same technique of escaping quotes embedded in 
strings using a pair of quotes back to back.

In VB

print "Test ""quoted"" strings." 

will output

Test "quoted" strings.

You can do the same with the strings you are building to send to SQLite. 
Using the following VB statement

strSQL = "create """ & strTable & """(""" & strColumn & """ INTEGER 
PRIMARY KEY)"

will produce a strSQL that contains the string

create "table1"("ID" INTEGER PRIMARY KEY)

HTH
Dennis Cote




-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Difference in these indices?

2007-03-27 Thread RB Smissaert
Thanks for clarifying that.
I think all the data to be inserted in tables with an INTEGER PRIMARY KEY
will be sorted on that key, but I will have to check as sometimes these
tables can be big, say a few million rows.

RBS

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: 27 March 2007 23:34
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Difference in these indices?

"RB Smissaert" <[EMAIL PROTECTED]> wrote:
> Thanks for that.
> So if I can then I should create the table with INTEGER PRIMARY KEY.
> Is it right that this won't affect the speed of any subsequent inserts or
> deletes?
> 

That depends on the data.

If you insert records in order of ascending integer primary
key, then the inserts will be very fast.  If you insert records
where the integer primary key is randomized, inserts will be
reasonably fast until the size of your table exceeds the size
of your disk cache.  Then each insert will need to do multiple
reads and writes to disk as it tries to figure out where in
your massive table is the right place to put the new record,
and then make space for that new record.  All this disk I/O
will slow things down dramatically.

Every table has an integer primary key whether you declare one
or not.  If you do not specify an integer primary key then one
is created for you automatically named "ROWID" or "OID".  If 
you do not specify a value for the integer primary key when
inserting, a value is selected automatically.  The value
selected is one more than the largest existing integer primary
key in that table.  That means that if you do not specify
integer primary keys, the keys choosen are in ascending order
and inserts are very fast.

--
D. Richard Hipp  <[EMAIL PROTECTED]>



-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Difference in these indices?

2007-03-27 Thread RB Smissaert
Thanks for that.
So if I can then I should create the table with INTEGER PRIMARY KEY.
Is it right that this won't affect the speed of any subsequent inserts or
deletes?

About the single quotes etc:
This is VB code, so I can't do:
Create table "table1"("ID" INTEGER PRIMARY KEY)

I can do:
Create table table1(ID INTEGER PRIMARY KEY)

As the table and the columns are often variables it will be something like:

strTable = "table1"
strColumn = "ID"

strSQL = "create " & strTable & "(" & strColumn & " INTEGER PRIMARY KEY)"


RBS

-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED] 
Sent: 27 March 2007 22:51
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Difference in these indices?

RB Smissaert wrote:
> Is there any difference in an index created like this:
>
> Create table 'table1'([ID] INTEGER PRIMARY KEY)
>
> with this:
>
> Create table 'table1'([ID] INTEGER)
>
> Create unique index idx_table1_ID on table1(ID)
>
> I tended to use the first form, but as that can make subsequent table
> inserts or deletes slower I am now moving to the second form.
>
>   
Yes there is. The first uses the key for the btree that stores the table 
to hold the id. The second uses a second comlpetely independent btree to 
store an index that holds records that contain the id number and the 
rowid of the corresponding record in the table. The table itself 
contains a rowid as the key of the table btree and the user id field.

SQLite version 3.3.13
Enter ".help" for instructions
sqlite> Create table 'table1'([ID] INTEGER PRIMARY KEY);
sqlite> select * from sqlite_master;
table|table1|table1|2|CREATE TABLE 'table1'([ID] INTEGER PRIMARY KEY)

SQLite version 3.3.13
Enter ".help" for instructions
sqlite> Create table 'table1'([ID] INTEGER);
sqlite> Create unique index idx_table1_ID on table1(ID);
sqlite> select * from sqlite_master;
table|table1|table1|2|CREATE TABLE 'table1'([ID] INTEGER)
index|idx_table1_ID|table1|3|CREATE UNIQUE INDEX idx_table1_ID on table1(ID)

The first version stores on integer for each record, and stores it in 
the btree key. The second stores four integers for each record, two in 
the table record and two in the index record. You are making your 
database much larger for no reason.

Also, you are using literal strings (delimited with a single quote) for 
your table names. This is not standard SQL and will not be portable. You 
are also using square brackets to quote your column names. This is also 
an SQLite and MS extension to standard SQL. You should quote identifiers 
such as table and column names with double quotes.

Create table 'table1'([ID] INTEGER PRIMARY KEY)

should be:

Create table "table1"("ID" INTEGER PRIMARY KEY)
  
HTH
Dennis Cote


-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-



[sqlite] Difference in these indices?

2007-03-27 Thread RB Smissaert
Is there any difference in an index created like this:

Create table 'table1'([ID] INTEGER PRIMARY KEY)

with this:

Create table 'table1'([ID] INTEGER)

Create unique index idx_table1_ID on table1(ID)

I tended to use the first form, but as that can make subsequent table
inserts or deletes slower I am now moving to the second form.

RBS





-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Any way to do this faster?

2007-03-26 Thread RB Smissaert
Had a good look at this now and doing:

delete from tableB
where not exists
(select id from tableA where tableA.id = tableB.id)

Is indeed quite a bit faster than doing:

delete from tableB
where id not in
(select tableA.id from tableA)

In my case about 3 times as fast.
Looking at the query plan with EXPLAIN QUERY PLAN was a good tip!

I think though that I gained more by looking more carefully when to put the
index on the id column. I need the index, but not before the delete, so I
gained a lot by creating the index after the delete. This meant less rows to
index plus less work to be done with the delete.
Probably there will be more places in my app where looking at the timing of
the index creation will speed things up, so thanks again for the advice.

RBS


-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED] 
Sent: 26 March 2007 18:16
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Any way to do this faster?

[EMAIL PROTECTED] wrote:
>
> I will need an index on that field later, but
> I could drop it prior to the delete and create
> a new one after.
>
>   
Don't do that. If you need the index, then leave it as is.

> Thinking about it I am not sure in any case of the
> value of an index after deletes on a table.
> Is it usually better to re-index after deletes?
>
>   
Indexes are updated automatically as records are added and deleted from 
a table, that's why they add overhead if they are not serving some 
purpose. Your index will be correct after you delete the records from 
tableB.

HTH
Dennis Cote


-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-



[sqlite] Any way to do this faster?

2007-03-25 Thread RB Smissaert
Simplified I have the following situation:

2 tables, tableA and tableB both with an integer field, called ID, holding
unique integer numbers in tableA and non-unique integer numbers in tableB.
Both tables have an index on this field and for tableA this is an INTEGER
PRIMARY KEY.
Now I need to delete the rows in tableB where this number doesn't appear in
the corresponding field in tableA.

Currently I do this with this SQL:

Delete from tableB where ID not in (select tableA.ID from tableA)

When table tableB gets big (say some 10 rows) this will get a bit slow
and I wonder if there is a better way to do this.

RBS






-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Question about speed of CASE WHEN

2007-03-18 Thread RB Smissaert
I use SQLite as a data manipulator, not as a database. I get data from a
server database, dump to SQLite, manipulate the data and finally dump to
Excel. As this is reporting software speed is important, so I will go with
the fastest method.

RBS

-Original Message-
From: Gerry Snyder [mailto:[EMAIL PROTECTED] 
Sent: 18 March 2007 23:46
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Question about speed of CASE WHEN

RB Smissaert wrote:
> Ok, now done some better testing and the method with CASE WHEN is indeed,
as
> expected a bit faster

To me the lookup table method seems like exactly what a relational 
database is used for.

The CASE WHEN would have to be dramatically faster, and in an area where 
timing was critical, for me to choose that way. If there were thousands 
of items, and changes were frequent, you wouldn't even consider CASE 
WHEN, would you?

Remember: timing isn't important, except when it is.

Gerry


-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Question about speed of CASE WHEN

2007-03-18 Thread RB Smissaert
Ok, now done some better testing and the method with CASE WHEN is indeed, as
expected a bit faster, I would say about a third. I have only tested this
with some 8 different convert values, so maybe it will be different if there
are much more different values to convert.

RBS


-Original Message-
From: RB Smissaert [mailto:[EMAIL PROTECTED] 
Sent: 18 March 2007 17:17
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Question about speed of CASE WHEN

Done some testing now and surprisingly, it seems the lookup method with a
join to a lookup table is very slightly faster than the CASE WHEN
construction. There isn't much in it though.

Also tried the shorter CASE WHEN construction, like this:

> case ENTRY_TYPE
>   when  9 then 'Issue
>   when  2 then 'Note'
Etc

But didn't get that to work as it always produced the first WHEN option.


RBS


-Original Message-
From: Christian Smith [mailto:[EMAIL PROTECTED] 
Sent: 18 March 2007 15:29
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Question about speed of CASE WHEN

RB Smissaert uttered:

> Looking at the fastest way to convert a field in a table and wonder if in
> general an update with a CASE WHEN construction or an update with a join
to
> a lookup table is faster.


My guess is that the CASE WHEN form will be faster for small number of 
possibilities. It compiles into a straight list of compare instructions.


>
> These conversions are of this type:
>
> UPDATE A3SQL77D_J
>   SET ENTRY_TYPE = (case
>   when ENTRY_TYPE = 9  then 'Issue
>   when ENTRY_TYPE = 2  then 'Note'
>   when ENTRY_TYPE = 1  then 'Encounter'
>   when ENTRY_TYPE = 8  then 'Authorisation'
>   when ENTRY_TYPE = 11  then 'Prescription'
>   when ENTRY_TYPE = 5  then 'Treatment'
>   when ENTRY_TYPE = 3  then 'Problem'
>   when ENTRY_TYPE = 13  then 'Discontinuation'
>   when ENTRY_TYPE = 6  then 'Reminder'
>   when ENTRY_TYPE = 14  then 'Adverse reaction'
>   when ENTRY_TYPE = -1  then 'Unknown'
>   when ENTRY_TYPE = 4  then 'Sub-problem'
>   when ENTRY_TYPE = 7  then 'Battery'
>   when ENTRY_TYPE = 10  then 'Return-Script'
>   else ENTRY_TYPE end)
>
> So, an integer value to be converted to a limited number of strings.
> I could figure this out with some testing, but maybe there are some
general
> rules that apply to this.


Testing is probably the best way. However, the difference in speed may not 
be significant given the increased maintainability of the table lookup 
based solution.


>
> Also, would the order of the CASE WHEN options make a difference,
> speed-wise, so would it be faster to have the WHEN options in decreasing
> order of frequency?


The sequence of code generated compares the cases in the order written. So 
the common cases should go first.


>
> RBS
>

Christian

--
 /"\
 \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
  X   - AGAINST MS ATTACHMENTS
 / \


-
To unsubscribe, send email to [EMAIL PROTECTED]

-





-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Question about speed of CASE WHEN

2007-03-18 Thread RB Smissaert
Done some testing now and surprisingly, it seems the lookup method with a
join to a lookup table is very slightly faster than the CASE WHEN
construction. There isn't much in it though.

Also tried the shorter CASE WHEN construction, like this:

> case ENTRY_TYPE
>   when  9 then 'Issue
>   when  2 then 'Note'
Etc

But didn't get that to work as it always produced the first WHEN option.


RBS


-Original Message-
From: Christian Smith [mailto:[EMAIL PROTECTED] 
Sent: 18 March 2007 15:29
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Question about speed of CASE WHEN

RB Smissaert uttered:

> Looking at the fastest way to convert a field in a table and wonder if in
> general an update with a CASE WHEN construction or an update with a join
to
> a lookup table is faster.


My guess is that the CASE WHEN form will be faster for small number of 
possibilities. It compiles into a straight list of compare instructions.


>
> These conversions are of this type:
>
> UPDATE A3SQL77D_J
>   SET ENTRY_TYPE = (case
>   when ENTRY_TYPE = 9  then 'Issue
>   when ENTRY_TYPE = 2  then 'Note'
>   when ENTRY_TYPE = 1  then 'Encounter'
>   when ENTRY_TYPE = 8  then 'Authorisation'
>   when ENTRY_TYPE = 11  then 'Prescription'
>   when ENTRY_TYPE = 5  then 'Treatment'
>   when ENTRY_TYPE = 3  then 'Problem'
>   when ENTRY_TYPE = 13  then 'Discontinuation'
>   when ENTRY_TYPE = 6  then 'Reminder'
>   when ENTRY_TYPE = 14  then 'Adverse reaction'
>   when ENTRY_TYPE = -1  then 'Unknown'
>   when ENTRY_TYPE = 4  then 'Sub-problem'
>   when ENTRY_TYPE = 7  then 'Battery'
>   when ENTRY_TYPE = 10  then 'Return-Script'
>   else ENTRY_TYPE end)
>
> So, an integer value to be converted to a limited number of strings.
> I could figure this out with some testing, but maybe there are some
general
> rules that apply to this.


Testing is probably the best way. However, the difference in speed may not 
be significant given the increased maintainability of the table lookup 
based solution.


>
> Also, would the order of the CASE WHEN options make a difference,
> speed-wise, so would it be faster to have the WHEN options in decreasing
> order of frequency?


The sequence of code generated compares the cases in the order written. So 
the common cases should go first.


>
> RBS
>

Christian

--
 /"\
 \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
  X   - AGAINST MS ATTACHMENTS
 / \


-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Question about speed of CASE WHEN

2007-03-18 Thread RB Smissaert
Sorry, had to rush off and missed your alternative.
Will do some testing now.

RBS


-Original Message-
From: T [mailto:[EMAIL PROTECTED] 
Sent: 18 March 2007 14:55
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Question about speed of CASE WHEN

Hi RBS,

>> Perhaps the alternative form of the CASE statement would be faster
>
> What form is that?

The form that I showed in my previous email. That is, using:

> case ENTRY_TYPE
>   when  9 then 'Issue
>   when  2 then 'Note'
etc

instead of:

>> case
>>  when ENTRY_TYPE = 9 then 'Issue
>>  when ENTRY_TYPE = 2 then 'Note'
etc

Tom



-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-



  1   2   3   >