Full Trust European Hosting

BLOG about Full Trust Hosting and Its Technology - Dedicated to European Windows Hosting Customer

European Entity Framework Hosting - HostForLIFE.eu :: Efficient Data Modification with Entity Framework Core

clock April 24, 2020 06:47 by author Peter

A simple rule says 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. With tools like Entity LINQ we can easily write efficient data modification queries.

Efficient Data Modification With Entity Framework Core 

Let's analyze the possible cases:
    INSERT
    UPDATE (DELETE)
    Upsert (UPDATE or INSERT)

INSERT case optimizations

Insert multiple rows in a single statement. Assuming we have a collection of promotions, the following snippet inserts them in a single query:
    public void InsertBatch(IEnumerable<Promotions> promos) 
    { 
        var query = DbContext.Promotions.Query((Promotions promo) => 
        { 
            var set = promo.@using((promo.PromotionName, promo.Discount, promo.StartDate, promo.ExpiredDate)); 
     
            INSERT().INTO(set); 
            var r = OUTPUT(INSERTED<Promotions>()); 
            VALUES(set.RowsFrom(promos)); 
     
            return r; 
        }); 
     
        foreach (var promo in query) 
            Console.WriteLine((promo.PromotionId, promo.PromotionName, promo.Discount, promo.StartDate, promo.ExpiredDate)); 
    } 


INSERT INTO ... SELECT ...
 
This is a so-called bulk insert. In the case that the data is already in the database, it is much cheaper to avoid data pulling altogether. The operation can potentially be performed inside the database. The following snippet copies addresses from one table to another according to some criteria without a single byte to leave the database.
    var cities = new[] { "Santa Cruz", "Baldwin" }; 
     
    DbContext.Database.Query((Stores stores, Addresses address) => 
    { 
        var set = address.@using((address.Street, address.City, address.State, address.ZipCode)); 
     
        INSERT().INTO(set); 
        SELECT((stores.Street, stores.City, stores.State, stores.ZipCode)); 
        FROM(stores); 
        WHERE(cities.Contains(stores.City)); 
    }); 


UPDATE case optimizations

A bulk update works when there is a need to update multiple rows in the same table. There is a special SQL construct for this case - UPDATE ... WHERE, which performs the update in a single query. Some databases, like SQL server, also support a more powerful UPDATE ... JOIN construct. Below we update all the tax configurations without pulling them to the application server:
    var one = 0.01M; 
    var two = 0.02M; 
    DbContext.Database.Query((Taxes taxes) => 
    { 
        UPDATE(taxes).SET(() => { 
                taxes.MaxLocalTaxRate += two; 
                taxes.AvgLocalTaxRate += one; 
            }); 
        WHERE(taxes.MaxLocalTaxRate == one); 
    }); 


Bulk delete, same idea for the delete case.

    private static void PrepareProductHistory(Products products) 
    { 
        var productHistory = ToTable<Products>(PRODUCT_HISTORY); 
     
        SELECT(products).INTO(productHistory); 
        FROM(products); 
     
        Semicolon(); 
    } 
     
    ... 
     
    var year = 2017; 
    DbContext.Database.Query((Products products) => 
    { 
        PrepareProductHistory(products); 
        var productHistory = ToTable<Products>(PRODUCT_HISTORY); 
     
        DELETE().FROM(productHistory); 
        WHERE(productHistory.ModelYear == year); 
    }); 


UPSERT optimization
Efficient Data Modification With Entity Framework Core
 
Upsert means UPDATE or INSERT in a single statement. In cases when a table has more than 1 unique constraint (in addition to PK), plain INSERT can fail on duplicate key. In those cases, we usually want to ignore, replace or combine some existing fields with new values. Most vendors support this capability, but using different syntax and providing different features.
 
MERGE
SQL Server and Oracle. In fact, this is the official standard. In its simplest form, it allows for the specification of what to do WHEN MATCHED, i.e. when there is a unique key collision, and what to do WHEN NOT MATCHED, i.e. we can INSERT safely.

    DbContext.Category.Query((Category category) =>   
    {   
        var staging = ToTable<Category>(CATEGORY_STAGING);   
       
        MERGE().INTO(category).USING(staging).ON(category == staging);   
       
        WHEN_MATCHED().THEN(MERGE_UPDATE().SET(() =>   
                {   
                    category.CategoryName = staging.CategoryName;   
                    category.Amount = staging.Amount;   
                }));   
       
        var set = category.@using((category.CategoryId, category.CategoryName, category.Amount));   
        WHEN_NOT_MATCHED().THEN(MERGE_INSERT(set.ColumnNames(), VALUES(set.RowFrom(staging))));   
       
        Semicolon();   
       
        return SelectAll(category);   
    });    

As the picture says, it's trickier than it should be. There are many tutorials and official documentation.
 
INSERT ... ON DUPLICATE ... - MySQL and Postgres. The syntax is much simpler and allows us to handle the most common case only (compared to feature-packed MERGE):

    // There is a store which might already exist in the database. 
    // Should we add it or update? (PK is not always the only UNIQUE KEY) 
    newOrExisting.LastUpdate = DateTime.Now; 
     
    DbContext.Database.Query((Store store) => 
    { 
        var view = store.@using((store.StoreId, store.AddressId, store.ManagerStaffId, store.LastUpdate)); 
        INSERT().INTO(view); 
        VALUES(view.RowFrom(newOrExisting)); 
        ON_DUPLICATE_KEY_UPDATE(() => store.LastUpdate = INSERTED_VALUES(store.LastUpdate)); 
    }); 

