7
I am working in a database that has the following feature, 90% a clustered index that integrates two columns.: FilialID
and DataCriacao
.
These tables are partitioned by FilialID
, the simple creation or removal of a Filial
causes the re-creation of these partitions.
At system level, FilialID
is unique and can be obtained globally, so I need to add a filtro
standard in all queries, where all entities that own the property FilialID
will be filtered by him.
for example, if I do the following query.:
var entity = db.EntitiesA
.Include(x => x.EntitiesB)
.Include(x => c.EntitiesC)
.Find(x => x.EntityAID = id);
should generate a similar consultation to the following.:
SELECT *
FROM EntitiesA A
JOIN EntitiesB B ON A.EntityAID = B.EntityAID
JOIN EntitiesB C ON A.EntityAID = C.EntityAID
WHERE
A.EntityAID = @id AND
A.FilialID = @filialId AND
B.FilialID = @filialId AND
C.FilialID = @filialId
MOTIVATION
Queries
Queries that use Partition elimination could have comparable or improved performance with Larger number of Partitions. Queries that do not use Partition elimination could take longer to execute as the number of Partitions increases.
For example, assume a table has 100 Million Rows and Columns A, B, and C. In scenario 1, the table is divided into 1000 Partitions on column A. In scenario 2, the table is divided into 10,000 Partitions on column a. A query on the table that has a WHERE clause Filtering on column A will perform Partition elimination and scan one Partition. That same query may run Faster in scenario 2 as there are Fewer Rows to scan in a Partition. A query that has a WHERE clause Filtering on column B will scan all Partitions. The query may run Faster in scenario 1 than in scenario 2 as there are Fewer Partitions to scan.
Queries que use Operators such as TOP or MAX/MIN on Columns other than the partitioning column may Experience Reduced performance with partitioning because all Partitions must be evaluated.
The result of the above consultation will be the same if I inform the FilialID
or omit it, but when informing the same, the partitions referring to the other Filiais
will be ignored, thus avoiding locks
unnecessary and improving consultation performance.
EDIT
Attempt using the Query Filter
of EntityFramework-Plus
, but the same does not work with Includes.
public MyContext()
{
this.Filter<EntityA>(set => set.Where(entity => entity.FilialID == Global.FilialID));
this.Filter<EntityB>(set => set.Where(entity => entity.FilialID == Global.FilialID));
this.Filter<EntityC>(set => set.Where(entity => entity.FilialID == Global.FilialID));
}
Note that this partition advantage is only true for the first table in the query. In your first example, it is only advantageous to have the
FilialID
in the parent table, ie,EntitiesA
. As theEntitiesB
andEntitiesC
are already "tied" in theEntitiesA
probably by a Foreign key, add the fieldFilialID
in these two is not only redundant, it does not help at all in the performance (incidentally, it can cause an extra check that is unnecessary). But for the first one it is indeed interesting yes, or if you have to doJOIN
with a table that doesn’t have FK with the others, then it would also make sense.– Alisson
Now returning to the question, your intention is to automatically add a
WHERE()
in the query (maybe inside theDbContext
) to filter this fieldFiltroID
(provided thatGlobal.FilialID
have some value filled), without needing to warm up with this in the rest of the system, is this?– Alisson
Btw my first statement is not valid if you can have the same Entitya Id for different branches, ie if the table is double key.
– Alisson