. NET face Question series [up]-LINQ: performance

. NET face Question series catalogue

When you use LINQ to sql, use a tool such as LINQPad to see the system-generated SQL statements, which will help you find out where the problem might occur.

Tips for improving performance avoid traversing the entire sequence

When we need only one material, we can consider the use of first/firstordefault/take/any, and so on, they will get the required information after the exit, rather than traverse the entire sequence (unless the last data is the desired haha). Methods such as Tolist/max/last/sum/contain are obvious to traverse the entire sequence.

For example, If you decide whether a collection has members, use any instead of count==0. Because count traversal is very time consuming if the collection has a very high number of Members.

Avoid repeatedly enumerating the same sequence

If you are enumerating the same sequence repeatedly, you may receive the following warning:

Generally see this hint, you need a tolist/todictionary/toarray and other similar methods. Repeating enumerations is unnecessary and time-consuming. In addition, If the program involves multiple threads, or if your sequence contains random factors, the results of each enumeration may be different. We only need to enumerate the same sequence once, then store the result as a generic Collection.

For example, our sequence has random numbers:

At this point we will traverse the sequence four Times. But each sequence will be Different. For example, If we call the sum method four times, there may be 4 different and. We must use the ToList method to force LINQ to execute ahead of Time.

Avoid unnecessarily caching the entire sequence

We have a number of ways to get the last member of the Sequence:

The top two methods are not the Best. When we call some methods of linq, we cache the entire sequence, which may not be necessary. We don't need to leave the entire sequence in memory, we just need to get the last Member.

When to use Tolist/toarray/todictionary and other methods

According to the previous two points, we can summarize when to use tolist/toarray/todictionary and other methods:

• Are you sure you need the whole sequence?
• Are you sure you're going to traverse the whole sequence more than once?
• If the sequence is not very large (because methods such as Tolist/toarray/todictionary will allocate a sequence object on the Heap)

Do you want to return ienumerable<t>?

Return ienumerable<t>, or return a list, or an array? Note that when you return to ienumerable<t>, you do not begin to traverse the sequence (this return is only performed when you force LINQ to execute ienumerable<t> Method ). of course, If the data comes from the far end, you can also choose iqueryable<t> it will not pull the data off the brain, but after all the screening, only tolist, the data from the remote download down. So when using orm, if it uses iqueryable, write your query as an expression rather than as a delegate. Reference: http://www.cnblogs.com/SieAppler/p/3501475.html

In addition, we can give callers the greatest convenience by returning a ienumerable<t> instead of a list or array. (give him a return of the most general Type)

SELECT n+1 Issues

Suppose you have a parent table (for example: a car) that associates a child table, such as a wheel (one-to-many). Now you want to all the parent table cars, Traverse all the cars and then print out all the information about the Wheels. The default practice will Be:

SELECT Carid from Cars;

Then for each car:

SELECT * from Wheel WHERE Carid =?

This will select 2 tables altogether N (the number of rows of the child Table) +1 (parent Table) times, so called the Select N+1 problem.

Examine the following Code. Suppose album is a table, artist is another table, album and artist are One-to-many relationships:

We know that foreach Forces LINQ to execute, so we can imagine this is also an example of a select N+1 problem: get all ALBUM (select * from ALBUM), and then iterate over each ALBUM title to check if it contains keywords , if met, then go to select Table artist, A total of select N+1 Times. The number of SELECT statements generated by the compiler can be checked by LINQPad or other means, and must be a n+1 sql statement.

Workaround: use an anonymous object as an intermediate table to pre-join two tables together :

The generated SQL will only have a sentence!

The 3rd in this article is a typical select N+1 problem. In the code, the first 100 score (one sql) are selected, then all score are traversed, and the value of name (100 sql) is obtained from the table Student.

