速度有点慢，这样的sql怎么优化

[Dragon_pan]

ALTER PROCEDURE [dbo].[Report_ProductionOrderByDate_sp] 
	-- Add the parameters for the stored procedure here
(   @InputString nvarchar(4000),
	@Start Datetime,
	@End datetime,
	@RegionType nvarchar(50)
) 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	CREATE TABLE #tempShredder (machineid uniqueidentifier) 
	insert   into   #tempShredder 
		exec dbo.Report_GetMachineIDs @InputString, @RegionType
    SELECT cdt.Denomnation,cdt.Denomversion,ralt.Quality,sum(ralt.Number) AS DenomNum,CONVERT(varchar(11),rsptA.ParsectStartTime,120) AS ParsectDate,css.SortingmodeName
	FROM Report_AccunitLog_tb ralt  
	INNER JOIN Config_Denom_tb cdt ON cdt.DenomId = ralt.DenomId
	INNER JOIN Report_ShiftParsect_tb rsptA ON rsptA.ShiftParsectId = ralt.ShiftParsectId
	INNER JOIN Config_StackingSorting_tb css ON css.StackingSortingModeId = rsptA.StackingSortingModeId
	WHERE ralt.ShiftParsectId IN 
		(
			SELECT rspt.ShiftParsectId FROM Report_Machine_tb rmt
			INNER JOIN Report_Identifier_tb rit ON rit.MachineId = rmt.MachineId
			INNER JOIN Report_ShiftParsect_tb rspt ON rit.ReportId=rspt.ReportId
			WHERE  rspt.ParsectStartTime BETWEEN @Start AND @End
			AND rmt.MachineId IN 
				( 
					SELECT machineid FROM #tempShredder
				)
		)
	GROUP BY cdt.Denomnation,cdt.Denomversion,quality,CONVERT(varchar(11),rsptA.ParsectStartTime,120),css.SortingmodeName
	ORDER BY CONVERT(varchar(11),rsptA.ParsectStartTime,120)
	DROP TABLE #tempShredder
END

[GraspXJK]

in和inner join 都是比较慢的，如果有表连接，要看具体每张表的数据量和运算次数，不然连接起来会很慢的

[hyde100]

研究下

[ws_hgo]

哇！友情帮UP

[csdyyr]

in-->join

[hyde100]

像DenomId连接字段上索引都建了吗？

[nzperfect]

没有数据，没有结构，不知道业务关系，单看语句，没法给你优化。

[Dragon_pan]

是的，建了索引了

[flairsky]

考虑把where里面的in改为join链接

[Dragon_pan]

外键是否也要加索引

[水族杰纶]

--TRY
in --> exists

[Dragon_pan]

ALTER PROCEDURE [dbo].[Report_ProductionOrderByDate_sp] 
	-- Add the parameters for the stored procedure here
(   @InputString nvarchar(4000),
	@Start Datetime,
	@End datetime,
	@RegionType nvarchar(50)
) 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	CREATE TABLE #tempShredder (machineid uniqueidentifier) 
	insert   into   #tempShredder 
		exec dbo.Report_GetMachineIDs @InputString, @RegionType
    SELECT cdt.Denomnation,cdt.Denomversion,ralt.Quality,sum(ralt.Number) AS DenomNum,CONVERT(varchar(11),rsptA.ParsectStartTime,120) AS ParsectDate,css.SortingmodeName
	FROM Report_AccunitLog_tb ralt  
	INNER JOIN Config_Denom_tb cdt ON cdt.DenomId = ralt.DenomId
	INNER JOIN Report_ShiftParsect_tb rsptA ON rsptA.ShiftParsectId = ralt.ShiftParsectId
	INNER JOIN Config_StackingSorting_tb css ON css.StackingSortingModeId = rsptA.StackingSortingModeId
	INNER JOIN Report_ShiftParsect_tb rspt ON rspt.ShiftParsectId = ralt.ShiftParsectId
	INNER JOIN Report_Identifier_tb rit ON rit.ReportId=rspt.ReportId
	INNER JOIN Report_Machine_tb rmt ON rmt.MachineId = rit.MachineId		
	WHERE  rspt.ParsectStartTime BETWEEN @Start AND @End
	AND rmt.MachineId IN 
	( 
		SELECT machineid FROM #tempShredder
	)
	GROUP BY cdt.Denomnation,cdt.Denomversion,quality,CONVERT(varchar(11),rsptA.ParsectStartTime,120),css.SortingmodeName
	ORDER BY CONVERT(varchar(11),rsptA.ParsectStartTime,120)
	DROP TABLE #tempShredder
END

换掉后反而没有以前的快了

[fcuandy]

非相关子查询 in 和 exists查询计划一样的。
相关子查询，exists可能更低于in.




把
SELECT rspt.ShiftParsectId FROM Report_Machine_tb rmt
INNER JOIN Report_Identifier_tb rit ON rit.MachineId = rmt.MachineId
INNER JOIN Report_ShiftParsect_tb rspt ON rit.ReportId=rspt.ReportId
WHERE rspt.ParsectStartTime BETWEEN @Start AND @End
AND rmt.MachineId IN
(
SELECT machineid FROM #tempShredder
)

生成临时表，

然后后再直接 where ... in(select ..临时表)
看看。

另外，建立合适的索引。

where rspt.ParsectStartTime BETWEEN @Start AND @End
如果是sql2005的话，可以尝试用:
where datediff(mi,@start,rspt....Time)>=0 and datediff...

也可以考滤in改内连接，不过要根具对应关系小心处理，否则可能取的数据都错了,比如重复行等等,或者效率更低下 。

[Dragon_pan]

经过优化，速度有了明显的提升，用了临时表，不能用太多的inner join