版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
联表查询是数据库系统常用的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秒以内完成；
优化的思路：先将主表的查询范围和结果栏位缩小并将结果写入临时表，然后将明细的结果集缩小，再使用内联查询，这样查询响应速度快且响应时间波动小；
96
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
