How can I optimize below query in SQL

Question

0.00/5 (No votes)

See more:

Below is the query that contains condition of sub query and case condition with it

select
pe.person_id as patientid,
t.trans_id as noteid,
convert(varchar(50),pe.enc_nbr) +'_'+ convert(varchar(50),PE.practice_id) as ticketnumber,
pe.enc_id as encounterid,
pe.practice_id as practiceid,
case when
replace(STUFF((SELECT '\par '+ record_text FROM era_eob_storage
WHERE file_archive_id IN
(SELECT file_archive_id FROM transactions t2
inner join era_eob_storage eob2 on eob2.trans_id = t2.trans_id
WHERE  eob2.trans_id = eob.trans_id and t2.person_id = '00A2A878-1C27-4453-B7E8-66093B11D510'
)and (trans_id = eob.trans_id or trans_id is null
and header_trans_id = eob.header_trans_id)
ORDER BY record_type, seq_nbr
FOR XML PATH('')
),1,5,''),char(13)+char(10),'\par ') is not null 

then

replace(STUFF((SELECT '\par '+ record_text FROM era_eob_storage
WHERE file_archive_id IN
(SELECT file_archive_id FROM transactions t2
inner join era_eob_storage eob2 on eob2.trans_id = t2.trans_id
WHERE  eob2.trans_id = eob.trans_id and t2.person_id = '00A2A878-1C27-4453-B7E8-66093B11D510'
)and (trans_id = eob.trans_id or trans_id is null
and header_trans_id = eob.header_trans_id)
ORDER BY record_type, seq_nbr
FOR XML PATH('')
),1,5,''),char(13)+char(10),'\par ')

else

replace(STUFF((SELECT '\par '+ record_text FROM era_eob_storage
WHERE file_archive_id IN (SELECT    file_archive_id FROM era_eob_storage
WHERE trans_id = eob.trans_id) and (trans_id = eob.trans_id or trans_id is null
and header_trans_id = eob.header_trans_id)
ORDER BY record_type, seq_nbr
FOR XML PATH('')
),1,5,''),char(13)+char(10),'\par ') end
as practice

from patient_encounter pe 
left join transactions t on t.source_id=pe.enc_id and t.practice_id = pe.practice_id and t.source_type = 'v'
inner join era_eob_storage  eob on eob.trans_id = t.trans_id
where  pe.person_id = '00A2A878-1C27-4453-B7E8-66093B11D510' and t.era_post_ind='Y'
and convert(varchar(50),pe.enc_nbr) +'_'+ convert(varchar(50),PE.practice_id)='11454322_0001'

I need to optimized it to get speed up for result

What I have tried:

Try to create temp table and added that subquery result in it.
But it not work.

Posted 30-Dec-21 23:03pm

Member 11776570

Updated 31-Dec-21 3:00am

Add a Solution

Comments

CHill60 31-Dec-21 8:17am

"But it not work" is not giving us any information at all. You have supplied no sample data, no expected results, no indication of what you are trying to achieve. Just a poorly formatted code dump. The chances of anyone being able to help you are slim

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2021-12-31T02:32:00

First of all, proper indention, new lines etc enhance the readability of a statement. So when you want to enhance a SQL statement, it's much easier when the statement is easy to read.

Having that said, one major problem you have is that you don't properly use aliases. For example

SQL

