EntityFramework's listener judges SQL performance metrics

Objective

When we use EF as an ORM framework, we may use LINQ or native SQL statements for data manipulation, when we are not sure whether our code will load the database, and when there is some pressure on the database, because the data in the project to do the corresponding operation is too much, We can not accurately go to the location, or we are not a professional DBA, unable to accurately analyze the merits of SQL performance, what should be done now? We don't need a DBA at all, and we can determine the good or bad of a piece of SQL code by doing it, which is what we need to talk about in this section, using the Listener in EF to judge SQL performance, as mentioned in the previous series. We go into the subject.

Dbcommandinterceptor

Needless to say, literally, we can immediately understand the "listening command", and let's look at the class as follows:

We don't need to take a closer look at these virtual methods, and we'll see exactly what we've been guessing before, the SQL commands for database operations. There is an important class in this class that is dbcommandinterceptioncontext , let's call the context of the Listener SQL command, and we'll see what this class contains. As follows:

In this class there is an important attribute UserState , oh, meaning the user state, according to the summary information, we can set the information we operate, but also a type of object, it seems to be conducive to the conversion between the object.

Next go to the topic, how do we judge SQL performance metrics? Answer: We retrieve the time that is consumed when executing the SQL and when the SQL is completed.

SQL Performance Judgment indicator

So the question is, how do we correct this time? This problem can be subdivided into two steps.

(1) How do you know if the SQL command is executing and executing?

(2) How do we set and get the time after we know it?

We hit division, first I build a class SQLProfiler , and this class is definitely inherited from Dbcommandinterceptor , so the code becomes this way.

 Public classSqlprofiler:dbcommandinterceptor { Public Override voidreaderexecuting (DbCommand command, dbcommandinterceptioncontext<dbdatareader>Interceptioncontext)            {Executing (interceptioncontext); Base.        Readerexecuting (command, Interceptioncontext); }         Public Override voidreaderexecuted (DbCommand command, dbcommandinterceptioncontext<dbdatareader>Interceptioncontext)            {Executed (command, Interceptioncontext); Base.        readerexecuted (command, Interceptioncontext); }         Public Override voidnonqueryexecuting (DbCommand command, dbcommandinterceptioncontext<int>Interceptioncontext)            {Executing (interceptioncontext); Base.        Nonqueryexecuting (command, Interceptioncontext); }         Public Override voidnonqueryexecuted (DbCommand command, dbcommandinterceptioncontext<int>Interceptioncontext)            {Executed (command, Interceptioncontext); Base.        nonqueryexecuted (command, Interceptioncontext); }         Public Override voidscalarexecuting (DbCommand command, dbcommandinterceptioncontext<Object>Interceptioncontext)            {Executing (interceptioncontext); Base.        Scalarexecuting (command, Interceptioncontext); }         Public Override voidscalarexecuted (DbCommand command, dbcommandinterceptioncontext<Object>Interceptioncontext)            {Executed (command, Interceptioncontext); Base.        scalarexecuted (command, Interceptioncontext); }}

Seems to find out what, as if the method parameters are what DbCommand and dbcommandinterceptioncontext, since this, we leaf out, in order to start from the execution of SQL command, we start from here time, The timing object is given to userstate, so the following code is available.

        Private void Executing<t> (dbcommandinterceptioncontext<t> interceptioncontext)        {            var New Stopwatch ();             = timer;            Timer. Start ();        }

It should be clear at this point that we get the timed object after execution and take advantage of the timing of the exception and the time we specify it to determine the final time it takes. After the execution completes the code is as follows:

Private voidexecuted<t> (DbCommand command, dbcommandinterceptioncontext<t>Interceptioncontext) {            varTimer =(Stopwatch) interceptioncontext.userstate; Timer.            Stop (); if(Interceptioncontext.exception! =NULL) {file.appendalllines (_logfile,New string[]                {                "Error SQL statement", InterceptionContext.Exception.Message, command. CommandText, Environment.stacktrace,string. Empty,string.            Empty,}); }            Else if(timer.) Elapsedmilliseconds >=_executiontime) {File.appendalllines (_logfile,New string[]                {                string. Format ("time consuming SQL statement ({0}ms)", timer. Elapsedmilliseconds), Command.commandtext, Environment.stacktrace,string. Empty,string.            Empty,}); }        }

The above _executiontime is the time we set in this class of constructors, the constructor is as follows:

        Private ReadOnly string _logfile;         Private ReadOnly int _executiontime;          Public SQLProfiler (stringint  executiontime)        {            = logFile;             = executiontime;        }

And logfile is the log that I want to output. Don't forget the most important thing at this point, which is to register in the Dbconfiguration configuration class (or register in the configuration file). As follows:

    Publicclass  mydbconfiguration:dbconfiguration    {         public  Mydbconfiguration ()        {            this. Addinterceptor (new SQLProfiler (@ "D:\log.txt"1));    }

Let's proceed to the test results.

Test results

           using (varnew  entitydbcontext ())            {                = { };                CTx. Database.sqlquery<Student> ("select * from a", Parameter). ToList ();                Console.readkey ();            }

The above table A actually does not exist, we just want to see if we can detect the exception and get its time. Come on, have a look.

"Effect One"

"Effect Two"

Conclusion

Of course, such as the above when executing the query when the table does not exist certainly will be error, but our focus is not here, it is verified that this table does not exist, but also to verify the time between execution and execution of SQL, and we can manually set through the constructor when the time is greater than we expected to calculate the need to improve SQL.

EntityFramework's listener judges SQL performance metrics