473,241 Members | 1,703 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,241 software developers and data experts.

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
Jul 12 '21 #1
2 11389
Banfa
9,065 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.
Jul 21 '21 #2
Chandler
3 2Bits
I would suggest
Step 1: rewrite the query body to
Expand|Select|Wrap|Line Numbers
  1.         SELECT o1.* FROM "sample"."order" o1 JOIN "sample".patient p1 ON o1.patient_id = p1.id
  2.         WHERE o1.sample_id != _sampleId AND p1.mrn = _mrn
  3.           AND o1.collection_date is not null AND o1.collection_date >= _createdDate
  4.  
  5.         union 
  6.  
  7.         SELECT o1.* FROM "sample"."order" o1 JOIN "sample".patient p1 ON o1.patient_id = p1.id
  8.         WHERE o1.sample_id != _sampleId AND p1.mrn = _mrn
  9.           AND o1.collection_date is null AND o1.receipt_date is not null
  10.                 AND o1.receipt_date >= _createdDate
  11.  
  12.         UNION
  13.  
  14.         (
  15.         SELECT o2.* FROM "sample"."order" o2 JOIN "sample".patient p2 ON o2.patient_id = p2.id
  16.          WHERE o2.status in(1,2,3) AND o2.sample_id != _sampleId
  17.          AND p2.mrn = _mrn
  18.            AND o2.collection_date is not null AND o2.collection_date < _createdDate
  19.            UNION ALL
  20.            SELECT o2.* FROM "sample"."order" o2 JOIN "sample".patient p2 ON o2.patient_id = p2.id
  21.          WHERE o2.status in(1,2,3) AND o2.sample_id != _sampleId
  22.          AND p2.mrn = _mrn and o2.collection_date is null AND o2.receipt_date is not null
  23.                  AND o2.receipt_date < _createdDate)
  24.  
  25.                  ORDER by created_date DESC LIMIT 1)
  26.  
  27.         ORDER by collection_date DESC, receipt_date DESC, created_date DESC;
  28.  
Step2. use index advise tool for pg(such as Paw Index Advisor) to accelerate the performance.
Jul 6 '22 #3

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

Similar topics

0
by: gord barq | last post by:
I have this query which does a left outer join and it takes forever (like half a day). Here are the results of an explain analysis. mysql> explain SELECT count(searchresult.title) AS number,...
3
by: Robert | last post by:
I am having performance issues on a SQL query in Access. My query is accessing and joining several tables (one very large one). The tables are linked ODBC. The client submits the query to the...
3
by: Alexander Anderson | last post by:
I have a DELETE statement that deletes duplicate data from a table. It takes a long time to execute, so I thought I'd seek advice here. The structure of the table is little funny. The following is...
18
by: Ron Johnson | last post by:
On Mon, 2003-08-25 at 16:28, Gregory S. Williamson wrote: > One of our sysads sent this link ... wondering if there is any comment on it from the world of actual users of linux and a database. > >...
6
by: Ryan | last post by:
I came across a situation that I've been unable to explain and was hoping somebody had an answer: I had written an update query which was taking about 8 seconds to run and considered it too...
1
by: frizzle | last post by:
Hi there, I'm building a forum with a mysql backend. The forum has the following structure: - category |--> forum |--> thread |--> replies Once the user has defined a forum (id), i first...
1
by: mamapossible | last post by:
Hi, I've spent hours trying to optimize this simple query: SELECT count(sites_jobs.id) as jobCount, sites_jobs_categories.frn_site_id, sites_jobs_categories.id, sites_jobs_categories.name,...
0
by: rashmigaikwad | last post by:
Hi All, I need help in optimizing the query mentioned below: SELECT SUM(CASE WHEN PROD_TYP='HBRMC' AND INC_MULTIPLE < 2.50 AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) ...
1
by: thoolsur | last post by:
how can i optimize my query using cost based optimizer and how i can select cost of query. please explain me with examples.
1
by: john111 | last post by:
After searching on the internet for 3 hours i couldn't find a single solution of it .... Can you please show me a way to fix it I am using web query function of excel to pull data from a stock...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.