468,743 Members | 2,157 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,743 developers. It's quick & easy.

Optimizing PgSQL Query function performance

I really need advice on the below, trying to use DB function getpreviousorders..

NOTE: But this DB function got much faster if run directly on DB, without data reference entity data by spring hibernate code in Order entity.

Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE FUNCTION "sample"."getpreviousorders"("_sampleid" varchar, "_mrn" varchar, "_createddate" timestamp)
  2.     RETURNS SETOF "sample"."order" AS $BODY$
  3. BEGIN
  4.     RETURN QUERY
  5.         SELECT o1.* FROM "sample"."order" o1 JOIN "sample".patient p1 ON o1.patient_id = p1.id
  6.         WHERE o1.sample_id != _sampleId AND p1.mrn = _mrn
  7.           AND ((o1.collection_date is not null AND o1.collection_date >= _createdDate)
  8.             OR (o1.collection_date is null AND o1.receipt_date is not null
  9.                 AND o1.receipt_date >= _createdDate))
  10.         UNION
  11.         (SELECT o2.* FROM "sample"."order" o2 JOIN "sample".patient p2 ON o2.patient_id = p2.id
  12.          WHERE (o2.status = 1 or o2.status = 2 OR o2.status = 3) AND o2.sample_id != _sampleId
  13.          AND p2.mrn = _mrn
  14.            AND ((o2.collection_date is not null AND o2.collection_date < _createdDate)
  15.              OR (o2.collection_date is null AND o2.receipt_date is not null
  16.                  AND o2.receipt_date < _createdDate)) ORDER by created_date DESC LIMIT 1)
  17.         ORDER by collection_date DESC, receipt_date DESC, created_date DESC;
  18. END; $BODY$
  19.     LANGUAGE plpgsql VOLATILE
  20.                      COST 100
  21.                      ROWS 1000
2 Weeks Ago #1
1 2537
Banfa
9,057 Expert Mod 8TB
Since the SELECT ... FROM ... part of both halves of the union are the same can you not just use a single select statement that combines the 2 where clauses (into a hideously large clause). I think that will result in not having to process every record twice, once for each SELECT.
1 Week Ago #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by gord barq | last post: by
3 posts views Thread by Alexander Anderson | last post: by
18 posts views Thread by Ron Johnson | last post: by
1 post views Thread by frizzle | last post: by
1 post views Thread by mamapossible | last post: by
1 post views Thread by thoolsur | last post: by
reply views Thread by zhoujie | last post: by
xarzu
2 posts views Thread by xarzu | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.