ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Speed-up Script (https://www.excelbanter.com/excel-programming/345546-speed-up-script.html)

Butaambala

Speed-up Script
 
Hello, I have two tables, two user-defined functions, and one script.
all of these objects are detailed below, including CREATE TABLE
statements, with CREATE INDEX lines.

the sript is working properly, but going very slowly. I am hoping that
someone can help me to make it more efficient! Thank you.

SCRIPT:
DECLARE cc CURSOR
FOR SELECT dteDate
FROM tblEndDates
ORDER BY dteDate

DECLARE @now datetime
DECLARE @then datetime
DECLARE @months int
DECLARE @year int, @month int

OPEN cc
FETCH NEXT FROM cc into @now
WHILE @@FETCH_STATUS=0

BEGIN

SET @months = 10
WHILE(@months<32)

BEGIN

SET @year = year(@now)
SET @month = month(@now)
SET @then = CONVERT(smalldatetime, str(@month) + '/1/' + str(@year))

INSERT INTO tblIR_LB ( dteDate,
[sglSTDEV(upper)],
[sglSTDEV(lower)],
intMA,
dblPL,
dblSTDEV,
dblIR,
intTotalDays,
intExposure,
intLB )
SELECT TOP 1 @now,
T1.[sglSTDEV(upper)],
T1.[sglSTDEV(lower)],
T1.[intMA],
dbo.GetPL([PL], [TOTALDAYS]) AS PL_Result,
dbo.GetSTDEV(T1.[SUM], T1.[SUMSQ], T1.[TOTALDAYS]) AS [STDEV],
dbo.GetPL([PL], [TOTALDAYS])/dbo.GetSTDEV(T1.[SUM], T1.[SUMSQ],
T1.[TOTALDAYS]) AS IR,
T1.TOTALDAYS,
T1.EXPOSURE,
@months+1
FROM (SELECT SUM([dblPL]*[intTotalDays]) AS PL,
SUM([dblSUMSQ]) AS SUMSQ,
SUM([dblSUM]) AS [SUM],
SUM([intExposure]) AS EXPOSURE,
SUM([intTotalDays]) AS TOTALDAYS,
[sglSTDEV(upper)],
[sglSTDEV(lower)],
[intMA]
FROM data
WHERE dteDate BETWEEN DATEADD(m, -@months, @then) AND @now
GROUP BY [sglSTDEV(upper)], [sglSTDEV(lower)],[intMA]) AS T1
ORDER BY IR DESC, [sglSTDEV(upper)] DESC, [sglSTDEV(lower)]
DESC,[intMA] DESC

SET @months = @months + 1

END

FETCH NEXT FROM cc into @now

END

CLOSE cc

DEALLOCATE cc



FUNCTIONS:

CREATE FUNCTION GetSTDEV(@dblSUM float, @dblSUMSQ float, @days int)
RETURNS float
AS
BEGIN
DECLARE @result float
SET @result = SQRT(((@days * @dblSUMSQ) - POWER(@dblSUM, 2)) /
(@days * (@days - 1))) * SQRT(252)
IF @result = 0
BEGIN
RETURN 1
END

RETURN @result
END



CREATE FUNCTION GetPL(@dblPL float, @days int)
RETURNS float
AS
BEGIN
IF @days < 0
BEGIN
RETURN (@dblPL / @days) * 252
END
RETURN 0
END




TABLES:

CREATE TABLE [dbo].[data] (
[dteDate] [smalldatetime] NULL ,
[dblPL] [float] NULL ,
[dblSUMSQ] [float] NULL ,
[dblSUM] [float] NULL ,
[intExposure] [float] NULL ,
[dblPL(mean)] [float] NULL ,
[dblSUMSQ(mean)] [float] NULL ,
[dblSUM(mean)] [float] NULL ,
[intExposure(mean)] [float] NULL ,
[intTotalDays] [int] NULL ,
[sglSTDEV(upper)] [float] NULL ,
[sglSTDEV(lower)] [float] NULL ,
[intMA] [smallint] NULL
) ON [PRIMARY]
GO

CREATE CLUSTERED INDEX [IDX_dteDate] ON [dbo].[data]([dteDate]) ON
[PRIMARY]
GO



CREATE TABLE [dbo].[tblEndDates] (
[dteDate] [smalldatetime] NULL
) ON [PRIMARY]
GO

CREATE CLUSTERED INDEX [IDX_dteDate] ON
[dbo].[tblEndDates]([dteDate]) ON [PRIMARY]
GO


Butaambala

Speed-up Script
 
APOLOGIES! I intended to post this in sqlserver.programming NOT Excel.
Please disregard - I will post in proper place.



All times are GMT +1. The time now is 05:40 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com