optimized database maintenance query for UDT_Endpoint_DeleteOrphans

FormerMember

The existing query in the stored procedure (udt_dbm_UDT_Endpoint_DeleteOrphans) is for deleting orphaned endpoints from the database.  The query inside is poorly optimized to where our environment suffered the query working for hours during the day, slowing everything down.  After I optimized it, the query takes 1 minute to run.

	delete from udt_Endpoint
		where
		EndpointID in
		(
			select e.EndpointID
			from [dbo].[UDT_Endpoint] e
			left join [dbo].[UDT_PortToEndpointCurrent] ptec with(nolock) on e.EndpointID = ptec.EndpointID
			left join [dbo].[UDT_PortToEndpointHistory] pteh with(nolock) on e.EndpointID = pteh.EndpointID
			left join [dbo].[UDT_IPAddressCurrent] ipac with(nolock) on e.EndpointID = ipac.EndpointID
			left join [dbo].[UDT_IPAddressHistory] ipah with(nolock) on e.EndpointID = ipah.EndpointID
			left join [dbo].[Wireless_Clients_SessionHistory] wcs with(nolock) on e.MACAddress = wcs.MACAddress
			where 1=1
			and ptec.EndpointID IS NULL
			and pteh.EndpointID IS NULL
			and ipac.EndpointID IS NULL
			and ipah.EndpointID IS NULL
			and wcs.MACAddress IS NULL
		)