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),			--机器编号
@bDate	NVARCHAR(20),					--开始日期
@eDate	NVARCHAR(20)					--结束日期
AS
SET NOCOUNT ON
DECLARE @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<>''
BEGIN
	SELECT @ip=IPAddr FROM DBO.CommunicationInfo WHERE MachineNo=@MachineNo
	IF @ip is NOT NULL AND @ip<>''
	  SET @wherestr+=' And A.ClientIP='''+@ip+''''
END
SET @bDate=CASE when ISNULL(@bDate,'')='' THEN CONVERT(VARCHAR(20),GETDATE(),120) else @bDate END
SET @eDate=CASE when ISNULL(@eDate,'')='' THEN CONVERT(VARCHAR(20),GETDATE(),120) else @eDate END
SET @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 Y
FROM(
	SELECT A.TotalNum,b.ParaName,CAST(b.ParaValue AS decimal(18,2)) ParaValue
	from dbo.主表 A(nolock) inner JOIN dbo.明细表B(nolock)
	ON A.id=b.Pid
	WHERE  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),			--机器编号
@bDate	NVARCHAR(20),					--开始日期
@eDate	NVARCHAR(20)					--结束日期
AS
SET NOCOUNT ON
DECLARE @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<>''
BEGIN
	SELECT @ip=IPAddr FROM DBO.CommunicationInfo WHERE MachineNo=@MachineNo
	IF @ip is NOT NULL AND @ip<>''
	  SET @wherestr+=' And ClientIP='''+@ip+''''
END
SET @bDate=CASE when ISNULL(@bDate,'')='' THEN CONVERT(VARCHAR(20),GETDATE(),120) else @bDate END
SET @eDate=CASE when ISNULL(@eDate,'')='' THEN CONVERT(VARCHAR(20),GETDATE(),120) else @eDate END
SET @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 '+@wherestr
exec(@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)) ParaValue
	into #tmp2
	from #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 B
	ON A.id=b.Pid
insert into #tmp(X,legend,Y)
SELECT TotalNum AS X,ParaName AS legend,AVG(ParaValue) AS Y
FROM #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=KebaVar
ORDER BY A.X

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

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

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