SQL 数据量比较大,使用零时表影响性能,

海棠是否依旧程序袁 2015-11-18 11:01:04

我现在一天数据可能在90W笔左右,有几只报表需要查询2天的数据,也就是180W左右,
我在存储过程里面使用了零时表.这样就会使Tempdb快速的增加,查询的时候tempdb会产生大量的buffer latch.
请问有没有方法优化?下面是引起自锁提示的错误:
Time out occurred while waiting for buffer latch -- type 4, bp 0000000BE1FDD000, page 1:3409296, stat 0x2ac00009, database id: 2, allocation unit id: 424070323372032/142595346661376, task 0x0000000024426748 : 0, waittime 10200, flags 0x100000001a, owning task 0x0000000024426748. Continuing to wait.

我弄得一个实体表只存放两天的数据,但是2天以外还是需要零时表,这个也不是什么好的解决方法,
各位大神帮忙解决下!万分感谢

...全文

130 9 点赞打赏收藏举报

写回复

9 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

海棠是否依旧 2015-11-19

引用 6 楼 szx1999 的回复:

OLTP系统上进行大数据查询很容易死锁。查询语句尽量加上with(nolock)吧。实在不行，把sp关键语句贴出来，我来帮你优化。

with(nolock)也加了,tempdb產生的卻是自鎖. ALTER PROCEDURE [dbo].[P_NewREPORT_KPI_Production_FirstYieldRate] -- Add the parameters for the stored procedure here @Model varchar(500), @Line varchar(500), @DateFrom varchar(50), @DateTo varchar(50), @repair varchar(20), --NoRepair:不含維修 Repair:僅維修 ALL:所有, @pageSizes INT=10000, @pageIndexs int=1 AS --exec P_NewREPORT_KPI_Production_FirstYieldRate '20-8J601AP01','','2015-07-14 08:00:00','2015-07-15 08:00:00','ALL',30,1 BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; declare @SqlText varchar(max)='' declare @SqlWhere varchar(max)='' declare @SqlReport varchar(max)='' select value into #tableModel from dbo.[f_char_split_to_table](@Model,',') select value into #tableLine from dbo.[f_char_split_to_table](@Line,',') if(len(rtrim(ltrim(@Model)))>0 ) begin set @SqlWhere = @SqlWhere + ' AND M.Model_No in (select value from #tableModel) ' end if(len(rtrim(ltrim(@Line)))>0) begin set @SqlWhere = @SqlWhere + ' AND M.LINE_CODE in ( select value from #tableLine ) ' end --不含維修 if(@repair='NoRepair') set @SqlReport=@SqlReport+' AND not exists(select 1 from #Repair R where M.BarCode=R.BarCode and M.Test_Time>R.RepairTime)' --僅維修 if(@repair='Repair') set @SqlReport=@SqlReport+' AND exists(select 1 from #Repair R where M.BarCode=R.BarCode)' set @SqlText = @SqlText+' Create Table #Main_Table( MODEL_NO varchar(50), LINE_CODE VARCHAR(50), STATION_CODE varchar(50), BARCODE varchar(50), TestResult VARCHAR(50), Test_Time datetime, Error_Code varchar(500), Host_Name varchar(100), Host_IP varchar(1000), Host_MAC varchar(1000), MACHINE_NO varchar(100) ) ----從原始數據讀取（全部寫入的那種）這一段代碼是原始數據,兩天有180W if exists(SELECT 1 FROM (SELECT MIN(BegTime) BegTime, MAX(EndTime) EndTime FROM dbo.MES_Report_Main_ETL_TimeControl WITH(NOLOCK)) A WHERE BegTime<='''+@DateFrom+''' AND EndTime>='''+@DateTo+''') begin insert into #Main_Table(MODEL_NO,LINE_CODE,STATION_CODE,BARCODE,TestResult,Test_Time,Error_Code,Host_Name,Host_IP,Host_MAC,MACHINE_NO) select MODEL_NO,LINE_CODE,STATION_CODE,BARCODE,Result,Last_Datetime,Error_Code,Host_Name,Host_IP,Host_MAC,MACHINE_NO from MES_Report_Main_ETL_RowData M with(nolock) where Last_Datetime>='''+@DateFrom+''' and Last_Datetime<'''+@DateTo+''' end else begin insert into #Main_Table(MODEL_NO,LINE_CODE,STATION_CODE,BARCODE,TestResult,Test_Time,Error_Code,Host_Name,Host_IP,Host_MAC,MACHINE_NO) exec P_Report_KPI_ETL_GetRawData '''+@DateFrom+''','''+@DateTo+''' end --取得每個條碼第一次的測試時間表 select MM.BARCODE,MIN(Test_Time) Test_Time INTO #firstTable from #Main_Table MM with(nolock) where TestResult=''FAIL'' group by MM.BARCODE ' set @SqlText = @SqlText+' --取出投入數和產出數 select A.MODEL_NO,A.STATION_CODE, A.InputTotal,(CAST(CAST(A.InputTotal as int) +CAST(FailTotal as int)as varchar(2))) as OutputTotal,isnull(B.FailTotal,0) FailTotal into #tt from ( --查出各站的總投入次數，入維修的BarCode在之后站不算投入 select M.MODEL_NO,M.STATION_CODE,COUNT(distinct BARCODE) InputTotal from #Main_Table M with(nolock) where not exists(select BARCODE from #firstTable R where BARCODE=M.BARCODE and M.Test_Time>R.Test_Time) AND not exists(select Barcode from dbo.SYS_PointInSpection where Barcode=M.BARCODE) '+@SqlWhere+' group by M.MODEL_NO,M.STATION_CODE ) A left join ( --查出各站的FAIL次數，入維修的BarCode在之后站不再計算 select M.MODEL_NO,M.STATION_CODE,COUNT(distinct M.barcode) FailTotal from #Main_Table M where not exists (select BarCode from #firstTable where BARCODE=M.BarCode and Test_Time<M.Test_Time ) and not exists(select Barcode from dbo.SYS_PointInSpection where Barcode=M.BARCODE) AND M.TestResult=''Fail'' and Test_Time=(SELECT MIN(Test_Time) FROM #Main_Table D WHERE D.BARCODE=M.BARCODE AND M.STATION_CODE=D.STATION_CODE) '+@SqlWhere+' group by M.MODEL_NO,M.STATION_CODE ) B on A.MODEL_NO=B.MODEL_NO and A.STATION_CODE=B.STATION_CODE order by A.MODEL_NO ' set @SqlText = @SqlText+' --過濾重復種機 select A.Model_No,A.STATION_CODE, A.InputTotal,(A.InputTotal-FailTotal ) as OutputTotal,A.FailTotal,isnull(M.SEQ,100) PSeq ,CAST(CAST((A.InputTotal - ISNULL(A.FailTotal,0)) AS DECIMAL(18,4))/CAST(A.InputTotal AS DECIMAL(18,4)) AS DECIMAL(18,4)) RATE into #temp from ( select M.Model_No ,Station_Code,Sum(InputTotal) InputTotal,SUM(FailTotal) FailTotal from #tt M group by M.Model_No,Station_Code )A left join MES_Report_Process M on A.Model_No=M.Model_no and A.Station_Code=M.Process_no order by A.MODEL_NO , PSeq select IDEntity(int,1,1) AS RwNum, *,YieldRate=(select cast(exp(SUM(log(case when RATE=0 then 1 else RATE end))) as decimal(19,3)) from #temp) into #NewTable from #temp select M.Model_No,M.STATION_CODE,InputTotal,OutputTotal,FailTotal,RATE from #NewTable M left join MES_Report_Process MP on upper(M.MODEL_NO)=MP.Model_no and upper(M.STATION_CODE)=MP.Process_no where RwNum Between ' + CONVERT(varchar(10),(@pageIndexs-1)*@pageSizes + 1)+' AND '+ CONVERT(varchar(10),@pageIndexs*@pageSizes)+' order by MP.SEQ select sum(InputTotal) InputTotals,sum(FailTotal) FailTotals,Count(0) as TatalCount from #temp drop table #NewTable drop table #tableModel drop table #tableLine if(object_id(''tempdb..#Main_Table'') is not null) drop table #Main_Table if(object_id(''tempdb..#Repair'') is not null) drop table #Repair if(object_id(''tempdb..#firstTable'') is not null) drop table #firstTable if(object_id(''tempdb..#tt'') is not null) drop table #tt if(object_id(''tempdb..#temp'') is not null) drop table #temp' --print (@SqlText) exec (@SqlText) END