473,702 Members | 2,436 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Posgre view is performing very slow

1 New Member
Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE VIEW vwstmACC AS 
  2.  SELECT DISTINCT ON (stmnt.chargeservicelineid) stmnt.created, stmnt.createdby, stmnt.lastupdated, stmnt.updatedby, stmnt.accountid, stmnt.practiceid, stmnt.chargegroupid, stmnt.chargeservicelineid, stmnt.patientid, stmnt.paymentpostingid, stmnt.chargeadjustmentid, stmnt.locationid, stmnt.specialityid, stmnt.placeofserviceid, stmnt.billtopatientpayerid, stmnt.paymentid, stmnt.patientdisplayid, stmnt.dateofservice, stmnt.posteddate, stmnt.status, stmnt.voideddate, stmnt.originalclaimreferencenumber, stmnt.adjustmentreason, stmnt.adjdescription, stmnt.adjposteddate, stmnt.name, stmnt.patientaspayer, stmnt.patfirstname, stmnt.patmiddlename, stmnt.patlastname, stmnt.chartnum, stmnt.sex, stmnt.healthcardnumber, stmnt.dob, stmnt.units, stmnt.fee, stmnt.chargeamount, stmnt.allowedamount, stmnt.deductibleamount, stmnt.coinsuranceamount, stmnt.copaymentamount, stmnt.totalpayments, stmnt.totaladjustments, stmnt.balanceamount, stmnt.voidedchargelinkid, stmnt.roworder, stmnt.methodofpayment, stmnt.payment_posteddate, stmnt.claimnumber, stmnt.claimsubstatus, stmnt.onholdreason, stmnt.claimvoideddate, stmnt.servicecodename, stmnt.servicecodenamedesc, stmnt.locationname, stmnt.locationdescription, stmnt.payer, stmnt.payercode, stmnt.payerlastname, stmnt.payerfirstname
  3.    FROM ( SELECT DISTINCT ON (csl.chargeservicelineid, pAYpOSTT.paymentpostingid, tva.chargeadjustmentid) cg.created, cg.createdby, cg.lastupdated, cg.updatedby, cg.accountid, cg.practiceid, cg.chargegroupid, csl.chargeservicelineid, cg.patientid, pAYpOSTT.paymentpostingid, tva.chargeadjustmentid, cg.locationid, cg.specialityid, cg.placeofserviceid, cg.billtopatientpayerid, pAYpOSTT.paymentid, pat_info.patientdisplayid, csl.dateofservicestart AS dateofservice, cg.posteddate, cg.status, cg.voideddate, cg.originalclaimreferencenumber, tva.adjustmentreason, tva.description AS adjdescription, tva.posteddate AS adjposteddate, practice.name, 
  4.                 CASE
  5.                     WHEN cg.patientaspayer = true THEN 'Patient'::text
  6.                     ELSE '3RD PARTY'::text
  7.                 END AS patientaspayer, pat_info.firstname AS patfirstname, pat_info.mi AS patmiddlename, pat_info.lastname AS patlastname, pat_info.chartnum, pat_info.sex, pat_info.healthcardnumber, pat_info.dob, csl.units, csl.fee, csl.chargeamount, csl.allowedamount, csl.deductibleamount, csl.coinsuranceamount, csl.copaymentamount, ( SELECT sum(p1.postedamount) AS sum
  8.                    FROM pAYpOSTT p1
  9.                   WHERE cg.accountid = p1.accountid AND cg.practiceid = p1.practiceid AND csl.chargeservicelineid = p1.chargeservicelineid AND p1.status::text = 'POSTED'::text) AS totalpayments, ( SELECT sum(a1.amount) AS sum
  10.                    FROM vwadjustments a1
  11.                   WHERE cg.accountid = a1.accountid AND cg.practiceid = a1.practiceid AND csl.chargeservicelineid = a1.chargeservicelineid) AS totaladjustments, csl.balanceamount, csl.voidedchargelinkid, csl.roworder, payments.methodofpayment, pAYpOSTT.posteddate AS payment_posteddate, claim.claimnumber, claim.substatus AS claimsubstatus, claim.onholdreason, claim.voideddate AS claimvoideddate, enterprise_servicecodes.codename AS servicecodename, enterprise_servicecodes.description AS servicecodenamedesc, pRACloc.locationname, pRACloc.description AS locationdescription, vwpatientpayers.payer, vwpatientpayers.payercode, vwpatientpayers.lastname AS payerlastname, vwpatientpayers.firstname AS payerfirstname
  12.            FROM charge_group cg
  13.       JOIN charge_service_line csl ON cg.accountid = csl.accountid AND cg.practiceid = csl.practiceid AND cg.chargegroupid = csl.chargegroupid AND cg.status::text = csl.status::text AND csl.substatus::text <> '2'::text AND cg.status::text = 'POSTED'::text
  14.    LEFT JOIN enterprise_servicecodes ON enterprise_servicecodes.entservicecodeid = csl.entservicecodeid
  15.    LEFT JOIN pAYpOSTT ON cg.accountid = pAYpOSTT.accountid AND cg.practiceid = pAYpOSTT.practiceid AND csl.chargeservicelineid = pAYpOSTT.chargeservicelineid AND pAYpOSTT.status::text = 'POSTED'::text
  16.    LEFT JOIN payments ON cg.accountid = payments.accountid AND cg.practiceid = payments.practiceid AND payments.paymentid = pAYpOSTT.paymentid AND payments.status::text = 'POSTED'::text
  17.    LEFT JOIN claim ON cg.accountid = claim.accountid AND cg.practiceid = claim.practiceid AND cg.chargegroupid = claim.chargegroupid
  18.    JOIN pRACloc ON cg.accountid = pRACloc.accountid AND cg.practiceid = pRACloc.practiceid AND cg.locationid = pRACloc.locationid
  19.    LEFT JOIN vwpatientpayers ON cg.accountid = vwpatientpayers.accountid AND cg.patientid = vwpatientpayers.patientid AND cg.billtopatientpayerid = vwpatientpayers.patientpayerid
  20.    JOIN pat_info ON cg.accountid = pat_info.accountid AND cg.patientid = pat_info.patientid
  21.    LEFT JOIN vwadjustments tva ON cg.accountid = tva.accountid AND cg.practiceid = tva.practiceid AND csl.chargeservicelineid = tva.chargeservicelineid
  22.    JOIN practice ON cg.accountid = practice.accountid AND cg.practiceid = practice.practiceid
  23.   WHERE (date(csl.posteddate) <> date(csl.voideddate) OR csl.voideddate IS NULL OR csl.substatus::text = '1'::text) AND cg.status::text = 'POSTED'::text
  24.   ORDER BY csl.chargeservicelineid, pAYpOSTT.paymentpostingid, tva.chargeadjustmentid, cg.chargegroupid, cg.dateofservice, csl.roworder) stmnt
  25.   ORDER BY stmnt.chargeservicelineid, stmnt.paymentpostingid, stmnt.chargeadjustmentid, stmnt.created DESC;
  26.  
  27. ALTER TABLE vwstmACC
  28.   OWNER TO postgr;
