我有一个包含派生表的 SQL 查询。
派生查询看起来像这样：
SELECT
ObjectId, MIN(StatusHistoryId) AS FirstStatusHistoryId
FROM
dbo.StatusHistory
WHERE
ObjectType = 'SchemeTypeApplication'
AND (StatusId = 504 OR StatusId = 501)
AND IsDeleted = 0
GROUP BY
ObjectId
这需要大约 2 分钟才能完成，它会拉回近 300k 行。整个查询（其中包含此查询）大约需要相同的时间。如果没有派生表，则需要不到一秒的时间，因此我知道是派生查询导致了问题。
我的问题是，有没有办法提高派生表查询的速度？也许向 StatusHistory 表添加一些索引（我对索引有点垃圾......）？或者除了使用派生表之外的其他方法？
任何建议表示赞赏。
谢谢
I have a SQL query that contains a derieved table.
The derived query looks something like this:
SELECT
ObjectId, MIN(StatusHistoryId) AS FirstStatusHistoryId
FROM
dbo.StatusHistory
WHERE
ObjectType = 'SchemeTypeApplication'
AND (StatusId = 504 OR StatusId = 501)
AND IsDeleted = 0
GROUP BY
ObjectId
This takes around 2 minutes to complete on it's own, it pulls back nearly 300k rows. The whole query (with this inside) takes around about the same. Without the derived table, it takes less than a second, so I know it's the derieved query that causing a problem.
My question is, is there anyway to improve the speed of the dereived table query? Maybe adding some indexes to the StatusHistory table (I'm a bit rubbish with indexing...)? Or some other approach other than using a derived table?
Any suggestions are appreciated.
Thanks
发布评论
评论（3）
正如 JonH 指出的那样，扩展索引并根据您的 WHERE 条件将预期结果的最小粒度视为关键元素...如果您的“ObjectType”是一个字符串，如果它更像是一个可枚举的字符串，则可能会得到更好的优化整数值的基础比文本还要多。至于粒度。这将返回最少数量的记录...状态 ID 为 501 和 504 组合（通过您的 OR 条件）与“SchemeTypeApplication”的对象类型。如果 501 有 50,000 条记录，504 有 30,000 条记录，但“SchemeTypeApplication”有 475,000 条记录，我将首先在状态 ID 上建立索引，否则，反之亦然...让索引突破 80,000 条记录，其中，如何475,000 个对象类型中的许多是 501 和 504，也许是 50,000 和你完成了。是的，您最初的评论是指返回大约 300k 行，但这只是根据查询的常用方式确定优化索引技术的示例。因此，我会索引类似
(statusID、ObjectType、IsDeleted、ObjectID)的内容
As JonH noted, expand an index and have the key elements based on what would be considered the smallest granularity of expected results based on you WHERE criteria... Your "ObjectType" being a string might be better optimized if it were more an an enumerable integer value basis than text too. As for granularity. Which would return the least amount of records... status ID being 501 and 504 combined (via your OR condition) vs the object type of "SchemeTypeApplication". If 501 has 50,000 records, 504 has 30,000 records, but "SchemeTypeApplication" has 475,000 records, I would have my index on the status ID first, otherwise, vice-versa... Let the index blow through 80,000 records and within that, how many of the 475,000 object types are 501 and 504, maybe 50,000 and you're done. Yes, your original comment refers to returning about 300k rows, but this is just a sample of determination of an optimizing index technique based on how your queries will commonly be used. So, I would index on something like
(statusID, ObjectType, IsDeleted, ObjectID)
请参阅上面的评论 - 如果
StatusID字段上还没有索引，您还可以引入索引。尝试按照可以返回的记录数的顺序将限制性更强的列放入 WHERE 子句中。See my comment above - you could also introduce an index on the
StatusIDfield if there isn't already one on it. Try to put your more restrictive columns in the WHERE clause in order of how many records could be returned.如果您使用的是 Management Studio 2008（带有 2008 或 2005 数据库），您可以查看您的执行计划，看看它是否建议创建任何缺失的索引。
我的猜测是它会对这个查询有一个建议。
这里有一些屏幕截图： http://weblogs.sqlteam.com/mladenp/archive/2008/12/29/SQL-Server-Management-Studio-2008-suggests-missing-indexes-with-actual.aspx
If you are using Management Studio 2008 (with a 2008 or 2005 database) you can look at your execution plan to see if it recommends any missing indexes that should be created.
My guess is that it will have a recommendation for this query.
There are some screenshots here: http://weblogs.sqlteam.com/mladenp/archive/2008/12/29/SQL-Server-Management-Studio-2008-suggests-missing-indexes-with-actual.aspx