Diagnosing Poor Data Access Code Using SQL Profiler
by Dave Wentzel
I created this article to assist DBAs and application developers who need to optimize data access code in their applications. Often when an application is not performing we try to trace the problem back to application code, database-related performance problems (ie, lack of indexes, too many temp tables), or network issues. In many cases with the myriad of tools we use we can trace a problem down to a particular SQL call that is taking too long to execute. It is common to then try to tune the SQL statement, but many times the problem may not be the SQL statement itself, but rather how it is being called by the application.
The data access layer is the component that retrieves the data from the database and allows it to be manipulated by the application code. In the Microsoft world this is ODBC or OLEDB and is coded using ActiveX Data Objects (ADO) or ADO.NET. Coding the data access layer seems like a simple task, we need to get the data from the database to the application. However if the data access layer is not coded properly performance problems will occur. This article covers a few cases where minor code changes to the data access layer show dramatic improvements in data access performance.
ADO Server-Side Cursors
It is common for an application to need to display database rows to the user. Usually this involves the developer writing a SQL SELECT statement that performs the necessary JOINs and applies WHERE conditions to retrieve the desired result set. In the case of an application written using ActiveX Data Objects (ADO), such as an ASP or VB6 application, the developer then builds an ADO Recordset object and loops through the data returned from the database server, populating an HTML table, dropdown box, or something similar with the data.
Let’s assume you have a table with 3 rows of data. The script in Appendix A will create this table for you in your database. Now you wish to retrieve that data into your application to display to the user. To keep the example simple let’s say we want to just display a Windows messagebox for each row of data. Figure 1 shows some sample code in vbscript that will do just that, simply save the text to a textfile, rename the file to Test1.vbs, replace the connection string to match your server, and doubleclick it to execute.
Figure 1
'Figure 1
'demonstrates server-side ADO cursors
'change servername, user/pwd, db parameters
sConnString = "Provider=sqloledb; Data Source=<your server>;Initial Catalog=<your db>; User Id=sa; Password=<your password>"
Set objConn = CreateObject("ADODB.Connection")
objConn.open sConnString
sql = "select col1, col2 from ADOTEST"
set objRS = CreateObject("ADODB.Recordset")
objRS.CursorType = 3 'adOpenStatic
'objRS.CursorLocation = 3 'adUseClient
objRS.LockType = 1 'adLockReadOnly
objRS.Open SQL, objConn
while not objRS.EOF
msgbox objRS.Fields(0).value & " From " & objRS.Fields(1).value
objRS.MoveNext
wend
objRS.close
objConn.close
Set objRS = Nothing
Set objConn = Nothing
This sample code shows a typical way data is retrieved in ADO. Note that a CursorType and LockType have been declared, but the CursorLocation is left to the default (the code is commented out above).
If you execute the code you should see 3 message boxes pop up in succession proving that you have successfully read from the database and displayed the data to the user.
But is this ADO code optimized? One way to see this is to look at the database traffic generated using SQL Profiler. For this example we have started SQL Profiler and created a new trace using the SQLProfilerStandard template that ships with the tool. The results are displayed in Figure 2.
Figure 2
click for larger image
If you are unfamiliar with SQL Profiler open SQL Books Online for more information. For this example we are interested in the EventClass column. An RPC:Completed event occurs whenever a client application sends a request to the database server for execution. You can consider each of these events (6 in the example above) to be “round trips” to the database server from the client, or 6 different database calls.
In the example the first round trip is a request to open a cursor (sp_cursoropen) that contains the SQL statement we wish to retrieve results for. Sp_cursoropen is an API routine that allows ADO to request a cursor that SQL Server understands. Note that ADO generated the call based on options that we declared for the ADO Recordset object. The second parameter (@P2) is the SQL statement that we need a cursor for. @P1 is an OUTPUT parameter that SQL Server sends back to the client to use as a handle for subsequent calls for cursor manipulation. Note that the client is requesting a particular handle to be used (180150000). Parameters @P3 and @P4 are passed to SQL Server specifying the scrolling and concurrency options for the cursor. However, notice that they are also read by the client after the call because the server may not actually create a cursor that matches what was requested.
The next 4 round trip events are calls to sp_cursorfetch. For the first call the first parameter is the handle that we received from the prior call. The second parameter specifies that we wish to fetch an absolute row number (a constant of 16), which is actually Row 1 (the third parameter), and only that row requested (the fourth parameter is the number of rows to fetch).
Note that the next calls to sp_cursorfetch are simply changing the row number that we wish to return. We should only be returning three rows of data but note that there is a fourth call to sp_cursorfetch. This is because ADO, based on the data returned to it from SQL Server, has no idea how many rows it needs to retrieve. The call to fetch the fourth row will return an end of file marker and ADO knows it has retrieved all of the data it requested.
The last round trip to SQL Server is a request from ADO to sp_cursorclose. This allows SQL Server to close and deallocate the resources needed for the cursor request.
By now it should be clear that we needed 6 round trips (or 6 database requests) to the server to return 3 rows of data using common ADO recordset options, which is a little inefficient for this example. This is known as a server-side cursor, in effect the SQL Server is managing the cursor. Excessive “chattiness” is not the only problem with server-side cursors. They also use tempdb resources on your database server inefficiently. If you have a “quiet” test server (one with not a lot of other activity) try executing sp_lock. You should note just a few lock entries for the entire database instance. Now execute the vbscript again but leave the message boxes open on screen. Run sp_lock again and you will notice that a new tempdb lock is open for the duration of the sp_cursor calls. This is because with a server-side ADO cursor the results are placed into tempdb for subsequent reference by the client. The tempdb object will only be destroyed after the call to sp_cursorclose.
So we have two performance issues with server-side ADO cursors, the round trip calls to the database server to fetch a row at a time and the tempdb utilization. On a very busy web application our team has noticed that SQL Server can become unresponsive because of the compounding affect of these issues. We have witnessed on production systems that tempdb latch contention becomes a bottleneck. This also affects the IIS connection pool to the database server. IIS spawns too many additional connections because each existing connection is used to service sp_cursorfetch commands very inefficiently instead of actually doing some real work. This is especially true if the client needs to return many more than the 3 rows our example returns.
So, how do we fix this? In our vbscript example we have a line of code commented out that specifies the CursorLocation. By default the CursorLocation is adUseServer, which produces the server-side cursors and subsequent sp_cursor calls. Uncomment that line of code and rerun the script with Profiler turned on. The Profiler results for my system are displayed in Figure 3. Note that only one round trip to the database server occurs, our simple SELECT statement. No fetch calls are made and the three rows are returned in one call. Note that the EventClass of the code is no longer an RPC but a simple SQL Batch. Also note that no objects are created in tempdb. It should be clear that a system under heavy load will perform better now, with far fewer round trip database calls and less contention on tempdb.
Figure 3
click for larger image
In ADO.NET (tested with 2.0) we don’t have to worry about server-side cursors because data access is now much more “disconnected” by default and relies on client-side data caching and manipulation. If your code is using the DataSet or DataReader objects you should be OK. Appendix B and C have sample DataSet and DataReader code using a VB.NET console application that can be executed with Profiler running to demonstrate ADO.NET’s new “disconnected” paradigm.
The lesson learned is that it is always worthwhile to run some quick tests of your application using Profiler before it is released to production. Simple code, such as our example, looks optimized and efficient at first glance, until we look at how it functions using Profiler. You can quickly see with Profiler if there are opportunities to radically improve performance with a few extra lines of code.
Prepare/Execute Model
The Prepare/Execute Model can also be performance-draining for your application. When a client needs to execute a SQL statement it can do so in a few different ways. Let’s assume the SQL statement is a standard SELECT. Generally, the client will issue an RPC or a SQL Batch event (visible in Profiler) to SQL Server. The SQL Server will know that some kind of SQL statement, maybe even a parameterized SQL statement, is being requested and will return the data accordingly. This seems simple. But actually, a few lines of code can tell the server not to execute the statement, but rather prepare the statement for later execution. The way the call is constructed will tell the database server if it should directly execute the statement or merely prepare the statement for later execution.
Why would we want to prepare a statement for execution later? When a SQL statement is sent to the database server it needs to be compiled and then executed. If the same SQL statement is going to be called repeatedly, with only slight parameter differences, it may be smarter for the database server to prepare (compile) the statement initially and return an ID back to the client for the prepared version. Whenever the client needs to run the similar statement it can call the prepared plan’s ID, with different parameters, thus reducing the compilation performance hit on the SQL Server. Or so that is the general idea. In older versions of SQL Server (6.5 and before) cached SQL plan reuse was sometimes a problem and the Prepare/Execute model showed performance gains if a similar statement was being executed more than 4 or 5 times. With the latest releases of SQL Server better algorithms for plan reuse have been devised rendering the Prepare/Execute model less useful. In fact many people believe it should be avoided entirely and SQL Server should handle all plan reuse internally.
Let’s look at an example of Prepared Execution. Occassionally your application may need to execute the same or very similar database calls repeatedly. For instance, Figure 4 is a vbscript example that updates the rows in our ADOTEST table with the current time when the update occurs. Admittedly this is not the best way to write this code, but we simply want to see the effects of the Prepare/Execute model. The code builds an update statement with two parameters that will be passed, one for the current date/time (the vbscript now function) and one for the row to be updated (the counter variable). Note that the command object is set to use Prepared execution in our code because we are calling a similar statement multiple times.
Figure 4
'demonstrates Prepare/Execute model
'change servername, user/pwd, db parameters
sConnString = "Provider=sqloledb; Data Source=<your server>;Initial Catalog=<your database>; User Id=sa; Password=<your password>"
Set objConn = CreateObject("ADODB.Connection")
objConn.open sConnString
Set objCmd = CreateObject("ADODB.Command")
objCmd.ActiveConnection = objConn
sql = "UPDATE ADOTEST SET col1 = ? WHERE col2 = ?"
objCmd.CommandText = sql
objCmd.Prepared = TRUE
msgbox "Preparing command"
counter = 1
do while counter < 4
objCmd.Execute ,Array(cstr(now),counter),&H00000080 'adExecuteNoRecords
msgbox "Updated Row " & cstr(counter)
counter = counter + 1
loop
msgbox "Beginning the unprepare process"
Set objCmd = Nothing
objConn.close
Set objConn = Nothing
Run this code with Profiler turned on. Wait a second or two before clicking OK on each message box. If you look at the data in ADOTEST you should see that each UPDATE statement ran for the given row, changing col1 to a different timestamp. Figure 5 shows the Profiler window.
Figure 5
Click for larger image
Note the first trip to the server called sp_prepexec. This is an API call that both prepares a statement and executes it in one step. Similar to sp_cursoropen the first parameter is asking for a handle for subsequent calls. The next parameter declares the datatypes for our passed parameters, followed by the parameterized SQL statement, followed by the values for those parameters. Note that subsequent calls to sp_execute use the handle provided by the server as well as the parameter values. The SQL statement does not have to be passed, saving some bytes on the wire. However, note the extra call required to unprepare, or destroy the plan on the database server. This occurs in our example when the command object is set to Nothing.
In older versions of MDAC you may see a different call being made instead of sp_prepexec. In older versions the first call made to the database server when using Prepared Execution was a call to sp_prepare. This would return the prepared plan id which would be used in subsequent calls to sp_execute. As you can see the development staff already noted the inefiiciencies with the extra call and combined functionality in a single call to sp_prepexec, a step in the right direction.
Now, run the code again with the code set to Prepared = FALSE. Your Profiler window should look similar to Figure 6. Note that the commands are now run with sp_executesql, which still uses parameter substitution (a good thing for plan reuse). But we have eliminated one round trip to the server, namely the call to sp_unprepare.
Figure 6
Click for larger image
From a performance perspective you could make the case that the prepare/execute model saves some resources by compiling the plan up front and also saves some network bytes by not resending the SQL statement for each execution request. However, let’s look at a different example. Figure 7 shows an example that is similar to our ADO cursor example. Here a command object, which is using prepared execution, is used to execute a single SELECT statement. Examining Profiler output in Figure 8 shows that to execute the SELECT statement requires an extra roundtrip to the server to perform the unprepare operation. Figure 9 shows Profiler output with the Prepared = TRUE commented out. Note that the unprepare operation was not needed. Now envision a busy web application that performed numerous SELECT activity where the developer used prepared execution for every call, even when it was not warranted, such as this example. You can see that your network traffic could be double and every unnecessary unprepared call puts a needless drain on both your IIS and database servers. It should be eliminated.
Figure 7
'demonstrates Prepare/Execute Model for SELECT statements
'change servername, user/pwd, db parameters
sConnString = "Provider=sqloledb; Data Source=<your server>;Initial Catalog=<your database>; User Id=sa; Password=<your password>"
Set objConn = CreateObject("ADODB.Connection")
objConn.open sConnString
Set objCmd = CreateObject("ADODB.Command")
objCmd.ActiveConnection = objConn
objCmd.CommandText = "select * from ADOTEST"
objCmd.Prepared = TRUE
Set objRS = objCmd.Execute ()
while not objRS.EOF
msgbox objRS.Fields(0).value & " From " & objRS.Fields(1).value
objRS.MoveNext
wend
objRS.close
objConn.close
Set objRS = Nothing
Set objCmd = Nothing
Set objConn = Nothing
Figure 8
Click for larger image
Figure 9
Click for larger image
Prepared execution is also available in ADO.NET and the same performance caveats apply. Appendix D shows some code for a VB.NET console application that INSERTs 5 rows of data into our test table using prepared execution.
Excessive Metadata Traffic
When looking at a Profiler trace you may see what I call “metadata traffic”. This is database traffic that really doesn’t seem to perform a business function. Often the calls just return information that is used for subsequent database calls. For instance, it is common to see calls requesting stored procedure parameter metadata such as the parameter's name, its datatype, and its ordinal position. Soon after that metadata call you will see the actual procedure execution in Profiler. This metadata retrieval is forcing an extra call for what might be better handled with some code on the client.
Let’s look at an example in VB.NET. In Appendix A we created a stored procedure called “proc_ADOTEST” that takes three input parameters with different datatypes. The procedure does nothing functionally with the parameters, it simply SELECTs the data in our test table, but based on how the procedure is written it will fail if called without the necessary parameters.
We now want to write a VB.NET console application that prompts for the parameter values and displays the data in the table for the user. A common approach a developer could use when calling stored procedures with parameters, especially many parameters, is to query the database metadata for the parameter names and data types. In just a few lines of code all parameter information can be loaded into the application instead of tediously coding the parameter information manually. Our first example, Figure 10, shows some code that derives the parameter metadata by making an extra call to the database. Then the user is prompted to enter the parameter values before the stored procedure is executed. To see the example in action simply copy and paste the code in Figure 10 into a new Visual Basic Console Application. To do this start Visual Studio, choose File|New Project, under Project Types choose Visual Basic, then Windows, then Console Application. Delete the boilerplate code and paste the code from Figure 10. Execute the code by pressing F5. Ensure Profiler is running.
Figure 10
Imports System.Data.SqlClient
Module Module1
Sub Main()
Dim sConn As String = "Data Source=<your server>;Network Library=DBMSSOCN;Initial Catalog=<your db>;User ID=sa;Password=<your password>;"
Dim sqlconn As New SqlConnection(sConn)
Dim sqlcmd As SqlCommand = New SqlCommand("proc_ADOTEST", sqlconn)
sqlcmd.CommandType = CommandType.StoredProcedure
sqlconn.Open()
SqlCommandBuilder.DeriveParameters(sqlcmd)
Dim parm As SqlParameter = New SqlParameter()
For Each parm In sqlcmd.Parameters
If parm.Direction = ParameterDirection.Input Then
parm.Value = InputBox(parm.ParameterName)
End If
Next
Dim dr As SqlDataReader = sqlcmd.ExecuteReader()
Do While dr.Read()
Console.WriteLine("ID retrieved: " & dr.Item(0).ToString)
Loop
MsgBox("Done!")
dr.Close()
sqlconn.Close()
End Sub
End Module
If we execute the code with Profiler running (Figure 11) we see two database calls, the call for the metadata and the call to execute the procedure. The metadata call is to “sp_procedure_params_rowset” because the application needs stored procedure parameter metadata, specifically the number of parameters and their names, datatypes, and direction (input, output, input/output).
Figure 11
Click for larger image
If performance is critical and you want to eliminate the metadata call you simply need to code the parameter information manually. Figure 12 shows code that does this. There are more lines of code, but executing this with Profiler running will show the extra call to the database server is eliminated. It is true that more “dynamic” code that queries SQL Server for parameter metadata is easier to maintain if changes are needed to number, name, or position of parameters, however, this obviously has a cost at runtime. On a mission critical system we want to eliminate all unnecessary overhead.
Figure 12
Imports System.Data.SqlClient
Module Module1
Sub Main()
Dim sConn As String = "Data Source=<your server>;Network Library=DBMSSOCN;Initial Catalog=<your database>;User ID=sa;Password=<your password>;"
Dim sqlconn As New SqlConnection(sConn)
Dim sqlcmd As SqlCommand = New SqlCommand("proc_ADOTEST", sqlconn)
sqlcmd.CommandType = CommandType.StoredProcedure
Dim parm1 As SqlParameter = sqlcmd.Parameters.Add("@datetime", SqlDbType.DateTime)
parm1.Value = InputBox("Enter a Date", "ADOTEST", "01/01/2007")
Dim parm2 As SqlParameter = sqlcmd.Parameters.Add("@integer", SqlDbType.Int)
parm2.Value = InputBox("Enter an Int", "ADOTEST", "1")
Dim parm3 As SqlParameter = sqlcmd.Parameters.Add("@varchar", SqlDbType.VarChar, 255)
parm3.Value = InputBox("Enter a string", "ADOTEST", "Hello World")
sqlconn.Open()
Dim dr As SqlDataReader = sqlcmd.ExecuteReader()
Do While dr.Read()
Console.WriteLine("ID retrieved: " & dr.Item(0).ToString)
Loop
MsgBox("Done!")
dr.Close()
sqlconn.Close()
End Sub
End Module
Classic ADO executes the same metadata query when a call is made to refresh parameters. Figure 13 shows some vbscript code that runs our test stored procedure in a similar fashion to the sample .NET code. If you change the RunOption variable to “ExplicitParams” you can run the code to demonstrate explicitly coding the parameters which does not make the extra metadata call. If you set its value to “RefreshParams” you can see the affect of running the code by making the extra round trip to the server.
Figure 13
'change servername, user/pwd, db parameters
Const adInteger = 3
Const adDBTimeStamp = 135
Const adVarChar = 200
Const adParamInput = &H0001
Const adCmdStoredProc = &H0004
sConnString = "Provider=sqloledb; Data Source=<your server>;Initial Catalog=<your database>; User Id=sa; Password=<your password>"
Set objConn = CreateObject("ADODB.Connection")
objConn.open sConnString
Set cmd = createObject("ADODB.Command")
cmd.ActiveConnection = objConn
cmd.CommandText= "proc_ADOTEST"
cmd.CommandType=adCmdStoredProc
'RunOption = "ExplicitParams" 'change these values to see the different calls in Profiler
RunOption = "RefreshParams"
If RunOption = "ExplicitParams" then
cmd.Parameters.append cmd.CreateParameter("@datetime",adDBTimeStamp,adParamInput,,"1/1/2007")
cmd.Parameters.append cmd.CreateParameter("@integer",adInteger,adParamInput,,1)
cmd.Parameters.append cmd.CreateParameter("@varchar",adVarChar,adParamInput,255,"test")
elseif RunOption = "RefreshParams" then
cmd.Parameters.refresh
cmd.Parameters("@datetime").Value = "1/1/2007"
cmd.Parameters("@integer").Value = 1
cmd.Parameters("@varchar").Value = "test"
End if
msgbox "Parameter(0): " & cmd.Parameters(0).Name & " " & cmd.Parameters(0).Value
msgbox "Parameter(1): " & cmd.Parameters(1).Name & " " & cmd.Parameters(1).Value
msgbox "Parameter(2): " & cmd.Parameters(2).Name & " " & cmd.Parameters(2).Value
if RunOption = "RefreshParams" then
msgbox "Parameter(3): " & cmd.Parameters(3).Name & " " & cmd.Parameters(3).Value
end if
Set Rs1 = cmd.Execute()
do until rs1.eof
msgbox "Read row: " & cstr(rs1(0).value)
rs1.movenext
Loop
Set cmd = Nothing
objConn.Close
Set objConn = Nothing
It is also interesting to note in Figure 13 that you get an “extra” parameter available to you if you query for parameter metadata instead of hard-coding the parameters. Try executing the code again paying careful attention to the number and names of the parameters. Every stored procedure has a RETURN value that can be set. Our stored procedure does not explicitly set a RETURN value so it has an implicit value of 0 when it completes successfully. When performing a command.parameters.refresh the RETURN parameter becomes “Parameters(0)”, the first parameter, whereas when we coded the parameter ourselves we skipped this parameter since we didn’t need it. This means the parameter ordinals are different depending on the method used. This can lead to confusion as to which ordinal parameters refer to which named parameters. This is another reason why I like explicitly coding parameters, there is no confusion.
There are other “metadata queries” you may see that can impact performance. If you run your application with Profiler turned on you may see events similar to these:
SET FMTONLY ON
SELECT …
SET FMTONLY OFF
The next event in Profiler will probably be a very similar SELECT statement, but this time without the SET FMTONLY options. The FMTONLY options instruct SQL Server to return only the metadata information about the columns that will be returned, without actually processing any of the rows.
You may even see commands like:
SET NO_BROWSETABLE ON
…<your command>...
SET NO_BROWSETABLE OFF
These commands are not documented very well in BOL, but a search on “Browse Mode” will give you a hint as to what is going on. Suffice it to say that these commands are also used during metadata retrieval.
Let’s look at an example. Figure 14 shows some vbscript code that passes a parameterized SELECT query to SQL Server. The example just wants to see all of the ids WHERE cola is BETWEEN a and z. The resulting SQL command is static, but uses ADO parameter substitution anyway. If you set the RunOption value to ExplicitParams you will see that the code block that is executed is manually building the parameter list, assigning datatypes, sizes, parameter direction, and values explicitly. This code requires no metadata retrieval for parameter information so if we execute this with Profiler running we see only one database call, the call to sp_executesql, Figure 15. ADO uses sp_executesql since we are performing parameter replacement. sp_executesql is a common method TSQL developers use to build and execute dynamic SQL strings.
Figure 14
'change servername, user/pwd, db parameters
'ado parameter refresh
Const adCmdText = &H0001
Const adParamInput = &H0001
Const adVarChar = 200
sConnString = "Provider=sqloledb; Data Source=<your server>;Initial Catalog=<your database>; User Id=sa; Password=<your password>"
Set objConn = CreateObject("ADODB.Connection")
objConn.open sConnString
Set cmd = createObject("ADODB.Command")
cmd.ActiveConnection = objConn
cmd.commandtype = adCmdText
cmd.Prepared = FALSE
cmd.CommandText = "SELECT id FROM ADOTEST where col1 BETWEEN ? AND ?"
'RunOption = "RefreshParams"
RunOption = "ExplicitParams"
If RunOption = "RefreshParams" then
cmd.Parameters.refresh
cmd.Parameters(0).Value = "a"
cmd.Parameters(1).Value = "z"
elseif RunOption = "ExplicitParams" then
with cmd
.Parameters.append .CreateParameter("@P1",adVarChar,adParamInput,255,"a")
.Parameters.append .CreateParameter("@P2",adVarChar,adParamInput,255,"z")
end with
End If
Set rs = cmd.Execute()
do while not rs.eof
msgbox rs.fields(0).value
rs.movenext
loop
msgbox "done"
set rs = Nothing
set cmd = Nothing
objConn.Close
Set objConn = Nothing
Figure 15
Click for larger image
If you run the code again with RunOption set to RefreshParams a call is made to the database server to determine dynamically the datatypes and sizes for the replacement parameter ‘?’. Figure 16 shows the Profiler results of the new code. Note that what previously took one database round trip now takes six! That’s an incredible amount of database traffic for such a simple command! Let’s analyze what each call is doing. The first call is to retrieve the metadata using SET FMTONLY commands. We then see calls to SET NO_BROWSETABLE, a metadata call in this case. What is interesting is the calls to sp_prepare and sp_unprepare. Note in our code that we explicitly set .Prepare = FALSE, yet we still get prepared execution with its extra database calls! This is a known “feature” of ADO. ADO inherently uses prepared execution when a call is made to cmd.Parameters.Refresh in a situation like this, and it cannot be configured otherwise. Notice that immediately after the metadata retrieval sp_unprepare is called and that the final call, the call doing the actual work we want, is not using prepared execution, which is what we, in fact, have coded. Using parameter retrieval in this example caused 5 additional roundtrips to the server.
Figure 16
Click for larger image
These extra events have a negligible performance affect for SQL Server, but, just like the other examples we have seen, can be performance-draining under load. Imagine again if each SELECT statement a web application generated was parameterized and utilized metadata retrieval calls. The result is a lot of unnecessary, extra round trips to the database server.
.NET CommandBuilder Calls
It is common for a .NET developer to populate a DataSet with data from a database that needs to be updated on the client and those updates sent back to the database. .NET has a class called the CommandBuilder that makes this very easy but, like we have seen many times so far, is not optimal. Figure 17 shows some VB.NET code for a console application that uses the CommandBuilder to update data in a database. The code first queries the database for the data in the table and then runs an UPDATE statement to change one data element. In an optimal application this should require two database calls, one to fetch the data and one to run the UPDATE. But this is not the case. Notice that nowhere in the code is an actual SQL UPDATE statement referenced. Instead, we call CommandBuilder which builds us a generic UPDATE statement.
Figure 17
Imports System.Data.SqlClient
Module Module1
Sub Main()
Dim sConn As String = "Data Source=<your server>;Network Library=DBMSSOCN;Initial Catalog=<your database>;User ID=sa;Password=<your password>;"
Dim sqlconn As New SqlConnection(sConn)
Dim sqlcmd As New SqlCommand
Dim da As New SqlDataAdapter()
Dim ds As New DataSet()
Dim dt As New DataTable()
Dim strChangedVal As String = Now().ToString
With sqlcmd
.CommandText = "select * from adotest"
.Connection = sqlconn
End With
da.SelectCommand = sqlcmd
da.Fill(ds, "ADOTEST")
dt = ds.Tables(0)
Dim cb As SqlCommandBuilder = New SqlCommandBuilder(da)
MsgBox("SQLCommandBuilder Insert Command:" & cb.GetInsertCommand().CommandText)
MsgBox("SQLCommandBuilder Update Command:" & cb.GetUpdateCommand().CommandText)
MsgBox("SQLCommandBuilder Delete Command:" & cb.GetDeleteCommand().CommandText)
MsgBox("Changing: " & dt.Rows(0).Item(1).ToString & " to " & strChangedVal)
dt.Rows(0).Item(1) = strChangedVal
da.Update(ds, "ADOTEST")
MsgBox("Done!")
sqlconn.Close()
End Sub
End Module
If you run this code with Profiler turned on you will see results similar to Figure 18. Note that it took five (5) database calls to handle a data fetch and UPDATE, something we could have coded by hand to make only 2 calls. Let’s look at the code to determine what is happening.
Figure 18
Click for larger image
First the code uses a DataAdapter to fill a DataSet with data from our table (“select * from adotest”). This is the first call we see in Profiler. We then use a SqlCommandBuilder to dynamically determine and build an INSERT, UPDATE, and DELETE SQL statement. We use MsgBox to display the actual commands but they are not needed, I just wanted to show you the DML it is creating. Simply instantiating the SqlCommandBuilder was enough to query the database for metadata. The actual query that was sent to SQL Server was:
SET FMTONLY OFF; SET NO_BROWSETABLE ON; SET FMTONLY ON;select * from adotest SET FMTONLY OFF; SET NO_BROWSETABLE OFF;
You will note that this traffic is very similar to what is seen in Figure 16 when we covered refreshing parameters in classic ADO. However, in ADO this required more round trips, in ADO.NET this has been compressed into one call, so it is clear that the development team for ADO recognized a performance problem and made a noticeable improvement in .NET.
In the code we then make one change to Row 1, Column 2, which we send back to the database server using CommandBuilder's UPDATE command:
exec sp_executesql N'UPDATE [adotest] SET [col1] = @p1 WHERE (([id] = @p2) AND ((@p3 = 1 AND [col1] IS NULL) OR ([col1] = @p4)) AND ((@p5 = 1 AND [col2] IS NULL) OR ([col2] = @p6)))', N'@p1 varchar(19),@p2 int,@p3 int,@p4 varchar(19),@p5 int,@p6 varchar(1)', @p1 = '4/6/2007 1:50:19 PM', @p2 = 1, @p3 = 0, @p4 = '4/6/2007 1:37:39 PM', @p5 = 0, @p6 = '1'
Here we expect to see the use of sp_executesql since we are sending a set of parameters to an UPDATE statement. However, note the inefficiency of the UPDATE statement. The CommandBuilder knows we are updating a single value so it expects to receive a “1 row(s) affected” message but it is not properly using our primary key defined on the id column as the basis for its WHERE clause. Instead it is comparing each column value in the DataSet with the column value it expects to see in the database. On top of that it needs to handle NULLs, just in case it encounters one, making the WHERE clause even more inefficient. In effect the UPDATE statement will not just overwrite what was in the row, it will instead ensure that no other changes have occurred to the row since it was last read. It is ensuring the consistency of the data. ADO.NET is choosing the transaction isolation level in this case. This may not be what we want for our application.
If you run the code again and look at the standard DELETE statement that CommandBuilder provides you will see that it is structured similarly to the UPDATE statement, performing the data consistency checks. It is also worth noting that since the table has an IDENTITY the CommandBuilder's INSERT statement does not attempt to populate it, which is what we would expect. It determined this by the metadata returned from SQL Server.
If you change the code to update additional rows you will see in Profiler that for each row being changed a separate UPDATE statement is required due to the nature of CommandBuilder’s one-row-change-at-a-time paradigm. The same holds true for the DELETEs, only one row can be affected at a time. Again, if we code this by hand we can overcome this behavior, if desired.
As with previous examples the code is not very efficient and requires extra round trips to the server. With a little effort a better constructed UPDATE statement could be written that utilizes available indexes better (though we don’t have any on our small test table) and accomplishes the result with fewer round trips.
Summary
When you examine Profiler traffic for your application you may see many calls to your database that do not seem to be answering business queries. In fact, you may not even recognize the calls as being part of your application. Data access technologies sometimes inefficiently make extra database calls, or simply make inefficient database calls, to satisfy internal data needs. These calls cannot always be eliminated, and they are not always problematic. But if your application is under heavy load the extra calls can be detrimental to performance. Often with only a few changes to code your database traffic can be reduced substantially. You may also eliminate contention on other SQL Server resources such as tempdb.
There is specific traffic you should look for. When you are using Profiler and you see heavy use of sp_cursor procedures you should understand that your application is using server-side cursors. If you see signs of Prepared execution you should be able to determine if it is needed and how it can be avoided if possible. If you see calls to FMTONLY, NO_BROWSETABLE, or “sp_procedure_params_rowset” you should know that extra metadata calls are being made that might be able to be eliminated. And if you see inefficient UPDATE and DELETE statements coupled with those metadata calls you may be a victim of inappropriate use of the CommandBuilder class.
It is always worthwhile to run your application with Profiler enabled to detect inefficient or unnecessary database calls.
APPENDIX A - Script to Build the Data Samples
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('ADOTEST'))
DROP TABLE ADOTEST
CREATE TABLE ADOTEST (id int identity (1,1) PRIMARY KEY,col1 varchar(20) null, col2 varchar(20) null)
Go
INSERT INTO ADOTEST VALUES ('Hello World1', '1')
INSERT INTO ADOTEST VALUES ('Hello World2', '2')
INSERT INTO ADOTEST VALUES ('Hello World3', '3')
Go
if exists (select * from sysobjects where id = object_id('proc_ADOTEST'))
drop proc proc_ADOTEST
go
create proc proc_ADOTEST
@datetime datetime
,@integer int
,@varchar varchar(255)
AS
select * from adotest
go
APPENDIX B - ADO.NET DataSet Sample Script
Imports System.Data.SqlClient
Module Module1
Sub Main()
Dim sConn As String = "Data Source=<your serverIP,port>;Network Library=DBMSSOCN;Initial Catalog=<your db>;User ID=sa;Password=<your password>;"
Dim sSQL As String = "SELECT col1, col2 FROM ADOTEST"
Dim sqlDA As New SqlDataAdapter()
Dim sqlDS As New DataSet()
Dim sqlConn As New SqlConnection(sConn)
Dim sqlcmd As New SqlCommand(sSQL, sqlConn)
sqlcmd.CommandType = CommandType.Text
sqlDA.SelectCommand = sqlcmd
sqlDA.Fill(sqlDS, "ADOTEST")
MsgBox("Done!")
End Sub
End Module
APPENDIX C - ADO.NET DataReader Sample Script
Imports System.Data.SqlClient
Module Module1
Sub Main()
Dim sConn As String = "Data Source=<your serverIP,port>;Network Library=DBMSSOCN;Initial Catalog=<your db>;User ID=sa;Password=<your password>;"
Dim sSQL As String = "SELECT col1, col2 FROM ADOTEST"
Dim sqlConn As New SqlConnection(sConn)
Dim sqlcmd As New SqlCommand(sSQL, sqlConn)
sqlcmd.CommandType = CommandType.Text
sqlConn.Open()
Dim dr As SqlDataReader = sqlcmd.ExecuteReader()
Do While dr.Read()
Console.WriteLine(dr.Item(0).ToString + " " + dr.Item(1).ToString)
Loop
sqlConn.Close()
MsgBox("Done", MsgBoxStyle.OkOnly)
End Sub
End Module
Appendix D - VB.NET Prepared Execution Example
Imports System.Data.SqlClient
Module Module1
Sub Main()
Dim sConn As String = "Data Source=<your server>;Network Library=DBMSSOCN;Initial Catalog=<your database>;User ID=sa;Password=<your password>;"
Dim sqlConn As New SqlConnection(sConn)
Dim sSQL As String = "INSERT ADOTEST (col1,col2) VALUES (getdate(),@RowID)"
Dim sqlcmd As New SqlCommand(sSQL, sqlConn)
Dim parmRowID As New SqlParameter("@RowID", SqlDbType.Int, 1, ParameterDirection.Input)
sqlcmd.Parameters.Add(parmRowID)
sqlConn.Open()
sqlcmd.Prepare()
Dim i As Integer
For i = 1 To 5
parmRowID.Value = i
sqlcmd.ExecuteNonQuery()
MsgBox("Inserted Row " & i.ToString)
Next
MsgBox("Done!")
End Sub
End Module