Batch/bulk updates are usually in the order of a magnitude faster than working with entities one by one. Optimizing those scenarios is usually an easy improvement.



SQL Server 2016 Hosting - HostForLIFE.eu :: Subqueries And Correlated Subqueries

clock April 15, 2020 09:53 by author Peter

Subqueries In SQL Server
Subqueries are enclosed in parentheses. Subquery is also called an inner query and the query which encloses that inner query is called an outer query. Many times subqueries can be replaced with joins.
select * from Employee where DepartmentID not in (select distinct DepartmentID from Department) 

Another example:
select Department_Name,(select count(*) from Employee where DepartmentID=d.DepartmentID) from Department as d; 

The above query is an example of using subquery in the select list. The above result can be achieved using join also; see the below query
select d.Department_Name,COUNT(e.empid) as empcount from Department d 
join Employee e on e.DepartmentID=d.DepartmentID 
group by d.Department_Name 
order by empcount; 


According to MSDN, you can nest up to 32 levels.

Columns present in subqueries cannot be used in the outer select list of a query.

Correlated Subqueries

If our subquery depends on the outer query for its value then it is called a Correlated Subquery. It means subquery depends on outer subquery. Correlated subqueries are executed for every single row executed by outer subqueries.

A correlated subquery can be executed independently,
select distinct Department_Name,(select count(*) from Employee where DepartmentID=d.DepartmentID group by DepartmentID) as empcount from Department as d order by empcount; 

What to choose for performance --  Subquery or Join?
According to MSDN, there is no big difference between queries that use sub-queries and joins.

But in some cases, we need to check the performance, and Join produces better performance because the nested query must be processed for each result of the outer query. In such cases, JOIN will perform better.

In general, JOIN works faster as compared to subqueries but in reality, it will depend on the execution plan generated by the SQL Server. If the SQL server generates the same execution plan then you will get the same result.



Magento Hosting - HostForLIFE.eu :: How to Getting A Products URL on Magento?

clock April 3, 2020 08:14 by author Peter

In this post, I will show you how to get a products URL on Magento. There is 3 methods you can use, all of which are in Mage_Catalog_Model_Product. And here is the code:
public function getUrlPath($category=null)
public function getUrlInStore($params = array())
public function getProductUrl($useSid = null)

The simplest way to explain is to just show the results of many calls. Given a product whose URL key is scott-large-coffee-table-set-multicolour upon the domain of http :// created. local the results are :
$product->getUrlPath();
    'scott-large-coffee-table-set-multicolour'
$product->getUrlPath($category);
    'tables/scott-large-coffee-table-set-multicolour'
// you cannot stop this method adding ___store to the URL, even by setting _store_to_url to false
$product->getUrlInStore();
    'http://made.local/tables/scott-large-coffee-table-set-multicolour?___store=default'
// you cannot stop this method adding ___store to the URL, even by setting _store_to_url to false
// note - see the "using _ignore_category" section below for an arguable bug with using this param
$product->getUrlInStore(array('_ignore_category' => true));
    'http://made.local/scott-large-coffee-table-set-multicolour?___store=default'
$product->getProductUrl();
    'http://made.local/tables/scott-large-coffee-table-set-multicolour'
$product->getProductUrl(true);
    'http://made.local/tables/scott-large-coffee-table-set-multicolour'


To discover what some other params could be passed to getUrlInStore (), notice URL Route Parameters. Using _ignore_category. The brief version, I wouldn't use this param, and rather use Mage: getUrl ($product-getUrlPath ())

In case you first fetch a products URL that contains the category, then use a similar product instance to commit to fetch a non-category URL, you'll rather both times obtain a URL that includes the category, begin to see the below code :
$product = Mage::getModel('catalog/product');
$product->getUrlInStore();
    'http://made.local/sofas/peter-2-seater-sofa-blue?___store=default'
$product->getUrlInStore(array('_ignore_category' => true));
    'http://made.local/sofas/peter-2-seater-sofa-blue?___store=default'
$product = Mage::getModel('catalog/product');
$product->getUrlInStore(array('_ignore_category' => true));
    'http://made.local/peter-2-seater-sofa-blue?___store=default'

The problem lies using the request_path key upon the $product model, that the Mage_Catalog_Model_Product_Url : : getUrl () sets, to become a cached worth for an otherwise intensive method of resolving a URL rewrite to an item inside a category.

To solve this, unset request_path first, as beneath :
$product->unsRequestPath();
$product->getUrlInStore(array('_ignore_category' => true));
    'http://made.local/peter-2-seater-sofa-blue?___store=default'

Note which any technique outlined in the top of the card that leads to the category to be present inside the returned URL can have a similar effect of caching the category.

 



About HostForLIFE.eu

HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

We have offered the latest Windows 2016 Hosting, ASP.NET Core 2.2.1 Hosting, ASP.NET MVC 6 Hosting and SQL 2017 Hosting.


Tag cloud

Sign in