SQLTeam.com | Weblogs | Forums

Execution time


#1

The following sql takes one second to run, Is there any way that I can reduce the time ?

SELECT distinct
cv.ClientGUID,
CV.ClientDisplayName,
CV.CurrentLocation,
CV.IDCode,
CV.VisitIDCode,
cv.ProviderDisplayName,
AR.Description,
O.Name,
O.OrderSetName,
O.IDCode,
oT.orderDosageLow Dose,
OT.OrderUom UOM,
o.RequestedDtm,
o.StopDtm
FROM CV3AlertRepository ar
INNER JOIN CV3ActiveVisitList cv
ON ar.ClientGUID = cv.ClientGUID
AND ar.ChartGUID = cv.ChartGUID
-- and ar.ClientVisitGUID=cv.VisitIDCode
INNER JOIN CV3ORDER o
ON cv.ClientGUID = o.ClientGUID
AND cv.ChartGUID = o.ChartGUID
AND cv.GUID = o.ClientVisitGUID
AND cv.ClientGUID = o.ClientGUID
AND cv.ChartGUID = o.ChartGUID
AND ar.ClientGUID = o.ClientGUID
AND ar.ChartGUID = o.ChartGUID

INNER JOIN CV3OrderCatalogMasterItem ocmi
ON o.OrderCatalogMasterItemGUID = ocmi.guid
INNER JOIN CV3OrderTask OT
ON ocmi.GUID=OT.OrderCatalogMasterItemGUID
AND o.OrderCatalogMasterItemGUID=ot.OrderCatalogMasterItemGUID
and o.ClientGUID=ot.ClientGUID
and o.ChartGUID=ot.ChartGUID
and o.guid=ot.orderguid
INNER JOIN CV3CatalogClassTypeValue ctv
ON ocmi.guid = ctv.CatalogMasterGUID
INNER JOIN CV3ClassType ct
ON ctv.ClassTypeGUID = ct.guid
WHERE ct.code LIKE 'Acute Kidney Injury - AKI'
AND value = 'renally eliminated'
AND AR.Description = 'AKI alert'

thanks


#2

Looking at the number of tables being used in your query,1 sec execution time should be reasonable.


#3

how many rows in the source tables, and how many in the result set?


#4

There are more than 30000 lines in two tables and other tables are less than 8000, The result of the query should be very low, I am dealing now with 3 rows.
thanks for looking


#5

Check the execution plan, What kinds of joins does it say? Hash, Nested Loops or Merge? (you want merge)


#6

Not sure, If I can merge join . . I added two more tables, to retrieve some more data and looks like got much more messy
Takes lot of time to execute now


SELECT distinct
cv.ClientGUID,
CV.ClientDisplayName,
CV.CurrentLocation,
CV.IDCode,
CV.VisitIDCode,
CPV.ROLECODE,
cp.DisplayName,
AR.Description,
O.Name,
O.OrderSetName,
O.IDCode,
oT.orderDosageLow Dose,
OT.OrderUom UOM,
o.RequestedDtm,
o.StopDtm
FROM CV3AlertRepository ar
INNER JOIN CV3ClientVisit cv
ON ar.ClientGUID = cv.ClientGUID
AND ar.ChartGUID = cv.ChartGUID
INNER JOIN CV3CareProviderVisitRole CPV
ON CV.Clientguid=CPV.ClientGUID
INNER JOIN CV3CareProvider CP
ON CPV.ProviderGUID=CP.GUID
INNER JOIN CV3ORDER o
ON cv.ClientGUID = o.ClientGUID
AND cv.ChartGUID = o.ChartGUID
AND cv.GUID = o.ClientVisitGUID
AND cv.ClientGUID = o.ClientGUID
AND cv.ChartGUID = o.ChartGUID

INNER JOIN CV3OrderCatalogMasterItem ocmi
ON o.OrderCatalogMasterItemGUID = ocmi.guid
INNER JOIN CV3OrderTask OT
ON ocmi.GUID=OT.OrderCatalogMasterItemGUID
AND o.OrderCatalogMasterItemGUID=ot.OrderCatalogMasterItemGUID
and o.ClientGUID=ot.ClientGUID
and o.ChartGUID=ot.ChartGUID
and o.guid=ot.orderguid
INNER JOIN CV3CatalogClassTypeValue ctv
ON ocmi.guid = ctv.CatalogMasterGUID
INNER JOIN CV3ClassType ct
ON ctv.ClassTypeGUID = ct.guid
WHERE ct.code LIKE 'Acute Kidney Injury - AKI'
AND value = 'renally eliminated'
AND AR.Description = 'AKI alert'