To reduce the execution Time

Transact-SQL
1

I have the query here, and it takes well over one minute 30 seconds to execute, Any suggestions are most welcome,

thanks

select cv.idcode as 'RHRN',
cv.visitidcode as 'Encounter',
cv.CurrentLocation ,
cpGrp.displayname as 'AttendingGroup' ,
ar.Description as 'AlertDescription',
ar.createdwhen as 'AlertCreatedWhen',
bo.value as 'CreatinineLevel',
bo.CreatedWhen as 'LabResultCreatedWhen',
ctv.value as 'ClassTypeValue',
isnull (o.ordersetname,'') as 'OrderSetName',
o.idcode as 'OrderID',
o.name as 'OrderName',
me.DosageLow as 'Dose',
me.Uom as 'UOM',
o.RequestedDtm as 'OrderRequestedDTM' ,
o.status as 'OrderStatus',
o.StopDtm 'OrderStopDTM'

into #tmp1
from cv3order o (nolock)
inner join cv3clientvisit cv (nolock) on o.ClientGUID = cv.ClientGUID and o.ChartGUID = cv.ChartGUID
inner join cv3CareProviderVisitRole cpvrGrp (nolock) on cpvrGrp.clientVisitGuid = cv.guid
inner join cv3CareProvider cpGrp (nolock)on cpGrp.guid = cpvrGrp.providerGuid and cpvrGrp.rolecode = 'attending group'
inner join cv3alertRepository ar (nolock) on ar.ClientVisitGUID = cv.guid
inner join cv3CatalogClassTypeValue ctv (nolock) on ctv.CatalogMasterGUID = o.OrderCatalogMasterItemGUID
inner join cv3ClassType ct (nolock) on ct.GUID = ctv.ClassTypeGUID
inner join CV3MedicationExtension me(nolock) on me.guid = o.guid
inner join CV3BasicObservation bo (nolock) on bo.ClientVisitGUID = cv.guid
where ar.mlmname = 'AHS_AKI_Creatinine_Result'
and ct.code = 'Acute Kidney Injury - AKI' and ctv.value in ('renally eliminated', 'nephrotoxicity')
and bo.ItemName = 'Creatinine LEVEL'
and cv.CurrentLocation not LIKE 'FMC-55%'
and cv.CurrentLocation not like 'FMC-37B%'
and cv.CurrentLocation not like 'ACH%'

select
RHRN,
Encounter,
max(LabResultCreatedWhen) as 'lastResultWhen'
into #tmp2 -- get the most recent lab result
from #tmp1
group by
RHRN,
Encounter

select
RHRN,
Encounter,
max(AlertCreatedWhen) as 'lastAlertWhen'
into #tmp3 -- get the most recent alert
from #tmp1
group by
RHRN,
Encounter

select t1.*
from #tmp1 t1
inner join #tmp2 t2 on t1.LabResultCreatedWhen = t2.lastResultWhen
inner join #tmp3 t3 on t1.AlertCreatedWhen = t3.lastAlertWhen
order by t1.RHRN

drop table #tmp1
drop table #tmp2
drop table #tmp3

2

Add this to the top: SET STATISTICS IO ON; SET STATISTICS TIME ON;

Run the whole thing (the SETs plus your script all in one window). Copy/paste the output of the SETs for us.

You're likely just missing some indexes.

1 Like
3

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'CV3ClassType'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CV3CatalogClassTypeValue'. Scan count 1, logical reads 235, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CV3CareProvider'. Scan count 5, logical reads 2905, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CV3MedicationExtension'. Scan count 0, logical reads 1889153, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CV3CareProviderVisitRole'. Scan count 121817, logical reads 19757996, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 4, logical reads 1568, physical reads 131, read-ahead reads 1437, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CV3Order'. Scan count 7500, logical reads 460041, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CV3ClientVisit'. Scan count 0, logical reads 58166, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CV3AlertRepository'. Scan count 5, logical reads 104724, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CV3BasicObservation'. Scan count 5, logical reads 9131424, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 171353 ms, elapsed time = 62705 ms.

(20693349 row(s) affected)
SQL Server parse and compile time:
CPU time = 1606 ms, elapsed time = 1622 ms.
Table '#tmp1_______________________________________________________________________________________________________________00000029D61D'. Scan count 5, logical reads 529904, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 37205 ms, elapsed time = 10510 ms.