...
FROM era_eob_storage
WHERE file_archive_id IN (SELECT file_archive_id 
                          FROM transactions t2
...

You haven't defined from what table the column file_archive_id is fetched. In fact SQL server makes the decision in such case and it may not be correct (taken that the syntax check is passed that is). While this may cause errors it also prolongs the parse time. So the first thing to do is to put all aliases in place.

Once that is done you can check that you have proper indices. For example following are probable candidates

Table patient_encounter
- Index 1: person_id

Table transactions
- Index 1: source_type, source_id, practice_id, era_post_ind
- Index 2: person_id, trans_id

Table era_eob_storage
- Index 1: trans_id
- Index 2: header_trans_id

Looking that the conditions with OR's I'm not sure if the result is correct. The following

SQL

and (    trans_id = eob.trans_id 
     or  trans_id is null 
     and header_trans_id = eob.header_trans_id)

should perhaps contain additional parenthesis. For example if trans_id should always match, then something like

SQL

and (   (  trans_id = eob.trans_id 
        or trans_id is null) 
    and header_trans_id = eob.header_trans_id)

One more observation, the check in the case structure is probably expensive, depending on the amount of data, so you should consider if it can be simplified. Since the check is only a true/false check, you don't necessarily need the formatted output . So depending on the data the check could perhaps be modified to something like

SQL

...
case 
   when (SELECT COUNT(*)
         FROM era_eob_storage
         WHERE file_archive_id IN (SELECT file_archive_id 
                                   FROM transactions t2
                                   inner join era_eob_storage eob2 
                                           on eob2.trans_id = t2.trans_id
                                   WHERE  eob2.trans_id = eob.trans_id 
                                   and t2.person_id = '00A2A878-1C27-4453-B7E8-66093B11D510')
         and (   trans_id = eob.trans_id 
              or trans_id is null 
              and header_trans_id = eob.header_trans_id)) > 0
then
...

CHill60 · Answer 2 · 2021-12-31T03:00:00

I had a quick look but it's impossible to understand what you are trying to do without some sample data.

The problem is with

SQL

WHERE  eob2.trans_id = eob.trans_id

and with

SQL

and (trans_id = eob.trans_id or trans_id is null
and header_trans_id = eob.header_trans_id)

Referring to the outer query within the sub-query creates what is known as a "correlated sub-query" and they spell doom for performance (see why at SQL server performance - Death by correlated subqueries - SQL Service[^] )

You were going along the right track by trying to create a temporary table for the sub-queries (nb plural! There is more than one) and you should persist in that direction.

Generate a table that contains your stuffed text for all trans ids and header_trans_ids e.g.

SQL

insert into #demo
select trans_id, header_trans_id, replace(STUFF((
	SELECT '\par '+ record_text FROM era_eob_storage
	WHERE file_archive_id IN
	(
		SELECT t2.file_archive_id FROM transactions t2
		inner join @era_eob_storage eob2 on eob2.trans_id = t2.trans_id
		WHERE  t2.person_id = '00A2A878-1C27-4453-B7E8-66093B11D510'
	)
	ORDER BY record_type, seq_nbr
	FOR XML PATH('')
	),1,5,''),char(13)+char(10),'\par ')  
from era_eob_storage

You could even do this for every person_id - it depends on the volume of data you are handling. Do something similar for the other sub-query. Then your query becomes something like the following (bear in mind I have not been able to test this!!)

SQL

select
	pe.person_id as patientid,
	t.trans_id as noteid,
	convert(varchar(50),pe.enc_nbr) +'_'+ convert(varchar(50),PE.practice_id) as ticketnumber,
	pe.enc_id as encounterid,
	pe.practice_id as practiceid,
isnull(d.stuffed,d2.stuffed) as practice
from patient_encounter pe 
left join transactions t on t.source_id=pe.enc_id and t.practice_id = pe.practice_id and t.source_type = 'v'
inner join era_eob_storage  eob on eob.trans_id = t.trans_id
left outer join #demo d on d.trans_id = eob.trans_id and d.header_trans_id = eob.header_trans_id
left outer join #demo2 d2 on d2.trans_id = eob.trans_id and d2.header_trans_id = eob.header_trans_id
where  pe.person_id = '00A2A878-1C27-4453-B7E8-66093B11D510' 
and t.era_post_ind='Y'
and convert(varchar(50),pe.enc_nbr) +'_'+ convert(varchar(50),PE.practice_id)='11454322_0001'

That final part of your where clause won't be helping either. Instead of

SQL

and convert(varchar(50),pe.enc_nbr) +'_'+ convert(varchar(50),PE.practice_id)='11454322_0001'

use

SQL

and pe.enc_nbr = 11454322 and practice_id=1

If Yyou use the "Improve Question" link to provide some sample data for each of the tables used, and also the results you would expect to see for that sample data, I might have a chance of helping you further.

How can I optimize below query in SQL

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0