db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From mustafa sener <msen...@yahoo.com>
Subject Query performance of joining table and a view
Date Mon, 31 Jul 2006 15:06:33 GMT
Hi everyone,


on table TABLE_EVENT:

CREATE TABLE TABLE_EVENT (
        OCCURRENCEIDENTIFIER INTEGER NOT NULL,
        NAME VARCHAR(255) NOT NULL,
        CLASS VARCHAR(255) NOT NULL,
        INSTANCE VARCHAR(255) NOT NULL,
        EVENT VARCHAR(255) NOT NULL,
        NOTIFICATIONLIST VARCHAR(255) NOT NULL,
        CATEGORY VARCHAR(255) DEFAULT NULL,
        NOTIFIEDAT TIMESTAMP DEFAULT NULL,
        CLEAREDAT TIMESTAMP DEFAULT NULL,
        LASTCHANGEDAT TIMESTAMP DEFAULT NULL,
        DURATION DOUBLE DEFAULT NULL,
        ELEMENTCLASS VARCHAR(200) DEFAULT NULL,
        ELEMENTINSTANCE VARCHAR(200) DEFAULT NULL,
        EVENTSTATE SMALLINT DEFAULT NULL,
        ISROOT SMALLINT DEFAULT NULL,
        CLEAREDONINIT SMALLINT DEFAULT NULL,
        EVENTTEXT VARCHAR(255) DEFAULT NULL,
        EVENTTYPE SMALLINT DEFAULT NULL,
        CREATEDAT TIMESTAMP DEFAULT NULL,
        ARCHIVEDAT TIMESTAMP DEFAULT NULL,
        SEVERITY INTEGER DEFAULT NULL,
        CERTAINTY DOUBLE DEFAULT NULL,
        TROUBLETICKETID VARCHAR(255) DEFAULT NULL,
        INSERTDTS TIMESTAMP DEFAULT NULL,
        UPDATEDTS TIMESTAMP DEFAULT NULL
    )

and on view VIEW_OCCURRENCE_PRIORITY:

CREATE VIEW VIEW_OCCURRENCE_PRIORITY (OCCURRENCEIDENTIFIER, UPDATEIDENTIFIER, ATTRIBUTEINTNAME,
ATTRIBUTEINTVALUE, CHANGESTART, CHANGEEND, INSERTDTS, UPDATEDTS) AS

SELECT DISTINCT
    priority1.OCCURRENCEIDENTIFIER,
    priority1.UPDATEIDENTIFIER,
    priority1.ATTRIBUTEINTNAME,
    priority1.ATTRIBUTEINTVALUE,
    priority1.CHANGESTART,
    priority1.CHANGEEND,
    priority1.INSERTDTS,
    priority1.UPDATEDTS
FROM
    OCCURRENCE_INT_ATTRS as priority1
WHERE
         priority1.UPDATEIDENTIFIER =
        (SELECT MAX(priority2.UPDATEIDENTIFIER)
         FROM
            OCCURRENCE_INT_ATTRS as priority2   
         WHERE priority2.OCCURRENCEIDENTIFIER=priority1.OCCURRENCEIDENTIFIER
         AND priority2.ATTRIBUTEINTNAME='Impact'
                 )

I am trying to run query:

SELECT     TABLE_EVENT.ISROOT, TABLE_EVENT.EVENTSTATE, TABLE_EVENT.CLEAREDAT,
    TABLE_EVENT.NOTIFIEDAT, TABLE_EVENT.DURATION, TABLE_EVENT.ELEMENTCLASS,
    TABLE_EVENT.CLASS, TABLE_EVENT.ELEMENTINSTANCE, TABLE_EVENT.INSTANCE,
    VIEW_OCCURRENCE_PRIORITY.ATTRIBUTEINTVALUE, TABLE_EVENT.OCCURRENCEIDENTIFIER
 FROM   TABLE_EVENT TABLE_EVENT INNER JOIN VIEW_OCCURRENCE_PRIORITY VIEW_OCCURRENCE_PRIORITY
ON
    ((TABLE_EVENT.OCCURRENCEIDENTIFIER=VIEW_OCCURRENCE_PRIORITY.OCCURRENCEIDENTIFIER) AND
    (TABLE_EVENT.INSERTDTS=VIEW_OCCURRENCE_PRIORITY.INSERTDTS)) AND
    (TABLE_EVENT.UPDATEDTS=VIEW_OCCURRENCE_PRIORITY.UPDATEDTS)
 WHERE  TABLE_EVENT.ISROOT=1 AND (TABLE_EVENT.EVENTSTATE=0 AND
    (TABLE_EVENT.CLEAREDAT>={ts '1987-05-22 18:14:39'} AND
    TABLE_EVENT.CLEAREDAT < {ts '2006-07-30 18:14:40'}) OR TABLE_EVENT.EVENTSTATE=1)


The problem is the result from this query returns me after 25 minutes later. Can anybody suggest
anything about What the problem is?

Thanx...

Mustafa


 		
---------------------------------
Do you Yahoo!?
 Get on board. You're invited to try the new Yahoo! Mail Beta.
Mime
View raw message