Mssql - Making Multiple Count Distinct Calls In A Query Runs Slowly

I have tables with the following schema: Device DeviceId Name Service ServiceId Name Software SoftwareId Name Device_Software DeviceId SoftwareId DiscoveryDate Device_Servi

Solution 1:

I'd try the following and see if it makes difference :

SELECT
device.name
a.cntSft, b.cntSrv
FROM device
LEFT JOIN
 ( SELECT deviceId, COUNT(DISTINCT softwareId) as cntSft FROM device_software 
 GROUPBY deviceId) a (ON a.deviceId = device.deviceId)
LEFT JOIN 
( SELECT deviceId, COUNT(DISTINCT serviceId) as cntSrv FROM device_service 
 GROUPBY deviceId) b (ON b.deviceId = device.deviceId);

You may also not need COUNT DISTINCT, but just COUNT with this version of query.

Solution 2:

You could consider indexed views on Device_Software and Device_Service:

CREATEVIEW dbo.v_Device_Software
WITH SCHEMABINDING
ASSELECT DeviceId, SoftwareId, DeviceCount = COUNT_BIG(*)
    FROM dbo.Device_Software
    GROUPBY DeviceId, SoftwareId;
GO
CREATEUNIQUE CLUSTERED INDEX x ON dbo.v_Device_Software(DeviceId, SoftwareId);
GO

CREATEVIEW dbo.v_Device_Service
WITH SCHEMABINDING
ASSELECT DeviceId, ServiceId, DeviceCount = COUNT_BIG(*)
    FROM dbo.Device_Service
    GROUPBY DeviceId, ServiceId;
GO
CREATEUNIQUE CLUSTERED INDEX x ON dbo.v_Device_Service(DeviceId, ServiceId);
GO

Now your query becomes:

SELECT
  device.name
  ,COUNT(vsoft.DeviceId)
  ,COUNT(vserv.DeviceId)
FROM
  dbo.device
LEFTOUTERJOIN dbo.v_Device_Service AS vserv
  ON device.deviceId = vserv.DeviceId
LEFTOUTERJOIN dbo.v_Device_Software AS vsoft
  ON device.deviceId = voft.DeviceId
GROUPBY device.name;

There are many restrictions, though, and you should be sure to test the impact this has on your entire workload, not just this one query.