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.