Entity Framework - High Performance Querying Using Temp Tables

-

Today I came across a nice article explaining how we can optimize a query with multiple Include() of related entities. Usage of a temporary is nice, but the way it's created is not. There is absolutely no need to bring the Ids to the app server and then push them back to the database. The whole operation can be performed in the database using SELECT ... INTO construct:

static void PrepareStagingCategories(Product product) {
    var temp = ToTable<Product>(TEMP_PRODUCT_IDS);

    // create  temporary table with product ids
    SELECT<Product>(product.ProductId.@as()).INTO(temp);
    FROM(product);
    WHERE(product.Name.Contains("Chocolate"));
}

Comments

Post a Comment

Popular posts from this blog

Hierarchical Query in SQL with C# and Entity Framework Core

-
Image
Hierarchies are very common in our lives and therefore in the problems we need to solve. The most familiar cases are organizations, products built from parts and other products, file systems etc. When an hierarchy is stored in a database table, it's usually represented by rows referencing parent rows, recursively. Suppose we want to query all the employees (directly or indirectly) reporting to a mid-level manager. Can we do that in SQL? Of course there is a naive solution to select employees directly reporting to that manager, take their ids, perform the query again, etc. But clearly it's not efficient. Fortunately SQL has a standard support for hierarchical queries in a single and efficient query. The following example demonstrates an hierarchical query using C# and EF Core using ELINQ : int ? managerId = null ; //external parameter - "highest" manager id DbContext.Staffs.Query(() =>
Read more

Efficient Data Modification With Entity Framework Core

-
Image
The rule of dumb tells that for optimal performance we need to make as few database requests as possible. This is especially relevant for insert and update scenarios, where we sometimes need to work with thousands of objects. Sending those objects to the database one by one is usually significantly slower than in a batch. (Spoiler - interactive examples of what's discussed below). The number of possible cases is pretty small, but our options and best approach might differ significantly. So, the cases: INSERT UPDATE ( DELETE ) Upsert ( UPDATE or INSERT ) INSERT For this case we have 2 possible optimizations Insert multiple rows in a single statement ( batch insert ) INSERT INTO ... SELECT ... construct, which inserts data from another table ( bulk insert ) UPDATE UPDATE ... WHERE / UPDATE JOIN - so called bulk update . Pay attention, this is not the conventional update where we want one value to be repla
Read more