这基本上与中提出的问题相同(在Postgres中加速慢速SELECT DISTINCT查询的解决方案)
同样的情况是,部署了一个庞大的数据库,由于旧的遗留应用程序,不可能将其正常化。不断添加新的行,删除旧的行,以满足某些标准。我已经通过使用CTE尝试了这个建议,但我认为这里没有性能提升,与原始的执行时间大致相同
select distinct [somecolumn]
from bigtable
同样适用于使用Group by
的建议。
似乎效果最好的是建议创建一个视图并查询该视图。(缓存已在查询之间重新设置种子)
我需要一些关于这方面的建议,因为我真的不明白为什么这会带来更好的性能。
create view [dbo].[vwDistinct]
with schemabinding
as
select
[somecolumn], count_big(*) as TableCount
from
dbo.BigTable
where
somecolumn IS NOT NULL
group by
somecolumn;
select distinct somecolumn
from vwDistinct
应用程序使用存储过程进行调用。该数据库位于SQL Server 2008 R2上,但如果有充分的理由,可以将其移动到SQL Server 2014。
谢谢
这是从vwDistinct 中选择某个列
<?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.2" Build="12.0.2000.8" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="41138.3" StatementId="1" StatementOptmLevel="FULL" CardinalityEstimationModelVersion="70" StatementSubTreeCost="5.10782" StatementText="select somecolumn from vwDistinct" StatementType="SELECT" QueryHash="0x23700E4CF62A8E4E" QueryPlanHash="0x79D8240601D270CB" RetrievedFromCache="true">
<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
<QueryPlan NonParallelPlanReason="EstimatedDOPIsOne" CachedPlanSize="16" CompileTime="59" CompileCPU="18" CompileMemory="336">
<MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1239807" EstimatedPagesCached="77487" EstimatedAvailableDegreeOfParallelism="1" />
<RelOp AvgRowSize="37" EstimateCPU="1.03877" EstimateIO="4.06905" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="944197" LogicalOp="Clustered Index Scan" NodeId="1" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="5.10782" TableCardinality="944197">
<OutputList>
<ColumnReference Database="[BigData]" Schema="[dbo]" Table="[vwDistinct]" Column="somecolumn" />
</OutputList>
<IndexScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="true" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[BigData]" Schema="[dbo]" Table="[vwDistinct]" Column="somecolumn" />
</DefinedValue>
</DefinedValues>
<Object Database="[BigData]" Schema="[dbo]" Table="[vwDistinct]" Index="[cdxDistinct]" IndexKind="ViewClustered" Storage="RowStore" />
<IndexedViewInfo>
<Object Database="[BigData]" Schema="[dbo]" Table="[BigTable]" />
</IndexedViewInfo>
</IndexScan>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
<Object Database="[BigData]" Schema="[dbo]" Table="[vwDistinct]" Index="[cdxDistinct]" IndexKind="ViewClustered" Storage="RowStore" />
显示您的视图已编入索引。根据您对的评论
大约2.5-3百万行,并且有大约100个不同的值。
查询
select distinct [somecolumn]
from bigtable
如果没有该视图,将扫描表索引中的250多万行,以找到所有不同的值。
但是,该视图将只包含100行。因此,当它存在时,它可以对视图的聚集索引执行扫描,以找到所有不同的值。
代价是修改somecolumn
的所有插入和任何更新都将更加昂贵。