> Whether or not the the secondary columns are needed is a function of one
> of the primary columns. That function involves values from another
> table, though, so the general case would require a join. That other
> table is small, however, so I generally cache it outside the database.
> Some pseudocode for my expected use would be something like:
>
> prepare("SELECT primary_columns FROM big_table WHERE some_criterion")
> while(step()) {
>
> if( F(primary_column_values) ) {
> Fetch secondary values
> }
>
> do something with primary and maybe secondary values;
>
> }
>
> Where F would be implemented outside the database.
> Thanks,
> Jeff
I assume that the primary SELECT shown above can be made suitably fast
by creating the appropriate indices on big_table.
If the secondary columns are kept in a separate, secondary_table, and
a fifth primary column is added that contains the ROWID of the approp-
riate entry in the secondary_table (or NULL if the secondary_table is
not needed), then the "Fetch secondary values" operation should be very
fast as well.
It seems to me that this approach would be faster than a join, and
would consume less space than an 8-column table containing mostly
NULLs in the secondary columns.
Of course, this approach would cost you some extra space, in the form
of the 5th primary column containing the secondary ROWID.
- Richard Klein
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
