Performance problems with sqlxml (FOR XML)

Hello all,
I am running into performance problems when handling larger data volumes and
querying sql-server with the FOR XML-clause.
I have not found a way to directly retrieve a DOM-Object from my query.
Instead, until now, I write the resultset into a bufferString line by line.
This process is extremely slow (see code below).
--> Is there a better way to handle this ? Can I retrieve a DOM-Document
directly when using the FOR XML-clause ?
Thanx,
Joachim
************* CODE EXAMPLE ***********************
Statement stmt = conn.createStatement();
stmt.execute(query + " FOR XML AUTO, ELEMENTS");
ResultSet rs = stmt.getResultSet();
StringBuffer buffer = new StringBuffer();
while(rs.next())
{
InputStream is = rs.getAsciiStream(1);
InputStreamReader isr = new InputStreamReader(is,"ISO-88591");
Reader in = new BufferedReader(isr);
int ch;
while ((ch = in.read()) > -1) {
buffer.append((char)ch);
}
in.close();
}
From .NET applications, you can use the managed SQLXML classes in SQLXML
3.0. See
http://www.microsoft.com/mspress/boo...#SampleChapter for
an overview.
From COM applications, you can do it with the SQLXML OLEDB provider in
SQLXML 3.0.
Here's a sample VB Script:
Const DBGUID_SQL = "{C8B522D7-5CF3-11CE-ADE5-00AA0044773D}"
Const adExecuteStream = 1024
Const adReadAll = -1
Const adCmdStoredProc = 4
Dim conn
Set conn = CreateObject("ADODB.Connection")
conn.Provider = "SQLXMLOLEDB.3.0"
conn.ConnectionString = "DATA PROVIDER=SQLOLEDB;" & _
"SERVER=(local);DATABASE=northwind;INTEGRATED SECURITY=sspi;"
conn.Open
Dim cmd
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
'Set the dialect
cmd.Dialect = DBGUID_SQL
Dim xmlDoc
Set xmlDoc= CreateObject("MSXML.DOMDocument")
cmd.Properties("xml root") = "QueryResults"
cmd.Properties("Output Stream") = xmlDoc
'Specify the command
cmd.CommandText = "SELECT * FROM Products FOR XML AUTO"
cmd.CommandType = adCmdStoredProc
'Execute the command returning a stream
cmd.Execute, , adExecuteStream
xmlDoc.Save "results.xml" 'or process the XML as appropriate
Cheers,
Graeme
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group Ltd.
www.contentmaster.com
"Joachim Blankenstein" <Joachim Blankenstein@.discussions.microsoft.com>
wrote in message news:75F8366C-32BB-4FB5-83EF-4B3209640E12@.microsoft.com...
Hello all,
I am running into performance problems when handling larger data volumes and
querying sql-server with the FOR XML-clause.
I have not found a way to directly retrieve a DOM-Object from my query.
Instead, until now, I write the resultset into a bufferString line by line.
This process is extremely slow (see code below).
--> Is there a better way to handle this ? Can I retrieve a DOM-Document
directly when using the FOR XML-clause ?
Thanx,
Joachim
************* CODE EXAMPLE ***********************
Statement stmt = conn.createStatement();
stmt.execute(query + " FOR XML AUTO, ELEMENTS");
ResultSet rs = stmt.getResultSet();
StringBuffer buffer = new StringBuffer();
while(rs.next())
{
InputStream is = rs.getAsciiStream(1);
InputStreamReader isr = new InputStreamReader(is,"ISO-88591");
Reader in = new BufferedReader(isr);
int ch;
while ((ch = in.read()) > -1) {
buffer.append((char)ch);
}
in.close();
}
|||Hello Graeme (and others),
thanx for the reply. Will the managed SQLXML classes also work with Java ?
If so, would you have an example of how to employ them to query SQL-Server
into XML ?
Thanx,
Joachim
"Graeme Malcolm" wrote:

