MSSQL联表查询优化

       联表查询是数据库系统常用的SQL方法，内联查询更是非常常见，如主表与明细查询，当主从表数据不大时，直接内联查询，其性能是可以接受的，当数据记录达到10万级、百万级、千万级时，直接内联查询，其性能就难以接受了（超过2秒的查询都会让用户很焦躁），特别是还有客户端在同时写操作，这里可以看一下优化实例：

A、未优化前的MSSQL

-- =============================================-- Author:<baronyang>-- Create date: <Create Date,,>-- Description:<未优化的SQL>-- =============================================Alter PROCEDURE dbo.存储过程名@ValNames NVARCHAR(max),--变量名称,多个用逗号隔开@DispatchOrder NVARCHAR(50),--派单号@MachineNo   NVARCHAR(50),--机器编号@bDateNVARCHAR(20),--开始日期@eDateNVARCHAR(20)--结束日期ASSET NOCOUNT ONDECLARE @sql NVARCHAR(max)='',@wherestr NVARCHAR(max)=''DECLARE @ip NVARCHAR(50)=''IF @DispatchOrder IS NOT NULL AND @DispatchOrder<>''  SET @wherestr+=' And A.DispatchOrder='''+@DispatchOrder+''''IF @MachineNo IS NOT NULL AND @MachineNo<>''BEGINSELECT @ip=IPAddr FROM DBO.CommunicationInfo WHERE MachineNo=@MachineNoIF @ip is NOT NULL AND @ip<>''  SET @wherestr+=' And A.ClientIP='''+@ip+''''ENDSET @bDate=CASE when ISNULL(@bDate,'')='' THEN CONVERT(VARCHAR(20),GETDATE(),120) else @bDate ENDSET @eDate=CASE when ISNULL(@eDate,'')='' THEN CONVERT(VARCHAR(20),GETDATE(),120) else @eDate ENDSET @wherestr+=' And A.UpLoadTime>='''+@bDate+''' And A.UpLoadTime<='''+@eDate+''''CREATE TABLE #tmp (X INT,Y DECIMAL(18,2),legend NVARCHAR(200))SET @sql='insert into #tmp(X,legend,Y)SELECT TotalNum AS X,ParaName AS legend,AVG(ParaValue) AS YFROM(SELECT A.TotalNum,b.ParaName,CAST(b.ParaValue AS decimal(18,2)) ParaValuefrom dbo.主表 A(nolock) inner JOIN dbo.明细表B(nolock)ON A.id=b.PidWHERE  paraName in (select fname from  dbo.Fn_ListToTable('''+@ValNames+''','','')) '+@wherestr+') tt GROUP by TotalNum,ParaName 'print(@sql)EXEC(@sql)SELECT A.X,A.Y,B.VarName AS legend FROM #tmp A INNER JOIN dbo.SysItemList B(nolock)ON A.legend=KebaVar

 A、优化后的MSSQL

Alter PROCEDURE dbo.存储过程名@ValNames NVARCHAR(max),--变量名称,多个用逗号隔开@DispatchOrder NVARCHAR(50),--派单号@MachineNo   NVARCHAR(50),--机器编号@bDateNVARCHAR(20),--开始日期@eDateNVARCHAR(20)--结束日期ASSET NOCOUNT ONDECLARE @sql NVARCHAR(max)='',@wherestr NVARCHAR(max)=''DECLARE @ip NVARCHAR(50)=''IF @DispatchOrder IS NOT NULL AND @DispatchOrder<>''  SET @wherestr+=' And DispatchOrder='''+@DispatchOrder+''''IF @MachineNo IS NOT NULL AND @MachineNo<>''BEGINSELECT @ip=IPAddr FROM DBO.CommunicationInfo WHERE MachineNo=@MachineNoIF @ip is NOT NULL AND @ip<>''  SET @wherestr+=' And ClientIP='''+@ip+''''ENDSET @bDate=CASE when ISNULL(@bDate,'')='' THEN CONVERT(VARCHAR(20),GETDATE(),120) else @bDate ENDSET @eDate=CASE when ISNULL(@eDate,'')='' THEN CONVERT(VARCHAR(20),GETDATE(),120) else @eDate ENDSET @wherestr+=' And UpLoadTime>='''+@bDate+''' And UpLoadTime<='''+@eDate+''''declare @timewhere nvarchar(1000)=' And UpLoadTime>='''+@bDate+''' And UpLoadTime<='''+@eDate+''''--临时模次表create table #tmpHistory (id int not null,TotalNum int)declare @sql1 nvarchar(1000)='insert into #tmpHistory(id,TotalNum) select id,TotalNum from dbo.主表(nolock) where 1=1 '+@wherestrexec(@sql1)CREATE TABLE #tmp (X INT,Y DECIMAL(18,2),legend NVARCHAR(200))SET @sql='SELECT A.TotalNum,b.ParaName,CAST(b.ParaValue AS decimal(18,2)) ParaValueinto #tmp2from #tmpHistory as A inner JOIN (select pid,ParaName,ParaValue from dbo.明细表(nolock) where pid in (select id from #tmpHistory)  And paraName in (select fname from  dbo.Fn_ListToTable('''+@ValNames+''','',''))) as BON A.id=b.Pidinsert into #tmp(X,legend,Y)SELECT TotalNum AS X,ParaName AS legend,AVG(ParaValue) AS YFROM #tmp2 GROUP by TotalNum,ParaName 'EXEC(@sql)SELECT A.X,A.Y,B.VarName AS legend FROM #tmp A INNER JOIN dbo.SysItemList B(nolock)ON A.legend=KebaVarORDER BY A.X

主表记录达到6万+，明细达到450万+

未优化前表查询响应时间10秒以前，优化后2秒以内完成；

优化的思路：先将主表的查询范围和结果栏位缩小并将结果写入临时表，然后将明细的结果集缩小，再使用内联查询，这样查询响应速度快且响应时间波动小；