Introduction to SQL Profiler, Page 2

Writing the Test Code

To demonstrate what SQL Profiler can do for you, I need a little code to execute. So I've put together a very simple application in Visual Basic .NET. This application has a DataGrid and two button controls. When you click the Load button, it loads the contents of the Northwind Customers table to the DataGrid. You can then use the DataGrid to add, edit, or delete data in the underlying DataSet. When you click the Save button, any changes you made are pushed back to the database. Here's the bulk of the code:

Private Sub btnLoad_Click(ByVal sender As System.Object, _
 ByVal e As System.EventArgs) Handles btnLoad.Click
    ' Retrieve all data from the Customers table
    Dim cmd As SqlCommand = _
     sqlConnection1.CreateCommand()
    cmd.CommandText = "SELECT * FROM Customers"
    Dim da As SqlDataAdapter = New SqlDataAdapter
    da.SelectCommand = cmd
    ' Put it into a dataset
    Dim Customers As DataSet = New DataSet
    da.Fill(Customers, "Customers")
    ' And show it on the user interface
    dgCustomers.DataSource = Customers
    dgCustomers.DataMember = "Customers"
End Sub

Private Sub btnSave_Click(ByVal sender As System.Object, _
 ByVal e As System.EventArgs) Handles btnSave.Click
    ' Reconnect to the Customers table
    Dim cmd As SqlCommand = _
    sqlConnection1.CreateCommand()
    cmd.CommandText = "SELECT * FROM Customers"
    Dim da As SqlDataAdapter = New SqlDataAdapter
    da.SelectCommand = cmd
    ' Use the CommandBuilder to build the INSERT, 
    ' UPDATE, and DELETE commands
    Dim cb As SqlCommandBuilder = _
     New SqlCommandBuilder(da)
    ' Save changes back to the database
    da.Update(dgCustomers.DataSource, "Customers")
End Sub

Now, the question is: what happens at the database when you push those two simple buttons? That's exactly the sort of question that SQL Profiler can answer for you. Let's find out!

Running the Trace

To start tracing, click the Run button in the New Trace dialog box. There are also buttons on the SQL Profiler toolbar to start, stop, and pause traces (SQL Profiler allows you to have multiple trace windows open and active at the same time). After starting the trace, I fired up the sample application, loaded the data, and then edited a single row. I saved the change, closed the application, and stopped the trace. Figure 5 shows the results.

Surprised at the amount of activity? There are statements to set things up the way that .NET likes them, and to retrieve the initial DataSet. Then there's a statement (including the SET FMTONLY command) whose job it is to retrieve the column names from the table; this information is used by the SqlCommandBuilder object to come up with its SQL statements. Finally, you can see the statement that was actually sent to the database to do the update. Here it is, formatted a bit:

exec sp_executesql N'UPDATE Customers 
 SET CompanyName = @p1 
 WHERE ( (CustomerID = @p2) AND 
  ((@p3 = 1 AND CompanyName IS NULL) OR
  (CompanyName = @p4)) AND 
  ((@p5 = 1 AND ContactName IS NULL) OR 
  (ContactName = @p6)) AND 
  ((@p7 = 1 AND ContactTitle IS NULL) OR 
  (ContactTitle = @p8)) AND 
  ((@p9 = 1 AND Address IS NULL) OR 
  (Address = @p10)) AND 
  ((@p11 = 1 AND City IS NULL) OR 
  (City = @p12)) AND 
  ((@p13 = 1 AND Region IS NULL) OR 
  (Region = @p14)) AND
  ((@p15 = 1 AND PostalCode IS NULL) OR 
  (PostalCode = @p16)) AND 
  ((@p17 = 1 AND Country IS NULL) OR 
  (Country = @p18)) AND 
  ((@p19 = 1 AND Phone IS NULL) OR 
  (Phone = @p20)) AND
  ((@p21 = 1 AND Fax IS NULL) OR 
  (Fax = @p22)) )', 
  N'@p1 nvarchar(4000),@p2 nchar(4000),
  @p3 int,@p4 nvarchar(4000),@p5 int,
  @p6 nvarchar(4000),@p7 int,@p8 nvarchar(4000),
  @p9 int,@p10 nvarchar(4000),@p11 int,
  @p12 nvarchar(4000),@p13 int,
  @p14 nvarchar(4000),@p15 int,
  @p16 nvarchar(4000),@p17 int,
  @p18 nvarchar(4000),@p19 int,
  @p20 nvarchar(4000),@p21 int,
  @p22 nvarchar(4000)', 
  @p1 = N'Alfred Futterkiste', @p2 = N'ALFKI', @p3 = 0, 
  @p4 = N'Alfreds Futterkiste',@p5 = 0, @p6 = N'Maria Anders', 
  @p7 = 0, @p8 = N'Sales Representative', @p9 = 0,
  @p10 = N'Obere Str. 57', @p11 = 0, @p12 = N'Berlin', 
  @p13 = 1, @p14 = NULL, @p15 = 0, @p16 = N'12209', @p17 = 0, 
  @p18 = N'Germany', @p19 = 0, @p20 = N'030-0074321', 
  @p21 = 0, @p22 = N'030-0076545'

Whew! That statement should show you why the CommandBuilder object isn't always a good idea. It will build general-purpose SQL statements that work for any update to the database - but they are often far bulker than necessary. In this particular case, UPDATE Customers SET CompanyName = 'Alfred Futterkiste' WHERE CustomerID = 'ALFKI' would have worked just as well. If you're trying to tune the performance of your data access code, SQL Profiler can help you see the effect that your changes are having.