1

I have a read-only database, from which I have to report on all our Wuhan virus cases. The execution plan and query can be found here.

I can't share the data, since it's health records, but this currently takes over an hour.

How can I improve this query?

--USE [Tableau_views_1]
--GO

--/****** Object:  View [dbo].[COVID]    Script Date: 3/20/2020 5:21:08 PM ******/
--SET ANSI_NULLS ON
--GO

--SET QUOTED_IDENTIFIER ON
--GO



--ALTER view [dbo].[COVID] AS 

With Positive as (
    Select cv.IDCode, cv.visitIDCode, bo.OrderGUID, bo.ClientVisitGUID
         , bo.ChartGUID, bo.status, bo.value
         , row_number() over (partition by bo.OrderGUID, bo.ClientVisitGUID, bo.ChartGUID 
                              order by bo.entered desc, bo.value) as ranker
from sn1.dbo.CV3ClientVisit cv
inner join SN1.dbo.CV3Order ord 
    on ord.ClientVisitGUID = cv.guid 
    and ord.ChartGUID =  cv.ChartGUID 
    and ord.ClientGUID = cv.ClientGUID
left join sn1.dbo.CV3BasicObservation bo 
    on bo.OrderGUID = ord.GUID 
    and bo.ClientVisitGUID = ord.ClientVisitGUID 
    and bo.ChartGUID = ord.ChartGUID
where ord.name in ( '2019 Novel Coronavirus (COVID19) NAA', 
     '2019 Novel Coronavirus (COVID19) PCR')
and bo.status in ('C', 'F') 
and bo.value = 'Detected'

), negative as (
    Select cv.IDCode, cv.visitIDCode, bo.OrderGUID, bo.ClientVisitGUID
         , bo.ChartGUID, bo.status, bo.value
         , row_number() over (partition by bo.OrderGUID, bo.ClientVisitGUID, bo.ChartGUID 
                              order by bo.entered desc, bo.value) as ranker
from sn1.dbo.CV3ClientVisit cv
inner join SN1.dbo.CV3Order ord 
    on ord.ClientVisitGUID = cv.guid 
   and ord.ChartGUID =  cv.ChartGUID 
   and ord.ClientGUID = cv.ClientGUID
left join sn1.dbo.CV3BasicObservation bo 
    on bo.OrderGUID = ord.GUID 
   and bo.ClientVisitGUID = ord.ClientVisitGUID 
   and bo.ChartGUID = ord.ChartGUID
where ord.name in ( '2019 Novel Coronavirus (COVID19) NAA', 
     '2019 Novel Coronavirus (COVID19) PCR')
and bo.status in ('C', 'F') 
and cv.idcode not in (Select idcode from Positive)

), Pending2 as (
    Select cv.IDCode, cv.visitIDCode, bo.OrderGUID, bo.ClientVisitGUID
         , bo.ChartGUID, bo.status, bo.value
         , row_number() over (partition by bo.OrderGUID, bo.ClientVisitGUID, bo.ChartGUID 
                              order by bo.entered desc, bo.value) as ranker
from sn1.dbo.CV3ClientVisit cv
inner join SN1.dbo.CV3Order ord 
    on ord.ClientVisitGUID = cv.guid 
    and ord.ChartGUID =  cv.ChartGUID 
    and ord.ClientGUID = cv.ClientGUID
left join sn1.dbo.CV3BasicObservation bo 
    on bo.OrderGUID = ord.GUID 
   and bo.ClientVisitGUID = ord.ClientVisitGUID 
   and bo.ChartGUID = ord.ChartGUID
where ord.name in ( '2019 Novel Coronavirus (COVID19) NAA', 
     '2019 Novel Coronavirus (COVID19) PCR')
and bo.status = 'P' 
and (cv.idcode not in (Select idcode from Positive) 
   or cv.IDCode not in (Select IDCode from negative))

), Pending as (
   Select * 
   from Pending2 
   where ranker = 1
)
Select distinct cv.IDCode as MREC, cv.visitidcode as visitid
     , cv.ClientDisplayName, pat.GenderCode
     , cv.ServiceGUID, cs.Description as service_desc, cv.admitdtm
     , cv.dischargedtm, cv.CurrentLocation
     , cast(cast(pat.birthyearnum as varchar(4))+ '-' + right('00' + cast(pat.birthmonthnum as varchar(2)),2) + '-' +
right('00' + cast(pat.birthdaynum as varchar(2)),2) as date) as dob
     , FLOOR(DATEDIFF(DAY, (cast(cast(pat.birthyearnum as varchar(4))+ '-' + right('00' + cast(pat.birthmonthnum as varchar(2)),2) + '-' +
right('00' + cast(pat.birthdaynum as varchar(2)),2) as date))
     , getdate()) / 365.25) as Age
     , cv.DischargeDisposition, chi.code, chi.description
     , hi.CreatedWhen, hi.TypeCode, cv. TypeCode as pattype
     , cv.ProviderDisplayName, cv.CareLevelCode
     , dis.Code as disposition_code, ord.idcode as ord_id
     , ord.RequestedDtm, ord.name as ord_name, prov.DisplayName as ordered_By
     , stat.Description as OrderStatusDesc, cast(STATHIS.TouchedWhen as smalldatetime) as Ord_status_Dtm
     , bo.ItemName, cast(bo.CreatedWhen as smalldatetime) as result_time
     , bo.status,  bo.value
     , case when cv.IDCode = po.idcode then 'Positive'
            when cv.idcode = neg.IDCode then 'negative'
            when cv.idcode = pen.idcode then 'Pending'
       end as result
from sn1.dbo.CV3ClientVisit cv
left join sn1.dbo.CV3Service cs 
    on cs.GUID = cv.ServiceGUID
left join sn1.dbo.CV3Client pat 
    on pat.GUID = cv.ClientGUID
left join sn1.db
5
  • Does it have to end up being wrapped in a view? Because even if you can't add indexes, you could make temp tables or table variables and populate them in intermediate steps. You would probably end up indexing those table variables in a roughly similar way to the index recommendations from SQL Server but some columns that are aggregated or whatever would get dropped. If that approach is workable and you still need view semantics, you could use a multi-line table-valued function instead of a view and still get that level of functionality - SELECT * FROM MLTVF();
    – Cade Roux
    Mar 21 '20 at 1:06
  • @CadeRoux thanks! ill look into that. i did get a big boost by importing the relevant orders into a temp table at the start. in the end, it has to get mounted in tabluea, but that also takes stored procedures, so it doesn't have to be a view.
    – Sneha
    Mar 21 '20 at 1:28
  • Remember then that temp tables and table variables can be indexed. So you can transform the data as best you can to get the most performance even if you can't index the source data the way you might like. And you'll likely be slimming down the rows and columns in those tables as you go as well. It's not that hard to be smarter than the optimizer.
    – Cade Roux
    Mar 21 '20 at 1:42
  • @CadeRoux the ability to index table variables is very limited, and there are some pretty bad performance hits you'll take on non-trivial table variables due to how the cardinality estimator handles them in 2016.
    – alroc
    Mar 21 '20 at 11:52
  • Is there something missing in the query (no join condition at the end)?
    – Lennart
    Mar 21 '20 at 15:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.