[jira] [Resolved] (CONNECTORS-1027) Database performance optimization

["Karl Wright (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/CONNECTORS-1027?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Karl Wright resolved CONNECTORS-1027.
-------------------------------------
    Resolution: Fixed

> Database performance optimization
> ---------------------------------
>
>                 Key: CONNECTORS-1027
>                 URL: https://issues.apache.org/jira/browse/CONNECTORS-1027
>             Project: ManifoldCF
>          Issue Type: Task
>          Components: Framework core
>    Affects Versions: ManifoldCF 1.7
>            Reporter: Karl Wright
>            Assignee: Karl Wright
>             Fix For: ManifoldCF 2.0
>
>         Attachments: CONNECTORS-1027.conjunction.patch, CONNECTORS-1027.ui.patch
>
>
> Some database queries seem to perform better against postgresql in different form.  For example:
> {code}
>  SELECT
>  t0.id,t0.jobid,t0.dochash,t0.docid,t0.status,t0.failtime,t0.failcount,
>  t0.priorityset FROM jobqueue t0
>  WHERE t0.status IN ('P','G')  AND t0.checkaction='R' AND t0.checktime
>  <= 1407246846166
>  AND EXISTS (
>    SELECT 'x' FROM jobs t1
>    WHERE t1.status  IN ('A','a')  AND t1.id=t0.jobid  AND t1.priority=5
>  )
>  AND NOT EXISTS (
>    SELECT 'x' FROM jobqueue t2
>    WHERE t2.dochash=t0.dochash AND t2.status IN
>  ('A','F','a','f','D','d') AND t2.jobid!=t0.jobid
>  )
>  AND NOT EXISTS (
>    SELECT 'x' FROM prereqevents t3,events t4
>    WHERE t0.id=t3.owner AND t3.eventname=t4.name
>  )
>  ORDER BY t0.docpriority ASC
>  LIMIT 480;
> {code}
> ...apparently performs better on some versions of postgresql when written like this:
> {code}
>  SELECT
>  t0.id,t0.jobid,t0.dochash,t0.docid,t0.status,t0.failtime,t0.failcount,
>  t0.priorityset FROM jobqueue t0
>  WHERE (t0.status = 'P' OR t0.status = 'G')  AND t0.checkaction='R' AND
>  t0.checktime <= 1407246846166
> AND EXISTS (
>    SELECT 'x' FROM jobs t1
>    WHERE (t1.status = 'A' OR t1.status = 'a')  AND t1.id=t0.jobid  AND
>  t1.priority=5
>  )
>  AND NOT EXISTS (
>    SELECT 'x' FROM jobqueue t2
>    WHERE t2.dochash=t0.dochash AND (t2.status = 'A' OR t2.status = 'F'
>  OR t2.status = 'a' OR t2.status = 'f' OR t2.status = 'D' OR t2.status
>  = 'd')
>    AND t2.jobid!=t0.jobid
>  )
>  AND NOT EXISTS (
>    SELECT 'x' FROM prereqevents t3,events t4
>    WHERE t0.id=t3.owner AND t3.eventname=t4.name
>  )
>  ORDER BY t0.docpriority ASC
>  LIMIT 480;
> {code}



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)