sql-server-2008 - 如何在点网核心中从前端处理长时间运行的程序（运行长达 2-4 小时）
问题描述
我写了一个程序，它花费了太多时间长达 4 小时。当我从 dot net core 的前端调用它时，它会显示超时或应用程序繁忙。所以我想知道有什么方法可以让我只启动程序而不等待响应和程序在后端连续执行。
以下是我的程序：-
表 MATCSECURITYRAWETL 包含 16 万条数据
CREATE Proc UpdateQuaterlyHistory As Begin Declare @PeerGroup Varchar(500),@RARTicker Numeric(18,15),@RARDeltaTicker Numeric(18,15),@RARBenchMark Numeric(18,15),@Result Varchar(10) ,@Qtr1Result Varchar (50),@Qtr2Result Varchar(50),@Qtr3Result VARchar(50),@UserName Varchar(100)='Admin'
Declare @QYear VArchar(10),@QMonth VArchar(10),@QuarterNO VArchar(5), @CUSIP VArchar(150), @Description VArchar(250),@Issuer_Display VArchar(75), @Issuer VArchar(25),
@Ticker VArchar(25), @Symbol VArchar(25), @AssetType VArchar(25), @Category_Display VArchar(100), @Category VArchar(25), @CategoryBoxDesc VArchar(25),
@MorningstarCategory VArchar(25), @StyleCode VArchar(25), @BroadAssetClass VArchar(25), @AssetClass VArchar(25), @ShareClass VArchar(25), @_12b1 VArchar(25),
@ExpenseRatio VArchar(25), @ExpenseRatioGross VArchar(25), @MiniQualPlnsInitPurchAmt VArchar(25), @Min401KAssets VArchar(25), @IsSoftClosed VArchar(25), @ClosedOn VArchar(25),
@Return03Year VArchar(25), @Return05Year VArchar(25), @StdDev03yr VArchar(25), @StdDev05yr VArchar(25), @Sharpe03yr VArchar(25), @Sharpe05yr VArchar(25),
@R203yr VArchar(25), @R205yr VArchar(25), @IsETF VArchar(10), @TradingNotes VArchar(2000), @MaxRedemptionFee VArchar(100),@Rank Numeric(18,0),@TickerInsertDate date
, @Bench_CUSIP VArchar(150), @Bench_Description VArchar(250),@Bench_Issuer_Display VArchar(75), @Bench_Issuer VArchar(25),
@Bench_Ticker VArchar(25), @Bench_Symbol VArchar(25), @Bench_AssetType VArchar(25), @Bench_Category_Display VArchar(25), @Bench_Category VArchar(25), @Bench_CategoryBoxDesc VArchar(25),
@Bench_MorningstarCategory VArchar(25), @Bench_StyleCode VArchar(25), @Bench_BroadAssetClass VArchar(25), @Bench_AssetClass VArchar(25), @Bench_ShareClass VArchar(25), @Bench__12b1 VArchar(25),
@Bench_ExpenseRatio VArchar(25), @Bench_ExpenseRatioGross VArchar(25), @Bench_MiniQualPlnsInitPurchAmt VArchar(25), @Bench_Min401KAssets VArchar(25)
,@Bench_IsSoftClosed VArchar(25), @Bench_ClosedOn VArchar(25),
@Bench_Return03Year VArchar(25), @Bench_Return05Year VArchar(25), @Bench_StdDev03yr VArchar(25), @Bench_StdDev05yr VArchar(25)
,@Bench_Sharpe03yr VArchar(25), @Bench_Sharpe05yr VArchar(25),
@Bench_R203yr VArchar(25), @Bench_R205yr VArchar(25), @Bench_IsETF VArchar(10), @Bench_TradingNotes VArchar(2000), @Bench_MaxRedemptionFee VArchar(100),@Bench_Rank Numeric(18,0)
,@Bench_TickerInsertDate date
Declare @HistoryTicker Varchar(50),@HistoryPeerGroup Varchar(200)
DECLARE TickerList CURSOR FOR
Select Ticker,Category_Display From MATCSECURITYRAWETL where ISNULL(Category_Display,'')<>''
and ISNULL(Ticker,'')<>''
Open TickerList
Fetch Next From TickerList
Into @HistoryTicker,@HistoryPeerGroup
While @@FETCH_STATUS=0
Begin
If Exists(Select ticker from V_MATCSECURITYRAWETL_Filtered where Category_Display=@HistoryPeerGroup)
begin
Select top 1 @PeerGroup=Category_Display From MATCSECURITYRAWETL Where Ticker=@HistoryTicker
Select * Into #LowExp From V_MATCSECURITYRAWETL_Filtered order by [Category_Display]
Select Top 1
ROW_NUMBER() over(partition by [Category_Display] order by cast([ExpenseRatio] as real) asc,cast([Sharpe05yr] as real)) as [CategoryExpenseRank]
,Insert_Date
,[Category_Display]
,[CUSIP]
,[Ticker]
,[Description]
,cast([ExpenseRatio] as varchar) [ExpenseRatio]
,cast([Return05Year] as varchar)[Return05Year]
,cast([StdDev05yr] as varchar)[StdDev05yr]
,cast([R205yr] as varchar) [R205yr]
,cast([Sharpe05yr] as varchar)[Sharpe05yr]
,cast([MiniQualPlnsInitPurchAmt] as varchar)[MiniQualPlnsInitPurchAmt]
,cast([Min401KAssets] as varchar)[Min401KAssets]
,TradingNotes,AssetClass As AssetClass,AssetType As AssetType,Category As Category
,Issuer_Display As Issuer_Display,Issuer As Issuer
,CategoryBoxDesc As CategoryBoxDesc,BroadAssetClass As BroadAssetClass,ShareClass As ShareClass,IsSoftClosed As IsSoftClosed
,ClosedOn As ClosedOn,IsETF As IsETF,MaxRedemptionFee As MaxRedemptionFee,StyleCode As StyleCode,
ExpenseRatioGross As ExpenseRatioGross,MorningstarCategory As MorningstarCategory
,StdDev03yr As StdDev03yr,Sharpe03yr As Sharpe03yr,R203yr As R203yr,'' As _12b1,Return03Year As Return03Year,Symbol As Symbol,'0' As Return01Year,'' As Sharpe01yr
,'' As R201yr,'' As StdDev01yr into #TickerData
from MATCSECURITYRAWETL Where Ticker=@HistoryTicker
Select top 1 * into #TickerData1 from #LowExp where Category_Display=@PeerGroup order by ExpenseRatio,[Category Expense Rank]
Select Top 1
@TickerInsertDate=Insert_Date
,@Rank= [CategoryExpenseRank]
,@Category_Display=[Category_Display]
,@CUSIP=Cast([CUSIP] AS Varchar)
,@Ticker=[Ticker]
,@Description=[Description]
,@ExpenseRatio=cast([ExpenseRatio] as varchar)
,@Return05Year=Cast(case when isnumeric([Return05Year])=1 then Return05Year else '0' end As varchar)
,@StdDev05yr=Cast(case when isnumeric([StdDev05yr])=1 then [StdDev05yr] else '0' end As varchar)
,@R205yr=Cast(case when isnumeric([R205yr])=1 then [R205yr] else '0' end As varchar)
,@Sharpe05yr=Cast(case when isnumeric([Sharpe05yr])=1 then [Sharpe05yr] else '0' end As varchar)
,@MiniQualPlnsInitPurchAmt=[MiniQualPlnsInitPurchAmt]
,@Min401KAssets=[Min401KAssets]
,@TradingNotes=TradingNotes
,@AssetClass=AssetClass
,@AssetType=AssetType
,@Category= Category
,@Issuer_Display=Issuer_Display
,@Issuer=Issuer
,@CategoryBoxDesc=CategoryBoxDesc,@BroadAssetClass=BroadAssetClass,@ShareClass=ShareClass,@IsSoftClosed=IsSoftClosed
,@ClosedOn=ClosedOn,@IsETF= IsETF,@MaxRedemptionFee= MaxRedemptionFee,@StyleCode=StyleCode,
@ExpenseRatioGross=ExpenseRatioGross,@MorningstarCategory= MorningstarCategory
,@StdDev03yr=Cast(case when isnumeric(StdDev03yr)=1 then StdDev03yr else '0' end As varchar)
,@Sharpe03yr=Cast(case when isnumeric(Sharpe03yr)=1 then Sharpe03yr else '0' end As varchar)
,@R203yr=Cast(case when isnumeric(R203yr)=1 then R203yr else '0' end As varchar)
,@Return03Year=Cast(case when isnumeric(Return03Year)=1 then Return03Year else '0' end As varchar)
,@Symbol= Symbol ,@_12b1=_12b1
from #TickerData
Select Top 1
@Bench_TickerInsertDate=Insert_Date
, @Bench_Rank= [Category Expense Rank]
,@Bench_Category_Display=[Category_Display]
,@Bench_CUSIP=[CUSIP]
,@Bench_Ticker=[Ticker]
,@Bench_Description=[Description]
,@Bench_ExpenseRatio=[ExpenseRatio]
,@Bench_Return05Year=case when isnumeric([Return05Year])=1 then [Return05Year] else '0' end
,@Bench_StdDev05yr=case when isnumeric([StdDev05yr])=1 then [StdDev05yr] else '0' end
,@Bench_R205yr=case when isnumeric([R205yr])=1 then [R205yr] else '0' end
,@Bench_Sharpe05yr=case when isnumeric([Sharpe05yr])=1 then [Sharpe05yr] else '0' end
,@Bench_MiniQualPlnsInitPurchAmt=[MiniQualPlnsInitPurchAmt]
,@Bench_Min401KAssets=[Min401KAssets]
,@Bench_TradingNotes=TradingNotes
,@Bench_AssetClass=AssetClass
,@Bench_AssetType=AssetType
,@Bench_Category= Category
,@Bench_Issuer_Display=Issuer_Display
,@Bench_Issuer=Issuer
,@Bench_CategoryBoxDesc=CategoryBoxDesc,@Bench_BroadAssetClass=BroadAssetClass,@Bench_ShareClass=ShareClass,@Bench_IsSoftClosed=IsSoftClosed
,@Bench_ClosedOn=ClosedOn,@Bench_IsETF= IsETF,@Bench_MaxRedemptionFee= MaxRedemptionFee,@Bench_StyleCode=StyleCode
,@Bench_ExpenseRatioGross=ExpenseRatioGross,@Bench_MorningstarCategory= MorningstarCategory
,@Bench_StdDev03yr=case when isnumeric(StdDev03yr)=1 then StdDev03yr else '0' end
,@Bench_Sharpe03yr=case when isnumeric(Sharpe03yr)=1 then Sharpe03yr else '0' end
,@Bench_R203yr= case when isnumeric(R203yr)=1 then R203yr else '0' end
,@Bench_Return03Year=case when isnumeric(Return03Year)=1 then Return03Year else '0' end
,@Bench_Symbol= Symbol ,@Bench__12b1=_12b1
from #TickerData1
set @RARTicker=dbo.CalculateRAR(@Return05Year,@Return03Year,0,@StdDev05yr,@StdDev03yr,0)
set @RARBenchMark=dbo.CalculateRAR(@Bench_Return05Year,@Bench_Return03Year,0,@Bench_StdDev05yr,@Bench_StdDev03yr,0)
set @RARDeltaTicker=dbo.CalculateRARDelta(@RARBenchMark,@Return05Year,@Return03Year,0,@StdDev05yr,@StdDev03yr,0)
set @QYear=YEAR(GETDATE())
set @QMonth=Month(GETDATE())
set @QuarterNO=dbo.GetQuarter(@QMonth)
if(Cast(@RARDeltaTicker AS Numeric(18,4))<=0)
begin
set @Result='PASS'
end
else
begin
set @Result='CAUTION'
end
if Exists(Select ID From QuarterlyUserTickerDataMaster_Clone Where QYear=@QYear and QuarterNO=@QuarterNO And Ticker=@Ticker)
begin
Declare @QID Numeric(18,0)
Select @QID=ID From QuarterlyUserTickerDataMaster_Clone Where QYear=@QYear and QuarterNO=@QuarterNO And Ticker=@Ticker
Update QuarterlyUserTickerDataMaster_Clone Set QYear=@QYear,QMonth=@QMonth,QuarterNO=@QuarterNO,CUSIP=@CUSIP,Description=@Description,Issuer_Display=@Issuer_Display,Issuer=@Issuer
,Ticker=@Ticker,Symbol=@Symbol,AssetType=@AssetType,Category_Display=@Category_Display,Category=@Category,CategoryBoxDesc=@CategoryBoxDesc,MorningstarCategory=@MorningstarCategory
,StyleCode=@StyleCode,BroadAssetClass=@BroadAssetClass,AssetClass=@AssetClass,ShareClass=@ShareClass,_12b1=@_12b1,ExpenseRatio=@ExpenseRatio,ExpenseRatioGross=@ExpenseRatioGross
,MiniQualPlnsInitPurchAmt=@MiniQualPlnsInitPurchAmt,Min401KAssets=@Min401KAssets,IsSoftClosed=@IsSoftClosed,ClosedOn=@ClosedOn,Return03Year=@Return03Year,Return05Year=@Return05Year
,StdDev03yr=@StdDev03yr,StdDev05yr=@StdDev05yr,Sharpe03yr=@Sharpe03yr,Sharpe05yr=@Sharpe05yr,R203yr=@R203yr,R205yr=@R205yr,IsETF=@IsETF,TradingNotes=@TradingNotes
,MaxRedemptionFee= @MaxRedemptionFee,UpdateBy=@UserName,UpdatedDate=GETDATE(),QtrResult=@Result,RAR=@RARTicker,RARDelta=@RARDeltaTicker,BenchTicker=@Bench_Ticker,BenchTickerRAR=@RARBenchMark
Where ID=@QID
end
else
begin
insert into QuarterlyUserTickerDataMaster_Clone(QYear,QMonth,QuarterNO,CUSIP,Description,Issuer_Display,Issuer,Ticker,Symbol,AssetType,Category_Display,Category
,CategoryBoxDesc,MorningstarCategory,StyleCode,BroadAssetClass,AssetClass,ShareClass,_12b1,ExpenseRatio,ExpenseRatioGross,MiniQualPlnsInitPurchAmt,Min401KAssets
,IsSoftClosed,ClosedOn,Return03Year,Return05Year,StdDev03yr,StdDev05yr,Sharpe03yr,Sharpe05yr,R203yr,R205yr,IsETF,TradingNotes,MaxRedemptionFee,CreateBy,CreatedDate,QtrResult
,BenchTicker,RARDelta,RAR,BenchTickerRAR)
values(@QYear,@QMonth,@QuarterNO,@CUSIP,@Description,@Issuer_Display,@Issuer,@Ticker,@Symbol,@AssetType,@Category_Display,@Category
,@CategoryBoxDesc,@MorningstarCategory,@StyleCode,@BroadAssetClass,@AssetClass,@ShareClass,@_12b1,@ExpenseRatio,@ExpenseRatioGross,@MiniQualPlnsInitPurchAmt,@Min401KAssets
,@IsSoftClosed,@ClosedOn,@Return03Year,@Return05Year,@StdDev03yr,@StdDev05yr,@Sharpe03yr,@Sharpe05yr,@R203yr,@R205yr,@IsETF,@TradingNotes,@MaxRedemptionFee,@UserName,GETDATE(),@Result
,@Bench_Ticker,@RARDeltaTicker,@RARTicker,@RARBenchMark)
end
Declare @QYearList Varchar(10),@QuarterNoList Varchar(10),@Res Varchar(10)='',@res1 VArchar(10)
,@QtrYear1 Varchar(20),@QtrYear2 Varchar(20),@QtrYear3 Varchar(20)
,@res2 VArchar(10),@res3 VArchar(10),@Count int=0
DECLARE QTRList CURSOR FOR
Select QYear,QNo from dbo.GetLastThreeQuarter(@QYear,@QuarterNO)
OPEN QTRList
FETCH NEXT FROM QTRList
INTO @QYearList,@QuarterNoList
WHILE @@FETCH_STATUS = 0
BEGIN
Set @Count=@Count+1
set @Res=null
Select @Res=isnull(QtrResult,'') From QuarterlyUserTickerDataMaster_Clone Where QYear=@QYearList And QuarterNO=@QuarterNoList And Ticker=@Ticker
if(@Count=1)
begin
Set @res1=ISNULL(@Res,'NA')
Set @QtrYear1=ISNULL(@QYearList,'NA')+' QTR- '+ISNULL(@QuarterNoList,'')
end
else if(@Count=2)
begin
Set @res2=ISNULL(@Res,'NA')
Set @QtrYear2=ISNULL(@QYearList,'NA')+' QTR- '+ISNULL(@QuarterNoList,'')
end
else if(@Count=3)
begin
Set @res3=ISNULL(@Res,'NA')
Set @QtrYear3=ISNULL(@QYearList,'NA')+' QTR- '+ISNULL(@QuarterNoList,'')
end
FETCH NEXT FROM QTRList
INTO @QYearList,@QuarterNoList
END
CLOSE QTRList;
DEALLOCATE QTRList;
Drop table #TickerData
Drop table #TickerData1
Drop table #LowExp
end
FETCH NEXT FROM TickerList
INTO @HistoryTicker ,@HistoryPeerGroup
END
CLOSE TickerList;
DEALLOCATE TickerList;
结尾
解决方案
推荐阅读
- php - 尝试在“mongodb”中使用 $or 运算符获取匹配值但未获取数据
- xaml - 除非我保存并重新加载，否则为什么我的图像直到我保存我的内容页面才绑定
- apache-spark-sql - 如何在 for 循环中使用 Spark 随后在数据集中添加列（其中 for 循环包含列名）
- mongodb - 在 Golang 中正确使用 Mongo
- c++ - 如何在 main 中定义一个常量并在其他头文件中使用它？
- file - 如何扫描 USB 连接中的文件？
- sql - 在 SQL 中查找特定值的具体数量
- java - 从引用路径 JAVA 的字符串中退出文本
- twitter - 有没有可能使用rest api在twitter中获取到给定日期的登录详细信息？
- python - Python打字，mypy根据类方法返回类型推断返回类型