Telerik blogs

Tracing the SQL Statements Generated by Telerik OpenAccess ORM

July 17, 2009 Productivity 0 Comments


One of the main jobs that the ORM software should handle is communicating with the database server and generating SQL statements for reading and writing data. Undoubtedly, the performance of each application developed by using an ORM tool depends on the generated SQL. It is a responsibility of the ORM software to optimize each statement and avoid unnecessary queries. So, when you are facing a difficult choice of an O/R Mapper, it is always meaningful to see and compare how your data is treated by different products. However, not all database engines are distributed with additional profiling software for observing the database events and tracing.

Telerik OpenAccess ORM has an integrated mechanism for tracing and logging the communication between the application and the database server. This includes tracing of the generated SQL statements. This post is focused on the different ways to use this functionality.

There are three available approaches for observing the events that can be used in different cases:

  • At runtime, using the TraceAdapter class;
  • Writing to the Output console while debugging;
  • Writing to a text file.

BackendConfig

First of all, to enable the tracing, open the Backend Configuration dialog from the Telerik -> OpenAccess -> Configuration menu. Then scroll to the Logging node and enable the Event tracing option. Another setting that is common for all tracing approaches is the Log level. It determines how detailed the log will be. The available logging levels are as follows:

  • none (default value): Logs no events.
  • errors: Logs only serious errors (such as connections timing out)
  • normal: Logs the most useful events that do not reduce performance significantly (e.g. SQL executed)
  • verbose: Logs lots of events (this slows down the performance, could be used while debugging)
  • all: Logs all possible events (this slows down the performance very significantly, could be used while debugging)

In our case, to trace the SQL statements, the normal level is detailed enough.

Approach 1:
The API of Telerik OpenAccess ORM provides the TraceAdapter class which can be used to trace events dynamically and process them in the code. To use the class, another “Listener” class is required. In the demo project related to this post a custom OpenAccessTracer class is implemented. It derives from System.Diagnostics.TraceListener and overrides some of its methods. In this case, the tracer class writes output messages to a TextBox control and counts the executed SQL statements. Of course, you can implement any custom logic to integrate the tracer into your application.
The important step here is to add the tracer to the Listeners collection of the singleton Telerik.OpenAccess.Diagnostics.TraceAdapter instance. This can be done in the constructor of the class.

public class OpenAccessTracer : TraceListener
{
    int id;
    int count;
    TextBox textBox;
    Thread myThread;

    public OpenAccessTracer(TextBox textBox)
        : base("Telerik OpenAccess ORM")
    {

        myThread = Thread.CurrentThread;

        Telerik.OpenAccess.Diagnostics.TraceAdapter.Instance.Level = "4";
        id = Telerik.OpenAccess.Diagnostics.TraceAdapter.Instance.Listeners.Add(this);
        this.textBox = textBox;
        count = 0;
    }

    protected override void Dispose(bool disposing)
    {
        this.textBox.Text += count.ToString() + " queries executed.";
        Telerik.OpenAccess.Diagnostics.TraceAdapter.Instance.Listeners.RemoveAt(id);
        base.Dispose(disposing);
    }

    internal void Reset()
    {
        this.textBox.Text = "";
        this.count = 0;
    }

    public override void Write(string message)
    {
    }

    public override void WriteLine(string message)
    {
        if (!myThread.Equals(Thread.CurrentThread))
            return;

        if (message.StartsWith("driver.stat.exec"))
        {
            count++;
            int index;
            if(message.IndexOf("SELECT") >= 0) 
                index = message.IndexOf("SELECT");
            else 
                index = message.IndexOf("INSERT");

            this.textBox.Text += string.Format("{0}: {1}\r\n", count.ToString(), message.ToString().Substring(index));
            this.textBox.SelectionStart = this.textBox.Text.Length;
            this.textBox.SelectionLength = 0;
            this.textBox.ScrollToCaret();
        }
    }
}
 

The WriteLine() method filters the messages and prints only the SQL statements. They can be easily recognized because they start with the "driver.stat.exec" string. To use the custom tracer class, just create an instance and initialize it.

Approach 2:
To enable logging to the console, set the Write Log Output to Console option to True. Then while debugging the application, the events will be shown in the Output window of Visual Studio.

Approach 3:
To log the events to a text file, open the Backend Configuration dialog, enable the Write events to text file option and specify the file name. The “.txt” extension will be added automatically. Note that the path specified in the file name can be either absolute or relative to the path of the application.

 

The demo application used in this topic has enabled all the three methods described above simultaneously. The full source code is available for download in our Code library.

Hope you will be satisfied with the results when you test it yourself. We are looking forward to getting your feedback.

Telerik Data Access

Comments

Comments are disabled in preview mode.