Any ORM-framework is an additional tier between application and database server that leads to the performance decreasing in comparison with native SQL statement execution. In LinqConnect a set of the solutions for performance increasing was implemented. We will consider these methods in this article.

We can mark out two ways of performance increasing:

1. Data fetching speed increasing
2. Data editing speed increasing

We consider the following approaches of the data fetching performance increasing in this article:

• Object Identity and Object Caching.

  Object Identity is the way of the object identifier caching. Every time when some row is fetched from the database, it is cached in the special table with the help of its primary key. This approach saves time and resources for LINQ query preparation and execution. Object Caching is used with Object Identity approach. In this case when new row has been fetched from the database, it is saved to the special hash-table. This record can be identified with the help of the primary key. If we want to query this record next time, object will be loaded from the hash-table.

• Disabling Change Tracking

  Change Tracking is a mechanism that keeps information about all changes that were made in DataContext. Apparently, Change Tracking leads to some performance loss when querying data, as a copy of each object should be created. Setting the DataContext.ObjectTrackingEnabled property to false allows disabling such behaviour, increasing speed of data fetch. However, this makes a data context read-only, and if you do want to perform update operations too, it is necessary to create another data context for this.

• Compiled Queries

  This functionality copies a common query caching technology from the Microsoft LINQ to SQL. It allows you to avoid compiling the same query several times. It decreases resource costs for repeated LINQ-query compilation.

• Query Caching

  This technology allows you to work with query cache in the automatic mode. Query is added to the cache automatically after first call. This approach decreases resource costs for the repeated query compilation too.

• Fetching Techniques

  In this section the approaches of data fetching optimizing are considered.

You can increase speed of data update with the help of the following approaches:

• Disabling Concurrency Check

  Concurrency Check approach allows to you to resolve the conflicts raised after the data update. If this option is turned off then we can avoid additional data checking.

• Choosing a correct algorythm of IdGeneration

  Sometimes we generate table primary key values on the client side and in this case usage of nonoptimal methods of generation may influence on the operation performance.

• Tweaking Batch Updates

  LinqConnect allows grouping several INSERT/UPDATE/DELETE statements into one SQL block and merging their parameters. It allows increasing DataContext.SubmitChanges performance because of reducing number of server calls. Default value of the maximal number of queries, united in the batch, is suitable for most applications, however you may tweak it for special cases.

The monitoring of the generated SQL statements is very important for the performance analysis. LinqConnect offers the following ways for monitoring SQL statements:

• Log is a DataContext property that allows to demonstrate generated SQL commands in any output (console output, stream, file).

• LinqMonitor is a special component for LINQ statements monitoring. It allows to print trace messages to the any output stream and provides for you a possibility to monitor your database operations from DataContext.

• dbMonitor is a special utility that allows you to watch all of the database calls within DataContext. Also, dbMonitor allows you to monitor remote applications that can be very useful for Web-sites and enterprise applications development and maintenance.