Aug 12 '15 #1
1 2170
Rabbit
12,516 Recognized Expert Moderator MVP
Your code is formatted terribly. It makes it very hard to diagnose so I had to reformat it just to be able to understand it.

There's lots of reasons your code is going to run slow. Too many to try to go over at this very moment. Instead, read our article on optimizing SQL: http://bytes.com/topic/sql-server/in...ze-sql-queries

It was written for SQL Server but many of the same concepts apply to all SQL engines. The syntax will be slightly different but equivalents exist in all the major database management systems.

After applying the concepts in that article, please post the updated SQL for further help. And for Pete's sake, please format the SQL so it is easier to read.

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT ON (stmnt.chargeservicelineid) 
  2.     stmnt.created, 
  3.     stmnt.createdby, 
  4.     stmnt.lastupdated, 
  5.     stmnt.updatedby, 
  6.     stmnt.accountid, 
  7.     stmnt.practiceid, 
  8.     stmnt.chargegroupid, 
  9.     stmnt.chargeservicelineid, 
  10.     stmnt.patientid, 
  11.     stmnt.paymentpostingid, 
  12.     stmnt.chargeadjustmentid, 
  13.     stmnt.locationid, 
  14.     stmnt.specialityid, 
  15.     stmnt.placeofserviceid, 
  16.     stmnt.billtopatientpayerid, 
  17.     stmnt.paymentid, 
  18.     stmnt.patientdisplayid, 
  19.     stmnt.dateofservice, 
  20.     stmnt.posteddate, 
  21.     stmnt.status, 
  22.     stmnt.voideddate, 
  23.     stmnt.originalclaimreferencenumber, 
  24.     stmnt.adjustmentreason, 
  25.     stmnt.adjdescription, 
  26.     stmnt.adjposteddate, 
  27.     stmnt.name, 
  28.     stmnt.patientaspayer, 
  29.     stmnt.patfirstname, 
  30.     stmnt.patmiddlename, 
  31.     stmnt.patlastname, 
  32.     stmnt.chartnum, 
  33.     stmnt.sex, 
  34.     stmnt.healthcardnumber, 
  35.     stmnt.dob, 
  36.     stmnt.units, 
  37.     stmnt.fee, 
  38.     stmnt.chargeamount, 
  39.     stmnt.allowedamount, 
  40.     stmnt.deductibleamount, 
  41.     stmnt.coinsuranceamount, 
  42.     stmnt.copaymentamount, 
  43.     stmnt.totalpayments, 
  44.     stmnt.totaladjustments, 
  45.     stmnt.balanceamount, 
  46.     stmnt.voidedchargelinkid, 
  47.     stmnt.roworder, 
  48.     stmnt.methodofpayment, 
  49.     stmnt.payment_posteddate, 
  50.     stmnt.claimnumber, 
  51.     stmnt.claimsubstatus, 
  52.     stmnt.onholdreason, 
  53.     stmnt.claimvoideddate, 
  54.     stmnt.servicecodename, 
  55.     stmnt.servicecodenamedesc, 
  56.     stmnt.locationname, 
  57.     stmnt.locationdescription, 
  58.     stmnt.payer, 
  59.     stmnt.payercode, 
  60.     stmnt.payerlastname, 
  61.     stmnt.payerfirstname
  62.  
  63. FROM 
  64.     (
  65.         SELECT DISTINCT ON (csl.chargeservicelineid, pAYpOSTT.paymentpostingid, tva.chargeadjustmentid) 
  66.             cg.created, 
  67.             cg.createdby, 
  68.             cg.lastupdated, 
  69.             cg.updatedby, 
  70.             cg.accountid, 
  71.             cg.practiceid, 
  72.             cg.chargegroupid, 
  73.             csl.chargeservicelineid, 
  74.             cg.patientid, 
  75.             pAYpOSTT.paymentpostingid, 
  76.             tva.chargeadjustmentid, 
  77.             cg.locationid, 
  78.             cg.specialityid, 
  79.             cg.placeofserviceid, 
  80.             cg.billtopatientpayerid, 
  81.             pAYpOSTT.paymentid, 
  82.             pat_info.patientdisplayid, 
  83.             csl.dateofservicestart AS dateofservice, 
  84.             cg.posteddate, 
  85.             cg.status, 
  86.             cg.voideddate, 
  87.             cg.originalclaimreferencenumber, 
  88.             tva.adjustmentreason, 
  89.             tva.description AS adjdescription, 
  90.             tva.posteddate AS adjposteddate, 
  91.             practice.name,
  92.  
  93.             CASE
  94.                 WHEN cg.patientaspayer = true THEN 'Patient'::text
  95.                 ELSE '3RD PARTY'::text
  96.             END AS patientaspayer, 
  97.  
  98.             pat_info.firstname AS patfirstname, 
  99.             pat_info.mi AS patmiddlename, 
  100.             pat_info.lastname AS patlastname, 
  101.             pat_info.chartnum, 
  102.             pat_info.sex, 
  103.             pat_info.healthcardnumber, 
  104.             pat_info.dob, 
  105.             csl.units, 
  106.             csl.fee, 
  107.             csl.chargeamount, 
  108.             csl.allowedamount, 
  109.             csl.deductibleamount, 
  110.             csl.coinsuranceamount, 
  111.             csl.copaymentamount, 
  112.  
  113.             (
  114.                 SELECT sum(p1.postedamount) AS sum
  115.                 FROM pAYpOSTT p1
  116.                 WHERE 
  117.                     cg.accountid = p1.accountid AND 
  118.                     cg.practiceid = p1.practiceid AND 
  119.                     csl.chargeservicelineid = p1.chargeservicelineid AND 
  120.                     p1.status::text = 'POSTED'::text
  121.             ) AS totalpayments, 
  122.  
  123.             (
  124.                 SELECT sum(a1.amount) AS sum
  125.                 FROM vwadjustments a1
  126.                 WHERE 
  127.                     cg.accountid = a1.accountid AND 
  128.                     cg.practiceid = a1.practiceid AND 
  129.                     csl.chargeservicelineid = a1.chargeservicelineid
  130.             ) AS totaladjustments, 
  131.  
  132.             csl.balanceamount, 
  133.             csl.voidedchargelinkid, 
  134.             csl.roworder, 
  135.             payments.methodofpayment, 
  136.             pAYpOSTT.posteddate AS payment_posteddate, 
  137.             claim.claimnumber, 
  138.             claim.substatus AS claimsubstatus, 
  139.             claim.onholdreason, 
  140.             claim.voideddate AS claimvoideddate, 
  141.             enterprise_servicecodes.codename AS servicecodename, 
  142.             enterprise_servicecodes.description AS servicecodenamedesc, 
  143.             pRACloc.locationname, 
  144.             pRACloc.description AS locationdescription, 
  145.             vwpatientpayers.payer, 
  146.             vwpatientpayers.payercode, 
  147.             vwpatientpayers.lastname AS payerlastname, 
  148.             vwpatientpayers.firstname AS payerfirstname
  149.  
  150.         FROM 
  151.             charge_group cg
  152.  
  153.             JOIN charge_service_line csl ON 
  154.                 cg.accountid = csl.accountid AND 
  155.                 cg.practiceid = csl.practiceid AND 
  156.                 cg.chargegroupid = csl.chargegroupid AND 
  157.                 cg.status::text = csl.status::text AND 
  158.                 csl.substatus::text <> '2'::text AND 
  159.                 cg.status::text = 'POSTED'::text
  160.  
  161.             LEFT JOIN enterprise_servicecodes ON 
  162.                 enterprise_servicecodes.entservicecodeid = csl.entservicecodeid
  163.  
  164.             LEFT JOIN pAYpOSTT ON 
  165.                 cg.accountid = pAYpOSTT.accountid AND 
  166.                 cg.practiceid = pAYpOSTT.practiceid AND 
  167.                 csl.chargeservicelineid = pAYpOSTT.chargeservicelineid AND 
  168.                 pAYpOSTT.status::text = 'POSTED'::text
  169.  
  170.             LEFT JOIN payments ON 
  171.                 cg.accountid = payments.accountid AND 
  172.                 cg.practiceid = payments.practiceid AND 
  173.                 payments.paymentid = pAYpOSTT.paymentid AND 
  174.                 payments.status::text = 'POSTED'::text
  175.  
  176.             LEFT JOIN claim ON 
  177.                 cg.accountid = claim.accountid AND 
  178.                 cg.practiceid = claim.practiceid AND 
  179.                 cg.chargegroupid = claim.chargegroupid
  180.  
  181.             JOIN pRACloc ON 
  182.                 cg.accountid = pRACloc.accountid AND 
  183.                 cg.practiceid = pRACloc.practiceid AND 
  184.                 cg.locationid = pRACloc.locationid
  185.  
  186.             LEFT JOIN vwpatientpayers ON 
  187.                 cg.accountid = vwpatientpayers.accountid AND 
  188.                 cg.patientid = vwpatientpayers.patientid AND 
  189.                 cg.billtopatientpayerid = vwpatientpayers.patientpayerid
  190.  
  191.             JOIN pat_info ON 
  192.                 cg.accountid = pat_info.accountid AND 
  193.                 cg.patientid = pat_info.patientid
  194.  
  195.             LEFT JOIN vwadjustments tva ON 
  196.                 cg.accountid = tva.accountid AND 
  197.                 cg.practiceid = tva.practiceid AND 
  198.                 csl.chargeservicelineid = tva.chargeservicelineid
  199.  
  200.             JOIN practice ON
  201.                 cg.accountid = practice.accountid AND
  202.                 cg.practiceid = practice.practiceid
  203.  
  204.         WHERE 
  205.             (
  206.                 date(csl.posteddate) <> date(csl.voideddate) OR 
  207.                 csl.voideddate IS NULL OR 
  208.                 csl.substatus::text = '1'::text
  209.             ) AND 
  210.             cg.status::text = 'POSTED'::text
  211.  
  212.         ORDER BY 
  213.             csl.chargeservicelineid, 
  214.             pAYpOSTT.paymentpostingid, 
  215.             tva.chargeadjustmentid, 
  216.             cg.chargegroupid, 
  217.             cg.dateofservice, 
  218.             csl.roworder
  219.     ) stmnt
  220.  
  221. ORDER BY 
  222.     stmnt.chargeservicelineid, 
  223.     stmnt.paymentpostingid, 
  224.     stmnt.chargeadjustmentid, 
  225.     stmnt.created DESC;