(2993 row(s) affected)
SQL Server parse and compile time:
CPU time = 2 ms, elapsed time = 2 ms.
Table '#tmp1_______________________________________________________________________________________________________________00000029D61D'. Scan count 5, logical reads 529904, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 37144 ms, elapsed time = 10537 ms.

(2993 row(s) affected)
SQL Server parse and compile time:
CPU time = 776 ms, elapsed time = 776 ms.

(120919 row(s) affected)
Table '#tmp3_______________________________________________________________________________________________________________00000029D677'. Scan count 5, logical reads 22, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#tmp2_______________________________________________________________________________________________________________00000029D66F'. Scan count 5, logical reads 22, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#tmp1_______________________________________________________________________________________________________________00000029D61D'. Scan count 5, logical reads 529904, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 6239 ms, elapsed time = 4574 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 4 ms.

SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

4

What indexes do you have on cv3CareProviderVisitRole? I would start with that table as it has the highest reads.

A quick glance at your query, I would ensure that clientVisitGuid is indexed. A composite index on providerGuid and rolecode also.

I would then look at the indexes for CV3BasicObservation and CV3MedicationExtension.

Those 3 tables are your heavy hitters. Index them appropriately.

You could view the execution plan and see if SQL Server has any indexes suggested for your script.

1 Like
5

Thank you for your time,
unfortunately do not have the access to view the execution plan, But I will check on the indexes.

6

Not much luck, I did index the 3 tables,

7

Please post the indexes and the new stats io/time output.

Plus we really need to see the execution plans. Please work with whomever has the access and post the showplan xml here.

8

Also, you're only using rows from cpvrGrp table that have a rolecode = 'attending group'. You should add that condition to the main join to that table; you can leave it in the join to cpGrp, it won't hurt anything. Specifically, you now have this code:

inner join cv3CareProviderVisitRole cpvrGrp (nolock) on cpvrGrp.clientVisitGuid = cv.guid
inner join cv3CareProvider cpGrp (nolock)on cpGrp.guid = cpvrGrp.providerGuid and cpvrGrp.rolecode = 'attending group'

It should be this:
inner join cv3CareProviderVisitRole cpvrGrp (nolock) on cpvrGrp.clientVisitGuid = cv.guid and cpvrGrp.rolecode = 'attending group'
inner join cv3CareProvider cpGrp (nolock)on cpGrp.guid = cpvrGrp.providerGuid and cpvrGrp.rolecode = 'attending group'

9

I have got the sql plan, not able to upload as the file or too big to cut and paste

10

Scott, I added 'attending group' too . does not make much difference

12

Would need to see the query plan to see why not. You might also need to (force SQL to) SELECT from the tables in a specific order to speed it up.

13

CompileCPU="3124" CompileMemory="22424">



<MissingIndex Database="[CR4TST402]" Schema="[dbo]"

17

I am narrowing down the result data, currently we are dealing with (19715776 row(s)

Right now I have added one more condition and bo.value >= '110' even then the result shows values less than 110

18

Since character 110 is not the same as numeric 110 the comparison is not the same. So if you want >= numeric 110 then bo.value needs to be numeric

19

Not defined as numeric, Its varchar(60) , I tried cast and did not work .. I am getting an error Conversion failed when converting the varchar value 'Error' to data type int.

20

I am using cast to filter out values ,

Getting the following error - Error converting data type varchar to float.

The error is because, I have values like '0.2' and '<1' as values in the columns - How can I omit these values

select cast(round(value,0) as INT)
from CV3BasicObservation (nolock)
where
cast(round(value,0) as INT) >= 110

21

You can use the ISNUMERIC function to exclude any non-numeric values.

22

Thanks,
select cast(Isnumeric(Value) as INT)
from CV3BasicObservation (nolock)
where Value >= 110
still getting an error Conversion failed when converting the varchar value '3.0' to data type int.

23

Do you want to include values with a decimal point or only integer ones?

If only integer ones, you need to do this:

select cast(Value as int) as value
from CV3BasicObservation with (nolock)
where 1 = case when value like '%[^0-9]%' then 0
when cast(value as int) > 110 then 1
else 0 end

24

Use ISNUMERIC in the WHERE clause.

1 Like