November 17, 2014 at 7:33 am
Hi,
I have an issue within a stored procedure. I have a temporary table (@tblResults) that has 4 columns that need to be populated with a calculation made from columns held within 2 other tables.
Joins
@tblResults tr JOIN dbo.MarketPrice mp
ON tr.Item = mp.Item
AND tr.[Month] = mp.[Month]
AND tr.[Year] = mp.[Year]
AND mp.[Date] BETWEEN tr.LatestStartDate AND tr.PriorEndDate
dbo.MillDifferentials can be joined on either of the former tables by
MillDifferentials.Item = Item
and MillDifferentials.[Month] = Month
and MillDifferentials.[Year] = Year
and
where
dbo.MillDifferentials.[date] must be BETWEEN @tblResults.LatestStartDate AND @tblResults.PriorEndDate
and MillDifferentials.Warehouse should be joined on @tblResults.Warehouse
USE [MyDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DECLARE @tblResults TABLE--CustBook entries
(
IdBIGINT IDENTITY ( 1, 1),
BookIdNVARCHAR( 10),
--BaseItemNVARCHAR( 10),
ItemNVARCHAR( 10),
WarehouseNVARCHAR( 10),
[Month]SMALLINT,
[Year]SMALLINT,
StartDateDATETIME,--Latest of either CustBook or CLOSED period START date
EndDateDATETIME,--Latest of either CustBook or CLOSED period END date
PriorStartDateDATETIME,--START date (12 months prior to combined Year/Month date)
PriorEndDateDATETIME,--END date (1 month prior to combined Year/Month date)
LatestStartDateDATETIME,--GREATEST of either StartDate or PriorStartDate
MaxPriceDECIMAL( 8,2),
MaxPriceDateDATETIME,
MinPriceDECIMAL( 8,2),
MinPriceDateDATETIME,
FoundTINYINT
)
;
INSERT @tblResults
VALUES('AC','TH0010','FRI',1,2015,'2013-10-01 00:00:00.000','2014-09-30 23:59:59.000','2014-01-01 00:00:00.000','2014-12-31 23:59:59.000','2014-01-01 00:00:00.000',NULL,NULL,NULL,NULL,0);
INSERT @tblResults
VALUES('AC','TH0010','FRI',2,2015,'2013-10-01 00:00:00.000','2014-09-30 23:59:59.000','2014-02-01 00:00:00.000','2015-01-31 23:59:59.000','2014-02-01 00:00:00.000',NULL,NULL,NULL,NULL,0);
INSERT @tblResults
VALUES('AC','TH0010','FRI',3,2015,'2013-10-01 00:00:00.000','2014-09-30 23:59:59.000','2014-03-01 00:00:00.000','2015-02-28 23:59:59.000','2014-03-01 00:00:00.000',NULL,NULL,NULL,NULL,0);
CREATE TABLE [dbo].[MarketPrice](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Date] [smalldatetime] NOT NULL,
[Item] [nvarchar](10) NOT NULL,
[Month] [smallint] NOT NULL,
[Year] [smallint] NOT NULL,
[Price] [decimal](8, 2) NOT NULL,
[Amendment_User] [nvarchar](60) NULL,
[Amendment_Date] [smalldatetime] NULL,
CONSTRAINT [PK_MarketPrice_1] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MarketPrice] ADD CONSTRAINT [DF_MarketPrice_Price] DEFAULT ((0.00)) FOR [Price]
GO
INSERT dbo.MarketPrice
VALUES('2014-01-04 00:00:00','TH0010',1,2015,'143.50','JBloggs','2014-01-21 00:00:00');
INSERT dbo.MarketPrice
VALUES('2014-01-21 00:00:00','TH0010',1,2015,'112.00','JBloggs','2014-01-02 00:00:00');
INSERT dbo.MarketPrice
VALUES('2014-01-22 00:00:00','TH0010',1,2015,'148.00','JBloggs','2014-01-02 00:00:00');
INSERT dbo.MarketPrice
VALUES('2014-04-28 00:00:00','TH0010',1,2015,'150.50','JBloggs','2014-04-28 00:00:00');
INSERT dbo.MarketPrice
VALUES('2014-10-29 00:00:00','TH0010',1,2015,'183.00','JBloggs','2014-04-29 00:00:00');
CREATE TABLE [dbo].[MillDifferentials](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Date] [datetime] NOT NULL,
[Item] [nvarchar](10) NOT NULL,
[Month] [smallint] NOT NULL,
[Year] [smallint] NOT NULL,
[Warehouse] [nvarchar](10) NOT NULL,
[Diff] [decimal](5, 2) NOT NULL,
[Amendment_User] [nvarchar](60) NULL,
[Amendment_Date] [smalldatetime] NULL,
CONSTRAINT [PK_MillDifferentials] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MillDifferentials] ADD CONSTRAINT [DF_MillDifferentials_Date] DEFAULT (getdate()) FOR [Date]
GO
ALTER TABLE [dbo].[MillDifferentials] ADD CONSTRAINT [DF_MillDifferentials_Diff] DEFAULT ((0.00)) FOR [Diff]
GO
INSERT dbo.MillDifferentials
VALUES('2014-01-20 00:00:00.000','TH0010',1,2015,'FRI','-0.50','JBloggs','2014-11-11 00:00:00');
INSERT dbo.MillDifferentials
VALUES('2014-04-28 00:00:00.000','TH0010',1,2015,'FRI','2.00','JBloggs','2014-10-06 00:00:00');
INSERT dbo.MillDifferentials
VALUES('2014-10-06 00:00:00.000','TH0010',1,2015,'FRI','1.50','JBloggs','2014-10-06 00:00:00');
Calculation for @tblResults
The @tblResults.MaxPrice is populated with the max combined value of the dbo.MarketPrice.Price + the dbo.MillDifferentials.Diff columns
The @tblResults.MaxPriceDate is populated with the dbo.MarketPrice.[date] from the above MaxPrice calculation
The @tblResults.MinPrice is populated with the min combined value of the dbo.MarketPrice.Price + the dbo.MillDifferentials.Diff columns
The @tblResults.MmiPriceDate is populated with the dbo.MarketPrice.[date] from the above MinPrice calculation
Where the 2 dbo.MarketPrice and dbo.MillDifferentials date fields are NOT equal, the last (chronologically) dbo.MillDifferentials.Diff value should be used (or '0' if no previous value found).
so expected results where @tblResults.Id = 1:
The dbo.MarketPrice.Price value of '2014-10-29' should be combined with the dbo.MillDifferentials.Diff value of '2014-10-06' - this produces the combined Max value of 184.50
The dbo.MarketPrice.Price value of '2014-04-28' should be combined with the dbo.MillDifferentials.Diff value of '2014-04-28'
The dbo.MarketPrice.Price value of '2014-01-22' should be combined with the dbo.MillDifferentials.Diff value of '2014-01-20'
The dbo.MarketPrice.Price value of '2014-01-21' should be combined with the dbo.MillDifferentials.Diff value of '2014-01-20' - this produces the combined Min value of 111.50
The dbo.MarketPrice.Price value of '2014-01-04' should be combined with '0.00' if there is no matching or previous dbo.MillDifferentials.Diff value OR the top 1/max (most recent) dbo.MillDifferentials.Diff value if a record is found before the specified @tblResults.LatestStartDate
Can anyone help with the most efficient method to help me, please?
Thanks in advance,
November 17, 2014 at 1:46 pm
please post the query you have at the moment so we can see if there are opportunities for improvement
Gerald Britton, Pluralsight courses
November 18, 2014 at 5:08 am
I have removed the initial section (which is largely irrelevant here). After the declarations (some variables might be superfluous), this example begins with the pre-loading of the @tblResults table.
In the actual data there are approx:
2440 rows in the @tblResults table
and
up to 365 MarketPrice records for each @tblResults row to read through
around 150 MillDifferentials records for each @tblResults cursor relationship
This section within the stored procedure takes over 90 minutes to complete which I need to reduce if possible.
In the following code, the 2 varaibles @c_BolMax AND @c_BolMin are used to compare the new values when updating @tblResults
DECLARE
@c_BookIdNVARCHAR( 10),
@c_ItemNVARCHAR( 10),
@c_WarehouseNVARCHAR( 10),
@c_MonthSMALLINT,
@c_strMonthNVARCHAR( 2),
@c_YearSMALLINT,
@c_CBStartDateDATETIME,
@c_CBEndDateDATETIME,
@c_ClosedStartDateDATETIME,
@c_ClosedEndDateDATETIME,
@c_MaxPriceDECIMAL( 8, 2),
@c_MinPriceDECIMAL( 8, 2),
@c_DateDATETIME,
@c_PriceDECIMAL( 8, 2),
@c_intBIGINT,--No longer used
@c_PriorDateDATETIME,
@c_strPriorDateNVARCHAR( 8),
@c_BookId1NVARCHAR( 10),
@c_Item1NVARCHAR( 10),
@c_Warehouse1NVARCHAR( 10),
@c_Month1SMALLINT,
@c_Year1SMALLINT,
@c_Date1DATETIME,
@c_Price1DECIMAL( 8, 2),
@c_LatestMDDateDATETIME,
@c_BolMaxBIT,
@c_BolMinBIT,
@c_UpdMaxPriceDECIMAL( 8, 2),
@c_UpdMinPriceDECIMAL( 8, 2)
DECLARE @tblResults TABLE--CustBook entries
(
BookIdNVARCHAR( 10),
ItemNVARCHAR( 10),
WarehouseNVARCHAR( 10),
[Month]SMALLINT,
[Year]SMALLINT,
StartDateDATETIME,
EndDateDATETIME,
PriorStartDateDATETIME,
PriorEndDateDATETIME,
LatestStartDateDATETIME,
MaxPriceDECIMAL( 8,2),
MaxPriceDateDATETIME,
MinPriceDECIMAL( 8,2),
MinPriceDateDATETIME,
FoundTINYINT
)
;
SET @c_MaxPrice = 0;
SET @c_MinPrice = 999999.99; -- 8,2
SET @c_BolMax = 0;
SET @c_BolMin = 0;
INSERT @tblResults
VALUES('AC', 'TH0010', 'FRI', 1, 2015, '2013-10-01 00:00:00.000', '2014-09-30 23:59:59.000', '2014-01-01 00:00:00.000', '2014-12-31 23:59:59.000', '2014-01-01 00:00:00.000', NULL, NULL, NULL, NULL, 0);
INSERT @tblResults
VALUES('AC', 'TH0010', 'FRI', 2, 2015, '2013-10-01 00:00:00.000', '2014-09-30 23:59:59.000', '2014-02-01 00:00:00.000', '2015-01-31 23:59:59.000', '2014-02-01 00:00:00.000', NULL, NULL, NULL, NULL, 0);
INSERT @tblResults
VALUES('AC', 'TH0010', 'FRI', 3, 2015, '2013-10-01 00:00:00.000', '2014-09-30 23:59:59.000', '2014-03-01 00:00:00.000', '2015-02-28 23:59:59.000', '2014-03-01 00:00:00.000', NULL, NULL, NULL, NULL, 0);
-- Cursor Find the Min Max values for the dbo.RMCustBookPurchases_staging records from the combined dbo.MarketPrice and dbo.MillDifferentials tables.
-- dates from dbo.MillDifferentials must be <= dates from dbo.MarketPrice
DECLARE tr2_cursor CURSOR FAST_FORWARD FOR
SELECT DISTINCT tr.BookId, tr.Item, tr.Warehouse, tr.[Month], tr.[Year], tr.LatestStartDate, tr.PriorEndDate, mp.Date, mp.Price
FROM @tblResults tr JOIN dbo.MarketPrice mp
ON tr.Item = mp.Item
AND tr.[Month] = mp.[Month]
AND tr.[Year] = mp.[Year]
AND mp.Date BETWEEN tr.LatestStartDate AND tr.PriorEndDate
ORDER BY tr.BookId, tr.Item, tr.Warehouse, tr.[Month], tr.[Year], mp.[Date] ASC
OPEN tr2_cursor
FETCH NEXT FROM tr2_cursor INTO @c_BookId, @c_Item, @c_Warehouse, @c_Month, @c_Year, @c_CBStartDate, @c_CBEndDate, @c_Date, @c_Price
WHILE @@FETCH_STATUS = 0
BEGIN
--If valid date found
IF (SELECT TOP 1 md1.Date AS LatestDate FROM dbo.MillDifferentials md1
WHERE md1.Item = @c_Item
AND md1.Warehouse = @c_Warehouse
AND md1.[Month] = @c_Month
AND md1.[Year] = @c_Year
AND md1.Date <= @c_Date
ORDER BY md1.Date DESC) IS NOT NULL
BEGIN
SET @c_LatestMDDate = (SELECT TOP 1 md1.Date AS LatestDate FROM dbo.MillDifferentials md1
WHERE md1.Item = @c_Item
AND md1.Warehouse = @c_Warehouse
AND md1.[Month] = @c_Month
AND md1.[Year] = @c_Year
AND md1.Date <= @c_Date
ORDER BY md1.Date DESC)
--If new @tblResults record found
IF (
@c_BookId1 != @c_BookId
OR @c_Item1 != @c_Item
OR @c_Warehouse1 != @c_Warehouse
OR @c_Month1 != @c_Month
OR @c_Year1 != @c_Year)
BEGIN
SET @c_BookId1 = @c_BookId;
SET @c_Item1 = @c_Item;
SET @c_Warehouse1 = @c_Warehouse;
SET @c_Month1 = @c_Month;
SET @c_Year1 = @c_Year;
SET @c_Date1 = @c_Date;
SET @c_BolMax = 0;
SET @c_BolMin = 0;
END
--Find match for MAX set
IF (SELECT TOP 1 MAX(@c_Price + md1.Diff) AS MaxPrice FROM dbo.MillDifferentials md1
WHERE md1.Item = @c_Item
AND md1.Warehouse = @c_Warehouse
AND md1.[Month] = @c_Month
AND md1.[Year] = @c_Year
AND md1.Date = @c_LatestMDDate) IS NOT NULL
BEGIN
--Reset if new @tblResults record
IF @c_BolMax = 0
BEGIN
SET @c_MaxPrice = 0;
SET @c_BolMax = 1;
END
SET @c_Price1 = (SELECT TOP 1 MAX(@c_Price + md1.Diff) AS MaxPrice FROM dbo.MillDifferentials md1
WHERE md1.Item = @c_Item
AND md1.Warehouse = @c_Warehouse
AND md1.[Month] = @c_Month
AND md1.[Year] = @c_Year
AND md1.Date = @c_LatestMDDate)
IF @c_MaxPrice <= @c_Price1
BEGIN
SET@c_UpdMaxPrice = (SELECT TOP 1 MAX(@c_Price + md1.Diff) AS MaxPrice FROM dbo.MillDifferentials md1
WHERE md1.Item = @c_Item
AND md1.Warehouse = @c_Warehouse
AND md1.[Month] = @c_Month
AND md1.[Year] = @c_Year
AND md1.Date = @c_LatestMDDate
)
UPDATE tr
SET MaxPrice = @c_UpdMaxPrice,
MaxPriceDate = @c_Date,
Found = 1
FROM @tblResults tr
WHERE tr.BookId = @c_BookId
AND tr.Item = @c_Item
AND tr.Warehouse = @c_Warehouse
AND tr.[Month] = @c_Month
AND tr.[Year] = @c_Year
SET @c_MaxPrice = @c_Price1
END
END
--Find match for MIN set
IF (SELECT TOP 1 MIN(@c_Price + md1.Diff) AS MinPrice FROM dbo.MillDifferentials md1
WHERE md1.Item = @c_Item
AND md1.Warehouse = @c_Warehouse
AND md1.[Month] = @c_Month
AND md1.[Year] = @c_Year
AND md1.Date = @c_LatestMDDate) IS NOT NULL
BEGIN
--Reset if new @tblResults record
IF @c_BolMin = 0
BEGIN
SET @c_MinPrice = 999999.99;
SET @c_BolMin = 1;
END
SET @c_Price1 = (SELECT TOP 1 MIN(@c_Price + md1.Diff) AS MinPrice FROM dbo.MillDifferentials md1
WHERE md1.Item = @c_Item
AND md1.Warehouse = @c_Warehouse
AND md1.[Month] = @c_Month
AND md1.[Year]= @c_Year
AND md1.Date = @c_LatestMDDate)
IF @c_MinPrice >= @c_Price1
BEGIN
SET@c_UpdMinPrice = (SELECT TOP 1 MIN(@c_Price + md1.Diff) AS MinPrice FROM dbo.MillDifferentials md1
WHERE md1.Item = @c_Item
AND md1.Warehouse = @c_Warehouse
AND md1.[Month] = @c_Month
AND md1.[Year] = @c_Year
AND md1.Date = @c_LatestMDDate
)
UPDATE tr
SET MinPrice = @c_UpdMinPrice,
MinPriceDate = @c_Date,
Found = 1
FROM @tblResults tr
WHERE tr.BookId = @c_BookId
AND tr.Item = @c_Item
AND tr.Warehouse = @c_Warehouse
AND tr.[Month] = @c_Month
AND tr.[Year] = @c_Year
SET @c_MinPrice = @c_Price1
END
END
--Set for evaluation (when checking for new record)
SET @c_BookId1 = @c_BookId
SET @c_Item1 = @c_Item
SET @c_Warehouse1 = @c_Warehouse
SET @c_Month1 = @c_Month
SET @c_Year1 = @c_Year
END
FETCH NEXT FROM tr2_cursor INTO @c_BookId, @c_Item, @c_Warehouse, @c_Month, @c_Year, @c_CBStartDate, @c_CBEndDate, @c_Date, @c_Price
END
CLOSE tr2_cursor
DEALLOCATE tr2_cursor
;
select * from @tblResults
;
I understand that there maybe be other approaches (i.e. maybe a 'while loop' would improve performance). But any coded examples would help.
Thanks in advance,
November 18, 2014 at 6:22 am
That's horrible code. A cursor loop is much the same as a while loop, you won't gain anything by changing your existing loop into a different one. What you really want to do is convert this awful code into something setbased. Without sample data to test against, that's going to be near impossible. Having said that, the principle is straightforward - I think - and an example could set you on the road. Try this query. Look at the result set it generates and compare it to the results of your existing code. You might get lucky and find a quick win.
SELECT
tr.BookId, tr.Item, tr.Warehouse, tr.[Month], tr.[Year], tr.LatestStartDate, tr.PriorEndDate,
mp.Date, mp.Price,
ou.LatestDate, ou.MaxPrice, ou.MinPrice
FROM @tblResults tr
INNER JOIN dbo.MarketPrice mp
ON tr.Item = mp.Item
AND tr.[Month] = mp.[Month]
AND tr.[Year] = mp.[Year]
AND mp.Date BETWEEN tr.LatestStartDate AND tr.PriorEndDate
OUTER APPLY (
SELECT
LatestDate = MAX(md1.Date),
MaxPrice = MAX(md1.Diff),
MinPrice = MIN(md1.Diff)
FROM dbo.MillDifferentials md1
WHERE md1.Item = tr.Item
AND md1.Warehouse = tr.Warehouse
AND md1.[Month] = tr.[Month]
AND md1.[Year] = tr.[Year]
AND md1.Date <= mp.Date
) ou
ORDER BY tr.BookId, tr.Item, tr.Warehouse, tr.[Month], tr.[Year], mp.[Date] ASC
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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply