0
SELECT version_stamp, 
           pscdesc_id, 
           psc, 
           pscdesctext 
    FROM   pscdesc_history 
    WHERE  ( psc IN (SELECT psc 
                     FROM   pso_history AS c 
                     WHERE  ( c.project = 121 
                               OR c.project IS NULL ) 
                     AND EXISTS (SELECT * FROM   pso_history AS x 
                                        WHERE  x.project = 121 
                                        AND x.pso_id = c.pso_id )) 
              OR psc IS NULL ) 
              AND EXISTS (SELECT * FROM   pscdesc_history AS xyy 
                       WHERE  xyy.psc IN (SELECT psc 
                                          FROM   pso_history AS c 
                                          WHERE  ( c.project = 121 
                                                   OR c.project IS NULL ) 
                                                 AND EXISTS (SELECT * FROM   pso_history AS x 
                                                             WHERE  x.project = 121 
                                                                    AND 
                                                            x.pso_id = c.pso_id)) 
                       AND xyy.pscdesc_id = pscdesc_history.pscdesc_id ) 

Maybe in prosa what I am trying to do:

Entries in the pscdesc_history table represent changes. Each entry is a change.

so a record with pscdesc_id can get changed, say pscdesctext gets changed. That gives us a new entry with the same pscdesc_id but a new version_stamp and pscdesctext the rest can be empty. To which project these entries (crucial, since I only want to deal with one project) belong, I don't know. I have to look at the table pso_history. psc is the foreign key to a third table.

Why don't I just join? Since this is a table full of records of changes, the actual foreign key psc might be null. The same applies for pso_history where the forgein key project might be null too.

So i have to get all records of pscdesc_history that are related to a record in pso_history with a pso_history.project of 121 or that are related to a record with pso_history.project that was at some point 121.

Is there something you can see where you would think I could speed up things ? replace the AND EXISTS with something else?

1

Use DISTINCT everywhere you can and I don't understand why not to join instead of correlating

                 SELECT psc 
                 FROM   pso_history AS c 
                 WHERE  ( c.project = 121 
                           OR c.project IS NULL ) 
                 AND EXISTS (SELECT * FROM   pso_history AS x 
                                    WHERE  x.project = 121 
                                    AND x.pso_id = c.pso_id )


                 SELECT DISTINCT c.psc
                 FROM pso_history AS c
                 JOIN pso_history AS x 
                 ON      ( c.project = 121 OR c.project IS NULL )
                    AND   x.project = 121 
                    AND   x.pso_id = c.pso_id

And the entire query will be

SELECT  xyx.version_stamp, xyx.pscdesc_id, xyx.psc, xyx.pscdesctext 
FROM    pscdesc_history AS xyx
JOIN    pscdesc_history AS xyy
ON  (xyx.psc IN (SELECT DISTINCT c.psc
                 FROM pso_history AS c
                 JOIN pso_history AS X 
                 ON      ( c.project = 121 OR c.project IS NULL )
                    AND   x.project = 121 
                    AND   x.pso_id = c.pso_id) 
     OR xyx.psc IS NULL)
AND xyy.pscdesc_id = xyx.pscdesc_id
AND  xyy.psc IN ( SELECT DISTINCT c.psc
                 FROM pso_history AS c
                 JOIN pso_history AS X 
                 ON      ( c.project = 121 OR c.project IS NULL )
                    AND   x.project = 121 
                    AND   x.pso_id = c.pso_id)

This must work twice farster I think, check the indexes also.

2
  • what do you mean with correlating? – Toskan Feb 11 '16 at 10:29
  • that seems like a nice query – Toskan Feb 11 '16 at 10:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.