sql-server - 如何创建索引以加快数据检索
问题描述
我在下面有这个查询,它从服务器获取了 6000+ 行项目,下载的数据只有 317KB,但需要将近 25 秒或 IIS 服务器超时。
-- Region Parameters
DECLARE @p0 Int = 0
DECLARE @p1 NVarChar(1000) = 'sample.user'
DECLARE @p2 Int = 0
DECLARE @p3 NVarChar(1000) = 'sample.user'
-- EndRegion
SELECT [t0].[Id], [t0].[DocNumber], [t0].[DocStatus], [t0].[Title], [t0].[SubmittedDate], [t0].[CreatedBy], [t0].[ApprovedByStep], [t0].[RoutedFor],
(CASE
WHEN [t2].[test] IS NULL THEN NULL
ELSE [t2].[ApprovedBy]
END) AS [HistApprovedBy]
FROM [TESTDB].[dbo].[SampleTable] AS [t0]
LEFT OUTER JOIN (
SELECT 1 AS [test], [t1].[RequestId], [t1].[DocType], [t1].[ApprovedBy]
FROM [TESTDB].[dbo].[SampleHistories] AS [t1]
) AS [t2] ON ([t0].[Id] = [t2].[RequestId]) AND (@p0 = [t2].[DocType])
WHERE ([t0].[CreatedBy] = @p1) OR (([t0].[DocStatus] <> @p2) AND ([t0].[CreatedBy] <> @p3))
ORDER BY [t0].[DocNumber]
我读到索引可以加快数据检索,我如何创建上面的sql查询的索引?如果您对如何加快速度有任何建议。我会很感激。
点击这里查看行动计划
供参考下面是我的 LINQ
var vm = (from splTbl in SampleTable
join splsplHist in SamplesplHistories on
new
{
Key1 = splTbl.Id,
Key2 = 0
}
equals
new
{
Key1 = splHist.RequestId,
Key2 = splHist.DocType
}
into subsplHists
from subsplHist in subsplHists.DefaultIfEmpty()
where (splTbl.CreatedBy == "sample.user")
|| (splTbl.DocStatus != 0 && splTbl.CreatedBy != "sample.user")
orderby splTbl.DocNumber
select new
{
splTbl.Id,
splTbl.DocNumber,
splTbl.DocStatus,
splTbl.Title,
splTbl.SubmittedDate,
splTbl.CreatedBy,
splTbl.ApprovedByStep,
splTbl.RoutedFor,
splHistApprovedBy = (subsplHist == null ? null : subsplHist.ApprovedBy)
}
).ToList()
.GroupBy(x => new {
x.Id,
x.DocNumber,
x.DocStatus,
x.Title,
x.SubmittedDate,
x.CreatedBy,
x.ApprovedByStep,
x.RoutedFor
}).Select(x => new
{
x.Key.Id,
x.Key.DocNumber,
x.Key.DocStatus,
x.Key.Title,
x.Key.SubmittedDate,
x.Key.CreatedBy,
x.Key.ApprovedByStep,
x.Key.RoutedFor,
splHistApprovedBy = string.Join(",", x.Where(i => i.splHistApprovedBy != null).Select(i => i.splHistApprovedBy).Distinct())
}).ToList();
解决方案
正如@Dale Burelle 所建议的那样
我创建了行动计划建议的以下索引。
CREATE NONCLUSTERED INDEX [<TransacList, sysname,>]
ON [dbo].[Histories] ([DocType])
INCLUDE ([RequestId],[ApprovedBy])
推荐阅读
- c# - ASP .NET RowDataBound 属性仅适用于一行(第一行)
- lotus-notes - Lotusscript:将数组转换为字符串的问题
- javascript - Typescript过滤器在Angular中返回一个空数组
- unicode - 为什么在逗号中选择“将运算符转换为 Unicode”后没有任何反应?
- python - 无法使用子进程在 python 中并行执行 linux 命令?
- c# - How to find(query) through a complex array for checking in IMongoCollection and LINQ?
- powershell - 在 powershell 脚本中尝试/捕获 aws cli 错误
- merge - Neo4J - 如何合并具有相同属性值(名称)但节点标签不同的节点
- json - 尝试解析 JSON 文件,但格式似乎不同或 JSON 文件有问题
- r - 如何从R中的网络表格中提取带有重音符号的数据