The workaround is also given in the article, which is to connect two tables Together. This section of the article, "query statistics by table", is still the Problem. To put it simply, use the LINQPad tool every time to see what the resulting SQL will look like. (there are a lot of other tools, of course, or the basic thing is to use SQL Profiler but it's more Cumbersome)

Performance issues with LINQ to SQL

To improve the speed of taking data from a database, you can refer to the following methods:

1. Define the appropriate indexes and keys in the tables in the database
2. Get only the columns you need (using ViewModel or improve your query) and rows (using iqueryable<t>)
3. Whenever possible, use one query instead of multiple
4. You can use asnotracking just to show the data without making subsequent Modifications. It does not affect the generated sql, but it can make the system maintain a lot of data, thereby improving performance
5. Using tools like reshaper, It may give you reminders when you write poor code

There are many tools we can use to get the SQL statements generated by the system, such as LINQPad or SQL Profiler. In EF6, we can also use this method:

Note: The compiler does not necessarily have the ability to translate your LINQ statements into sql, such as the IndexOf method of a string is not Supported.

Using Linqoptimizer to elevate the performance of LINQ statements

Linqoptimizer can be obtained through nuget. You can make Linqoptimizer optimize your LINQ statements by raising the Asqueryexpr method on the Ienumerable<t>. Use the Run method to Execute:

LINQ: Alternative Selection

Do not optimize prematurely until performance bottlenecks are Found.

1. Is there a LINQ statement that needs to run for a long time?
2. Do you want to get the data on the database and run the LINQ statement? (this means that there is a LINQ statement to SQL Expression Conversions)
3. Is the size of the data huge?
4. Do you need to repeat the same LINQ statement very often ?

LINQ VS Foreach (repeating extremely many timesRun the same LINQ Statement)

Under what circumstances does LINQ behave better than foreach? What is the performance gap between the two? The sequence of the following example has 10 million members, and we do a few simple operations on Them.

Results:

You can see that foreach behaves slightly better. The extra overhead of LINQ is to convert a lambda expression to a delegate form , and foreach does not need to. While this little extra overhead can be largely ignored for normal situations, there may be a noticeable difference in performance if you repeat 10 million times.

LINQ VS PLINQ (run repeatedly the sameLINQ Statements)

obviously, If we run the same task repeatedly, and the task doesn't have anything to do with it (without summarizing the results), we can think of multithreading to solve the problem and reuse the resources of the System:

It only took 423 milliseconds after Execution. typically, The result of execution will be equal to the time of foreach, divided by the number of cores of the system cpu. When the CPU is dual-core, the speed can probably be increased by one Time. of course, for a single-core machine, PLINQ is meaningless.

Consider using PLINQ when your machine has multicore and you are dealing with the same tasks (for example, downloading content from different websites and doing the same processing). But PLINQ also needs some extra overhead: it accesses the thread pool, creates new threads, assigns tasks to individual threads, and then collects the results of the Task. so, you need to measure whether PLINQ can really speed up your code.

Custom ORM

In general, you will only consider putting your own ORM into production using the following conditions:

• There are a number of specific complex queries that appear extensively in the project, where the ORM has done a lot of optimizations and performed better than EF
• There are some specific business logic, such as parsing an expression into xml, and EF has no corresponding function
• The performance requirements of your project have been very demanding, resulting in some of the performance-acceptable methods of EF becoming unacceptable to you. For example, EF uses reflection, but if your ORM is used only for the software you develop, you can anticipate everything in advance, so you don't have to reflect

The goal of most ORM development is simply:

• Make query syntax more similar to SQL
• Added a number of syntax sugars or code generation shortcuts to make writing code a little faster
• Performance is comparable to ef, and some are not even as EF
• Without thorough testing.
• Self-Study use

In general, it takes a long time to develop an ORM yourself to ensure that there is no error and is used in the production Environment. In most cases, EF is already a good choice. Performance is a double-edged sword, and it may also ruin your code, making your code difficult to Maintain.

LINQ Performance Issues: Summary

• Use tools such as LINQPad to observe the generated sql. Once you've optimized it, run it again on the LINQPad to see if it's causing a noticeable performance boost.
• Do you need to filter the data on the database and run the LINQ statement? If so, consider returning iqueryable<t>, and examine the Compiler-built intermediate SQL Statement.
• Is the size of the data huge? Avoid premature tolist and return large-scale data of the ienumerable/iqueryable<t> Type.
• Do you need to repeat the same LINQ statement very often ? Consider using foreach or PLINQ to optimize performance.
• Use Linqoptimizer to optimize LINQ Statements.
• Using tools like reshaper, it might give you a reminder when you write poor Code.
• The Msdn,nuget query has a ready-made method (for example, to get the last element).
• Write unit tests to ensure that your optimization is Correct.

. NET face Question series [up]-LINQ: performance