> From .NET applications, you can use the managed SQLXML classes in SQLXML
> 3.0. See
> http://www.microsoft.com/mspress/boo...#SampleChapter for
> an overview.
> From COM applications, you can do it with the SQLXML OLEDB provider in
> SQLXML 3.0.
> Here's a sample VB Script:
> Const DBGUID_SQL = "{C8B522D7-5CF3-11CE-ADE5-00AA0044773D}"
> Const adExecuteStream = 1024
> Const adReadAll = -1
> Const adCmdStoredProc = 4
> Dim conn
> Set conn = CreateObject("ADODB.Connection")
> conn.Provider = "SQLXMLOLEDB.3.0"
> conn.ConnectionString = "DATA PROVIDER=SQLOLEDB;" & _
> "SERVER=(local);DATABASE=northwind;INTEGRATED SECURITY=sspi;"
> conn.Open
> Dim cmd
> Set cmd = CreateObject("ADODB.Command")
> Set cmd.ActiveConnection = conn
> 'Set the dialect
> cmd.Dialect = DBGUID_SQL
> Dim xmlDoc
> Set xmlDoc= CreateObject("MSXML.DOMDocument")
> cmd.Properties("xml root") = "QueryResults"
> cmd.Properties("Output Stream") = xmlDoc
> 'Specify the command
> cmd.CommandText = "SELECT * FROM Products FOR XML AUTO"
> cmd.CommandType = adCmdStoredProc
> 'Execute the command returning a stream
> cmd.Execute, , adExecuteStream
> xmlDoc.Save "results.xml" 'or process the XML as appropriate
> Cheers,
> Graeme
> --
> Graeme Malcolm
> Principal Technologist
> Content Master
> - a member of CM Group Ltd.
> www.contentmaster.com
>
> "Joachim Blankenstein" <Joachim Blankenstein@.discussions.microsoft.com>
> wrote in message news:75F8366C-32BB-4FB5-83EF-4B3209640E12@.microsoft.com...
> Hello all,
> I am running into performance problems when handling larger data volumes and
> querying sql-server with the FOR XML-clause.
> I have not found a way to directly retrieve a DOM-Object from my query.
> Instead, until now, I write the resultset into a bufferString line by line.
> This process is extremely slow (see code below).
> --> Is there a better way to handle this ? Can I retrieve a DOM-Document
> directly when using the FOR XML-clause ?
> Thanx,
> Joachim
> ************* CODE EXAMPLE ***********************
> Statement stmt = conn.createStatement();
> stmt.execute(query + " FOR XML AUTO, ELEMENTS");
> ResultSet rs = stmt.getResultSet();
> StringBuffer buffer = new StringBuffer();
> while(rs.next())
> {
> InputStream is = rs.getAsciiStream(1);
> InputStreamReader isr = new InputStreamReader(is,"ISO-88591");
> Reader in = new BufferedReader(isr);
> int ch;
> while ((ch = in.read()) > -1) {
> buffer.append((char)ch);
> }
> in.close();
> }
>
>
|||If you are using .Net C# then you should be able to get the result set as a
XmlReader from which you can directly create a DOM.
Chandra
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
news:Ogr2tYQbFHA.1360@.TK2MSFTNGP10.phx.gbl...
> From .NET applications, you can use the managed SQLXML classes in SQLXML
> 3.0. See
> http://www.microsoft.com/mspress/boo...#SampleChapter for
> an overview.
> From COM applications, you can do it with the SQLXML OLEDB provider in
> SQLXML 3.0.
> Here's a sample VB Script:
> Const DBGUID_SQL = "{C8B522D7-5CF3-11CE-ADE5-00AA0044773D}"
> Const adExecuteStream = 1024
> Const adReadAll = -1
> Const adCmdStoredProc = 4
> Dim conn
> Set conn = CreateObject("ADODB.Connection")
> conn.Provider = "SQLXMLOLEDB.3.0"
> conn.ConnectionString = "DATA PROVIDER=SQLOLEDB;" & _
> "SERVER=(local);DATABASE=northwind;INTEGRATED SECURITY=sspi;"
> conn.Open
> Dim cmd
> Set cmd = CreateObject("ADODB.Command")
> Set cmd.ActiveConnection = conn
> 'Set the dialect
> cmd.Dialect = DBGUID_SQL
> Dim xmlDoc
> Set xmlDoc= CreateObject("MSXML.DOMDocument")
> cmd.Properties("xml root") = "QueryResults"
> cmd.Properties("Output Stream") = xmlDoc
> 'Specify the command
> cmd.CommandText = "SELECT * FROM Products FOR XML AUTO"
> cmd.CommandType = adCmdStoredProc
> 'Execute the command returning a stream
> cmd.Execute, , adExecuteStream
> xmlDoc.Save "results.xml" 'or process the XML as appropriate
> Cheers,
> Graeme
> --
> Graeme Malcolm
> Principal Technologist
> Content Master
> - a member of CM Group Ltd.
> www.contentmaster.com
>
> "Joachim Blankenstein" <Joachim Blankenstein@.discussions.microsoft.com>
> wrote in message
> news:75F8366C-32BB-4FB5-83EF-4B3209640E12@.microsoft.com...
> Hello all,
> I am running into performance problems when handling larger data volumes
> and
> querying sql-server with the FOR XML-clause.
> I have not found a way to directly retrieve a DOM-Object from my query.
> Instead, until now, I write the resultset into a bufferString line by
> line.
> This process is extremely slow (see code below).
> --> Is there a better way to handle this ? Can I retrieve a DOM-Document
> directly when using the FOR XML-clause ?
> Thanx,
> Joachim
> ************* CODE EXAMPLE ***********************
> Statement stmt = conn.createStatement();
> stmt.execute(query + " FOR XML AUTO, ELEMENTS");
> ResultSet rs = stmt.getResultSet();
> StringBuffer buffer = new StringBuffer();
> while(rs.next())
> {
> InputStream is = rs.getAsciiStream(1);
> InputStreamReader isr = new InputStreamReader(is,"ISO-88591");
> Reader in = new BufferedReader(isr);
> int ch;
> while ((ch = in.read()) > -1) {
> buffer.append((char)ch);
> }
> in.close();
> }
>