InstantDB Project
About InstantDB
Project Mail Lists
Short History
Reporting Bugs
Screen Shots
3rd Party Examples
FAQs

Software
Downloads
Documentation
CVS Repositories
Roadmap
License

About Enhydra.org
Who We Are
News, Articles & Events
Getting Involved
Contact Us

Community
Demos
Contributions
Resources
Case Studies
On The Edge! -NEW-
Commercial Vendors

Hi there,

I'm having some performance problems with OUTER JOINs.
Here are my table definitions:

Table entity (basic information -> email)
============
CREATE TABLE entity (
entity_id int UNIQUE PRIMARY KEY,
entity_email CHAR(60));

Table attribute (additionial attributes for all entities like last name)
===============
CREATE TABLE attribute (
attribute_id int UNIQUE PRIMARY KEY,
attribute_name CHAR(60));

Table entity_attribute (used to store concrete values of additional
attributes)
======================
REATE TABLE entity_attribute (
entity_id int,
attribute_id int,
attribute_value CHAR(250));

The following SELECT statement runs fine with some records
(In the subquery I compute the id's of the entities to include
in the outer query):

SELECT entity.entity_id, entity.entity_email,
entity_attribute.attribute_value
FROM entity FULL OUTER JOIN entity_attribute USING ( entity_id )
WHERE entity_attribute.attribute_id = 3
AND entity.entity_id IN
( SELECT entity_list.entity_id FROM entity_list
  WHERE entity_list.list_id = 1 )
ORDER BY entity_attribute.attribute_value ASC

If I insert 500 records into the entity_attribute table
it may take up to half a minute to perform the query.
Query time seems to increase very fast when adding more
records. I have indexes on all tables by the way.
I am using InstantDB 3.26. I measured the query times
with the commsql tool running on 800 Mhz PIII with
512 MB RAM and Win 2K operation system.

Any help on this problem would be very much appreciated.
Thanx in advance,

Thomas.

-----------------------------------------------------------------------------
To unsubscribe from this mailing list, send email to majordomo@enhydra.org
with the text "unsubscribe instantdb" in the body of the email.
If you have other questions regarding this mailing list, send email to
the list admin at owner-instantdb@enhydra.org.