sql - 如何加快以下审计日志查询
问题描述
我正在查询Audit旧商业应用程序中的日志表。Audit日志有13M记录。该表具有10字段。我的查询看起来像这样。我正在获取用户 - 来自客户表中具有文档 ID 的日期范围。客户表有行（从-800K更改文档表的用户）6/24/20217/4/2021
SELECT DISTINCT Usrid, usrname as Usrname
FROM Audit
where tsstamp >= Convert(Date,'6/24/2021',101) and tsstamp <= Convert(Date,'7/4/2021',101)
and Docid in (Select docid from [Customer] group by docid )
group by usrid, usrname order by usrname
该Audit表在 usrid、usrname 和 tsstamp 上有一个索引。Customer 表在 DocId 上有索引。
我能做些什么来加快速度？新索引？改变SQL？当前查询大约需要2几分钟才能运行。跑步SQL Server 2014
提前致谢
<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.557" Build="15.0.2148.135" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="18239.6" StatementId="1" StatementOptmLevel="FULL" CardinalityEstimationModelVersion="70" StatementSubTreeCost="164.664" StatementText="
SELECT Usrid, usrname as Usrname
 FROM ae_audit A
 where tsstamp >= Convert(Date,'6/24/2021',101) and tsstamp <= Convert(Date,'7/4/2021',101)
 and docid in (Select docid from [dbo].[Customer] )
 group by usrid, usrname order by usrname" StatementType="SELECT" QueryHash="0xD2B5046EC138CF08" QueryPlanHash="0xAFE594D06EE293D1" RetrievedFromCache="false" SecurityPolicyApplied="false">
