articles >> performance tuning >> Use OPENXML to Minimize SQL Server Database ...

Use OPENXML to Minimize SQL Server Database Calls

By : Jay Buchta Mar 25, 2001

OPENXML is a new function added to SQL Server 2000 that provides a rowset view over an XML document. Since a rowset is simply a set of rows that contain columns of data, OPENXML is the function that allows an XML document to be treated in the familiar relational database format. It allows for the passing of an XML document to a T-SQL stored procedure for the updating of data. 

The purpose of this article is to describe how the SQL Server 2000 OPENXML function can be applied to a multiple row insert of data in a single database call. Code will be presented that accepts a two dimensional variant array from an ASP client, creates an XML document string from it, and then calls a SQL Server 2000 stored procedure that inserts new rows into a table. This can be an effective alternative to looping through an array and calling a stored procedure to insert a row each time.

The example provided inserts 10 rows into a table, so the OPENXML approach is cutting the database calls from 10 to 1 in this case. This minimization of database calls can translate into significant performance and scalability benefits. Each time a database call is made, network and database resources are utilized. The more demands you make for these resources, the more likely you will experience degradation in your application’s performance. OPENXML enables you to, in essence, package data together in a single call (as XML), map it to a rowset view, and execute all of the inserts within the same database call which results in a minimization of the utilization of these resources. 

The purpose of the following code is to save a user’s responses to a series of online course evaluation questions. The responses to the questions are collected in a two-dimensional variant array. The array represents survey question numbers and their responses for a given survey respondent. Listing 1 shows the SurveyToXML function that resides in a middle tier business object compiled as a VB ActiveX.dll. The array is passed from an ASP page to this business object. You can pass the array to the COM object ByVal, but you must first enclose the array in parenthesis. Failure to do so results in a VBScript runtime error. Microsoft has a knowledgebase article about the use of parenthesis to force ByVal that explains this further. It makes sense to pass the array ByVal in this case because you are passing to a middle tier component across the network. Here is a code snippet from the ASP page showing how to pass the array ByVal:

 

Listing 1 - ASP Calling COM Function

Dim objBusEval, arrSurvey
set objBusEval = server.CreateObject("SurveyXML.savesurvey")
if objBusEval.SurveyToXML((arrsurvey)) then 
…
else 
… 
end if

The following function is called by the ASP page (see Listing 1):

 

Listing 2 - VB COM Function

Public Function SurveyToXML( ByVal arrSurvey As Variant) As Boolean
Dim objXMLdoc As MSXML.DOMDocument
Dim objXMLRootElem As MSXML.IXMLDOMElement
Dim objXMLAnswers As MSXML.IXMLDOMElement
Dim objXMLAttribEvalID As IXMLDOMAttribute
Dim objXMLAttribQnum As IXMLDOMAttribute
Dim objXMLAttribAnswer As IXMLDOMAttribute
Dim x As Integer
Dim y As Integer
Dim EvalID As Integer
Dim objDBSurvey As dbSurvey.dbSaveSurvey

On Error GoTo ErrHandler

Set objDBSurvey = New dbSurvey.dbSaveSurvey

EvalID = objDBSurvey.GetEvalID("Alex", "Rodriguez", "arod@texasrangers.com", 1, vbNullString)

Set objXMLdoc = New MSXML.DOMDocument

With objXMLdoc

Set objXMLRootElem = .createElement("insert")
.appendChild objXMLRootElem

For y = LBound(arrSurvey, 2) To UBound(arrSurvey, 2)

Set objXMLAnswers = .createElement("Answers")
.documentElement.appendChild objXMLAnswers

Set objXMLAttribEvalID = .createAttribute("EvalID")
objXMLAttribEvalID.nodeValue = EvalID
objXMLAnswers.setAttributeNode objXMLAttribEvalID

Set objXMLAttribQnum = .createAttribute("QNum")
objXMLAttribQnum.nodeValue = arrSurvey(0, y)
objXMLAnswers.setAttributeNode objXMLAttribQnum

Set objXMLAttribAnswer = .createAttribute("Answer")
objXMLAttribAnswer.nodeValue = arrSurvey(1, y)
objXMLAnswers.setAttributeNode objXMLAttribAnswer

Next y

If objDBSurvey.SaveXMLResponse(.xml) Then
SurveyToXML = True
Else
SurveyToXML = False
End If
End With

SurveyToXML = True

Cleanup:
Set objDBSurvey = Nothing
Set objXMLdoc = Nothing
Exit Function

ErrHandler:
SurveyToXML = False
GoTo Cleanup
End Function

The client passes in a two-dimensional variant array with the first dimension index 0 as the Question Number (Qnum) and first dimension index 1 as the Answer. The second dimension of the array represents the ordinal number of questions answered. The EvalID, which connects the answers to a specific evaluation or survey, is created and returned from the database. The three values required to insert an answer to a specific survey question are the EvalID, the Qnum, and the Answer.