Patient dashboard loading is delayed due to the slow execution of demographics.php

OpenEMR Development
,
1

The patient dashboard takes 2-3 minutes to load when a patient has over 500 encounters, causing significant delays in workflow. The issue appears to be related to processing a large volume of encounter data during dashboard loading. Optimizing performance in such cases is essential to improve usability and efficiency.

Please investigate and suggest a resolution.

2

@satya

I have faced same issue. I fixed it and after that it will take 1-2 seconds only.
For this I have optimized query.

Let me create new Pull request and my code over there and share with you soon.

3

@satya Added solution at

Replace the get_patient_balance method in library/patient.inc.php file

function get_patient_balance($pid, $with_insurance = false, $eid = false)
{
$bindingValue = ;
if ($with_insurance) {
$bindingValue = 1;
} else {
$bindingValue = 0;
}
$bindingValue = $pid;

$appendSql = '';
if ($eid) {
    $appendSql = " AND encounter = ?";
    $bindingValue[] = $eid;
}
$sql = "select
  sum(
    CASE WHEN ? = 1
    AND frresult.last_level_closed < frresult.insurance_data_count
    AND frresult.stmt_count = 0 THEN (
      SELECT
        COALESCE(
          SUM(b.fee),
          0.00
        ) - COALESCE(
          SUM(a.pay_amount),
          0.00
        ) AS ptbal
      FROM
        billing b
        LEFT JOIN ar_activity a ON b.pid = a.pid
        AND b.encounter = a.encounter
        AND a.deleted IS NULL
        AND a.payer_type = 0
      WHERE
        b.pid = frresult.pid
        AND b.encounter = frresult.encounter
        AND b.code_type = 'copay'
        AND b.activity = 1
    ) ELSE (

SELECT
( (
SELECT
COALESCE(SUM(fee), 0)
FROM
billing
WHERE
pid = frresult.pid
AND encounter = frresult.encounter
AND activity = 1
) + (
SELECT
COALESCE(SUM(fee), 0)
FROM
drug_sales
WHERE
pid = frresult.pid
AND encounter = frresult.encounter
) - (
SELECT
COALESCE(SUM(pay_amount), 0) + COALESCE(SUM(adj_amount), 0)
FROM
ar_activity
WHERE
deleted IS NULL
AND pid = frresult.pid
AND encounter = frresult.encounter
)
) as amount
) END
) AS balance
from
(
SELECT
fe.pid,
fe.encounter,
fe.last_level_billed,
fe.last_level_closed,
fe.stmt_count,
(
SELECT
count(idata.id)
FROM
insurance_data idata
WHERE
idata.pid = fe.pid
AND idata.type IN (
‘primary’, ‘secondary’, ‘tertiary’
)
AND (
idata.date <= DATE(fe.date)
OR idata.date IS NULL
)
AND idata.provider IS NOT NULL
AND idata.provider != ‘’
ORDER BY
FIELD(
idata.type, ‘primary’, ‘secondary’,
‘tertiary’
),
idata.date DESC
LIMIT
3
) as insurance_data_count
FROM
form_encounter fe
WHERE
pid = ? " . $appendSql . "
) as frresult ;";

$result = sqlQuery($sql, $bindingValue);
$balance = !empty($result['balance']) ? number_format($result['balance'], 2, '.', '') : "0.00";
return sprintf('%01.2f', $balance);

}

patient.inc.php (3.3 KB)

Please implement with your code and let me know its works or not.

@sjpadgett Please help me to add this fix in upcoming release.

4

@Valcz , Thanks for your prompt update.

Let me try your suggested solution.