<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
<QueryPlan CachedPlanSize="80" CompileTime="30" CompileCPU="30" CompileMemory="1608">
<ThreadStat Branches="3" />
<Warnings>
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(date,[A].[tsstamp],0)" />
<PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(date,[A].[tsstamp],0)>='2021-06-24'" />
<PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(date,[A].[tsstamp],0)<='2021-07-04'" />
</Warnings>
<MemoryGrantInfo SerialRequiredMemory="2048" SerialDesiredMemory="32464" GrantedMemory="0" MaxUsedMemory="0" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="26424092" EstimatedPagesCached="3303011" EstimatedAvailableDegreeOfParallelism="2" MaxCompileMemory="2632800" />
<RelOp AvgRowSize="28" EstimateCPU="0.151886" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="18239.6" LogicalOp="Gather Streams" NodeId="0" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="164.664">
<OutputList>
<ColumnReference Database="[DEMODB]" Schema="[dbo]" Table="[AUDIT]" Alias="[A]" Column="usrid" />
<ColumnReference Database="[DEMODB]" Schema="[dbo]" Table="[AUDIT]" Alias="[A]" Column="usrname" />
</OutputList>
<Parallelism>
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[DEMODB]" Schema="[dbo]" Table="[AUDIT]" Alias="[A]" Column="usrname" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="28" EstimateCPU="1.04933" EstimateIO="0.00563063" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="18239.6" LogicalOp="Distinct Sort" NodeId="1" Parallel="true" PhysicalOp="Sort" EstimatedTotalSubtreeCost="164.512">
<OutputList>
<ColumnReference Database="[DEMODB]" Schema="[dbo]" Table="[AUDIT]" Alias="[A]" Column="usrid" />
<ColumnReference Database="[DEMODB]" Schema="[dbo]" Table="[AUDIT]" Alias="[A]" Column="usrname" />
</OutputList>
<MemoryFractions Input="0.134666" Output="1" />
<Sort Distinct="true">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[DEMODB]" Schema="[dbo]" Table="[AUDIT]" Alias="[A]" Column="usrname" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[DEMODB]" Schema="[dbo]" Table="[AUDIT]" Alias="[A]" Column="usrid" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="28" EstimateCPU="0.143659" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="30675" LogicalOp="Repartition Streams" NodeId="2" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="163.457">
<OutputList>
<ColumnReference Database="[DEMODB]" Schema="[dbo]" Table="[AUDIT]" Alias="[A]" Column="usrid" />
<ColumnReference Database="[DEMODB]" Schema="[dbo]" Table="[AUDIT]" Alias="[A]" Column="usrname" />
</OutputList>
<Parallelism PartitioningType="Hash">
<PartitionColumns>
<ColumnReference Database="[DEMODB]" Schema="[dbo]" Table="[AUDIT]" Alias="[A]" Column="usrname" />
<ColumnReference Database="[DEMODB]" Schema="[dbo]" Table="[AUDIT]" Alias="[A]" Column="usrid" />
</PartitionColumns>
<RelOp AvgRowSize="28" EstimateCPU="1.75944" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="30675" LogicalOp="Right Semi Join" NodeId="3" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="163.313">
<OutputList>
<ColumnReference Database="[DEMODB]" Schema="[dbo]" Table="[AUDIT]" Alias="[A]" Column="usrid" />
<ColumnReference Database="[DEMODB]" Schema="[dbo]" Table="[AUDIT]" Alias="[A]" Column="usrname" />
</OutputList>
<MemoryFractions Input="1" Output="0.865334" />
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[DEMODB]" Schema="[dbo]" Table="[Customer]" Column="docid" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[DEMODB]" Schema="[dbo]" Table="[AUDIT]" Alias="[A]" Column="docid" />
</HashKeysProbe>
<ProbeResidual>
<ScalarOperator ScalarString="[DEMODB].[dbo].[AUDIT].[docid] as [A].[docid]=[DEMODB].[dbo].[Customer].[docid]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DEMODB]" Schema="[dbo]" Table="[AUDIT]" Alias="[A]" Column="docid" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DEMODB]" Schema="[dbo]" Table="[Customer]" Column="docid" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</ProbeResidual>
<RelOp AvgRowSize="11" EstimateCPU="0.282677" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="212146" LogicalOp="Bitmap Create" NodeId="4" Parallel="true" PhysicalOp="Bitmap" EstimatedTotalSubtreeCost="150.242">
<OutputList>
<ColumnReference Database="[DEMODB]" Schema="[dbo]" Table="[Customer]" Column="docid" />
</OutputList>
<Bitmap>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Bitmap1006" />
</DefinedValue>
</DefinedValues>
<HashKeys>
<ColumnReference Database="[DEMODB]" Schema="[dbo]" Table="[Customer]" Column="docid" />
</HashKeys>
<RelOp AvgRowSize="11" EstimateCPU="0.282677" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="212146" LogicalOp="Repartition Streams" NodeId="5" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="150.242">
<OutputList>
<ColumnReference Database="[DEMODB]" Schema="[dbo]" Table="[Customer]" Column="docid" />
</OutputList>
<Parallelism PartitioningType="Hash">
<PartitionColumns>
<ColumnReference Database="[DEMODB]" Schema="[dbo]" Table="[Customer]" Column="docid" />
</PartitionColumns>
<RelOp AvgRowSize="11" EstimateCPU="49.4448" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="212146" LogicalOp="Partial Aggregate" NodeId="6" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="149.96">
<OutputList>
<ColumnReference Database="[DEMODB]" Schema="[dbo]" Table="[Customer]" Column="docid" />
</OutputList>
<MemoryFractions Input="0" Output="0" />
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[DEMODB]" Schema="[dbo]" Table="[Customer]" Column="docid" />
</HashKeysBuild>
<BuildResidual>
<ScalarOperator ScalarString="[DEMODB].[dbo].[Customer].[docid] = [DEMODB].[dbo].[Customer].[docid]">
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DEMODB]" Schema="[dbo]" Table="[Customer]" Column="docid" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DEMODB]" Schema="[dbo]" Table="[Customer]" Column="docid" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</BuildResidual>
<RelOp AvgRowSize="11" EstimateCPU="8.03384" EstimateIO="92.481" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="14606900" EstimatedRowsRead="14606900" LogicalOp="Table Scan" NodeId="7" Parallel="true" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="100.515" TableCardinality="14606900">
<OutputList>
<ColumnReference Database="[DEMODB]" Schema="[dbo]" Table="[Customer]" Column="docid" />
</OutputList>
<TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[DEMODB]" Schema="[dbo]" Table="[Customer]" Column="docid" />
</DefinedValue>
</DefinedValues>
<Object Database="[DEMODB]" Schema="[dbo]" Table="[Customer]" IndexKind="Heap" Storage="RowStore" />
</TableScan>
</RelOp>
</Hash>
</RelOp>
</Parallelism>
</RelOp>
</Bitmap>
</RelOp>
<RelOp AvgRowSize="32" EstimateCPU="0.183895" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="64596.7" LogicalOp="Repartition Streams" NodeId="8" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="11.3113">
<OutputList>
<ColumnReference Database="[DEMODB]" Schema="[dbo]" Table="[AUDIT]" Alias="[A]" Column="usrid" />
<ColumnReference Database="[DEMODB]" Schema="[dbo]" Table="[AUDIT]" Alias="[A]" Column="docid" />
<ColumnReference Database="[DEMODB]" Schema="[dbo]" Table="[AUDIT]" Alias="[A]" Column="usrname" />
</OutputList>
<Parallelism PartitioningType="Hash">
<PartitionColumns>
<ColumnReference Database="[DEMODB]" Schema="[dbo]" Table="[AUDIT]" Alias="[A]" Column="docid" />
</PartitionColumns>
<RelOp AvgRowSize="53" EstimateCPU="0.394836" EstimateIO="10.3809" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="64596.7" EstimatedRowsRead="717741" LogicalOp="Clustered Index Scan" NodeId="10" Parallel="true" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="10.7757" TableCardinality="717741">
<OutputList>
<ColumnReference Database="[DEMODB]" Schema="[dbo]" Table="[AUDIT]" Alias="[A]" Column="usrid" />
<ColumnReference Database="[DEMODB]" Schema="[dbo]" Table="[AUDIT]" Alias="[A]" Column="docid" />
<ColumnReference Database="[DEMODB]" Schema="[dbo]" Table="[AUDIT]" Alias="[A]" Column="usrname" />
</OutputList>
<IndexScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[DEMODB]" Schema="[dbo]" Table="[AUDIT]" Alias="[A]" Column="usrid" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[DEMODB]" Schema="[dbo]" Table="[AUDIT]" Alias="[A]" Column="docid" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[DEMODB]" Schema="[dbo]" Table="[AUDIT]" Alias="[A]" Column="usrname" />
</DefinedValue>
</DefinedValues>
<Object Database="[DEMODB]" Schema="[dbo]" Table="[AUDIT]" Index="[primeind]" Alias="[A]" IndexKind="Clustered" Storage="RowStore" />
<Predicate>
<ScalarOperator ScalarString="CONVERT_IMPLICIT(date,[DEMODB].[dbo].[AUDIT].[tsstamp] as [A].[tsstamp],0)>='2021-06-24' AND CONVERT_IMPLICIT(date,[DEMODB].[dbo].[AUDIT].[tsstamp] as [A].[tsstamp],0)<='2021-07-04' AND PROBE([Bitmap1006],[DEMODB].[dbo].[AUDIT].[docid] as [A].[docid],N'[IN ROW]')">
<Logical Operation="AND">
<ScalarOperator>
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="GE">
<ScalarOperator>
<Convert DataType="date" Style="0" Implicit="true">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DEMODB]" Schema="[dbo]" Table="[AUDIT]" Alias="[A]" Column="tsstamp" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2021-06-24'" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="LE">
<ScalarOperator>
<Convert DataType="date" Style="0" Implicit="true">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DEMODB]" Schema="[dbo]" Table="[AUDIT]" Alias="[A]" Column="tsstamp" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2021-07-04'" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
<ScalarOperator>
<Intrinsic FunctionName="PROBE">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Bitmap1006" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DEMODB]" Schema="[dbo]" Table="[AUDIT]" Alias="[A]" Column="docid" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="N'[IN ROW]'" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</Parallelism>
</RelOp>
</Hash>
</RelOp>
</Parallelism>
</RelOp>
</Sort>
</RelOp>
</Parallelism>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
解决方案
我建议EXISTS在这里使用。
SELECT DISTINCT Usrid
,usrname AS Usrname
FROM Audit a
WHERE tsstamp >= Convert(DATE, '6/24/2021', 101)
AND tsstamp <= Convert(DATE, '7/4/2021', 101)
AND EXISTS (
SELECT 1
FROM [Customer] c
WHERE c.docid = a.Docid
)
--Docid in (Select docid from [Customer] group by docid )
GROUP BY usrid
,usrname
ORDER BY usrname
推荐阅读
- c# - 具体类可以将方法实现委托给子类吗？
- google-sheets - Google 表格：如何组合过滤器、查询和 MINIFS
- android - Mockito：需要但未调用，ViewModel 协程测试
- node.js - 从 AWS Lambda 层导入依赖项时找不到模块“jsonwebtoken”错误
- c# - 如何使用 Get 和 c# HttpClient 发送复杂对象
- javascript - 类型'typeof import ...'上不存在属性'auth''firebase / auth
- c++ - std::make_unique
- python - ImportError：尝试将 Odoo 10 数据库迁移到 Odoo 11 时没有名为“werkzeug”的模块
- reactjs - 从 AsyncStorage 导入数据并推送到 firebase react native
- format - 在 FORTRAN 的 2003/2008 版本中需要格式化 READ/WRITE 的帮助