September 13, 2018 at 2:50 am
Hi
I have a table with the following structure:
CREATE TABLE [dbo].[SellerFeedbacks](
[IdSeller] [varchar](50) NOT NULL,
[Date] Date NOT NULL,
[Feedback30Count] [int] NOT NULL,
[Feedback30Positive] [int] NOT NULL,
[Feedback90Count] [int] NOT NULL,
[Feedback90Positive] [int] NOT NULL,
[Feedback365Count] [int] NOT NULL,
[Feedback365Positive] [int] NOT NULL,
CONSTRAINT [PK_SellerFeedbacks] PRIMARY KEY CLUSTERED
(
[IdSeller] ASC,
[Date] ASC
)
Every month this table gets over 7.000.000 rows.
There's a query that gets the 30 day feedback from today, a month ago, two months ago, ....
But it's a RBAR and each query is executed once:
DECLARE @Date DATE = GETDATE();
/* use a cursor to get seller id from certain conditions
and iterate the cursor */
SELECT TOP 1 @Feedback30Count_1 = Feedback30Count FROM SellerFeedbacks (NOLOCK) WHERE IdSeller = @IdSeller AND Date < DATEADD(month, -1, @Date) ORDER BY Date DESC;
SELECT TOP 1 @Feedback30Count_2 = Feedback30Count FROM SellerFeedbacks (NOLOCK) WHERE IdSeller = @IdSeller AND Date < DATEADD(month, -2, @Date) ORDER BY Date DESC;
Is there a way of putting those two queries in just one?
I'll try to get rid of the RBAR later...
Thanks,
Pedro
PS: I can't edit the table structure since it's being used by an ETL external process that another department made. Just add indexes...
September 13, 2018 at 6:12 am
Couldn't you just UNION the two together?
"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
September 13, 2018 at 6:23 am
Grant Fritchey - Thursday, September 13, 2018 6:12 AMCouldn't you just UNION the two together?
Yes but that will read the table twice, won't it?
I thought of making a ROW_NUMBER() with a PARTITION on the year/month of date and getting the first row but that would also probably read the entire table (340.000.000 rows...)
The TOP 1 with IdSeller and Date < .. is quite fast for a single record but it uses RBAR, for 300.000 rows, which is very slow, but removing the cycle with TOP and ORDER Date DESC isn't simple (if even possible...)
September 13, 2018 at 6:59 am
PiMané - Thursday, September 13, 2018 2:50 AMHiI have a table with the following structure:
CREATE TABLE [dbo].[SellerFeedbacks]([IdSeller] [varchar](50) NOT NULL,
[Date] Date NOT NULL,
[Feedback30Count] [int] NOT NULL,
[Feedback30Positive] [int] NOT NULL,
[Feedback90Count] [int] NOT NULL,
[Feedback90Positive] [int] NOT NULL,
[Feedback365Count] [int] NOT NULL,
[Feedback365Positive] [int] NOT NULL,
CONSTRAINT [PK_SellerFeedbacks] PRIMARY KEY CLUSTERED
(
[IdSeller] ASC,
[Date] ASC
)
Every month this table gets over 7.000.000 rows.
There's a query that gets the 30 day feedback from today, a month ago, two months ago, ....
But it's a RBAR and each query is executed once:
DECLARE @Date DATE = GETDATE();
/* use a cursor to get seller id from certain conditions
and iterate the cursor */SELECT TOP 1 @Feedback30Count_1 = Feedback30Count FROM SellerFeedbacks (NOLOCK) WHERE IdSeller = @IdSeller AND Date < DATEADD(month, -1, @Date) ORDER BY Date DESC;
SELECT TOP 1 @Feedback30Count_2 = Feedback30Count FROM SellerFeedbacks (NOLOCK) WHERE IdSeller = @IdSeller AND Date < DATEADD(month, -2, @Date) ORDER BY Date DESC;
Is there a way of putting those two queries in just one?
I'll try to get rid of the RBAR later...Thanks,
PedroPS: I can't edit the table structure since it's being used by an ETL external process that another department made. Just add indexes...
If you create an index this might be a faster way of retrieving all data for all IsSellers
CREATE INDEX IX_SellerFeedbacks_IdSeller_Date ON SellerFeedbacks(IdSeller,Date) INCLUDE (Feedback30Count)
GO
;WITH CTE AS
(
SELECT DISTINCT IdSeller
FROM SellerFeedbacks
)
SELECT CTE.IdSeller,[1Month].Feedback30Count1Month, [2Month].Feedback30Count2Month
FROM CTE
OUTER APPLY(SELECT TOP(1) Feedback30Count Feedback30Count1Month
FROM SellerFeedbacks
WHERE IdSeller = @IdSeller
AND Date < DATEADD(month, -1, @Date)
ORDER BY Date DESC) [1Month]
OUTER APPLY(SELECT TOP(1) Feedback30Count Feedback30Count2Month
FROM SellerFeedbacks
WHERE IdSeller = @IdSeller
AND Date < DATEADD(month, -2, @Date)
ORDER BY Date DESC) [2Month]
September 13, 2018 at 7:38 am
Jonathan AC Roberts - Thursday, September 13, 2018 6:59 AMPiMané - Thursday, September 13, 2018 2:50 AMHiI have a table with the following structure:
CREATE TABLE [dbo].[SellerFeedbacks]([IdSeller] [varchar](50) NOT NULL,
[Date] Date NOT NULL,
[Feedback30Count] [int] NOT NULL,
[Feedback30Positive] [int] NOT NULL,
[Feedback90Count] [int] NOT NULL,
[Feedback90Positive] [int] NOT NULL,
[Feedback365Count] [int] NOT NULL,
[Feedback365Positive] [int] NOT NULL,
CONSTRAINT [PK_SellerFeedbacks] PRIMARY KEY CLUSTERED
(
[IdSeller] ASC,
[Date] ASC
)
Every month this table gets over 7.000.000 rows.
There's a query that gets the 30 day feedback from today, a month ago, two months ago, ....
But it's a RBAR and each query is executed once:
DECLARE @Date DATE = GETDATE();
/* use a cursor to get seller id from certain conditions
and iterate the cursor */SELECT TOP 1 @Feedback30Count_1 = Feedback30Count FROM SellerFeedbacks (NOLOCK) WHERE IdSeller = @IdSeller AND Date < DATEADD(month, -1, @Date) ORDER BY Date DESC;
SELECT TOP 1 @Feedback30Count_2 = Feedback30Count FROM SellerFeedbacks (NOLOCK) WHERE IdSeller = @IdSeller AND Date < DATEADD(month, -2, @Date) ORDER BY Date DESC;
Is there a way of putting those two queries in just one?
I'll try to get rid of the RBAR later...Thanks,
PedroPS: I can't edit the table structure since it's being used by an ETL external process that another department made. Just add indexes...
If you create an index this might be a faster way of retrieving all data for all IsSellers
CREATE INDEX IX_SellerFeedbacks_IdSeller_Date ON SellerFeedbacks(IdSeller,Date) INCLUDE (Feedback30Count)
GO
;WITH CTE AS
(
SELECT DISTINCT IdSeller
FROM SellerFeedbacks
)
SELECT CTE.IdSeller,[1Month].Feedback30Count1Month, [2Month].Feedback30Count2Month
FROM CTE
OUTER APPLY(SELECT TOP(1) Feedback30Count Feedback30Count1Month
FROM SellerFeedbacks
WHERE IdSeller = @IdSeller
AND Date < DATEADD(month, -1, @Date)
ORDER BY Date DESC) [1Month]
OUTER APPLY(SELECT TOP(1) Feedback30Count Feedback30Count2Month
FROM SellerFeedbacks
WHERE IdSeller = @IdSeller
AND Date < DATEADD(month, -2, @Date)
ORDER BY Date DESC) [2Month]
With a small change I managed to remove the RBAR.. Thanks a lot!!! 😀
September 13, 2018 at 7:51 am
PiMané - Thursday, September 13, 2018 7:38 AMJonathan AC Roberts - Thursday, September 13, 2018 6:59 AMPiMané - Thursday, September 13, 2018 2:50 AMHiI have a table with the following structure:
CREATE TABLE [dbo].[SellerFeedbacks]([IdSeller] [varchar](50) NOT NULL,
[Date] Date NOT NULL,
[Feedback30Count] [int] NOT NULL,
[Feedback30Positive] [int] NOT NULL,
[Feedback90Count] [int] NOT NULL,
[Feedback90Positive] [int] NOT NULL,
[Feedback365Count] [int] NOT NULL,
[Feedback365Positive] [int] NOT NULL,
CONSTRAINT [PK_SellerFeedbacks] PRIMARY KEY CLUSTERED
(
[IdSeller] ASC,
[Date] ASC
)
Every month this table gets over 7.000.000 rows.
There's a query that gets the 30 day feedback from today, a month ago, two months ago, ....
But it's a RBAR and each query is executed once:
DECLARE @Date DATE = GETDATE();
/* use a cursor to get seller id from certain conditions
and iterate the cursor */SELECT TOP 1 @Feedback30Count_1 = Feedback30Count FROM SellerFeedbacks (NOLOCK) WHERE IdSeller = @IdSeller AND Date < DATEADD(month, -1, @Date) ORDER BY Date DESC;
SELECT TOP 1 @Feedback30Count_2 = Feedback30Count FROM SellerFeedbacks (NOLOCK) WHERE IdSeller = @IdSeller AND Date < DATEADD(month, -2, @Date) ORDER BY Date DESC;
Is there a way of putting those two queries in just one?
I'll try to get rid of the RBAR later...Thanks,
PedroPS: I can't edit the table structure since it's being used by an ETL external process that another department made. Just add indexes...
If you create an index this might be a faster way of retrieving all data for all IsSellers
CREATE INDEX IX_SellerFeedbacks_IdSeller_Date ON SellerFeedbacks(IdSeller,Date) INCLUDE (Feedback30Count)
GO
;WITH CTE AS
(
SELECT DISTINCT IdSeller
FROM SellerFeedbacks
)
SELECT CTE.IdSeller,[1Month].Feedback30Count1Month, [2Month].Feedback30Count2Month
FROM CTE
OUTER APPLY(SELECT TOP(1) Feedback30Count Feedback30Count1Month
FROM SellerFeedbacks
WHERE IdSeller = @IdSeller
AND Date < DATEADD(month, -1, @Date)
ORDER BY Date DESC) [1Month]
OUTER APPLY(SELECT TOP(1) Feedback30Count Feedback30Count2Month
FROM SellerFeedbacks
WHERE IdSeller = @IdSeller
AND Date < DATEADD(month, -2, @Date)
ORDER BY Date DESC) [2Month]With a small change I managed to remove the RBAR.. Thanks a lot!!! 😀
What was your small change?
September 13, 2018 at 7:55 am
Jonathan AC Roberts - Thursday, September 13, 2018 7:51 AMPiMané - Thursday, September 13, 2018 7:38 AMJonathan AC Roberts - Thursday, September 13, 2018 6:59 AMPiMané - Thursday, September 13, 2018 2:50 AMHiI have a table with the following structure:
CREATE TABLE [dbo].[SellerFeedbacks]([IdSeller] [varchar](50) NOT NULL,
[Date] Date NOT NULL,
[Feedback30Count] [int] NOT NULL,
[Feedback30Positive] [int] NOT NULL,
[Feedback90Count] [int] NOT NULL,
[Feedback90Positive] [int] NOT NULL,
[Feedback365Count] [int] NOT NULL,
[Feedback365Positive] [int] NOT NULL,
CONSTRAINT [PK_SellerFeedbacks] PRIMARY KEY CLUSTERED
(
[IdSeller] ASC,
[Date] ASC
)
Every month this table gets over 7.000.000 rows.
There's a query that gets the 30 day feedback from today, a month ago, two months ago, ....
But it's a RBAR and each query is executed once:
DECLARE @Date DATE = GETDATE();
/* use a cursor to get seller id from certain conditions
and iterate the cursor */SELECT TOP 1 @Feedback30Count_1 = Feedback30Count FROM SellerFeedbacks (NOLOCK) WHERE IdSeller = @IdSeller AND Date < DATEADD(month, -1, @Date) ORDER BY Date DESC;
SELECT TOP 1 @Feedback30Count_2 = Feedback30Count FROM SellerFeedbacks (NOLOCK) WHERE IdSeller = @IdSeller AND Date < DATEADD(month, -2, @Date) ORDER BY Date DESC;
Is there a way of putting those two queries in just one?
I'll try to get rid of the RBAR later...Thanks,
PedroPS: I can't edit the table structure since it's being used by an ETL external process that another department made. Just add indexes...
If you create an index this might be a faster way of retrieving all data for all IsSellers
CREATE INDEX IX_SellerFeedbacks_IdSeller_Date ON SellerFeedbacks(IdSeller,Date) INCLUDE (Feedback30Count)
GO
;WITH CTE AS
(
SELECT DISTINCT IdSeller
FROM SellerFeedbacks
)
SELECT CTE.IdSeller,[1Month].Feedback30Count1Month, [2Month].Feedback30Count2Month
FROM CTE
OUTER APPLY(SELECT TOP(1) Feedback30Count Feedback30Count1Month
FROM SellerFeedbacks
WHERE IdSeller = @IdSeller
AND Date < DATEADD(month, -1, @Date)
ORDER BY Date DESC) [1Month]
OUTER APPLY(SELECT TOP(1) Feedback30Count Feedback30Count2Month
FROM SellerFeedbacks
WHERE IdSeller = @IdSeller
AND Date < DATEADD(month, -2, @Date)
ORDER BY Date DESC) [2Month]With a small change I managed to remove the RBAR.. Thanks a lot!!! 😀
What was your small change?
Where you have WHERE IdSeller = @IdSeller I replaced with WHERE IdSeller = CTE.IdSeller
My concern is that the OUTER APPLY might behave like a scalar function .
September 13, 2018 at 8:02 am
PiMané - Thursday, September 13, 2018 7:55 AMJonathan AC Roberts - Thursday, September 13, 2018 7:51 AMPiMané - Thursday, September 13, 2018 7:38 AMJonathan AC Roberts - Thursday, September 13, 2018 6:59 AMPiMané - Thursday, September 13, 2018 2:50 AMHiI have a table with the following structure:
CREATE TABLE [dbo].[SellerFeedbacks]([IdSeller] [varchar](50) NOT NULL,
[Date] Date NOT NULL,
[Feedback30Count] [int] NOT NULL,
[Feedback30Positive] [int] NOT NULL,
[Feedback90Count] [int] NOT NULL,
[Feedback90Positive] [int] NOT NULL,
[Feedback365Count] [int] NOT NULL,
[Feedback365Positive] [int] NOT NULL,
CONSTRAINT [PK_SellerFeedbacks] PRIMARY KEY CLUSTERED
(
[IdSeller] ASC,
[Date] ASC
)
Every month this table gets over 7.000.000 rows.
There's a query that gets the 30 day feedback from today, a month ago, two months ago, ....
But it's a RBAR and each query is executed once:
DECLARE @Date DATE = GETDATE();
/* use a cursor to get seller id from certain conditions
and iterate the cursor */SELECT TOP 1 @Feedback30Count_1 = Feedback30Count FROM SellerFeedbacks (NOLOCK) WHERE IdSeller = @IdSeller AND Date < DATEADD(month, -1, @Date) ORDER BY Date DESC;
SELECT TOP 1 @Feedback30Count_2 = Feedback30Count FROM SellerFeedbacks (NOLOCK) WHERE IdSeller = @IdSeller AND Date < DATEADD(month, -2, @Date) ORDER BY Date DESC;
Is there a way of putting those two queries in just one?
I'll try to get rid of the RBAR later...Thanks,
PedroPS: I can't edit the table structure since it's being used by an ETL external process that another department made. Just add indexes...
If you create an index this might be a faster way of retrieving all data for all IsSellers
CREATE INDEX IX_SellerFeedbacks_IdSeller_Date ON SellerFeedbacks(IdSeller,Date) INCLUDE (Feedback30Count)
GO
;WITH CTE AS
(
SELECT DISTINCT IdSeller
FROM SellerFeedbacks
)
SELECT CTE.IdSeller,[1Month].Feedback30Count1Month, [2Month].Feedback30Count2Month
FROM CTE
OUTER APPLY(SELECT TOP(1) Feedback30Count Feedback30Count1Month
FROM SellerFeedbacks
WHERE IdSeller = @IdSeller
AND Date < DATEADD(month, -1, @Date)
ORDER BY Date DESC) [1Month]
OUTER APPLY(SELECT TOP(1) Feedback30Count Feedback30Count2Month
FROM SellerFeedbacks
WHERE IdSeller = @IdSeller
AND Date < DATEADD(month, -2, @Date)
ORDER BY Date DESC) [2Month]With a small change I managed to remove the RBAR.. Thanks a lot!!! 😀
What was your small change?
Where you have WHERE IdSeller = @IdSeller I replaced with WHERE IdSeller = CTE.IdSeller
My concern is that the OUTER APPLY might behave like a scalar function .
Oh yes, well spotted, I thought I'd put that.
OUTER APPLY behaves much like a left join. If you put the index on I said and have a look at the execution plan you will see it does a seek to get the value.
September 13, 2018 at 8:05 am
Will do thank 🙂
The problem is that right now I can't test it since an index on this table takes almost 1h to create... It has 340.000.000 rows and it's a "low" azure instance.
September 13, 2018 at 8:12 am
PiMané - Thursday, September 13, 2018 8:05 AMWill do thank 🙂
The problem is that right now I can't test it since an index on this table takes almost 1h to create... It has 340.000.000 rows and it's a "low" azure instance.
You'll get a big performance improvement from the index, so it's worth trying to add it.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply