MSSQL-在查询中进行多个计数的不同调用运行缓慢 [英] MSSQL - Making multiple count distinct calls in a query runs slowly

问题描述

我有具有以下模式的表：

I have tables with the following schema:

• DeviceId


• 名称

• ServiceId


• 名称

• SoftwareId


• 名称

• DeviceId


• SoftwareId


• DiscoveryDate

• DeviceId


• ServiceId


• DiscoveryDate

现在，我是尝试编写一个给出设备的查询以及该设备具有的独特软件和服务的数量。

Now, I'm trying to write a query that gives the a Device, and the number of distinct software and services that device has.

如果我运行以下查询，则会得到一个结果5秒（设备有50,000行，软件和服务都有200行，链接表包括每个设备到每个软件和服务的链接。仅用于测试）

If I run the following query I get a result back within 5 seconds (device has 50,000 rows, software and service both have 200 and the link tables include a link for every device to every software and service. Just for testing purposes).

SELECT
  device.name
  ,COUNT(DISTINCT(device_software.softwareId))
FROM
  device
LEFT OUTER JOIN
  device_software ON device.deviceId = device_software.deviceId
GROUP BY device.name

但是，如果我尝试扩展查询以包括两者的计数，则需要花费更长的时间（〜30分钟，并且仍在进行）：

But if I try to expand the query to include the counts for both, it takes much much longer (~30 minutes and still going):

SELECT
  device.name
  ,COUNT(DISTINCT(device_software.softwareId))
  ,COUNT(DISTINCT(device_service.serviceId))
FROM
  device
LEFT OUTER JOIN
  device_service ON device.deviceId = device_service.deviceId
LEFT OUTER JOIN
  device_software ON device.dDeviceId = device_software.deviceId
GROUP BY device.name

现在，因为它位于存储过程中，我可以单独获取两个计数并将其结合起来，但这似乎很简单。我想知道是否有人知道有一个更好的方法可以在单个查询中执行此操作，而不会造成严重的性能损失？

Now since this is in a stored procedure, I could just get the two counts individually and combine that, but that seems like a hack. I was wondering if anyone knows of a better way to go about doing this in a single query without having a massive performance hit?

推荐答案

我会尝试以下操作，看看是否有帮助：

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 
 GROUP BY deviceId) a (ON a.deviceId = device.deviceId)
LEFT JOIN 
( SELECT deviceId, COUNT(DISTINCT serviceId) as cntSrv FROM device_service 
 GROUP BY deviceId) b (ON b.deviceId = device.deviceId);

您可能也不需要 COUNT DISTINCT ，但使用此版本的查询只需 COUNT 。

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

这篇关于MSSQL-在查询中进行多个计数的不同调用运行缓慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！