August 5, 2015 at 10:48 am
Hello,
I have a procedure that was taking around 11-12 seconds to run on SQL Server 2012 Enterprise edition with Always ON configured..
There is table named fs.performance that has 21995968 records and it is referenced a couple of times in the procedure.
I analyzed the procedure in DETA and it suggested a couple of indexes on table fs.performance. I created those indexes and the longest this procedure takes to run (tested with different parameters) is around 5 seconds now.
This procedure gets called from application using different parameters. It runs fast for some parameters and slow for some other. Please suggest some ways to tune this procedure so that I get the results within under a second
Attached are the details.
Thanks in advance.
August 5, 2015 at 11:12 am
I took a quick review of the sproc. I lost count of the things that are suboptimal in it, and that doesn't take into account anything about the data structures themselves.
The complexity of this goes WAY beyond free help on a forum, at least for me. Fully optimizing this entire sproc and it's data structures could be 10+ hours of time, and I don't do that for free. 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 5, 2015 at 11:38 am
I'll support what Kevin mentioned.
There's a great room for improvement and I'm sure it wouldn't stop with this procedure as the whole database will have similar problems.
I'd suggest that you hire some expert that won't only help you to improve performance, but will also teach you how to prevent future performance problems.
August 5, 2015 at 2:07 pm
Further on Kevin's and Luis's answers, optimizing this code to half the execution time should not be too hare, neither getting under one second but this takes some time, do you think is worth the effort? How many times a day is this code executed?
😎
August 5, 2015 at 3:26 pm
Yogeshwar Phull (8/5/2015)
Hello,I have a procedure that was taking around 11-12 seconds to run on SQL Server 2012 Enterprise edition with Always ON configured..
There is table named fs.performance that has 21995968 records and it is referenced a couple of times in the procedure.
I analyzed the procedure in DETA and it suggested a couple of indexes on table fs.performance. I created those indexes and the longest this procedure takes to run (tested with different parameters) is around 5 seconds now.
This procedure gets called from application using different parameters. It runs fast for some parameters and slow for some other. Please suggest some ways to tune this procedure so that I get the results within under a second
Attached are the details.
Thanks in advance.
To perhaps assist others in helping you, I've at least "prettified" the code to make it more readable, and eliminated the cursor, as it doesn't appear to be the least bit necessary. Here's the updated code, with the procedure part commented out (so that some of SSMS's ability to syntax check can function better while I was prettifying). and also the cursor commented out and it's replacement immediately following:
--CREATE procedure [rpi].[udf_GetPerformance_FS] (
DECLARE @LipperIdList varchar(MAX) = ''
, @FundsOnly char(1) = '0'-- really a bit but FundNets can't do that. Default 0 in CASE not passed
, @LanguageId varchar(10) = ''
, @WebsiteId varchar(10) = ''
, @RangeId varchar(10) = '';
--)
--AS
--BEGIN
--FundGroupId will be NULL IF providing a LipperId list, only populated IF using triplet
DECLARE @LipperId AS TABLE (
LipperId int,
Idx INT IDENTITY(1,1)
);
DECLARE @BaseCurrency char(3), @BaseCurrencyName varchar(100);
DECLARE @latestrptPerfReportDate date;-- we will always return the latest data, but the FS tables will accumulate multiple ReportDates
SELECT @latestrptPerfReportDate = MAX(reportdate)
FROM fs.performance
WHERE ReportDate < DATEADD(day, -14, GETDATE())
SELECT @languageid = CASE WHEN ISNUMERIC(@languageid) = 1 THEN @languageid ELSE NULL END
SELECT @websiteid = CASE WHEN ISNUMERIC(@websiteid) = 1 THEN @websiteid ELSE NULL END
SELECT @rangeid = CASE WHEN ISNUMERIC(@rangeid) = 1 THEN @rangeid ELSE NULL END
/*
DECLARE @RetailWebsite int
SELECT @RetailWebsite = id FROM rpt.website WHERE name = 'Retail'
*/
-- start BY inserting based ON @LipperIdList
INSERT INTO @LipperId (LipperId)
SELECT String
FROM app.udf_SplitString(@LipperIdList, ',')
ORDER BY idx;-- we will respect the ORDER they were sent in
IF @WebsiteId IS NOT NULL -- sufficient to test just one of the three parameters to see IF we are using this approach
BEGIN
INSERT INTO @LipperId (LipperId)
SELECT fl.LipperId
FROM [rpi].[udf_FC_GetFundList] (@LanguageId, @WebsiteId, @RangeId, NULL /* spotlight */, NULL /* sandprating */) AS fl
ORDER BY RangeName, fundname, shareclass;
/*
FROM [rpi].[udf_GetFundList_Brief] (@LanguageId, @WebsiteId, @RangeId) AS fl
INNER JOIN rpt.portfolioattributes AS pa
ON fl.fundid = pa.fundid
*/
END
--/* Not everyone in our list may have the same date values available, so we take the worst, to ensure all funds have something to display
--*/
IF @FundsOnly = 1
-- we throw out anything that isn't a fund-type lipperid. We do this indirectly AS we don't have a fundtype/securitytype to rely upON AND these are also in app.fund
BEGIN
DELETE
FROM @LipperId
WHERE Lipperid IN ( -- automatically DISTINCT
SELECT LipperBenchmarkId
FROM rpt.portfolioattributes
UNION
SELECT lippersectorid
FROM rpt.portfolioattributes
);
END
CREATE TABLE #results(
idx int,
RowType varchar(10),-- 'Fund' / 'BM', 'S'
FundGroupId int,-- will be SET with an UPDATE statement later
FundId int,-- fundId (FT's app.Fund.Id )
fsFundId int,
Primary_Share_Class varchar(3),
LipperId int,
CurrencyCode char(3),
InceptionDate date,
ISIN varchar(12),
FundName varchar(100),
FundShortName varchar(100),
ShareClass varchar(100),
Returns_1mth decimal(20,10),
Returns_3mth decimal(20,10),
Returns_6mth decimal(20,10),
Returns_1Yr decimal(20,10),
Returns_3Yr decimal(20,10),
Returns_5Yr decimal(20,10),
Minus1 decimal(20,10),
Minus2 decimal(20,10),
Minus3 decimal(20,10),
Minus4 decimal(20,10),
YTD decimal(20,10),
PCT1Y0_MTH decimal(20,10),
PCT1Y1_MTH decimal(20,10),
PCT1Y2_MTH decimal(20,10),
PCT1Y3_MTH decimal(20,10),
PCT1Y4_MTH decimal(20,10)
);
-- main select
INSERT INTO #results (
RowType ,
FundGroupId ,
FundId,
Primary_Share_Class,
LipperId ,
CurrencyCode ,
InceptionDate ,
ISIN,
ShareClass,
Returns_1mth ,
Returns_3mth ,
Returns_6mth ,
Returns_1Yr ,
Returns_3Yr,
Returns_5Yr ,
Minus1 ,
Minus2 ,
Minus3 ,
Minus4 ,
YTD ,
PCT1Y0_MTH ,
PCT1Y1_MTH ,
PCT1Y2_MTH,
PCT1Y3_MTH ,
PCT1Y4_MTH )
SELECT
'Fund' AS RowType,-- 'Fund' in the CASE of these records.
NULL AS FundGroupId,-- will be SET with an UPDATE statement later, needed here AS SELECT into
f.Id AS FundId,-- fundId (FT's app.Fund.Id )
pa.Primary_Share_Class,
l.LipperId,
f.CurrencyId AS CurrencyCode,
pa.InceptionDate,
f.AccountGr AS ISIN,
pa.ShareClass,
Returns_1mth,
Returns_3mth,
Returns_6mth,
Returns_1Yr,
Returns_3Yr,
Returns_5Yr,
Minus1,
Minus2,
Minus3,
Minus4,
YTD,
PCT1Y0_MTH,
PCT1Y1_MTH,
PCT1Y2_MTH,
PCT1Y3_MTH,
PCT1Y4_MTH
FROM @lipperid AS l
INNER JOIN rpt.portfolioattributes AS pa
ON pa.lipperid = l.lipperid
INNER JOIN app.fund AS F
ON f.id = pa.fundid
INNER JOIN fs.FundIdentifier AS fi
ON fi.LipperFundId= l.lipperid-- FS is varchar but implicit casting
INNER JOIN lu.currency AS c
ON c.id = F.CurrencyId-- force results only in base currency of fund. However there calENDar year records available in some other currencies
LEFT JOIN (
SELECT FundId,
Currency, -- descriptive name
-- start with cumuative performance measures
SUM(CASE WHEN ProductId = 1 AND BasisId = 1 AND PeriodValue = 1 AND PeriodUnit = 'M' THEN PerformanceValue1 ELSE NULL END) AS Returns_1mth,
SUM(CASE WHEN ProductId = 1 AND BasisId = 1 AND PeriodValue = 3 AND PeriodUnit = 'M' THEN PerformanceValue1 ELSE NULL END) AS Returns_3mth,
SUM(CASE WHEN ProductId = 1 AND BasisId = 1 AND PeriodValue = 6 AND PeriodUnit = 'M' THEN PerformanceValue1 ELSE NULL END) AS Returns_6mth,
SUM(CASE WHEN ProductId = 1 AND BasisId = 1 AND PeriodValue = 1 AND PeriodUnit = 'Y' THEN PerformanceValue1 ELSE NULL END) AS Returns_1Yr,
SUM(CASE WHEN ProductId = 1 AND BasisId = 1 AND PeriodValue = 3 AND PeriodUnit = 'Y' THEN PerformanceValue1 ELSE NULL END) AS Returns_3Yr,
SUM(CASE WHEN ProductId = 1 AND BasisId = 1 AND PeriodValue = 5 AND PeriodUnit = 'Y' THEN PerformanceValue1 ELSE NULL END) AS Returns_5Yr,
-- now calENDar year
SUM(CASE WHEN ProductId = 3 AND BasisId = 1 AND PeriodUnit = CAST(DATEPART(yy, @latestRptPerfReportDate)-1 AS varchar(4)) THEN PerformanceValue1 ELSE NULL END) AS Minus1,
SUM(CASE WHEN ProductId = 3 AND BasisId = 1 AND PeriodUnit = CAST(DATEPART(yy, @latestRptPerfReportDate)-2 AS varchar(4)) THEN PerformanceValue1 ELSE NULL END) AS Minus2,
SUM(CASE WHEN ProductId = 3 AND BasisId = 1 AND PeriodUnit = CAST(DATEPART(yy, @latestRptPerfReportDate)-3 AS varchar(4)) THEN PerformanceValue1 ELSE NULL END) AS Minus3,
SUM(CASE WHEN ProductId = 3 AND BasisId = 1 AND PeriodUnit = CAST(DATEPART(yy, @latestRptPerfReportDate)-4 AS varchar(4)) THEN PerformanceValue1 ELSE NULL END) AS Minus4,
-- Discrete Annual Returns
SUM(CASE WHEN ProductId = 2 AND BasisId = 5 THEN PerformanceValue1 ELSE NULL END ) AS PCT1Y0_MTH,
SUM(CASE WHEN ProductId = 2 AND BasisId = 6 THEN PerformanceValue1 ELSE NULL END ) AS PCT1Y1_MTH,
SUM(CASE WHEN ProductId = 2 AND BasisId = 7 THEN PerformanceValue1 ELSE NULL END ) AS PCT1Y2_MTH,
SUM(CASE WHEN ProductId = 2 AND BasisId = 8 THEN PerformanceValue1 ELSE NULL END ) AS PCT1Y3_MTH,
SUM(CASE WHEN ProductId = 2 AND BasisId = 9 THEN PerformanceValue1 ELSE NULL END ) AS PCT1Y4_MTH,
SUM(CASE WHEN ProductId = 3 AND BasisId = 1 AND PeriodValue = 1 THEN PerformanceValue1 ELSE NULL END) AS YTD -- wAS 4
FROM fs.Performance AS p
WHERE ReportDate = @latestrptPerfReportDate
AND Note = 'Fund'
GROUP BY FundId, Currency
) AS pf
ON pf.fundid = fi.FundId
AND pf.Currency = c.Name;
SELECT TOP 1 @BaseCurrency = r.CurrencyCode, @BaseCurrencyName = c.Name
FROM #results AS r
INNER JOIN lu.Currency AS c
ON r.CurrencyCode = c.Id
-- IF there were no records found for funds THEN the BM AND Sector will also return no rows - BY design
-- testing, show interim results
--SELECT * FROM #results
-- Add benchmarks, limited to the assumed commonly used CurrencyCode FROM funds
INSERT INTO #results (
RowType ,
FundGroupId,
FundId,
fsFundId,
Primary_Share_Class,
LipperId,
CurrencyCode,
InceptionDate,
ISIN,
ShareClass,
Returns_1mth,
Returns_3mth,
Returns_6mth,
Returns_1Yr,
Returns_3Yr,
Returns_5Yr,
Minus1,
Minus2,
Minus3,
Minus4,
YTD,
PCT1Y0_MTH,
PCT1Y1_MTH,
PCT1Y2_MTH,
PCT1Y3_MTH,
PCT1Y4_MTH )
SELECT DISTINCT
'BM' AS RowType,
NULL AS FundGroupId, -- will be SET with an UPDATE statement later, needed here AS SELECT into
f.Id AS FundId, -- fundId (FT's app.Fund.Id )
NULL ,
pa.Primary_Share_Class,
l.LipperId,
c.Id AS CurrencyCode, -- different FROM Fund
NULL, -- Benchmark's done have pa.InceptionDate
f.AccountGr AS ISIN,
NULL, -- Benchmarks don't have pa.ShareClass,
Returns_1mth,
Returns_3mth,
Returns_6mth,
Returns_1Yr,
Returns_3Yr,
Returns_5Yr,
Minus1,
Minus2,
Minus3,
Minus4,
YTD,
PCT1Y0_MTH,
PCT1Y1_MTH,
PCT1Y2_MTH,
PCT1Y3_MTH,
PCT1Y4_MTH
FROM @lipperid AS l
INNER JOIN app.fund F
ON CAST(l.lipperid AS varchar(50))= CAST(f.AccountGr AS varchar(50))
INNER JOIN rpt.portfolioattributes AS pa
ON f.Id = pa.fundid
INNER JOIN fs.FundIdentifier AS fi
ON fi.LipperBenchmarkId= l.lipperid-- FS is varchar but implicit casting
INNER JOIN @lipperid AS l_fund_level
ON l_fund_level.LipperId = fi.LipperFundId /* Join back to @lipperId, this time again the fund, to restrict the SET of funds that contribute
to those that will be shown in the tabular output, i.e. restrict distinc t versions available */
INNER JOIN (
SELECT FundId,
Currency, -- descriptive name. BENCHMARKS SECTION
-- start with cumuative performance measures
SUM(CASE WHEN ProductId = 1 AND BasisId = 1 AND PeriodValue = 1 AND PeriodUnit = 'M' THEN PerformanceValue1 ELSE NULL END) AS Returns_1mth,
SUM(CASE WHEN ProductId = 1 AND BasisId = 1 AND PeriodValue = 3 AND PeriodUnit = 'M' THEN PerformanceValue1 ELSE NULL END) AS Returns_3mth,
SUM(CASE WHEN ProductId = 1 AND BasisId = 1 AND PeriodValue = 6 AND PeriodUnit = 'M' THEN PerformanceValue1 ELSE NULL END) AS Returns_6mth,
SUM(CASE WHEN ProductId = 1 AND BasisId = 1 AND PeriodValue = 1 AND PeriodUnit = 'Y' THEN PerformanceValue1 ELSE NULL END) AS Returns_1Yr,
SUM(CASE WHEN ProductId = 1 AND BasisId = 1 AND PeriodValue = 3 AND PeriodUnit = 'Y' THEN PerformanceValue1 ELSE NULL END) AS Returns_3Yr,
SUM(CASE WHEN ProductId = 1 AND BasisId = 1 AND PeriodValue = 5 AND PeriodUnit = 'Y' THEN PerformanceValue1 ELSE NULL END) AS Returns_5Yr,
-- now calENDar year
SUM(CASE WHEN ProductId = 3 AND BasisId = 1 AND PeriodUnit = CAST(DATEPART(yy, @latestRptPerfReportDate)-1 AS varchar(4)) THEN PerformanceValue1 ELSE NULL END) AS Minus1,
SUM(CASE WHEN ProductId = 3 AND BasisId = 1 AND PeriodUnit = CAST(DATEPART(yy, @latestRptPerfReportDate)-2 AS varchar(4)) THEN PerformanceValue1 ELSE NULL END) AS Minus2,
SUM(CASE WHEN ProductId = 3 AND BasisId = 1 AND PeriodUnit = CAST(DATEPART(yy, @latestRptPerfReportDate)-3 AS varchar(4)) THEN PerformanceValue1 ELSE NULL END) AS Minus3,
SUM(CASE WHEN ProductId = 3 AND BasisId = 1 AND PeriodUnit = CAST(DATEPART(yy, @latestRptPerfReportDate)-4 AS varchar(4)) THEN PerformanceValue1 ELSE NULL END) AS Minus4,
-- Discrete Annual Returns
SUM(CASE WHEN ProductId = 2 AND BasisId = 5 THEN PerformanceValue1 ELSE NULL END) AS PCT1Y0_MTH,
SUM(CASE WHEN ProductId = 2 AND BasisId = 6 THEN PerformanceValue1 ELSE NULL END) AS PCT1Y1_MTH,
SUM(CASE WHEN ProductId = 2 AND BasisId = 7 THEN PerformanceValue1 ELSE NULL END) AS PCT1Y2_MTH,
SUM(CASE WHEN ProductId = 2 AND BasisId = 8 THEN PerformanceValue1 ELSE NULL END) AS PCT1Y3_MTH,
SUM(CASE WHEN ProductId = 2 AND BasisId = 9 THEN PerformanceValue1 ELSE NULL END) AS PCT1Y4_MTH,
SUM(CASE WHEN ProductId = 3 AND BasisId = 1 AND PeriodValue = 1 THEN PerformanceValue1 ELSE NULL END) AS YTD
FROM fs.Performance AS p
WHERE ReportDate = @latestrptPerfReportDate
AND Note = 'Benchmark'
AND Currency = @BaseCurrencyName
GROUP BY FundId, Currency
) AS pf
ON pf.fundid = fi.FundId
INNER JOIN lu.currency AS c
ON c.Name = pF.Currency
-- AND pf.Currency = c.Name
/* actually slows overall time
CREATE index ix_results_fundgroupid ON #results(FundGroupId)
*/
-- Add SECTORS
INSERT INTO #results (
RowType ,
FundGroupId, -- will be SET with an UPDATE statement later, needed here AS SELECT into
FundId, -- fundId (FT's app.Fund.Id )
fsFundId,
Primary_Share_Class,
LipperId,
CurrencyCode,
InceptionDate,
ISIN,
ShareClass,
Returns_1mth,
Returns_3mth,
Returns_6mth,
Returns_1Yr,
Returns_3Yr,
Returns_5Yr,
Minus1,
Minus2,
Minus3,
Minus4,
YTD,
PCT1Y0_MTH,
PCT1Y1_MTH,
PCT1Y2_MTH,
PCT1Y3_MTH,
PCT1Y4_MTH )
SELECT DISTINCT -- DISTINCT AS many of the FS funds will share the sector AND only one is needeed. They are not necessarily consistent (thank you Aberdeen for multiple differentcopies of what should be a single copy of this data)
'S' AS RowType,
NULL AS FundGroupId, -- will be SET with an UPDATE statement later, needed here AS SELECT into
f.Id AS FundId, -- fundId (FT's app.Fund.Id )
pf.FundId AS fsFundId,
pa.Primary_Share_Class,
l.LipperId,
c.Id AS CurrencyCode, -- different FROM Fund
NULL, -- Sectors done have pa.InceptionDate
f.AccountGr AS ISIN,
NULL, -- Sectors don't have pa.ShareClass,
Returns_1mth,
Returns_3mth,
Returns_6mth,
Returns_1Yr,
Returns_3Yr,
Returns_5Yr,
Minus1,
Minus2,
Minus3,
Minus4,
YTD,
PCT1Y0_MTH,
PCT1Y1_MTH,
PCT1Y2_MTH,
PCT1Y3_MTH,
PCT1Y4_MTH
FROM @lipperid AS l
INNER JOIN app.fund AS F
ON CAST(l.lipperid AS varchar(50)) = CAST(f.AccountGr AS varchar(50))
LEFT JOIN rpt.portfolioattributes AS pa -- changed 04 March 2013
ON f.Id = pa.fundid
LEFT JOIN fs.FundIdentifier AS fi
ON fi.LipperSectorId = l.lipperid -- FS is varchar but implicit casting
LEFT JOIN @lipperid AS l_fund_level
ON l_fund_level.LipperId = fi.LipperFundId /* Join back to @lipperId, this time again the fund, to restrict the SET of funds that contribute
to those that will be shown in the tabular output, i.e. restrict distinc t versions available */
INNER JOIN (
SELECT FundId,
Currency, -- descriptive name. BENCHMARKS SECTION
-- start with cumuative performance measures
SUM(CASE WHEN ProductId = 1 AND BasisId = 1 AND PeriodValue = 1 AND PeriodUnit = 'M' THEN PerformanceValue1 ELSE NULL END ) AS Returns_1mth,
SUM(CASE WHEN ProductId = 1 AND BasisId = 1 AND PeriodValue = 3 AND PeriodUnit = 'M' THEN PerformanceValue1 ELSE NULL END ) AS Returns_3mth,
SUM(CASE WHEN ProductId = 1 AND BasisId = 1 AND PeriodValue = 6 AND PeriodUnit = 'M' THEN PerformanceValue1 ELSE NULL END ) AS Returns_6mth,
SUM(CASE WHEN ProductId = 1 AND BasisId = 1 AND PeriodValue = 1 AND PeriodUnit = 'Y' THEN PerformanceValue1 ELSE NULL END ) AS Returns_1Yr,
SUM(CASE WHEN ProductId = 1 AND BasisId = 1 AND PeriodValue = 3 AND PeriodUnit = 'Y' THEN PerformanceValue1 ELSE NULL END ) AS Returns_3Yr,
SUM(CASE WHEN ProductId = 1 AND BasisId = 1 AND PeriodValue = 5 AND PeriodUnit = 'Y' THEN PerformanceValue1 ELSE NULL END ) AS Returns_5Yr,
-- now calENDar year
SUM(CASE WHEN ProductId = 3 AND BasisId = 1 AND PeriodUnit = CAST(DATEPART(yy, @latestRptPerfReportDate)-1 AS varchar(4)) THEN PerformanceValue1 ELSE NULL END ) AS Minus1,
SUM(CASE WHEN ProductId = 3 AND BasisId = 1 AND PeriodUnit = CAST(DATEPART(yy, @latestRptPerfReportDate)-2 AS varchar(4)) THEN PerformanceValue1 ELSE NULL END ) AS Minus2,
SUM(CASE WHEN ProductId = 3 AND BasisId = 1 AND PeriodUnit = CAST(DATEPART(yy, @latestRptPerfReportDate)-3 AS varchar(4)) THEN PerformanceValue1 ELSE NULL END ) AS Minus3,
SUM(CASE WHEN ProductId = 3 AND BasisId = 1 AND PeriodUnit = CAST(DATEPART(yy, @latestRptPerfReportDate)-4 AS varchar(4)) THEN PerformanceValue1 ELSE NULL END ) AS Minus4,
-- Discrete Annual Returns. Not expected to exist
SUM(CASE WHEN ProductId = 2 AND BasisId = 5 THEN PerformanceValue1 ELSE NULL END ) AS PCT1Y0_MTH,
SUM(CASE WHEN ProductId = 2 AND BasisId = 6 THEN PerformanceValue1 ELSE NULL END ) AS PCT1Y1_MTH,
SUM(CASE WHEN ProductId = 2 AND BasisId = 7 THEN PerformanceValue1 ELSE NULL END ) AS PCT1Y2_MTH,
SUM(CASE WHEN ProductId = 2 AND BasisId = 8 THEN PerformanceValue1 ELSE NULL END ) AS PCT1Y3_MTH,
SUM(CASE WHEN ProductId = 2 AND BasisId = 9 THEN PerformanceValue1 ELSE NULL END ) AS PCT1Y4_MTH,
SUM(CASE WHEN ProductId = 3 AND BasisId = 1 AND PeriodValue = 1 THEN PerformanceValue1 ELSE NULL END ) AS YTD
FROM fs.Performance AS p
WHERE ReportDate = @latestrptPerfReportDate
AND Note = 'Sector'
AND Currency = @BaseCurrencyName
/* force this fund to be in the list of LipperIds of interest so we don't get all possible variations of this sectors,
but onoly variations WHERE we are going to report data ON the fund level AS well. This will strip out needless variation
*/
GROUP BY FundId, Currency-- this logic is a little overblown now, AS we are using a single currency, so could be simplified
) AS pf
ON pf.fundid = fi.FundId
INNER JOIN lu.currency AS c
ON c.name = pF.Currency
-- Hide values IF incepted <1 yr ago. If no inceptiON date assume is old.
-- If website parameter is NULL this will not work, but that parameter will always be SET in normal operatiON of website.
UPDATE r
SET returns_1mth = NULL,
returns_3mth = NULL,
returns_6mth = NULL,
Returns_1Yr = NULL,
Returns_3yr = NULL,
Returns_5yr = NULL,
YTD = NULL,
Minus1=NULL,
Minus2 = NULL,
Minus3 = NULL,
Minus4 = NULL,
PCT1Y0_MTH = NULL,
PCT1Y1_MTH = NULL,
PCT1Y2_MTH = NULL,
PCT1Y3_MTH = NULL,
PCT1Y4_MTH = NULL
FROM #results AS r
WHERE InceptionDate > DATEADD(mm, -13, GETDATE())
AND RowType = 'Fund'
/* AND @WebsiteId = @RetailWebsite */
-- Insert any that are missing - need to do here rather than LEFT JOIN later AS setting names is messy
/* INSERT INTO #Results(LipperId)
SELECT LipperId FROM @LipperId l
WHERE not EXISTS ( SELECT * FROM #results r WHERE r.LipperId = l.LipperId)
*/
-- Set the FundName AND FundShortName.
-- for 'funds'
UPDATE r
SET FundId = f.id, FundName = f.fullname , FundShortName = f.ShortName
FROM #results AS r
INNER JOIN rpt.PortfolioAttributes AS pa
ON pa.LipperId = r.LipperId
INNER JOIN app.Fund AS f
ON pa.Fundid = f.Id;
-- for 'benchmarks' AND 'sectors' (represented differently)
UPDATE r
SET FundId = f.id, FundName = f.fullname , FundShortName = f.ShortName
FROM #results AS r
INNER JOIN app.Fund AS f
ON CAST(r.LipperId AS varchar(100)) = AccountGr;
-- SET the FundGroupId (take FROM any range even IF in multiple, so a random matching row will do)
UPDATE r
SET FundGroupId = fd.FundGroupId
FROM #results r
INNER JOIN rpt.funddisplay AS fd
ON r.FundId = fd.FundId;
-- Fund renaming (IF changing find other copies of this code */
DECLARE @FundGroupId int, @FromText varchar(255), @ToText varchar(255), @fetch_status1 int;
-- we don't have a simple parameter to work with so we have to JOINn FROM #results to find range (or possibly multiple ranges).
/*
DECLARE Replacements AS CURSOR
FOR SELECT DISTINCT fr.FundGroupId, FromText, ToText
FROM rpt.FundRename AS fr
INNER JOIN #results AS r
ON fr.fundgroupid = r.fundgroupid
OPEN Replacements
FETCH NEXT FROM Replacements INTO @FundGroupId, @FromText, @ToText
SET @fetch_status1 = @@fetch_status
WHILE @fetch_status1 <> -1 -- in CASE we need the @@fetch_status later AND it is confused BY the inner cursor
BEGIN
UPDATE #Results
SET FundName = REPLACE(fundname, @fromtext, COALESCE(@totext,''))
WHERE FundGroupId = @fundGroupId -- in CASE we have a mix of fundgroups
FETCH NEXT FROM Replacements INTO @FundGroupId, @FromText, @ToText
SET @fetch_status1 = @@fetch_status
END
*/
-- THIS IS THE CURSOR REPLACEMENT
WITH DISTINCT_FUND_RENAMES AS (
SELECT DISTINCT fr.FundGroupId, FromText, ToText
FROM rpt.FundRename AS fr
INNER JOIN #results AS r
ON fr.fundgroupid = r.fundgroupid
)
UPDATE R
SET R.FundName = REPLACE(R.FundName, DFR.FromText, COALESCE(DFR.ToText,''))
FROM #results AS R
INNER JOIN DISTINCT_FUND_RENAMES AS DFR
ON R.FundGroupId = DFR.FundGroupId
-- remove any secondary sectors WHERE a primary exists
DELETE r
FROM #results AS r
INNER JOIN fs.fundidentifier AS fi
ON fi.fundid = r.fsfundid
INNER JOIN rpt.vwFund AS f
ON f.AccountGr = fi.ISIN
INNER JOIN rpt.PortfolioAttributes AS pa
ON pa.Fundid = f.Id
WHERE pa.Primary_Share_Class = 'No'
AND EXISTS (
SELECT *
FROM #results AS r1
INNER JOIN fs.fundidentifier AS fi1
ON fi1.fundid = r1.fsfundid
INNER JOIN rpt.vwFund AS f1
ON f1.AccountGr = fi1.ISIN
INNER JOIN rpt.portfolioattributes AS pa1
ON pa1.fundid = f1.id
WHERE pa1.Primary_Share_Class = 'Yes'
AND r1.lipperid = r.lipperid)
DELETE FROM r2
FROM #results AS r1
INNER JOIN #results AS r2
ON r2.FundId = r1.FundId
WHERE r2.fsFundId > r1.fsFundId
-- return results. Have to substitute NULL s for the calENDar year ones AS not done ON websie
SELECT DISTINCT
l.idx AS RowNumber,
r.RowType,
r.Primary_Share_Class,
@latestrptPerfReportDate AS ReportDate,
r.FundGroupId,
fg.Name,
r.FundId,
-- r.fsFundId,
l.LipperId,
r.CurrencyCode,
r.InceptionDate,
r.ISIN,
r.FundName,
r.FundShortName,
r.ShareClass,
COALESCE(CAST(CAST(r.Returns_1mth AS decimal(10,2)) AS varchar(20)),'-') AS Returns_1mth,
COALESCE(CAST(CAST(r.Returns_3mth AS decimal(10,2)) AS varchar(20)),'-') AS Returns_3mth,
COALESCE(CAST(CAST(r.Returns_6mth AS decimal(10,2)) AS varchar(20)),'-') AS Returns_6mth,
COALESCE(CAST(CAST(r.Returns_1Yr AS decimal(10,2)) AS varchar(20)),'-') AS Returns_1Yr,
COALESCE(CAST(CAST(r.Returns_3Yr AS decimal(10,2)) AS varchar(20)),'-') AS Returns_3Yr,
COALESCE(CAST(CAST(r.Returns_5Yr AS decimal(10,2)) AS varchar(20)),'-') AS Returns_5Yr,
COALESCE(CAST(CAST(r.YTD AS decimal(10,2)) AS varchar(20)),'-') AS YTD,
--, 0 AS Minus1
COALESCE(CAST(CAST(r.Minus1 AS decimal(10,2)) AS varchar(20)),'-') AS Minus1,
COALESCE(CAST(CAST(r.Minus2 AS decimal(10,2)) AS varchar(20)),'-') AS Minus2,
COALESCE(CAST(CAST(r.Minus3 AS decimal(10,2)) AS varchar(20)),'-') AS Minus3,
COALESCE(CAST(CAST(r.Minus4 AS decimal(10,2)) AS varchar(20)),'-') AS Minus4,
COALESCE(CAST(CAST(r.PCT1Y0_MTH AS decimal(10,2)) AS varchar(20)),'-') AS PCT1Y0_MTH,
COALESCE(CAST(CAST(r.PCT1Y1_MTH AS decimal(10,2)) AS varchar(20)),'-') AS PCT1Y1_MTH,
COALESCE(CAST(CAST(r.PCT1Y2_MTH AS decimal(10,2)) AS varchar(20)),'-') AS PCT1Y2_MTH,
COALESCE(CAST(CAST(r.PCT1Y3_MTH AS decimal(10,2)) AS varchar(20)),'-') AS PCT1Y3_MTH,
COALESCE(CAST(CAST(r.PCT1Y4_MTH AS decimal(10,2)) AS varchar(20)),'-') AS PCT1Y4_MTH
FROM @lipperid AS l
INNER JOIN #results AS r-- inner JOIN AS we are guaranteed to ahve them (due to inserting any missing earlier)
ON l.LipperId = r.LipperId
LEFT JOIN rpt.fundGROUP AS fg
ON r.fundgroupid = fg.id
ORDER BY l.Idx -- IF using triplet THEN will depEND ON earlier INSERT INTO @lipperId being ordered
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 6, 2015 at 1:43 am
Thanks for the replies.
Well, I am trying to fix something that was already in place for sometime and I have no clue on how the front end works. So the only thing i can do is restructuring of this proc but can't remove or add anything that makes end results look different. This proc is executed quite often in a day. If I can get some clues on the topmost things that need immediate attention, I can bang my head against those.
Thanks again for your views on this.:-)
August 6, 2015 at 2:22 am
Yogeshwar Phull (8/6/2015)
Thanks for the replies.Well, I am trying to fix something that was already in place for sometime and I have no clue on how the front end works. So the only thing i can do is restructuring of this proc but can't remove or add anything that makes end results look different. This proc is executed quite often in a day. If I can get some clues on the topmost things that need immediate attention, I can bang my head against those.
Thanks again for your views on this.:-)
Probably the lowest hanging fruit here is the tempdb configurations, earlier filtering of the sets in Q5 and Q6 and possibly changed the way the @lipperid table variable is used.
😎
August 6, 2015 at 4:43 am
1) I see vw..., indicating likely views. Lord only knows what those have in them
2) Table variables should be replaced with temp tables
3) Someone got rid of the silly cursor
4) you have functions around columns in WHERE clause. Number one thing that needs to go there
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 6, 2015 at 6:42 am
Those three aggregate subqueries queries against the 'performance' table appear to be very expensive. Why not do them in one shot, which would offer you tuning possibilities for that component in isolation from the rest of the query. Try this hack. When the results are the same (if they are not, you're a developer, figure it out) then post up the Actual execution plan grabbed from an execution with a decent amount of data running through it, and provide us with some idea of performance against the original version.
Edit: check that your statistics are up to date. Actual and expected rows for the 'performance' table are wildly different.
USE [KSDK_157]
GO
/****** Object: StoredProcedure [rpi].[udf_GetPerformance_FS] Script Date: 05/08/2015 12:07:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [rpi].[udf_GetPerformance_FS] (
@LipperIdList VARCHAR(MAX) = ''
,@FundsOnly char(1) = '0', -- really a bit but FundNets can't do that. Default 0 in case not passed
@LanguageId varchar(10) = '',
@WebsiteId varchar(10) = '',
@RangeId varchar(10) = ''
)
AS
BEGIN
select @languageid = case when isnumeric(@languageid)=1 then @languageid else null end
select @websiteid = case when isnumeric(@websiteid)=1 then @websiteid else null end
select @rangeid = case when isnumeric(@rangeid)=1 then @rangeid else null end
declare @LipperId table (LipperId int, Idx INT IDENTITY(1,1)) --FundGroupId will be NULL if providing a LipperId list, only populated if using triplet
declare @BaseCurrency char(3), @BaseCurrencyName varchar(100)
-- we will always return the latest data, but the FS tables will accumulate multiple ReportDates
declare @latestrptPerfReportDate date
select @latestrptPerfReportDate = max(reportdate) from fs.performance where ReportDate < DATEADD(day, -14,getdate())
/*
declare @RetailWebsite int
select @RetailWebsite = id from rpt.website where name = 'Retail'
*/
-- start by inserting based on @LipperIdList
insert into @LipperId (LipperId)
select String
from app.udf_SplitString(@LipperIdList, ',')
ORDER BY idx -- we will respect the order they were sent in
if @WebsiteId is not null -- sufficient to test just one of the three parameters to see if we are using this approach
begin
insert into @LipperId (LipperId)
select fl.LipperId
from
[rpi].[udf_FC_GetFundList] (@LanguageId, @WebsiteId, @RangeId, null /* spotlight */, null /* sandprating */) fl
order by RangeName, fundname, shareclass
/*
from [rpi].[udf_GetFundList_Brief] (@LanguageId, @WebsiteId, @RangeId) fl
join rpt.portfolioattributes pa
on fl.fundid = pa.fundid
*/
end
--/* Not everyone in our list may have the same date values available, so we take the worst, to ensure all funds have something to display
----*/
if @FundsOnly = 1
-- we throw out anything that isn't a fund-type lipperid. We do this indirectly as we don't have a fundtype/securitytype to rely upon and these are also in app.fund
begin
delete from @LipperId
where Lipperid in ( -- automatically distinct
select LipperBenchmarkId from rpt.portfolioattributes
union select lippersectorid from rpt.portfolioattributes )
end
create table #results( idx int,
RowType varchar(10), -- 'Fund' / 'BM', 'S'
FundGroupId int, -- will be set with an update statement later
FundId int, -- fundId (FT's app.Fund.Id )
fsFundId int,
Primary_Share_Class varchar(3),
LipperId int,
CurrencyCode char(3),
InceptionDate date,
ISIN varchar(12),
FundName varchar(100),
FundShortName varchar(100),
ShareClass varchar(100),
Returns_1mth decimal(20,10),
Returns_3mth decimal(20,10),
Returns_6mth decimal(20,10),
Returns_1Yr decimal(20,10),
Returns_3Yr decimal(20,10),
Returns_5Yr decimal(20,10),
Minus1 decimal(20,10),
Minus2 decimal(20,10),
Minus3 decimal(20,10),
Minus4 decimal(20,10),
YTD decimal(20,10),
PCT1Y0_MTH decimal(20,10),
PCT1Y1_MTH decimal(20,10),
PCT1Y2_MTH decimal(20,10),
PCT1Y3_MTH decimal(20,10),
PCT1Y4_MTH decimal(20,10)
)
-------------------------------------------------------------------------------------------------------------------------------
SELECT
FundId,
Currency, -- descriptive name
Note,
-- start with cumuative performance measures
sum(case when ProductId = 1 and BasisId = 1 and PeriodValue = 1 and PeriodUnit = 'M' then PerformanceValue1 else null end ) as Returns_1mth,
sum(case when ProductId = 1 and BasisId = 1 and PeriodValue = 3 and PeriodUnit = 'M' then PerformanceValue1 else null end ) as Returns_3mth,
sum(case when ProductId = 1 and BasisId = 1 and PeriodValue = 6 and PeriodUnit = 'M' then PerformanceValue1 else null end ) as Returns_6mth,
sum(case when ProductId = 1 and BasisId = 1 and PeriodValue = 1 and PeriodUnit = 'Y' then PerformanceValue1 else null end ) as Returns_1Yr,
sum(case when ProductId = 1 and BasisId = 1 and PeriodValue = 3 and PeriodUnit = 'Y' then PerformanceValue1 else null end ) as Returns_3Yr,
sum(case when ProductId = 1 and BasisId = 1 and PeriodValue = 5 and PeriodUnit = 'Y' then PerformanceValue1 else null end ) as Returns_5Yr,
-- now calendar year
sum(case when ProductId = 3 and BasisId = 1 and PeriodUnit = cast(datepart(yy, @latestRptPerfReportDate)-1 as varchar(4)) then PerformanceValue1 else null end ) as Minus1,
sum(case when ProductId = 3 and BasisId = 1 and PeriodUnit = cast(datepart(yy, @latestRptPerfReportDate)-2 as varchar(4)) then PerformanceValue1 else null end ) as Minus2,
sum(case when ProductId = 3 and BasisId = 1 and PeriodUnit = cast(datepart(yy, @latestRptPerfReportDate)-3 as varchar(4)) then PerformanceValue1 else null end ) as Minus3,
sum(case when ProductId = 3 and BasisId = 1 and PeriodUnit = cast(datepart(yy, @latestRptPerfReportDate)-4 as varchar(4)) then PerformanceValue1 else null end ) as Minus4,
-- Discrete Annual Returns
sum(case when ProductId = 2 and BasisId = 5 then PerformanceValue1 else null end ) as PCT1Y0_MTH,
sum(case when ProductId = 2 and BasisId = 6 then PerformanceValue1 else null end ) as PCT1Y1_MTH,
sum(case when ProductId = 2 and BasisId = 7 then PerformanceValue1 else null end ) as PCT1Y2_MTH,
sum(case when ProductId = 2 and BasisId = 8 then PerformanceValue1 else null end ) as PCT1Y3_MTH,
sum(case when ProductId = 2 and BasisId = 9 then PerformanceValue1 else null end ) as PCT1Y4_MTH,
sum(case when ProductId = 3 and BasisId = 1 and PeriodValue = 1 then PerformanceValue1 else null end ) as YTD -- was 4
INTO #Performance
FROM fs.Performance p
WHERE ReportDate = @latestrptPerfReportDate
AND Note IN ('Fund','Benchmark','Sector')
AND ProductId IN (1,2,3)
--and ProductId = 1-- Discrete performance
-- and BasisId = 1
GROUP BY FundId, Currency
CREATE CLUSTERED INDEX cx_Helper ON #Performance (FundId,Currency,Note)
-------------------------------------------------------------------------------------------------------------------------------
-- main select
insert into #results (
RowType ,
FundGroupId ,
FundId,
Primary_Share_Class,
LipperId ,
CurrencyCode ,
InceptionDate ,
ISIN,
ShareClass,
Returns_1mth ,
Returns_3mth ,
Returns_6mth ,
Returns_1Yr ,
Returns_3Yr,
Returns_5Yr ,
Minus1 ,
Minus2 ,
Minus3 ,
Minus4 ,
YTD ,
PCT1Y0_MTH ,
PCT1Y1_MTH ,
PCT1Y2_MTH,
PCT1Y3_MTH ,
PCT1Y4_MTH )
select
'Fund' as RowType, -- 'Fund' in the case of these records.
Null as FundGroupId, -- will be set with an update statement later, needed here as select into
f.Id as FundId, -- fundId (FT's app.Fund.Id )
pa.Primary_Share_Class,
l.LipperId,
f.CurrencyId as CurrencyCode,
pa.InceptionDate,
f.AccountGr as ISIN,
pa.ShareClass,
Returns_1mth,
Returns_3mth,
Returns_6mth,
Returns_1Yr,
Returns_3Yr,
Returns_5Yr,
Minus1,
Minus2,
Minus3,
Minus4,
YTD,
PCT1Y0_MTH,
PCT1Y1_MTH,
PCT1Y2_MTH,
PCT1Y3_MTH,
PCT1Y4_MTH
from @lipperid l
join rpt.portfolioattributes pa
on pa.lipperid = l.lipperid
join app.fund F
on f.id = pa.fundid
join fs.FundIdentifier fi
on fi.LipperFundId= l.lipperid -- FS is varchar but implicit casting
join lu.currency c
on c.id = F.CurrencyId -- force results only in base currency of fund. However there calendar year records available in some other currencies
LEFT JOIN #Performance pf
ON pf.fundid = fi.FundId
AND pf.Currency = c.Name
AND pf.Note = 'Fund'
-------------------------------------------------------------------------------------------------------------------------------------------
select top 1 @BaseCurrency = CurrencyCode , @BaseCurrencyName = c.Name
from #results r
join lu.Currency c
on r.CurrencyCode = c.Id
-- if there were no records found for funds then the BM and Sector will also return no rows - by design
-- testing, show interim results
--select * from #results
-- Add benchmarks, limited to the assumed commonly used CurrencyCode from funds
insert into #results (
RowType ,
FundGroupId,
FundId,
fsFundId,
Primary_Share_Class,
LipperId,
CurrencyCode,
InceptionDate,
ISIN,
ShareClass,
Returns_1mth,
Returns_3mth,
Returns_6mth,
Returns_1Yr,
Returns_3Yr,
Returns_5Yr,
Minus1,
Minus2,
Minus3,
Minus4,
YTD,
PCT1Y0_MTH,
PCT1Y1_MTH,
PCT1Y2_MTH,
PCT1Y3_MTH,
PCT1Y4_MTH )
select
distinct
'BM' as RowType,
Null as FundGroupId, -- will be set with an update statement later, needed here as select into
f.Id as FundId, -- fundId (FT's app.Fund.Id )
null,
pa.Primary_Share_Class,
l.LipperId,
c.Id as CurrencyCode, -- different from Fund
NULL, -- Benchmark's done have pa.InceptionDate
f.AccountGr as ISIN,
NULL, -- Benchmarks don't have pa.ShareClass,
Returns_1mth,
Returns_3mth,
Returns_6mth,
Returns_1Yr,
Returns_3Yr,
Returns_5Yr,
Minus1,
Minus2,
Minus3,
Minus4,
YTD,
PCT1Y0_MTH,
PCT1Y1_MTH,
PCT1Y2_MTH,
PCT1Y3_MTH,
PCT1Y4_MTH
from @lipperid l
join app.fund F
on cast(l.lipperid as varchar(50))= cast(f.AccountGr as varchar(50))
join rpt.portfolioattributes pa
on f.Id = pa.fundid
join fs.FundIdentifier fi
on fi.LipperBenchmarkId= l.lipperid -- FS is varchar but implicit casting
join @lipperid l_fund_level
on l_fund_level.LipperId = fi.LipperFundId /* Join back to @lipperId, this time again the fund, to restrict the set of funds that contribute
to those that will be shown in the tabular output, i.e. restrict distinc t versions available */
INNER JOIN #Performance pf
ON pf.fundid = fi.FundId
AND pf.Note = 'Benchmark'
AND pf.Currency = @BaseCurrencyName
INNER JOIN lu.currency c
on c.Name = pF.Currency
-- and pf.Currency = c.Name
/* actually slows overall time
create index ix_results_fundgroupid on #results(FundGroupId)
*/
-- Add SECTORS
insert into #results (
RowType ,
FundGroupId, -- will be set with an update statement later, needed here as select into
FundId, -- fundId (FT's app.Fund.Id )
fsFundId,
Primary_Share_Class,
LipperId,
CurrencyCode,
InceptionDate,
ISIN,
ShareClass,
Returns_1mth,
Returns_3mth,
Returns_6mth,
Returns_1Yr,
Returns_3Yr,
Returns_5Yr,
Minus1,
Minus2,
Minus3,
Minus4,
YTD,
PCT1Y0_MTH,
PCT1Y1_MTH,
PCT1Y2_MTH,
PCT1Y3_MTH,
PCT1Y4_MTH )
select
distinct -- distinct as many of the FS funds will share the sector and only one is needeed. They are not necessarily consistent (thank you Aberdeen for multiple differentcopies of what should be a single copy of this data)
'S' as RowType,
Null as FundGroupId, -- will be set with an update statement later, needed here as select into
f.Id as FundId, -- fundId (FT's app.Fund.Id )
pf.FundId as fsFundId,
pa.Primary_Share_Class,
l.LipperId,
c.Id as CurrencyCode, -- different from Fund
NULL, -- Sectors done have pa.InceptionDate
f.AccountGr as ISIN,
NULL, -- Sectors don't have pa.ShareClass,
Returns_1mth,
Returns_3mth,
Returns_6mth,
Returns_1Yr,
Returns_3Yr,
Returns_5Yr,
Minus1,
Minus2,
Minus3,
Minus4,
YTD,
PCT1Y0_MTH,
PCT1Y1_MTH,
PCT1Y2_MTH,
PCT1Y3_MTH,
PCT1Y4_MTH
from @lipperid l
join app.fund F
on cast(l.lipperid as varchar(50))= cast(f.AccountGr as varchar(50))
left join rpt.portfolioattributes pa -- changed 04 March 2013
on f.Id = pa.fundid
left join fs.FundIdentifier fi
on fi.LipperSectorId= l.lipperid -- FS is varchar but implicit casting
left join @lipperid l_fund_level
on l_fund_level.LipperId = fi.LipperFundId /* Join back to @lipperId, this time again the fund, to restrict the set of funds that contribute
to those that will be shown in the tabular output, i.e. restrict distinc t versions available */
INNER JOIN #Performance pf
ON pf.fundid = fi.FundId
AND pf.Note = 'Sector'
AND pf.Currency = @BaseCurrencyName
join lu.currency c
on c.name = pF.Currency
-- Hide values if incepted <1 yr ago. If no inception date assume is old.
-- If website parameter is null this will not work, but that parameter will always be set in normal operation of website.
update r
set returns_1mth = null,
returns_3mth = null,
returns_6mth = null,
Returns_1Yr = null,
Returns_3yr = null,
Returns_5yr = null,
YTD = null,
Minus1=null,
Minus2 = null,
Minus3 = null,
Minus4 = null,
PCT1Y0_MTH = null,
PCT1Y1_MTH = null,
PCT1Y2_MTH = null,
PCT1Y3_MTH = null,
PCT1Y4_MTH = null
from #results r
where InceptionDate>dateadd(mm, -13, getdate())
and RowType = 'Fund'
/* and @WebsiteId = @RetailWebsite */
-- Insert any that are missing - need to do here rather than left join later as setting names is messy
/* insert into #Results(LipperId)
select LipperId from @LipperId l
where not exists ( select * from #results r where r.LipperId = l.LipperId)
*/
-- Set the FundName and FundShortName.
-- for 'funds'
update r set FundId = f.id, FundName = f.fullname , FundShortName = f.ShortName
from #results r
join rpt.PortfolioAttributes pa
on pa.LipperId = r.LipperId
join app.Fund f
on pa.Fundid = f.Id
-- for 'benchmarks' and 'sectors' (represented differently)
update r set FundId = f.id, FundName = f.fullname , FundShortName = f.ShortName
from #results r
join app.Fund f
on cast(r.LipperId as varchar(100)) = AccountGr
-- set the FundGroupId (take from any range even if in multiple, so a random matching row will do)
update r
set FundGroupId = fd.FundGroupId
from #results r
join rpt.funddisplay fd
on r.FundId = fd.FundId
-- Fund renaming (if changing find other copies of this code */
declare @FundGroupId int, @FromText varchar(255), @ToText varchar(255), @fetch_status1 int
-- we don't have a simple parameter to work with so we have to joinn from #results to find range (or possibly multiple ranges).
declare Replacements cursor
for select distinct fr.FundGroupId, FromText, ToText from
rpt.FundRename fr
join #results r
on fr.fundgroupid = r.fundgroupid
open Replacements
fetch next from Replacements into @FundGroupId, @FromText, @ToText
set @fetch_status1 = @@fetch_status
while @fetch_status1 <> -1 -- in case we need the @@fetch_status later and it is confused by the inner cursor
begin
update #Results
set FundName = replace(fundname, @fromtext, coalesce(@totext,''))
where FundGroupId = @fundGroupId -- in case we have a mix of fundgroups
fetch next from Replacements into @FundGroupId, @FromText, @ToText
set @fetch_status1 = @@fetch_status
end
-- remove any secondary sectors where a primary exists
delete from r
from #results r
join
fs.fundidentifier fi on fi.fundid = r.fsfundid
join
rpt.vwFund f on f.AccountGr = fi.ISIN
join
rpt.PortfolioAttributes pa on pa.Fundid = f.Id
where pa.Primary_Share_Class = 'No'
and exists (select *
from #results r1
join
fs.fundidentifier fi1 on fi1.fundid = r1.fsfundid
join
rpt.vwFund f1 on f1.AccountGr = fi1.ISIN
join
rpt.portfolioattributes pa1 on pa1.fundid = f1.id
where pa1.Primary_Share_Class = 'Yes'
and r1.lipperid = r.lipperid)
delete from r2
from #results r1
inner join
#results r2 on r2.FundId = r1.FundId
where r2.fsFundId > r1.fsFundId
-- return results. Have to substitute nulls for the calendar year ones as not done on websie
select distinct
l.idx as RowNumber,
r.RowType,
r.Primary_Share_Class,
@latestrptPerfReportDate as ReportDate,
r.FundGroupId,
fg.Name,
r.FundId,
-- r.fsFundId,
l.LipperId,
r.CurrencyCode,
r.InceptionDate,
r.ISIN,
r.FundName ,
r.FundShortName
,r.ShareClass
,coalesce(cast(cast(r.Returns_1mth as decimal(10,2)) as varchar(20)),'-') as Returns_1mth,
coalesce(cast(cast(r.Returns_3mth as decimal(10,2)) as varchar(20)),'-') as Returns_3mth,
coalesce(cast(cast(r.Returns_6mth as decimal(10,2)) as varchar(20)),'-') as Returns_6mth,
coalesce(cast(cast(r.Returns_1Yr as decimal(10,2)) as varchar(20)),'-') as Returns_1Yr,
coalesce(cast(cast(r.Returns_3Yr as decimal(10,2)) as varchar(20)),'-') as Returns_3Yr,
coalesce(cast(cast( r.Returns_5Yr as decimal(10,2)) as varchar(20)),'-') as Returns_5Yr,
coalesce(cast(cast(r.YTD as decimal(10,2)) as varchar(20)),'-') as YTD
--, 0 as Minus1
,coalesce(cast(cast(r.Minus1 as decimal(10,2)) as varchar(20)),'-') as Minus1
,coalesce(cast(cast(r.Minus2 as decimal(10,2)) as varchar(20)),'-') as Minus2
,coalesce(cast(cast(r.Minus3 as decimal(10,2)) as varchar(20)),'-') as Minus3
,coalesce(cast(cast(r.Minus4 as decimal(10,2)) as varchar(20)),'-') as Minus4
,coalesce(cast(cast(r.PCT1Y0_MTH as decimal(10,2)) as varchar(20)),'-') as PCT1Y0_MTH
,coalesce(cast(cast(r.PCT1Y1_MTH as decimal(10,2)) as varchar(20)),'-') as PCT1Y1_MTH
,coalesce(cast(cast(r.PCT1Y2_MTH as decimal(10,2)) as varchar(20)),'-') as PCT1Y2_MTH
,coalesce(cast(cast(r.PCT1Y3_MTH as decimal(10,2)) as varchar(20)),'-') as PCT1Y3_MTH
,coalesce(cast(cast(r.PCT1Y4_MTH as decimal(10,2)) as varchar(20)),'-') as PCT1Y4_MTH
from @lipperid l
join #results r -- inner join as we are guaranteed to ahve them (due to inserting any missing earlier)
on l.LipperId = r.LipperId
left join rpt.fundgroup fg
on r.fundgroupid = fg.id
order by l.Idx -- if using triplet then will depend on earlier insert into @lipperId being ordered
END
GO
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 6, 2015 at 7:18 am
Stuff like this has to go:
INNER JOIN app.fund F
ON CAST(l.lipperid AS varchar(50))= CAST(f.AccountGr AS varchar(50))
You're going to have to work through this in really small pieces. There's no magic bullet or quick solution. The fact that views are being used within this is pretty frightening on top of everything else this code is doing wrong. Look to eliminate those too. I agree on using temp tables instead of table variables. I didn't even read enough to see the cursor.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 6, 2015 at 8:09 am
Thanks everyone.
I'll work on these suggestions and will post on how it worked out.
Cheers!!
August 6, 2015 at 8:38 am
MANY things can cause those est/actual row count differences: table vars, UDFs, functions around columns in where, stuff inside views, mis-matched column data types in joins, mismatched datatypes in parameters to their columns, and yes, even out-of-date statistics. 😀
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 7, 2015 at 6:29 am
[font="Comic Sans MS"]One simple improvement would be to avoid DDL in the middle of DML statements.
Move to the top, before any DML the following:[/font]
[font="Courier New"]CREATE TABLE #results(...[/font]
[font="Comic Sans MS"]No, this is not the magic bullet, just something to be avoided. I seem to recall from way back when that defining a new table in the middle of select's caused overhead.[/font]
August 7, 2015 at 7:45 am
j-1064772 (8/7/2015)
[font="Comic Sans MS"]One simple improvement would be to avoid DDL in the middle of DML statements.Move to the top, before any DML the following:[/font]
[font="Courier New"]CREATE TABLE #results(...[/font]
[font="Comic Sans MS"]No, this is not the magic bullet, just something to be avoided. I seem to recall from way back when that defining a new table in the middle of select's caused overhead.[/font]
Huh??? I don't think there is any truth to this. If there is I would love to see some documentation or article explaining this. Not saying it isn't correct but without something to back it up I disagree.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 7, 2015 at 7:50 am
Sean Lange (8/7/2015)
j-1064772 (8/7/2015)
[font="Comic Sans MS"]One simple improvement would be to avoid DDL in the middle of DML statements.Move to the top, before any DML the following:[/font]
[font="Courier New"]CREATE TABLE #results(...[/font]
[font="Comic Sans MS"]No, this is not the magic bullet, just something to be avoided. I seem to recall from way back when that defining a new table in the middle of select's caused overhead.[/font]
Huh??? I don't think there is any truth to this. If there is I would love to see some documentation or article explaining this. Not saying it isn't correct but without something to back it up I disagree.
Well, maybe if you gave the table a dirty name you might end up with dirty data?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply