Tracing the SQL Statements Generated by Telerik OpenAccess ORM

Posted by: the telerik blogs, on 17 Jul 2009 | View original | Bookmarked: 0 time(s)

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.

Category: SQL | Other Posts: View all posts by this blogger | Report as irrelevant | View bloggers stats | Views: 1347 | Hits: 4

Similar Posts

  • Using WCF with SQL Azure and Telerik OpenAccess more
  • Connecting to SQL Azure with Telerik OpenAccess more
  • How to display data from different tables using one data source more
  • Binding Hierarchical RadGrid for ASP.NET Ajax with Telerik OpenAccess ORM more
  • Binding Hierarchical RadGrid with Telerik OpenAccess ORM more
  • Using the Telerik OpenAccess WCF Wizard with Multiple Versions of OpenAccess more
  • Using LinqPad with Telerik OpenAccess ORM more
  • Telerik Introduces Free Web Testing Framework for ASP.NET AJAX and Silverlight more
  • How to Enhance Projects Using MSBuild on a Machine Without Telerik OpenAccess ORM Installed more
  • How to debug a stored procedure in your Sql Server 2005 more

News Categories

.NET | Agile | Ajax | Architecture | ASP.NET | BizTalk | C# | Certification | Data | DataGrid | DataSet | Debugger | DotNetNuke | Events | GridView | IIS | Indigo | JavaScript | Mobile | Mono | Patterns and Practices | Performance | Podcast | Refactor | Regex | Security | Sharepoint | Silverlight | Smart Client Applications | Software | SQL | VB.NET | Visual Studio | W3 | WCF | WinFx | WPF | WSE | XAML | XLinq | XML | XSD