[sqlite] Success stories

[Alexey Pechnikov]

Hello!

I did migrate two projects from PostgreSQL (one is ~22 Gb database with very 
complex reports) and now migrate oracle project (>100 Gb billing database 
with distributed data collectors) for best performance on multi-core servers 
and SATA disks. SQLite database may be replicated or copied very simple and 
it's very useful property for distributed projects. I did use SQLite some 
years on linux servers && windows desktops && linux desktops && winmobile 
PDA/Smartphones and now I'm know that SQLite is sure remedy and I can 
eliminate PostgreSQL/Oracle from my projects completely.

My results is up to 60x better performance and more quality C && tcl code and 
more fast development process.

There are some patterns for hight load SQLIte projects development in SQLite 
Wiki. But successful examples of big projects is more comprehensive knowledge 
for developers.

Thanks for excellent database engine!

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

Re: [sqlite] General help, a bit OT

[cstrader]

OK, I've played with this a bit.  Adding new columns to the datatable 
through a custom function is really slow because it enumerates every cell in 
the column.  I don't need that -- I just need a blank column -- full of 
nulls -- but with a column name.

I can add the columns to the datatable after I've retreived it, or store the 
empty columns in the database.

Seems like there might be something better.

Thanks

__

My original question:

I have to read thousands of tables and operate on each one in sequence.  I
need to do this fast.

I want to read the data from 6 stored columns and many rows from each table.

Once the table is read, I need to create derived columns (e.g. (new) Column
C = column A + ColumnB).

I'm finding it pretty slow to add the new columns to each table before
working on it.

I'm finding it faster to store the columns to be derived (empty) in the
database -- but this makes the db larger.

Is there an established, best practice, method for this problem?  More
generally, is there a place I could look for advice on how to optimize these
procedures?

Thanks


- Original Message - 
From: "Szomraky, Stefan" <[EMAIL PROTECTED]>
To: "General Discussion of SQLite Database" 
Sent: Tuesday, July 15, 2008 8:40 AM
Subject: Re: [sqlite] General help, a bit OT


>> -Original Message-
>> From: [EMAIL PROTECTED]
>> [mailto:[EMAIL PROTECTED] On Behalf Of cstrader
>> Sent: Tuesday, July 15, 2008 2:28 PM
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] General help, a bit OT
>>
>> Oh, sorry, the example was just an example.  The calculations
>> I need to make on on ColC cannot be done within SQLite.
>>
>> > Why does SELECT ColA, ColB, ColA+ColB AS ColC FROM tab1; not work?
>> >
>
> AFAIK, the current .NET provider for SQLITE supports custom functions
> implemented in .NET.
> Maybe this will work?
>
> SELECT ColA, ColB, custCLRfct(ColA, ColB) AS ColC FROM tab1;
>
> You might also want to implement the function in C for speed
> improvements.
>
> Greetings,
> Stefan.
> ___
> 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] database file size isn't really very small

[Corey Nelson]

Ah, ha! I had actually originally planned on doing that but when I read the
primary key could only be an integer I assumed it was a 32 bit integer so I
would need a separate column for the date. But you're right of course and I
see now that as of version 3, I can use 64 bit integers as the primary key!
This brings my database file down to a very reasonable 513024 bytes and
speeds up my code to boot.

What's more, since nearly all the prices in my data can be represented in 6
characters or less, I found I can save even more space by storing them as
strings like this:

CREATE TABLE StockName (date INTEGER PRIMARY KEY, price TEXT);

This brings my test database file size down to the bargain-basement size of
431104 bytes! Right on par with my text file format (which I will no longer
be needing).

Thanks a lot guys!

Corey

On Fri, Jul 18, 2008 at 1:32 PM, Filip Navara <[EMAIL PROTECTED]> wrote:

> Not really two copies as the integer could be primary key ...
> something along the lines of
>
> CREATE TABLE StockName (date INTEGER PRIMARY KEY, price REAL);
>
> Regards,
> F.
>
> On Fri, Jul 18, 2008 at 10:03 PM, Jay A. Kreibich <[EMAIL PROTECTED]> wrote:
> > On Fri, Jul 18, 2008 at 11:58:16AM -0700, Corey Nelson scratched on the
> wall:
> >> > sqlite3 Ticks.db ".dump"
> >> BEGIN TRANSACTION;
> >> CREATE TABLE StockName (date TEXT UNIQUE ON CONFLICT REPLACE, price
> REAL);
> >
> >> I would expect the database file to store a bit of "extra" data but it's
> >> 2.17 times bigger than the text file!
> >
> >  As others have pointed out, the issue is with the index, which is
> >  created automatically by the "UNIQUE" constraint.
> >
> >  In SQLite an index holds a full copy of the data.  Since the vast
> >  majority of your data (byte wise) is the date string, slightly more
> >  than 2x sounds just about right.
> >
> >  You can try to convert the date to a large integer.  SQLite supports
> >  up to 64 bit ints (signed), which should hold your current
> >  representation without problems.  You'll still have two copies, but
> >  the data should be much smaller.
> >
> >   -j
> >
> > --
> > Jay A. Kreibich < J A Y  @  K R E I B I.C H >
> >
> > "'People who live in bamboo houses should not throw pandas.' Jesus said
> that."
> >   - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006"
> > ___
> > 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] rtree woes with SQLITE_OMMIT_...

[Ralf Junker]

>> I am running the rtree module against an SQLite build which has  
>> lots of functionality SQLITE_OMIT_...ed.
>
>Can you be more specific? Exactly which SQLITE_OMIT symbols
>are defined?

Sure. Here is the list:

  SQLITE_OMIT_ALTERTABLE
  SQLITE_OMIT_ANALYZE
  SQLITE_OMIT_ATTACH
  SQLITE_OMIT_AUTHORIZATION
  SQLITE_OMIT_AUTOINCREMENT
  SQLITE_OMIT_AUTOVACUUM
  SQLITE_OMIT_BETWEEN_OPTIMIZATION
  SQLITE_OMIT_BLOB_LITERAL
  SQLITE_OMIT_CAST
  SQLITE_OMIT_CHECK
  SQLITE_OMIT_COMPLETE
  SQLITE_OMIT_COMPOUND_SELECT
  SQLITE_OMIT_EXPLAIN
  SQLITE_OMIT_FLAG_PRAGMAS
  SQLITE_OMIT_FOREIGN_KEY
  SQLITE_OMIT_GET_TABLE
  SQLITE_OMIT_GLOBALRECOVER
  SQLITE_OMIT_INCRBLOB
  SQLITE_OMIT_INTEGRITY_CHECK
  SQLITE_OMIT_LIKE_OPTIMIZATION
  SQLITE_OMIT_LOAD_EXTENSION
  SQLITE_OMIT_MEMORYDB
  SQLITE_OMIT_OR_OPTIMIZATION
  SQLITE_OMIT_PAGER_PRAGMAS
  SQLITE_OMIT_PROGRESS_CALLBACK
  SQLITE_OMIT_QUICKBALANCE
  SQLITE_OMIT_REINDEX
  SQLITE_OMIT_SCHEMA_VERSION_PRAGMAS
  SQLITE_OMIT_SHARED_CACHE
  SQLITE_OMIT_SUBQUERY
  SQLITE_OMIT_TCL_VARIABLE
  SQLITE_OMIT_TEMPDB
  SQLITE_OMIT_TRACE
  SQLITE_OMIT_TRIGGER
  SQLITE_OMIT_UTF16
  SQLITE_OMIT_VACUUM
  SQLITE_OMIT_VIEW
  SQLITE_OMIT_XFER_OPT

I have reconstructed a few tests from rtree1.test and the errors only show with 
the SQLITE_OIMIT_... symbols defined. Otherwise the tests run just fine.

>Rtree uses ALTER TABLE as part of the xRename() callback. The
>xRename() callback is invoked when the sqlite user does an
>ALTER TABLE. So, the rtree code that uses ALTER TABLE will
>never be invoked when the module is being used by an sqlite
>build that does not support ALTER TABLE. I suspect the same
>is true of fts3.

Right, I did not think of the fact that with SQLITE_OMIT_ALTERTABLE defined the 
parser already triggers a syntax error so it never reaches the virtual tree.

>It looks like there are a few extra lines of code that could
>be omitted from the build when SQLITE_OMIT_ALTERTABLE is
>defined though.

Agreed, but this would not save much footprint so it might not be worth the 
effort. I was more thinking in terms of detecting potential errors at 
compilation before they occur at run-time.

Many thanks for looking into this,

Ralf  

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

Re: [sqlite] rtree woes with SQLITE_OMMIT_...

[Dan]

On Jul 19, 2008, at 1:50 AM, Ralf Junker wrote:

> I am running the rtree module against an SQLite build which has  
> lots of functionality SQLITE_OMIT_...ed.

Can you be more specific? Exactly which SQLITE_OMIT symbols
are defined?

> Surprisingly, I receive strange errors like SQLITE_NOMEM for simple  
> statements like
>
>   CREATE VIRTUAL TABLE t6 USING rtree(ii, x1, x2);
>
> or
>
>   SELECT ii FROM t6 WHERE x1>2;
>
> Question: Does the rtree module rely on some SQLite functionality  
> which I might have omitted?
>
> I notice that rtree uses ALTER TABLE without checking for  
> SQLITE_OMIT_ALTERTABLE. Even though this is not causing me problems  
> right now, it might help to exclude alter table functionality from  
> rtree.c conditinally or issue an appropriate error when called.
>
> Btw, ALTER TABLE also applies to FTS3. Is this worth an extra  
> thread or even a ticket?

Rtree uses ALTER TABLE as part of the xRename() callback. The
xRename() callback is invoked when the sqlite user does an
ALTER TABLE. So, the rtree code that uses ALTER TABLE will
never be invoked when the module is being used by an sqlite
build that does not support ALTER TABLE. I suspect the same
is true of fts3.

It looks like there are a few extra lines of code that could
be omitted from the build when SQLITE_OMIT_ALTERTABLE is
defined though.

Dan.

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