Too many items in where clause

Transact-SQL
1

I have got an SQL here, and I am wondering, how can I reduce the no of lines in the where clause.

SELECT
AR.ClientGUID,
AR.ChartGUID,
AR.Description,
CV.ClientDisplayName,
CV.CurrentLocation,
O.Name,
O.OrderSetName,
O.IDCode,
o.RequestedDtm,
o.StopDtm
FROM
CV3AlertRepository AR,
CV3Clientvisit cv,
CV3ORDER O,
CV3OrderCatalogMasterItem ocmi,
CV3CatalogClassTypeValue ctv,
CV3ClassType ct
WHERE
O.OrderCatalogMasterItemGUID=ocmi.guid
and ctv.CatalogMasterGUID = ocmi.guid
and ct.guid = ctv.ClassTypeGUID
and ct.code like 'Acute Kidney Injury - AKI'
and value='renally eliminated'
and cv.ClientGUID = O.ClientGUID
and cv.ChartGUID = O.ChartGUID
and cv.GUID = O.ClientVisitGUID
AND CV.ClientGUID=AR.ClientGUID
AND CV.ChartGUID =AR.ChartGUID
AND CV.ClientGUID=O.ClientGUID
AND CV.ChartGUID =O.ChartGUID
AND AR.ClientGUID=O.ClientGUID
AND AR.ChartGUID =O.ChartGUID
AND AR.Description='AKI alert'

2

Put them all on the same line ? Seriously why does this matter ? Having less condition on the where clause does not translate to faster query

By the way, you should use proper ansi join syntax, and this does reduce the no of lines in the where clause

there you go. left 3 lines in where clause

FROM CV3AlertRepository ar
INNER JOIN CV3Clientvisit cv
	ON ar.ClientGUID = cv.ClientGUID
		AND ar.ChartGUID = cv.ChartGUID
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 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'
1 Like
3

Yes . . Thanks a lot . . . . this is awesome

4

I added three more lines as I need to get some more columns,
The performance changed, was executing in one sec now became 25 seconds

FROM CV3AlertRepository ar
INNER JOIN CV3Clientvisit cv
ON ar.ClientGUID = cv.ClientGUID
AND ar.ChartGUID = cv.ChartGUID
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
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'