Reasoning
When comparing the column of the unpartitioned table with the partitioned table, the sql server may not know DayOfMonth It will remain, even if all are 15.
As a result, I would not know what partitions to return to satisfy this filtering by joining the two tables.
A different example to give more information can be found here.
Tests
I was able to recreate your problem, for more questions, add as much information as possible.
This could be table definition, indexes, partition scheme, …
The DDL and DML is at the bottom.
When executing the query we can obtain the same result:
62 partitions returned.
In search predicates, try to filter what you can, these are the two filters in CalendarId they don't have the + DayOfMonth-1.
This translates into
WHERE ...
AND (( dc.CalendarId < 20190701)
OR (dc.CalendarId >= 20270601))
...
When you run the query with these filters, you will see the same number of rows returned when accessing the table in the query plan.
Only after obtaining this data, can you and will join the AccountInfo table in the ID& # 39; s and the DayOfMonth - 1
And to confirm when we change the query:
SELECT
ai.Name
,SUM(dc.Component )
,SUM(dc.Value) Value
FROM
Warm.DailyCosts dc
JOIN Warm.AccountInfo ai
ON dc.AccountInfoId = ai.Id
WHERE
ai.CorrelationId = '00000000-0000-0000-0000-000000000000'
AND ((dc.CalendarId >= (20190601 - 1)
AND dc.CalendarId < 20190701)
OR (dc.CalendarId >= 20270601
AND dc.CalendarId < (20270601 - 1)))
GROUP BY Name
Yes delete the partitions:
Solution
I will assume that DayOfMonth just go up to 31 days.
If you know these limits, you can & # 39; encode them & # 39; so that the sql server knows which partitions to look for. After this, you can add additional filtering.
E.G.
WITH CTE
AS
(
SELECT
ai.Name
,dc.Component
,dc.Value
,dc.CalendarId
,ai.DayOfMonth
FROM
Warm.DailyCosts dc
JOIN Warm.AccountInfo ai
ON dc.AccountInfoId = ai.Id
WHERE
ai.CorrelationId = '00000000-0000-0000-0000-000000000000'
AND ((dc.CalendarId >= (20190601 )
AND dc.CalendarId < 20190701)
OR (dc.CalendarId >= 20270601
AND dc.CalendarId < (20270701)))
)
SELECT Name,Component,SUM(Value)
FROM CTE
WHERE ((CalendarId >= (20190601 + DayOfMonth - 1)
AND CalendarId < 20190701)
OR (CalendarId >= 20270601
AND CalendarId < (20270601 + DayOfMonth - 1)))
GROUP BY
Name
,Component
The sole purpose of the cte is to let the SQL server know that it can also filter dc.CalendarId >= dc.CalendarId >= (20190601 ) OR dc.CalendarId < (20270701)).
Side note: Adding a restriction does not work to enforce this.
This query gives us the result we want, with the correct separation of the partition:
Only access to partitions 19 and 21.
Test data
CREATE SCHEMA WARM
GO
CREATE TABLE Warm.DailyCosts(ID INT IDENTITY(1,1) NOT NULL,
Component int,
Value int,
CalendarId INT,
AccountInfoId int
)
CREATE TABLE Warm.AccountInfo(Id INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
Name VARCHAR(25),
CorrelationId uniqueidentifier,
DayOfMonth int
);
USE (master)
GO
ALTER DATABASE (test) ADD FILEGROUP (Partitionfg)
GO
USE (test)
GO
CREATE PARTITION FUNCTION (PF_CostDateByYearMonth)(int) AS RANGE RIGHT FOR VALUES (
20180101,
20180201,
20180301,
20180401,
20180501,
20180601,
20180701,
20180801,
20180901,
20181001,
20181101,
20181201,
20190101,
20190201,
20190301,
20190401,
20190501,
20190601,
20190701,
20270601,
20270701,
20270801,
20270901,
20271001,
20271101,
20271201,
20280101,
20280201,
20280301,
20280401,
20280501,
20280601,
20280701,
20280801,
20280901,
20281001,
20281101,
20281201,
20290101,
20290201,
20290301,
20290401,
20290501,
20290601,
20290701,
20290801,
20290901,
20291001,
20291101,
20291201,
20300101,
20300201,
20300301,
20300401,
20300501,
20300601,
20300701,
20300801,
20300901,
20301001,
20301101,
20301201)
CREATE PARTITION SCHEME (PS_CostDateByYearMonth)
AS PARTITION (PF_CostDateByYearMonth)
ALL TO ( (Partitionfg) );
CREATE UNIQUE CLUSTERED INDEX ptci ON Warm.DailyCosts(CalendarId,Id) ON (PS_CostDateByYearMonth)(CalendarId) ;
USE (master)
GO
ALTER DATABASE (test) ADD FILE ( NAME = N'TestPartition', FILENAME = N'D:DATATestPartition.ndf' , SIZE = 3072KB , FILEGROWTH = 65536KB ) TO FILEGROUP (Partitionfg)
GO
USE (test)
GO
INSERT INTO Warm.DailyCosts(Component,
Value,
CalendarId,
AccountInfoId
)
SELECT TOP(1000000) --1M
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
20180101 + ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) / 1000 % 1000 ,
ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2
-- 2018
INSERT INTO Warm.DailyCosts(Component,
Value,
CalendarId,
AccountInfoId
)
SELECT TOP(1000000) --1M
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
20190101 + ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) / 1000 % 1000 ,
ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2
-- 2019
INSERT INTO Warm.DailyCosts(Component,
Value,
CalendarId,
AccountInfoId
)
SELECT TOP(1000000) --1M
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
20280101 + ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) / 1000 % 1000 ,
ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2
-- 2028
INSERT INTO Warm.AccountInfo(
Name ,
CorrelationId,
DayOfMonth
)
SELECT TOP(3000000) --3M
CAST(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS varchar(10)) + 'a',
'00000000-0000-0000-0000-000000000000',
15
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2
CREATE NONCLUSTERED INDEX (IX_Component_Value_DailyCosts) ON Warm.DailyCosts
(Component,Value)
ON (PS_CostDateByYearMonth)(CalendarId);
GO
