Geek asks and answers

Measuring the runtime of many methods

I am working on a project in WPF that uses Entity Framework with SQL Server. Optimize there shit. Everything is too slow. And I want to diagnose which parts of the code kill performance - I think there are only a few (I mean a lot, but not all) places that are poorly designed and kill performance.

Now for each table we have a class, for example UserRepository. This is not exactly a repository template. These classes have methods such as: GetAll(...), GetById(...), GetNewest(...), GetAllWithHigherSalaryThan(int salary, int companyId)etc., many db access methods. The database is used only in repository classes.

I do not want to talk about refactoring here. I just want to measure how long each method has been running and how many times it has been executed at run time. From this information I can find errors.

I want to measure about 100 methods that "choose from db" in many classes. SQL Server Provider does not do this because these methods are executed countless times, and analyzing logs from the profiler is, if possible, a nightmare with our approach to the database.

Method Example:

public IEnumerable<Foo> GetFoo(int y, int z)
{
        return Context.Where(p =>
            p.X == null &&
            p.Y == y &&
            p.Time >= z).OrderBy(x => x.Time).AsEnumerable();
}

Now I was thinking about adding a stopwatch to each method, measuring the execution time, counting the performances and transferring it to some singleton or something else, and then displaying it. Of course, I will turn off this diagnosis when I am done with it, but this approach is a very weekly course. I need to edit each method, and I don’t know how to turn it off after - I mean, I know that I can use something like #define DEBUG, but anyway.

- Reflections, ... , #?

?

+3
2

- , fav - dotTrace Jetbrains, .

, :

Postsharp - AOP. .

Postsharp :

[Serializable] 
public class TraceAttribute : OnMethodBoundaryAspect 
{ 
    public override void OnEntry( MethodExecutionArgs args ) 
    { 
        // start measuring time here
    } 

    public override void OnExit( MethodExecutionArgs args ) 
    { 
        // stop measuring here 
    } 
}

( MyNamespace):

#if DEBUG 
[assembly: Trace( AttributeTargetTypes = "MyNamespace.*", 
    AttributeTargetTypeAttributes = MulticastAttributes.Public, 
    AttributeTargetMemberAttributes = MulticastAttributes.Public )] 
#endif

, .

, csv, SQL Server, , , , . , - ( ).

: http://www.postsharp.net/aspects/examples/logging

- PostSharp Express - .

+4

. IDE/Debugger , , , .

MSSQL, , , ya. , :

SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
 ((CASE qs.statement_end_offset
 WHEN -1 THEN DATALENGTH(qt.TEXT)
 ELSE qs.statement_end_offset
 END - qs.statement_start_offset)/2)+1),
 qs.execution_count,
 qs.total_logical_reads, qs.last_logical_reads,
 qs.total_logical_writes, qs.last_logical_writes,
 qs.total_worker_time,
 qs.last_worker_time,
 qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
 qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
 qs.last_execution_time,
 qp.query_plan
 FROM sys.dm_exec_query_stats qs
 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
 CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
 ORDER BY qs.total_logical_reads DESC -- logical reads
 -- ORDER BY qs.total_logical_writes DESC -- logical writes
 -- ORDER BY qs.total_worker_time DESC -- CPU time


SELECT DISTINCT TOP 10
 t.TEXT QueryName,
 s.execution_count AS ExecutionCount,
 s.max_elapsed_time AS MaxElapsedTime,
 ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime,
 s.creation_time AS LogCreatedOn,
 ISNULL(s.execution_count / DATEDIFF(s, s.creation_time, GETDATE()), 0) AS FrequencyPerSec
 FROM sys.dm_exec_query_stats s
 CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
 ORDER BY
 s.max_elapsed_time DESC
 GO

, , , , 10 , , 1 000 000 , .

- . , -, , , .

0

More articles:


All Articles