Aug 12 '15 #2

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

Similar topics

9
2382
by: Neil | last post by:
I've been discussing here a SQL 7 view which scrolls slowly when linked to an Access 2000 MDB. After trying various things, I've distilled it down to the following: when the linked view has a virtual primary key, it is slow; when there is no virtual primary key, it scrolls quickly. Looking at Profiler, I see that when there is no virtual primary key, Access sends a simple select command to SQL Server. However, when there is a virtual...
12
2635
by: Neil | last post by:
I previously posted re. this, but thought I'd try again with a summary of facts. I have an Access 2000 MDB with a SQL Server 7 back end. There is a view that is linked to the database via ODBC and has been in place for several years without any performance problems. Recently I added a couple of fields to the output of the view, and it became very slow when scrolling. When just opened in the database window, the linked view takes about...
2
4616
by: Steve K | last post by:
Hi, I'm on a Mac, OSX version 10.3.5, 1.25GB SDRAM, 400 MHz PowerPC G4 I have DreamweaverMX 2004, version 7.0.1. It is painfully slow!! It's very slow working in design view, and almost just as slow working in code view. I'm always in split view so I can see what I've done in the code. So I click into the design view and it takes almost 5-8 seconds for the changes I just made in the code to "happen."
9
3002
by: AnandaSim | last post by:
Hi All, I've had Access 97, 2000 connections to the corporate Oracle database for a few years now - but seldom use it. When I did use it years ago, performance was not fast but the features were good enough. Now, the version of Access is 2003 with Office SP1 applied, Windows XP against Oracle 9.2. And attached tables are real slow - 5 mins to get to datasheet view and data is incorrect - the same row is repeated several times even in...
6
1938
by: VM | last post by:
I have a 35000 line datagrid and updating one field in all 35000 rows takes an eternity. How come? Since I don't have the datasource available (it may be a table or view), in my example I do it like this: for (int i=0;i<34000;i++) //the first 2000 records took 2 mins. { dataGrid_auditAddress = "MARKED"; } Any help would be appreciated.
50
5714
by: diffuser78 | last post by:
I have just started to learn python. Some said that its slow. Can somebody pin point the issue. Thans
8
5103
by: Sham | last post by:
I am trying to perform the following query on a table that has been indexed using Full Text Search. The table contains multiple columns than have been indexed. (Below, all xml columns are indexed). dbo.maintable(ProfileID int pk) dbo.fts_table(ProfileID int pk fk, col1 xml, col2 xml, col3 xml) I want to perform a query that will return any rows that contain ‘x’ and ‘y’ in any columns. I.e. ‘x’ could be in col1 and ‘y’ could be in
2
9839
by: existential.philosophy | last post by:
This is a new problem for me: I have some queries that open very slowly in design view. My benchmark query takes about 20 minutes to open in design view. That same query takes about 20 minutes to open in datasheet view. As an experiment, I deleted all rows in all tables; after that, the query took only seconds to open in both design view and datasheet view. From these facts, I conclude that Access is evaluating the query when I go to...
1
1917
by: muradjuber | last post by:
hello all i have view on live database take big time and i dont have other ideas to solve it it consist of two tables and they are joined right and i make analyze the two tables and it not worked
0
8652
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
8979
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
7829
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5907
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4412
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4667
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3104
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2399
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2036
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.