Re: [sqlite] Query Flattening / Query Optimization / Correlated Subquery Substitution

Hello, I don't want to be annoying but why nowadays people are sub-abusing-sub-selecting instead of using JOINs? moreover, that is in most cases faster (a lot) and certainly more Human Readable. Best Regards On Mon, Feb 24, 2014 at 8:34 PM, Keith Medcalf wrote: > >

[sqlite] Query Flattening / Query Optimization / Correlated Subquery Substitution

Previous send munged. If it managed to make it properly, my apologies for posting the same message twice. Using the following schema: CREATE TABLE TKRawData ( EmpNo text collate nocase not null, CustNo integer not null, JobNo integer not null, RawYear integer not null, RawMonth integer

[sqlite] Query Flattening / Query Optimization

Using the following schema: CREATE TABLE TKRawData ( EmpNo text collate nocase not null, CustNo integer not null, JobNo integer not null, RawYear integer not null, RawMonth integer not null, RawDays real not null, primary key (EmpNo, JobNo, CustNo, RawYear, RawMonth) ); CREATE TABLE

[sqlite] Performance tuning: winDelete() tweak

Hi, Earlier today I tried to show how to use a profiler to somebody while running a checkout on a very recent Subversion build on Windows. This build contained Sqlite 3.8.3.1. We happened to see that one of the functions that caught about 15% of the profile samples was the function

Re: [sqlite] partially excluding records

Thanks everyone who helped me. This is what I ended up with: SELECT key , COALESCE(SUM(CASE WHEN STATUS!='C' THEN 1 END) ,SUM(CASE WHEN STATUS='C' THEN 1 END)) CNT , COALESCE(MIN(CASE WHEN STATUS!='C' THEN STATUS END) || CASE WHEN COUNT(DISTINCT CASE WHEN STATUS!='C' THEN

Re: [sqlite] Unnecessary implicit conversion (may lead to a bug)

On 02/24/2014 05:54 AM, skywind mailing lists wrote: In afpUnlock(sqlite3_file *, int) the sharedLockByte is defined as an int (int sharedLockByte = SHARED_FIRST+pInode->sharedByte;) although all other related variables and the following function parameters are defined as unsigned long

Re: [sqlite] Network Storage

On Mon, 24 Feb 2014 13:54:39 +0100 Richard Schülein wrote: > So that means, if there is only 1 machine accessing the network database, > there should be no problem with the network access and the locking? I ended using only one sqlite full accessing the db. All others submit

Re: [sqlite] Network Storage

So that means, if there is only 1 machine accessing the network database, there should be no problem with the network access and the locking? Best regards/Mit freundlichen Grüßen Richard Schülein Novel-Tech GmbH Am Krautgarten 4 D-91717 Wassertrüdingen Germany fon: +49 (0) 9832 / 706 814 fax:

Re: [sqlite] Network Storage

On Mon, Feb 24, 2014 at 1:27 PM, Simon Slavin wrote: > So single-user single-process use of a database, without hardware faults > or power faults, should not present a problem. > Minor addenda/caveat: i have seen NFS-based systems which slow down (by 100x or more) apps

Re: [sqlite] Network Storage

On 24 Feb 2014, at 12:07pm, Richard Schülein wrote: > Is there anything needed to store the Database itself on a shared network > devices? In my opinion this is similar to an USB drive etc. That means, that > the drive don’t even know, where the database is stored. Or is

Re: [sqlite] Network Storage

As a quick search will reveal, many network file systems have broken locking. This can and will lead to database corruption as soon as more than one process attempts to write to the database because SQLite relies on file locking to implement transactions. SQLite will happily open whatever path

[sqlite] Network Storage

Hi, i have an general question regarding SQLite and Network. As I can read here http://www.sqlite.org/cvstrac/wiki?p=SqliteNetwork there can be problems with that…. Do anybody have experience on that. We use SQLLite on an embedded Linuxsystem on an Audioserver which store his files on local

Re: [sqlite] Still trying to track down loadable extensions

On Mon, 24 Feb 2014 00:43:40 +0200, Alek Paunov wrote: Just in case if Alessandro Furieri do not follow the list closely Hi Alek, I can simply add few more details to your already excellent introduction. SpatiaLite is a loadable extension adding standard Spatial SQL capabilities to the