[EMAIL PROTECTED] uttered:
"Brandon, Nicholas (UK)" <[EMAIL PROTECTED]> wrote:
I don't want to hijack this thread (not too much anyway) but this got me
thinking about JOINs since I have a database that uses a similar concept
(one table holds a number of key-value pairs for another).
As I understand it, an INNER JOIN is equivalent in pseudo-code to:
For (every record in table A)
for (every record in table B)
for (...)
where another for loop is added with each additional JOIN.
Using Christian's example, would SQLite use the pseudo-code represented
above or does it use some intelligence that all the JOINs are from the
same table and hence the pseudo-code is:
for (every record in table "instances")
for (every record in table "instance_fields")
It does a nested loop. There is no optimization for when the
same table is joined multiple times. How often does that happy,
really?
Not very often, certainly not worth optimizing for. I will only use the
view for generating a transient table cache for viewing like objects and
reporting.
I'm writing an general purpose object storage library, hence the need for
arbitrary fields. But the view is not needed when querying or updating
single objects, only when generating a view of all similar objects in a
table form.
For reference, using a SPARCclassic, I get essentially a 4x speed hit
using the view against selecting data from the equivalent cache table.
Acceptable trade off IMO.
--
D. Richard Hipp <[EMAIL PROTECTED]>
Thanks,
Christian
--
/"\
\ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
