December 29, 2015 at 11:41 pm
Hi Friends,
one of my colleague asked me to rewrite this script as this is having a performance issue. Unfortunately he is not sure what is the result he is looking for. So I am not able to help him. But can some one tell me what is the other method to rewrite this script? or what exactly this script is doing? So I could help him by explaining.
I am sorry for not providing proper details.
SELECT
er1.RowID,
er1.CurrencyID,
er1.Rate,
er1.DateEffective AS StartDate,
MIN(COALESCE(er2.DateEffective,CONVERT(datetime,'99991231'))) AS EndDate
FROM
dbo.ExchangeRate er1
LEFT OUTER JOIN
dbo.ExchangeRate er2 ON er1.CurrencyId = er2.CurrencyId AND er1.DateEffective < er2.DateEffective
GROUP BY
er1.RowId,er1.CurrencyID,er1.Rate,er1.DateEffective
Thanks,
Charmer
December 30, 2015 at 12:19 am
Well its not so complicated in terms of understanding what current query is doing. its actually creating Start Date and End Date of you ExchangeRate for a currencyId.
This will show you how its working. i have put some sample data in it.
Declare @ExchangeRate table (rowid int , currencyid char(3), rate decimal(11,5), DateEffective datetime )
----- Inserted a currency Data
insert into @ExchangeRate (rowid, currencyid, rate, DateEffective )
select 1, 'USD', 1.2, Dateadd(dd,-10, getdate()) union all
select 2, 'USD', 1.1, Dateadd(dd,-9, getdate()) union all
select 3, 'USD', 1.3, Dateadd(dd,-8, getdate()) union all
select 4, 'USD', 1.4, Dateadd(dd,-5, getdate()) union all
select 5, 'USD', 2.5, Dateadd(dd,-1, getdate())
----- Inserted a another currency Data
insert into @ExchangeRate (rowid, currencyid, rate, DateEffective )
select 6, 'PKR', 11.2, Dateadd(dd,-10, getdate()) union all
select 7, 'PKR', 11.1, Dateadd(dd,-9, getdate()) union all
select 8, 'PKR', 11.3, Dateadd(dd,-8, getdate()) union all
select 9, 'PKR', 11.4, Dateadd(dd,-5, getdate()) union all
select 10, 'PKR', 12.5, Dateadd(dd,-1, getdate())
----- current query
SELECT
er1.RowID,
er1.CurrencyID,
er1.Rate,
er1.DateEffective AS StartDate,
MIN(COALESCE(er2.DateEffective,CONVERT(datetime,'99991231'))) AS EndDate
FROM
@ExchangeRate er1
LEFT OUTER JOIN
@ExchangeRate er2 ON er1.CurrencyId = er2.CurrencyId AND er1.DateEffective < er2.DateEffective
GROUP BY
er1.RowId,er1.CurrencyID,er1.Rate,er1.DateEffective
December 30, 2015 at 12:37 am
Thanks twin.devil.
well, I tried with cte but the result are not similar. Is there any better way to do this? Just curious..
SELECT er.rowid, er.CurrencyId
,er.Rate, er.DateEffective
FROM (SELECT er.rowid,er.currencyid
,Rate
,ROW_NUMBER() OVER (
PARTITION BY er.CurrencyID ORDER BY DateEffective DESC
) AS RowNumber
, DateEffective
FROM @ExchangeRate er
WHERE DateEffective <= '99991231'
) er
WHERE RowNumber = 1
Thanks,
Charmer
December 30, 2015 at 12:55 am
I have tried to put a sample data to test the current query result with the new query. With a bit of data for stress test.
DDL Structure
USE TempDB
GO
IF Object_Id('dbo.ExchangeRate') IS NOT NULL
DROP TABLE dbo.ExchangeRate
GO
Create Table dbo.ExchangeRate
(
RowId int identity(1,1),
CurrencyId Char(3),
Rate decimal(11,5),
DateEffective datetime
)
----- Few Assumtions to be made
--------- Created a Clustered Index on RowID
CREATE CLUSTERED INDEX IXC_ExchangeRate_RowId
ON dbo.ExchangeRate (RowId)
--------- Created a NonClustered Index on the DateEffective
CREATE NONCLUSTERED INDEX IXC_ExchangeRate_DateEffective
ON dbo.ExchangeRate (DateEffective ASC)
GO
Now fill this newly created table with some sample data. Please note that everything is on assumptions here
--------------------- Sample Data Generation
;WITH CurrencyCte
AS
(
select 'USD' as CurrencyID union all
select 'VEB' as CurrencyID union all
select 'AED' as CurrencyID union all
select 'THB' as CurrencyID
)
insert into ExchangeRate
(CurrencyID, Rate, DateEffective)
SELECT TOP 10000
cur.CurrencyID
, CAST(ABS(CHECKSUM(NEWID())%10000 /1000.0) AS MONEY) AS Rate
, CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME) AS DateEffective
FROM Master.dbo.SysColumns sc1
CROSS JOIN Master.dbo.SysColumns sc2
CROSS JOIN CurrencyCte cur
GO
Once we are done with it ... Following are the queries which can be tested on this sample data.
-------- Orginial query
set statistics Time ON
SET Statistics IO ON
SELECT
er1.RowID,
er1.CurrencyID,
er1.Rate,
er1.DateEffective AS StartDate,
MIN(COALESCE(er2.DateEffective,CONVERT(datetime,'99991231'))) AS EndDate
FROM
ExchangeRate er1
LEFT OUTER JOIN
ExchangeRate er2 ON er1.CurrencyId = er2.CurrencyId AND er1.DateEffective < er2.DateEffective
GROUP BY
er1.RowId,er1.CurrencyID,er1.Rate,er1.DateEffective
set statistics Time OFF
SET Statistics IO OFF
---------- New Query
set statistics Time ON
SET Statistics IO ON
Select er1.*, isnull(xx.DateEffective, '9999-12-31') AS EndDate
from ExchangeRate er1
Cross apply
(
Select
min(er2.DateEffective) as DateEffective
from ExchangeRate er2
where er1.CurrencyId = er2.CurrencyId
and er1.DateEffective < er2.DateEffective
--Order by er2.DateEffective
)xx
set statistics Time OFF
SET Statistics IO OFF
Have placed Orginal query and New query you will see the difference.
Following are the stats it giving me on the PC For old query
(10000 row(s) affected)
Table 'ExchangeRate'. Scan count 18, logical reads 248, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 4, logical reads 120161, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 15405 ms, elapsed time = 5633 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(10000 row(s) affected)
Table 'ExchangeRate'. Scan count 10001, logical reads 102611, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 78 ms, elapsed time = 169 ms.
hope it helps.
December 30, 2015 at 4:36 am
Not too bad twin.devil but there is still room for improvement
😎
Test harness
USE tempdb;
GO
SET NOCOUNT ON;
--/*
IF OBJECT_ID(N'dbo.ExchangeRate') IS NOT NULL DROP TABLE dbo.ExchangeRate;
CREATE TABLE dbo.ExchangeRate
(
ExchangeRateID INT IDENTITY(1,1) NOT NULL CONSTRAINT pk_dbo_ExchangeRate_ExchangeRateID PRIMARY KEY CLUSTERED
, CurrencyID CHAR(3) NOT NULL
, Rate DECIMAL(11,5) NOT NULL
, DateEffective DATETIME NOT NULL
);
DECLARE @SAMPLE_SIZE INT = 10000; -- Number of "Transactions"
DECLARE @BASE_DATE DATETIME = CONVERT(DATE,'2012-01-01',126); -- Base Date, all dates are based on this.
DECLARE @DATE_RANGE INT = 1461; -- +/- 4 Years
DECLARE @TIME_RANGE INT = 86400; -- 0 - 24 Hours
;WITH T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
,(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS X(N))
, NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7)
,BASE_DATA AS
(
SELECT
NM.N
, CHAR (65 + ABS(CHECKSUM(NEWID())) % 4)
+ CHAR (65 + ABS(CHECKSUM(NEWID())) % 4)
+ CHAR (65 + ABS(CHECKSUM(NEWID())) % 4) AS CurrencyID
,DATEADD(DAY,CHECKSUM(NEWID()) % @DATE_RANGE,@BASE_DATE) AS DateEffective
FROM NUMS NM
)
INSERT INTO dbo.ExchangeRate(CurrencyID,Rate,DateEffective)
SELECT
BD.CurrencyID
,ABS(CHECKSUM(BD.CurrencyID)) * (1.0 / POWER(10, FLOOR(LOG10(ABS(CHECKSUM(BD.CurrencyID)))) - 1))
+ CHECKSUM(NEWID()) * (1.0 / POWER(10, FLOOR(LOG10(ABS(CHECKSUM(BD.CurrencyID))))))
,DATEADD(SECOND,ABS(CHECKSUM(NEWID())) % @TIME_RANGE,BD.DateEffective)
FROM BASE_DATA BD
ORDER BY BD.DateEffective ASC
,BD.CurrencyID ASC;
CREATE NONCLUSTERED INDEX NCLIDX_DBO_EXCHANGERATE_CURRENCY_EFFDATE_INCL_RATE ON dbo.ExchangeRate
(
CurrencyID ASC
,DateEffective ASC
)
INCLUDE ( Rate , ExchangeRateID );
-- */
DECLARE @timer TABLE (T_TEXT VARCHAR(100) NOT NULL, T_TS DATETIME2(7) NOT NULL DEFAULT (SYSDATETIME()));
DECLARE @INT_BUCKET INT = 0;
DECLARE @CHAR_BUCKET CHAR(3) = '';
DECLARE @DECIMAL_BUCKET DECIMAL(11,5) = 0;
DECLARE @DATETIME_BUCKET_01 DATETIME = 0;
DECLARE @DATETIME_BUCKET_02 DATETIME = 0;
INSERT INTO @timer(T_TEXT) VALUES ('DRY RUN');
SELECT
@INT_BUCKET = EXR.ExchangeRateID
,@CHAR_BUCKET = EXR.CurrencyID
,@DECIMAL_BUCKET = EXR.Rate
,@DATETIME_BUCKET_01 = EXR.DateEffective --AS START_DATE
FROM dbo.ExchangeRate EXR
INSERT INTO @timer(T_TEXT) VALUES ('DRY RUN');
INSERT INTO @timer(T_TEXT) VALUES ('ROW_NUMBER');
;WITH BASE_DATA AS
(
SELECT
EXR.ExchangeRateID
,ROW_NUMBER() OVER
(
PARTITION BY EXR.CurrencyID
ORDER BY EXR.DateEffective
) AS EXR_RID
,EXR.CurrencyID
,EXR.Rate
,EXR.DateEffective
FROM dbo.ExchangeRate EXR
)
SELECT
@INT_BUCKET = BD_FROM.ExchangeRateID
,@CHAR_BUCKET = BD_FROM.CurrencyID
,@DECIMAL_BUCKET = BD_FROM.Rate
,@DATETIME_BUCKET_01 = BD_FROM.DateEffective --AS START_DATE
,@DATETIME_BUCKET_02 = ISNULL(BD_TO.DateEffective, '9999-12-31') --AS END_DATE
FROM BASE_DATA BD_FROM
LEFT OUTER JOIN BASE_DATA BD_TO
ON BD_FROM.CurrencyID = BD_TO.CurrencyID
AND BD_FROM.EXR_RID = BD_TO.EXR_RID - 1
;
INSERT INTO @timer(T_TEXT) VALUES ('ROW_NUMBER');
INSERT INTO @timer(T_TEXT) VALUES ('Cross apply');
Select
@INT_BUCKET = er1.ExchangeRateID
,@CHAR_BUCKET = er1.CurrencyID
,@DECIMAL_BUCKET = er1.Rate
,@DATETIME_BUCKET_01 = er1.DateEffective
,@DATETIME_BUCKET_02 = isnull(xx.DateEffective, '9999-12-31') --AS EndDate
from ExchangeRate er1
Cross apply
(
Select
min(er2.DateEffective) as DateEffective
from ExchangeRate er2
where er1.CurrencyId = er2.CurrencyId
and er1.DateEffective < er2.DateEffective
)xx;
INSERT INTO @timer(T_TEXT) VALUES ('Cross apply');
INSERT INTO @timer(T_TEXT) VALUES ('ORIGINAL QUERY');
SELECT
@INT_BUCKET = er1.ExchangeRateID
,@CHAR_BUCKET = er1.CurrencyID
,@DECIMAL_BUCKET = er1.Rate
,@DATETIME_BUCKET_01 = er1.DateEffective --AS StartDate
,@DATETIME_BUCKET_02 = MIN(COALESCE(er2.DateEffective,CONVERT(datetime,'99991231'))) --AS EndDate
FROM
dbo.ExchangeRate er1
LEFT OUTER JOIN
dbo.ExchangeRate er2 ON er1.CurrencyId = er2.CurrencyId AND er1.DateEffective < er2.DateEffective
GROUP BY
er1.ExchangeRateID,er1.CurrencyID,er1.Rate,er1.DateEffective
INSERT INTO @timer(T_TEXT) VALUES ('ORIGINAL QUERY');
/* -- 2012 OR LATER LEAD
INSERT INTO @timer(T_TEXT) VALUES ('LEAD');
SELECT
@INT_BUCKET = EXR.ExchangeRateID
,@CHAR_BUCKET = EXR.CurrencyID
,@DECIMAL_BUCKET = EXR.Rate
,@DATETIME_BUCKET_01 = EXR.DateEffective --AS START_DATE
,@DATETIME_BUCKET_02 = LEAD(EXR.DateEffective,1,'9999-12-31') OVER
(
PARTITION BY EXR.CurrencyID
ORDER BY EXR.DateEffective
) --AS END_DATE
FROM dbo.ExchangeRate EXR;
INSERT INTO @timer(T_TEXT) VALUES ('LEAD');
*/
SELECT
T.T_TEXT
,DATEDIFF(MICROSECOND,MIN(T.T_TS),MAX(T.T_TS)) AS DURATION
FROM @timer T
GROUP BY T.T_TEXT
ORDER BY DURATION ASC;
Results (original query only up to 100000 rows)
1000 ROWS
T_TEXT DURATION
-------------------------- -----------
DRY RUN 1000
ROW_NUMBER 6000
Cross apply 7001
ORIGINAL QUERY 16001
10000 ROWS
T_TEXT DURATION
------------------------ -----------
DRY RUN 7000
ROW_NUMBER 29002
Cross apply 55003
ORIGINAL QUERY 622036
100000 ROWS
-----------------------------
T_TEXT DURATION
---------------- ------------
DRY RUN 25002
ROW_NUMBER 205011
Cross apply 524030
ORIGINAL QUERY 25848479
1000000 ROWS
T_TEXT DURATION
---------------- -----------
DRY RUN 238014
ROW_NUMBER 2224127
Cross apply 4984285
10000000 ROWS
T_TEXT DURATION
---------------- -----------
DRY RUN 2382136
ROW_NUMBER 28553633
Cross apply 49442828
December 30, 2015 at 5:55 am
Hi Friends
First of all, thank you so much for all your suggestions. I really appreciate it.
I got this script from my colleague. I tested this script on the environment. I noticed that the issue is with the second part. He is trying to join the table variable twice and that makes the performance degrade. I tried with out joining table variable and script executed super fast. table variable stores around 11k records. I am not able to see the execution plan since it is a PROD server, no permission.
DECLARE @TempExchangeRate TABLE (
CurrencyId int NOT NULL , Rate decimal(18,6) NOT NULL, StartDate datetime NOT NULL, EndDate datetime NOT NULL);
INSERT INTO @TempExchangeRate
SELECT
er1.CurrencyID,
er1.Rate,
er1.DateEffective AS StartDate,
MIN(COALESCE(er2.DateEffective,CONVERT(datetime,'99991231'))) AS EndDate
FROM
dbo.ExchangeRate er1
LEFT OUTER JOIN
dbo.ExchangeRate er2 ON er1.CurrencyId = er2.CurrencyId AND er1.DateEffective < er2.DateEffective
GROUP BY
er1.CurrencyID,er1.Rate,er1.DateEffective
SELECT
cba.CustomerID ,
SUM((cba.ExposureAdjustment * (1 / cer.Rate)) * cuser.Rate) AS Exposure
FROM
dbo.CustomerBalanceAdjustment cba
INNER JOIN dbo.Customer cus ON cba.CustomerID = cus.CustomerID
INNER JOIN dbo.Company c ON cus.ColcoID = c.CompanyID
LEFT OUTER JOIN @TempExchangeRate cer ON c.CurrencyID = cer.CurrencyID AND cba.ModifiedOn >= cer.StartDate AND cba.ModifiedOn < cer.EndDate
LEFT OUTER JOIN @TempExchangeRate cuser ON cus.CurrencyID = cuser.CurrencyID AND cba.ModifiedOn >= cuser.StartDate AND cba.ModifiedOn < cuser.EndDate
WHERE
c.CurrencyID <> cus.CurrencyID
GROUP BY
cba.CustomerID
Friends, please give me your suggestions, is there a room to fine tune this query ? I will try to get the sample data as soon as possible
Thanks in advance for the suggestions
Thanks,
Charmer
December 30, 2015 at 6:50 am
I have attached the execution plan here...Please take a look at that.
Thanks,
Charmer
December 30, 2015 at 11:27 pm
Eirikur Eiriksson (12/30/2015)
Not too bad twin.devil but there is still room for improvement😎
Thanks for sharing the test Harness script Eirikur and YES there is always room for improvements. 🙂
December 31, 2015 at 12:55 am
Charmer (12/30/2015)
I have attached the execution plan here...Please take a look at that.
For the 1st part of the query, we suggestion have already been given, So i would be moving towards the 2nd part of the query.
i would suggest two things here:
1. Get the set of CustomerBalanceAdjustment, Customer and Company with WHERE Clause before joining it to the variable table. There is will reduce the chunk of data before the calculation part.
2. Try to change the Variable Tables to Temp Table because sql server will always estimate only single row whether there are 11K rows in the table. For Temp tables the plan will be better. because in future the records will increase from 11K.
Following this the query upon above suggestion.
-------- Replace the Variable Table with Temp Table
CREATE TABLE #TempExchangeRate (CurrencyId int NOT NULL , Rate decimal(18,6) NOT NULL, StartDate datetime NOT NULL, EndDate datetime NOT NULL);
--- Didn't replace the orginal query, Its upon OP to decide
INSERT INTO #TempExchangeRate
SELECT
er1.CurrencyID,
er1.Rate,
er1.DateEffective AS StartDate,
MIN(COALESCE(er2.DateEffective,CONVERT(datetime,'99991231'))) AS EndDate
FROM
dbo.ExchangeRate er1
LEFT OUTER JOIN
dbo.ExchangeRate er2 ON er1.CurrencyId = er2.CurrencyId AND er1.DateEffective < er2.DateEffective
GROUP BY
er1.CurrencyID,er1.Rate,er1.DateEffective
;
-------- Create the CTE to reduce the chunk of data in the later part of the query
WITH CTE_CustomerBalanceAdjustment
AS
(
SELECT
cus.CustomerID
, c.CompanyID
, cba.ExposureAdjustment
, cba.ModifiedOn
, cus.CurrencyIDAS CustomerCurrencyID
, c.CurrencyIDAS CompanyCurrencyID
FROM
dbo.CustomerBalanceAdjustment cba
INNER JOIN dbo.Customer cus ON cba.CustomerID = cus.CustomerID
INNER JOIN dbo.Company c ON cus.ColcoID = c.CompanyID
WHERE
c.CurrencyID <> cus.CurrencyID
)
------- No change in logic here.
SELECT
cba.CustomerID,
SUM((cba.ExposureAdjustment * (1 / cer.Rate)) * cuser.Rate) AS Exposure
FROM
CTE_CustomerBalanceAdjustment cba
LEFT OUTER JOIN #TempExchangeRate cerON cba.CompanyCurrencyID = cer.CurrencyIDAND cba.ModifiedOn >= cer.StartDateAND cba.ModifiedOn < cer.EndDate
LEFT OUTER JOIN #TempExchangeRate cuser ON cba.CustomerCurrencyID = cuser.CurrencyIDAND cba.ModifiedOn >= cuser.StartDateAND cba.ModifiedOn < cuser.EndDate
GROUP BY
cba.CustomerID
--- Clean up temp db space because its SQL SERVER Precious :)
DROP TABLE #TempExchangeRate
January 3, 2016 at 11:28 pm
Thanks for the suggestion. I informed to my colleague and he said he is executing this script in an OPENROWSET. And it is going to execute in different servers. He told temp table does not work and so he is using table variable
Or sub query. He told using table variable since no other choice.
Thanks,
Charmer
January 4, 2016 at 1:05 am
Charmer (1/3/2016)
Thanks for the suggestion. I informed to my colleague and he said he is executing this script in an OPENROWSET. And it is going to execute in different servers. He told temp table does not work and so he is using table variableOr sub query. He told using table variable since no other choice.
Can you share which part of the query is executing on the Other server? usually its a good practice to get the set from the other server to the local server and use that set on local server. its perform better. As i don't really know what kind of other work this query is doing. I think is time to get the actual picture whats exactly scenario is.
January 4, 2016 at 2:14 am
Hi Twin,
Actually it is a SP and below is the complete script that executes in different server. There is a config table which is called by another SP which holds all the server instance and database name. While running, it passes the linked server instance name and the database name based on the parameter they pass. He told that since it is a linked server, they can't use temp tables since it won't be created on linked server while execution.
SET @ReportSQLPart2_Local = 'SET @ReportSQLPart2_Result = (SELECT * FROM OPENQUERY([' + @DatabaseLinkInstance_Local + '],' + CHAR(39)
SET @ReportSQLPart2_Local = @ReportSQLPart2_Local +
'SET NOCOUNT ON;
DECLARE @TempExchangeRate TABLE (RowId int NOT NULL, CurrencyId int NOT NULL, Rate decimal(18,6) NOT NULL, StartDate datetime NOT NULL, EndDate datetime NOT NULL);
DECLARE @TempCustomerBalance TABLE (CustomerId int NOT NULL, ExposureAdjustment decimal (18,6) NOT NULL);
DECLARE @TempCustomerSecurityValue TABLE (CustomerId int NOT NULL, GuaranteeValue decimal(18,6));
INSERT INTO @TempExchangeRate
SELECT
er1.RowID,
er1.CurrencyID,
er1.Rate,
er1.DateEffective AS StartDate,
MIN(COALESCE(er2.DateEffective,CONVERT(datetime,' + CHAR(39) + CHAR(39) + '99991231' + CHAR(39) + CHAR(39) + '))) AS EndDate
FROM
'+@DatabaseName_Local+'.dbo.ExchangeRate er1
LEFT OUTER JOIN
'+@DatabaseName_Local+'.dbo.ExchangeRate er2 ON er1.CurrencyId = er2.CurrencyId AND er1.DateEffective < er2.DateEffective
GROUP BY
er1.RowId,er1.CurrencyID,er1.Rate,er1.DateEffective
INSERT INTO @TempCustomerBalance
SELECT
cba.CustomerID,
SUM((cba.ExposureAdjustment * (1 / cer.Rate)) * cuser.Rate) AS Exposure
FROM
'+@DatabaseName_Local+'.dbo.CustomerBalanceAdjustment cba
INNER JOIN '+@DatabaseName_Local+'.dbo.Customer cus ON cba.CustomerID = cus.CustomerID
INNER JOIN '+@DatabaseName_Local+'.dbo.Company c ON cus.ColcoID = c.CompanyID
LEFT OUTER JOIN @TempExchangeRate cer ON c.CurrencyID = cer.CurrencyID AND cba.ModifiedOn >= cer.StartDate AND cba.ModifiedOn < cer.EndDate
LEFT OUTER JOIN @TempExchangeRate cuser ON cus.CurrencyID = cuser.CurrencyID AND cba.ModifiedOn >= cuser.StartDate AND cba.ModifiedOn < cuser.EndDate
WHERE
c.CurrencyID <> cus.CurrencyID
GROUP BY
cba.CustomerID
UNION ALL
SELECT
c.CustomerID,SUM(cba.ExposureAdjustment) AS Exposure
FROM
'+@DatabaseName_Local+'.dbo.CustomerBalanceAdjustment cba
INNER JOIN '+@DatabaseName_Local+'.dbo.Customer c ON cba.CustomerID = c.CustomerID
INNER JOIN '+@DatabaseName_Local+'.dbo.Company comp on c.ColcoID = comp.CompanyID
WHERE
CONVERT(date,cba.ModifiedOn) <= ' + CHAR(39) + CHAR(39) + CONVERT(varchar, @ReportDate_Local, 112) + CHAR(39) + CHAR(39) + '
AND c.CurrencyID = comp.CurrencyID AND c.ColcoID = ' + CAST(@ColcoId_Local AS nvarchar) + '
GROUP BY
c.CustomerID
INSERT INTO @TempCustomerSecurityValue
SELECT
cs.CustomerID
,SUM((cs.GuaranteeValue*(1/cer.Rate)) * co_cer.Rate) AS GuaranteeValue
FROM
'+@DatabaseName_Local+'.dbo.CustomerGuarantees cs
INNER JOIN '+@DatabaseName_Local+'.dbo.Customer c ON cs.CustomerID = c.CustomerID
INNER JOIN '+@DatabaseName_Local+'.dbo.Company co ON c.ColcoID = co.CompanyID
INNER JOIN @TempExchangeRate cer ON cs.CurrencyID = cer.CurrencyID
INNER JOIN @TempExchangeRate co_cer ON co.CurrencyID = co_cer.CurrencyID
WHERE
cs.GuaranteeExpiryDate >= ' + CHAR(39) + CHAR(39) + CONVERT(varchar, @ReportDate_Local, 112) + CHAR(39) + CHAR(39) + '
AND cs.ModifiedOn >= cer.StartDate AND cs.ModifiedOn < cer.EndDate
AND cs.ModifiedOn >= co_cer.StartDate AND cs.ModifiedOn < co_cer.EndDate
GROUP BY
cs.CustomerID
SELECT
c.CustomerId
,cb.ExposureAdjustment AS TotalExposure
,COALESCE(cgv.GuaranteeValue,0) AS SecurityValue
FROM
'+@DatabaseName_Local+'.dbo.Customer c
INNER JOIN '+@DatabaseName_Local+'.dbo.Company co ON c.ColcoId = co.CompanyId AND co.CompanyId = ' + CAST(@ColcoId_Local AS nvarchar) + '
LEFT OUTER JOIN @TempCustomerBalance AS cb ON cb.CustomerId = c.CustomerId
LEFT OUTER JOIN @TempCustomerSecurityValue AS cgv ON cgv.CustomerId = c.CustomerId'
SET @ReportSQLPart2_Local = @ReportSQLPart2_Local + CHAR(39) + ') FOR XML RAW('+ CHAR(39) +'ReportSQLPart2'+ CHAR(39) +'),TYPE)'
below is the part where we have problem in performance.
INSERT INTO @TempCustomerBalance
SELECT
cba.CustomerID,
SUM((cba.ExposureAdjustment * (1 / cer.Rate)) * cuser.Rate) AS Exposure
FROM
'+@DatabaseName_Local+'.dbo.CustomerBalanceAdjustment cba
INNER JOIN '+@DatabaseName_Local+'.dbo.Customer cus ON cba.CustomerID = cus.CustomerID
INNER JOIN '+@DatabaseName_Local+'.dbo.Company c ON cus.ColcoID = c.CompanyID
LEFT OUTER JOIN @TempExchangeRate cer ON c.CurrencyID = cer.CurrencyID AND cba.ModifiedOn >= cer.StartDate AND cba.ModifiedOn < cer.EndDate
LEFT OUTER JOIN @TempExchangeRate cuser ON cus.CurrencyID = cuser.CurrencyID AND cba.ModifiedOn >= cuser.StartDate AND cba.ModifiedOn < cuser.EndDate
WHERE
c.CurrencyID <> cus.CurrencyID
GROUP BY
cba.CustomerID
I checked running below script alone, it produces 3.5 million rows. Sample screen shot is also attached.
SELECT
cba.CustomerID
, cba.ModifiedOn
,cba.ExposureAdjustment
, cus.CurrencyID as CustomerCurrencyID
, c.CurrencyID as CompanyCurrencyID
FROM
dbo.CustomerBalanceAdjustment cba
INNER JOIN dbo.Customer cus ON cba.CustomerID = cus.CustomerID
INNER JOIN dbo.Company c ON cus.ColcoID = c.CompanyID
WHERE
c.CurrencyID <> cus.CurrencyID
Thanks,
Charmer
January 4, 2016 at 5:00 am
Well well well, you are using OPENQUERY so the temporary tables are thrown out of the window. I will not ask you to create a SP on each database, as this will be very painful for u. so what ever you are currently looking for is somehow make this query perform a better then what it is currently doing. So for this you can do these
Replace your following query
SELECT
er1.RowID,
er1.CurrencyID,
er1.Rate,
er1.DateEffective AS StartDate,
MIN(COALESCE(er2.DateEffective,CONVERT(datetime,' + CHAR(39) + CHAR(39) + '99991231' + CHAR(39) + CHAR(39) + '))) AS EndDate
FROM
'+@DatabaseName_Local+'.dbo.ExchangeRate er1
LEFT OUTER JOIN
'+@DatabaseName_Local+'.dbo.ExchangeRate er2 ON er1.CurrencyId = er2.CurrencyId AND er1.DateEffective < er2.DateEffective
GROUP BY
er1.RowId,er1.CurrencyID,er1.Rate,er1.DateEffective
With this query
;WITH BASE_DATA AS
(
SELECT
EXR.RowID
,ROW_NUMBER() OVER
(
PARTITION BY EXR.CurrencyID
ORDER BY EXR.DateEffective
) AS EXR_RID
,EXR.CurrencyID
,EXR.Rate
,EXR.DateEffective
FROM '+@DatabaseName_Local+'.dbo.ExchangeRate EXR
)
INSERT INTO @TempExchangeRate
(RowId, CurrencyId, Rate, StartDate, EndDate)
SELECT
BD_FROM.ExchangeRateID
, BD_FROM.CurrencyID
, BD_FROM.Rate
, BD_FROM.DateEffective
, ISNULL(BD_TO.DateEffective, '+ CHAR(39) + CHAR(39) + '99991231' + CHAR(39) + CHAR(39) +')--AS END_DATE
FROM BASE_DATA BD_FROM
LEFT OUTER JOIN BASE_DATA BD_TO
ON BD_FROM.CurrencyID = BD_TO.CurrencyID
AND BD_FROM.EXR_RID = BD_TO.EXR_RID - 1
below is the part where we have problem in performance.
INSERT INTO @TempCustomerBalance
SELECT
cba.CustomerID,
SUM((cba.ExposureAdjustment * (1 / cer.Rate)) * cuser.Rate) AS Exposure
FROM
'+@DatabaseName_Local+'.dbo.CustomerBalanceAdjustment cba
INNER JOIN '+@DatabaseName_Local+'.dbo.Customer cus ON cba.CustomerID = cus.CustomerID
INNER JOIN '+@DatabaseName_Local+'.dbo.Company c ON cus.ColcoID = c.CompanyID
LEFT OUTER JOIN @TempExchangeRate cer ON c.CurrencyID = cer.CurrencyID AND cba.ModifiedOn >= cer.StartDate AND cba.ModifiedOn < cer.EndDate
LEFT OUTER JOIN @TempExchangeRate cuser ON cus.CurrencyID = cuser.CurrencyID AND cba.ModifiedOn >= cuser.StartDate AND cba.ModifiedOn < cuser.EndDate
WHERE
c.CurrencyID <> cus.CurrencyID
GROUP BY
cba.CustomerID
I checked running below script alone, it produces 3.5 million rows. Sample screen shot is also attached.
For this you can do the following query
SELECT
cba.CustomerID,
SUM((cba.ExposureAdjustment * (1 / cer.Rate)) * cuser.Rate) AS Exposure
FROM
(
SELECT
cba.CustomerID
, cba.ModifiedOn
, cus.CurrencyIDAS CustomerCurrencyID
, c.CurrencyIDAS CompanyCurrencyID
, SUM(cba.ExposureAdjustment)AS ExposureAdjustment
FROM
'+@DatabaseName_Local+'.dbo.CustomerBalanceAdjustment cba
INNER JOIN '+@DatabaseName_Local+'.dbo.Customer cus ON cba.CustomerID = cus.CustomerID
INNER JOIN '+@DatabaseName_Local+'.dbo.Company c ON cus.ColcoID = c.CompanyID
WHERE
c.CurrencyID <> cus.CurrencyID
GROUP BY
cba.CustomerID
, cba.ModifiedOn
, cus.CurrencyID
, c.CurrencyID
) A
LEFT OUTER JOIN @TempExchangeRate cerON A.CompanyCurrencyID = cer.CurrencyID
AND A.ModifiedOn >= cer.StartDate
AND A.ModifiedOn < cer.EndDate
LEFT OUTER JOIN @TempExchangeRate cuser ON A.CustomerCurrencyID = cuser.CurrencyID
AND A.ModifiedOn >= cuser.StartDate
AND A.ModifiedOn < cuser.EndDate
using preaggregation technique which will bring less rows to be processed at left outer join. Try it out.
January 4, 2016 at 5:42 am
Thanks, Twin. I will try as like as you told. I will get back to you with my result.
Thanks,
Charmer
January 4, 2016 at 6:59 am
Thank you so much , Twin. It works absolutely fine. There are minor decimal point difference in the Exposure field. But that's okay...Script executes lighting fast. 🙂
Thanks,
Charmer
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply