On Mon, Aug 8, 2011 at 2:19 AM, Igor Tandetnik <itandet...@mvps.org> wrote:
> Alexey Pechnikov <pechni...@mobigroup.ru> wrote:
>> 2011/8/7 Simon Slavin <slav...@bigfraud.org>:
>>> You don't need to. The SQLite expressions I listed tell you how to achieve
>>> the result without doing that.
>>
>> Really? And how can you perform the query like to:
>>
>> sqlite> create table t1(ids text);
>> sqlite> insert into t1 (ids) values ('1 2 3');
>> sqlite> insert into t1 (ids) values ('2 3 4');
>> sqlite> insert into t1 (ids) values ('3 4 5');
>
> I can normalize this table, then use joins.
>
>> A simple calculation: if each list of identifiers have about 1000
>> items and there are
>> 1 000 000 lists than the table of relations (t1.rowid, t2.rowid) will
>> have 1 000 000 000
>> rows!
>
> One way or the other, you need to store 1,000,000,000 pieces of information.
> Why is it that storing them in 1,000,000 rows holding 1000 items each is
> unremarkable, but storing them in 1,000,000,000 rows holding one item each is
> exclamation point-worthy?
>
I will let you heavyweights duke it out, but re. the above point,
SQLite (and more databases) have a per row system overhead that can
very quickly overweight the actual data if the data are too granular
with each row storing just a tiny amount. For example, the overhead
for the cells in a raster dataset, if stored one per row, will quickly
surpass the size of the actual data.
> If reducing the number of rows is your ultimate goal, why don't you create a
> table with one row, holding the whole data structure encoded into one huge
> string or blob? That'll best optimize the one metric you seem to believe
> matters the most.
>
>> It's too slow
>
> ... when compared to what alternative? Linearly scanning all those lists?
>
>> Of cource all
>> systems store lists of
>> identifiers in similar situations.
>
> I find it hard to believe that every single system does - surely systems
> exist that do not denormalize their data this way. In fact, I doubt the
> design you describe is common, let alone universally accepted.
> --
> Igor Tandetnik
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
-----------------------------------------------------------------------
Assertions are politics; backing up assertions with evidence is science
=======================================================================
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
