大数据量存储过程优化

杨松凯 2012-07-20 02:44:01

现有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 '用时/毫秒'

，请大家帮忙看看。

...全文

195 1 打赏收藏转发到动态举报

写回复

用AI写文章

1 条回复

切换为时间正序

请发表友善的回复…

发表回复

天-笑 2012-07-20

打赏
举报

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



--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  替换