大数据量存储过程优化

[杨松凯]

现有2个存储，经测试发现，第一存储快于第二个存储，可我还是觉得第二个存储会快些。在本地测试第一个存储还过得去，第二个就坑爹了。放到服务器上：第一个还能执行，第二个直接就超时。现在就是搞不懂是服务器的问题还是存储的问题。还有，第二个存储是否真的就比第一个慢？ 第一个存储或第二个是否还可以优化。本地数据库，一张表为10000+的数据，服务器上貌似就50000起，或者10W+，我应该怎么优化。

USE [SGPS_SZ]
GO
/****** Object:  StoredProcedure [dbo].[spMileageStatement]    Script Date: 07/19/2012 13:39:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,YangSongKai>
-- Create date: <Create 2012-07-02,,>
-- Description:	<Description,,里程报表>
-- =============================================

ALTER PROCEDURE [dbo].[spMileageStatement](
 @CarNo varchar(30),--车牌号
 @CompanyId varchar(100),--驾校id
 @StartDay varchar(20),--开始日期（年月日）
 @EndDay varchar(20),--结束日期
 @StartTime varchar(20),--开始时间（时分秒）
 @EndTime varchar(20)--结束时间
	)
AS

--自定义变量
declare @result int--日期相差多少天
declare @S datetime--开始时间
declare @E datetime--结束时间
declare @GpsPath varchar(30)--表名
declare @number int--循环种子
declare @WHERES VARCHAR(1000)
declare @SQL VARCHAR(3000)
declare @t varchar(20)
set @S=CONVERT(Datetime,@StartDay+@StartTime)
set @E=CONVERT(Datetime,@StartDay+@EndTime)
--set @GpsPath='dbo.GpsPath'+CONVERT( VARCHAR(8), CAST(FLOOR(CAST(@S AS FLOAT)) AS DATETIME), 112)
set @number=0
set @result=DATEDIFF (d,CONVERT(Datetime,@StartDay),CONVERT(Datetime,@EndDay))

create TABLE #temptb    
(   
    TerminalNo varchar(20),
    CarNo varchar(20),
    Gpstime varchar(100),
    CompanyName varchar(50),
    Mileage decimal(18,2),
)

 
declare @begin_date datetime declare @end_date datetime select @begin_date = getdate() 
	while(@number<=@result)
    begin
    set @GpsPath='dbo.GpsPath'+CONVERT( VARCHAR(8), CAST(FLOOR(CAST(@S AS FLOAT)) AS DATETIME), 112)
    IF(@CarNo='' or @CarNo is null)
	SET @WHERES=' AND dbo.Company.CompanyId='''+@CompanyId+''' and GpsTime between '''+CONVERT(VARCHAR(19),@S,120)+''' and '''+CONVERT(VARCHAR(19),@E,120)+''''
	ELSE
	begin
	set @t=(select TerminalNo from Terminal where CarId=(select CarId from Car where CarNo=@CarNo))
    set @WHERES=' and GpsTime between '''+CONVERT(VARCHAR(19),@S,120)+''' and '''+CONVERT(VARCHAR(19),@E,120)+''' and dbo.Terminal.TerminalNo='''+@t+''''
	end
    --循环查出数据集插入到临时表中
	SET @SQL='insert into #temptb(TerminalNo,CarNo,Gpstime,CompanyName,Mileage)(SELECT '+@GpsPath+'.TerminalNo,CarNo,GpsTime,CompanyName,Mileage 
	from '+@GpsPath+'
	inner join dbo.Terminal on '+@GpsPath+'.TerminalNo=dbo.Terminal.TerminalNo
	inner join dbo.Car on dbo.Terminal.CarId=dbo.Car.CarId
	inner join dbo.Company on dbo.Car.CompanyId=dbo.Company.CompanyId 
	 WHERE 1=1 '+@WHERES+'
	group by '+@GpsPath+'.TerminalNo,CarNo,GpsTime,CompanyName,dbo.Company.CompanyId,Mileage)'
    Exec(@SQL) 
    
	set @number=@number+1
	set @S=DATEADD(d,1,@S)
	set @E=DATEADD(d,1,@E)
	end


	select ROW_NUMBER() Over(order by CompanyName desc) as rowId,TerminalNo,CarNo,CompanyName,sum(Mileage) Mileage
	from(
		select TerminalNo,CarNo,CompanyName,convert(varchar(8),Gpstime,112) date,
			   max(Mileage) - min(Mileage) mileage
		from #temptb
		group by TerminalNo,CarNo,CompanyName,convert(varchar(8),Gpstime,112)
	) t
	group by TerminalNo,CarNo,CompanyName --order by TerminalNo asc
		
if object_id('tempdb..#emptb')>0
drop table #temptb



select @end_date = getdate() select datediff(ms,@begin_date,@end_date) as '用时/毫秒'

第二个：

USE [SGPS_SZ]
GO
/****** Object:  StoredProcedure [dbo].[spMileageStatement]    Script Date: 07/20/2012 08:54:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,YangSongKai>
-- Create date: <Create 2012-07-02,,>
-- Description:	<Description,,里程报表>
-- =============================================

ALTER PROCEDURE [dbo].[spMileageStatement](
 @CarNo varchar(30),--车牌号
 @CompanyId varchar(100),--驾校id
 @StartDay varchar(20),--开始日期（年月日）
 @EndDay varchar(20),--结束日期
 @StartTime varchar(20),--开始时间（时分秒）
 @EndTime varchar(20)--结束时间
	)
AS

--自定义变量
declare @result int--日期相差多少天
declare @S datetime--开始时间
declare @E datetime--结束时间
declare @GpsPath varchar(30)--表名
declare @number int--循环种子
declare @WHERES VARCHAR(1000)
declare @SQL VARCHAR(3000)
declare @t varchar(20)
set @S=CONVERT(Datetime,@StartDay+@StartTime)
set @E=CONVERT(Datetime,@StartDay+@EndTime)
--set @GpsPath='dbo.GpsPath'+CONVERT( VARCHAR(8), CAST(FLOOR(CAST(@S AS FLOAT)) AS DATETIME), 112)
set @number=0
set @result=DATEDIFF (d,CONVERT(Datetime,@StartDay),CONVERT(Datetime,@EndDay))

create TABLE #temptb    
(   
    TerminalNo varchar(20),
    CarNo varchar(20),
    Gpstime varchar(100),
    CompanyName varchar(50),
    Mileage decimal(18,2),
)

 
declare @begin_date datetime declare @end_date datetime select @begin_date = getdate() 
	while(@number<=@result)
    begin
    set @GpsPath='dbo.GpsPath'+CONVERT( VARCHAR(8), CAST(FLOOR(CAST(@S AS FLOAT)) AS DATETIME), 112)
    IF(@CarNo='' or @CarNo is null)
	--SET @WHERES=' AND dbo.Company.CompanyId='''+@CompanyId+''' and GpsTime between '''+CONVERT(VARCHAR(19),@S,120)+''' and '''+CONVERT(VARCHAR(19),@E,120)+''''
	SET @WHERES=' and GpsTime between '''+CONVERT(VARCHAR(19),@S,120)+''' and '''+CONVERT(VARCHAR(19),@E,120)+''' and TerminalNo in (select TerminalNo from Terminal where CarId in (select CarId from Car where CompanyId='''+@CompanyId+'''))'
	ELSE
	begin
	set @t=(select TerminalNo from Terminal where CarId=(select CarId from Car where CarNo=@CarNo))
    set @WHERES=' and GpsTime between '''+CONVERT(VARCHAR(19),@S,120)+''' and '''+CONVERT(VARCHAR(19),@E,120)+''' and dbo.Terminal.TerminalNo='''+@t+''''
	end
    
	SET @SQL='insert into #temptb(TerminalNo,GpsTime,Mileage)(SELECT TerminalNo,GpsTime,Mileage 
	from '+@GpsPath+'
	 WHERE 1=1 '+@WHERES+' 
	)'
    Exec(@SQL) 
	set @number=@number+1
	set @S=DATEADD(d,1,@S)
	set @E=DATEADD(d,1,@E)
	end
select t.TerminalNo,sum(Mileage) Mileage,CarNo,CompanyName
from(
    select TerminalNo,convert(varchar(8),Gpstime,112) date,
           max(Mileage) - min(Mileage) mileage
    from #temptb
    group by TerminalNo,convert(varchar(8),Gpstime,112)
) t
inner join dbo.Terminal on t.TerminalNo=dbo.Terminal.TerminalNo
	inner join dbo.Car on dbo.Terminal.CarId=dbo.Car.CarId
	inner join dbo.Company on dbo.Car.CompanyId=dbo.Company.CompanyId 
group by t.TerminalNo,CarNo,CompanyName
		
if object_id('tempdb..#emptb')>0
drop table #temptb



select @end_date = getdate() select datediff(ms,@begin_date,@end_date) as '用时/毫秒'

，请大家帮忙看看。

[天-笑]

仔细看了下，我也觉得第一个块，
先提两个优化的地方

--1:
declare @SQL VARCHAR(3000)
--改成 
declare @SQL NVARCHAR(3000)
Exec(@SQL) 
--改成 （下面的语句可以重复利用执行计划）
execute sp_executesql @Sql

--2：
SET @WHERES=' and GpsTime between '''+CONVERT(VARCHAR(19),@S,120)+''' and '''+CONVERT(VARCHAR(19),@E,120)+''' and TerminalNo in (select TerminalNo from Terminal where CarId in (select CarId from Car where CompanyId='''+@CompanyId+'''))'

这里where 里的in 用exists  替换