db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mo Maison <momai...@yahoo.fr>
Subject Re: Question regarding SubSelect Performance
Date Thu, 27 Sep 2012 19:24:17 GMT

Hi,

I do not fully understand your query because you are joining

document_table.id on 'alias.id' ?
Given your columns names and your sample data set, I'd think you
should join on alias.referring_document ? (the document_attribute_table
is a N--N association intermediate table, isn't it ?
The document_attribute_table.id column would be superfluous ?)

In this case, why not a simple left join :
   SELECT * FROM document_table doc
   LEFT JOIN document_attribute_table da
          ON da.referring_document = doc.id
   WHERE da.referring_attribute = 2
      OR da.referring_attribute IS NULL
   ORDER BY da.attribute_value -- do NULL go first or last ?

Regarding performances, I observed that you should either declare
your foreign key constraints to Derby, or create an index on
da.referring_document to speed up the join.

HTH...



Le 27/09/2012 15:00, Tino Schmidt a écrit :
> Hello,
>
> I have a problem with the following issue: I have two (three) tables, one contains documents
(document_table) and another one (document_attribute_table) contains attributes for these
documents (The third table contains the definition of the attributes). Now i want to sort
documents regarding to a specific attribute, the problem is, documents which does not have
this particular attribute should be considered by this too.
>
> Simple example of the 2 tables:
>
> document_table:
> id  | name
> -------------------
> 0   | doc1
> 1   | doc2
> -------------------
>
> document_attribute_table:
> id  | referring_document    | referring_attribute   | attribute_value
> -----------------------------------------------------------------------
> 0   | 0                     | 1                     | value1
> 1   | 0                     | 2                     | value2
> 2   | 1                     | 1                     | value3
> -----------------------------------------------------------------------
>
> The following select statement does what is intended:
>
> SELECT * FROM document_table LEFT JOIN (SELECT * FROM document_attribute_table WHERE
referring_attribute=2) alias on alias.id=document_table.id order by alias.attribute_value
>
> However this statement is horribly slow - now i'm looking for a "better" solution to
this problem.
>
>

Mime
View raw message