如果在数据检索时几乎所有列都在使用，那么SQL Server 2012的新特性列存储架构是多么有用

Question

我有一个大型表的庞大数据库，而从多个表中检索数据(查询大约由10个内部连接组成)需要很长时间(在某些情况下10-20秒或分钟)才能从数据库中获取数据。该查询需要大多数表的几乎所有列和其他表的近一半列。

我的问题是在我的情况下使用SQL Server 2012列存储架构有多大帮助。目前我使用的是SQL Server 2008。任何关于时间优化的建议都是受欢迎的，因为我非常需要优化这种类型的查询

SELECT Seq.SeqId,Seq.ThreadId, Seq.Subject,LastAccessed, Seq.State, Seq.AlarmSent,Prop.RegionCode ,Seq.Rating , Seq.ClassId, Seq.SeqMedium, 
Seq.SeqPriority, Seq.StatusDate , Seq.SeqCreateDt, Seq.StartDate, Seq.LastModified, Seq.Resolved, Seq.SeqSrcId, Seq.StatusReason , SeqDocument.DocId, 
SeqDocument.IsCheckedOut, SeqDocument.CheckedOutById , SeqDocument.Remarks, Prop.PahLName ,OC.ClassID,OC.ClassName,OC.ClassShortName,OC.Description ,
OC.ParentID,OC2.ClassName AS ParentClassName,OC.OrgID ,CM.DeptID,CM.Severity,CM.Nature ,ST.TargetClsId,ST.TargetSubClsId,ST.TargetId, ST.ResponderActive, 
ST.TargetType, ST.SentDate ,SF.FlowId, SF.InwardNo, SF.OutwardNo,SF.InwardDate,SF.OutwardDate ,SF.FlowOrder,SF.OriginatorState,SF.ResponderState,SF.PrevFlowId ,
SF.SeqFlowSrcClsId,SF.SeqFlowSrcId, OrgP.Name, SF.FlowStatus  , 
CASE Prop.BCPNO  WHEN '' THEN Prop.TempNo  ELSE Prop.BCPNO END  AS FileNo, Prop.DeveloperName ,PROP.BCPNO,PROP.CaseType, Prop.OwnerFName,
PROP.InwardDate as ProposalInwardDate, PROP.CaseTrack ,PROP.TempNo, PROP.FastTrackNo ,Prop.RevNo ,PROP.CTSNo as CTSNo, PROP.FinalPlotNo as FinalPlotNo, 
SeqDocument.CurrentSequence,IsNull(PROP.ProposalID,0) as ProposalID,IsNull(PROP.PlotRegion,0) as PlotRegion,
IsNull(Prop.ReservationID,0) as ReservationID, Prop.ADRunKey,IsNull(Prop.ApplicantName, '') as ApplicantName, Prop.CaseSubType,
IsNull(Prop.OwnerPeth,'') as OwnerPeth,   Prop.ProposedProject, P.Name as ConsultantName, Prop.CURRENTSTAGEID, Seq.SequenceMode, Seq.Sequencebody, 
Seq.Phaseid, PROP.RegionCode, Seq.SequenceNo, Seq.Validity, Seq.BillDate, Seq.BillNo, Seq.TransactionRegion 
FROM Sequence Seq 
INNER JOIN Sequence$Document SeqDocument ON Seq.SeqId = SeqDocument.DocId 
LEFT OUTER JOIN ClassMapping CM ON Seq.ClassId = CM.ClassID 
LEFT OUTER JOIN ObjectClass OC ON CM.ClassID = OC.ClassID 
LEFT OUTER JOIN ObjectClass  OC2 ON OC.ParentID = OC2.ClassID 
LEFT OUTER JOIN Sequence_Flow SF ON Seq.SeqID = SF.SeqID 
LEFT OUTER JOIN OrgPeople OrgP ON Seq.SeqSrcId = OrgP.Id And Seq.SeqSrcClsId = OrgP.ClsId  
LEFT OUTER JOIN TemplateProposals PROP ON Seq.SeqID = PROP.DocId 
LEFT OUTER JOIN Sequence_Flow_Target ST ON SF.FlowId = ST.FlowId 
LEFT OUTER JOIN People P ON P.PeopleId = PROP.ArchitectId 
WHERE SeqClsId = 6 AND SeqSubClsId = 1  AND Seq.SeqTypId = 3 
AND Seq.SeqSubTypId = 0 AND Seq.State IN (1,2,3,5)  AND ST.ResponderActive IN ('1', '2') 
AND ST.TARGETPOSTID IN (132,1333,135,136 )  
Order By  ProposalInwardDate  DESC

此查询在Intel (i5 3.00GHz) 8GB RAM上花费50秒(大约)，结果为5676行。

序列176232行38列，序列$文档132776行8列，6行10列，对象类6行11列，Sequence_Flow 4847730行22列，组织人员4656行11列，90889行64列，Sequence_Flow_Target 521621行9列，人4655行67列。

所有属性数据类型都在(numeric, string, Date)

Answer 1

在你的情况下，Columnstore索引将不太有用-你正在使用左连接，不支持SQL Server 2012的批处理执行模式。你会得到压缩的改进(它的质量将取决于有多相似)。字符串列有多大)，但不幸的是，总的来说没有明显的改进。

迁移到SQL Server 2014和集群Columnstore可能会帮助你很多。:)