Optimize LINQ queries on SQL data provider

Let’s take a look at how a query execution works for the SQL data provider.

public IProduct GetProductById(Guid productId)
{
	using (DataConnection connection = new DataConnection())
	{
		IQueryable<IProduct> products = connection.Get<IProduct>();

		return (from product in products
			where product.Id == productId
			select product).FirstOrDefault();
	}
}

Execution time:

1. Compiling query: ~ 7ms
2. SqlServer round-time-trip: ~ 5-10 ms (may be slower depending on where it is hosted)
3. Parsing & execution: ~ 2 ms.

And we have 15-20 ms for one query. So, if we’re up to achieving a perfect response time (20 ms) with ordinary requests, we cannot afford a single LINQ statement to be executed.

If we compare that result (15 ms) with the one we have for XmlDataProvider in the section above (<1ms), it may seem that using the SQL data provider does not make any sense at all. In order to fix this disadvantage, in C1 CMS you can use cache on data types. If data interface is marked as “cachable”, сonnection.Get<…>() will return not a LINQ2SQL query, but an in-memory copy of the table (represented by System.Linq.EnumerableQuery<T> class). And, if this is the case, the same rules as for the XML data provider are applied.

The optimized code will be looking like:

public IProduct GetProductById(Guid productId)
{
	using (DataConnection connection = new DataConnection())
	{
		// Interface IProduct is marked as cacheable
		IEnumerable<IProduct> products = connection.Get<IProduct>();

		return (from product in products
			where product.Id == productId
			select product).FirstOrDefault();
	}
}

As you can see, IEnumerable<> is used instead of IQueryable<> (Tip 1) in order to avoid wasting time on query compilation.

TIP 3: For data types without a lot of data (<10.000 rows) it makes sense to check the "Has caching" checkbox in the type editor, which will have the system keep the data rows in memory, thus making it unnecessary to round-trip to a SQL server.

As all basic queries are optimized, you may consider caching the results of LINQ queries, as this approach produces the best results.

Here's an example of using the custom QueryCache class, which allows creating "data items by property's value" cache.

public class Products
{
	private static readonly QueryCache<Product, Guid> _productById = new QueryCache<Product, Guid>("Product by id", p => p.Id, 10000);

	public static Product GetProductById(Guid productId)
	{
		return _productById[productId];
	}
}

In this way the actual data query will be executed only the first time, other calls to _productById[productId] will lead to just a hash table look-up performance-wise. Once data is modified, QueryCache automatically clears the related cached rows.

TIP 4: For simple queries, use QueryCache class, and try to break up complex queries into simpler "cacheable" parts.

You can also use C1 CMS’s built-in page rendering profiler. Please read more in "Identifying Slow Functions".