Make your own free website on Tripod.com

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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: InstantDB: OUTER JOIN Performance


Thomas

Could you zip up the 500 record version and mail it to me on 
peter.hearty@lutris.com? I'll have a look at what's going on.

Regards

Pete

----- Original Message -----
From: "Thomas Herre" <thomas.herre@inxnet.de>
Date: Monday, February 19, 2001 12:54 pm
Subject: InstantDB: OUTER JOIN Performance

> 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.orgwith 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.
> 

-----------------------------------------------------------------------------
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.