sql server reporting services: Hi,



I am experiencing unacceptable performance when reading XML files using a
custom XML data processing extensions with RS on SS 2000.



The XML data processing extensions I have used is the one on the MSDN site
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/RSDSetEx3.asp)



This works fine with low volumes of data but as the number of records
increase in the XML file the time taken to open the file increases
exponentially. The timings I have recorded are:



records seconds

1,000 = 1

2,500 = 3

5,000 = 6

10,000 = 22

15,000 = 75





The largest XML file I have contains 40,000 records and takes about 1200
secs (20 mins) to open which is a show stopper.



Has anyone else run into this problem and if so how did you resolve it?



An example of XML File



<REPORT>

<base><a>DETAILED</a><b>Repeat</b><c>www.fourthsite.com</c><d>/music/store/files123/</d><e>pop.asp</e><f>?download=ring498</f><g>12</g><h>1</h><i>2</i><j>1</j><k>12</k><l>101</l><m>200</m><o>0</o><p>100</p><q>1</q><r>-5</r></base>

<base><a>DETAILED</a><b>Repeat</b><c>www.fourthsite.com</c><d>/music/store/files123/</d><e>pop.asp</e><f>?download=ring498</f><g>12</g><h>1</h><i>2</i><j>1</j><k>12</k><l>101</l><m>200</m><o>0</o><p>100</p><q>1</q><r>-5</r></base>

<base><a>DETAILED</a><b>Repeat</b><c>www.fourthsite.com</c><d>/music/store/files123/</d><e>pop.asp</e><f>?download=ring498</f><g>12</g><h>1</h><i>2</i><j>1</j><k>12</k><l>101</l><m>200</m><o>0</o><p>100</p><q>1</q><r>-5</r></base>

..

</REPORT>



My thanks,

Jez

Hello Jez,

Based on the code for the dataset extension, it uses ReadXml mehtod to read
data from xml file.

internal void CreateDataSet(string schemaFile)
{

// Open an XML doc to hold the data.
XmlDocument xmlDoc = new XmlDocument();
// Create the DataSet.
DataSet ds = new DataSet("Customers");
// Create the schema for the DataSet.
ds.ReadXmlSchema(schemaFile);
// Parse the command text string for the files.
string[] parameters = this.ParseCmdText();
// Get the XML data and
// merge it into the DataSet.
try
{
for(int i=0;i<parameters.GetLength(0);i++)
{
DataSet tempDs = new DataSet();
tempDs.ReadXml(parameters[i]);
ds.Merge(tempDs);
}

}
catch (Exception e)
{
throw new Exception(e.Message);
}

// Set the DataSet variable used in
// the rest of the DataReader members
// to the one just produced.
_ds = ds;
// Set the current row to -1
// to prepare for reading.
_currentRow = -1;

}

You may want to use this function in your own test if the big file take
much time. Also, because reporting services rending all pages before
returning HTML stream to the client, it may take a lot of of time. You may
want to use some constraint to query less records or less columns to make
the processing/rendering time less.


Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.




--------------------
| From: "jez" <jnel@nospam.nospam>
| Subject: Performance problem with RS XML data source
| Date: Thu, 7 Apr 2005 18:30:47 +0100
| Lines: 68
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| Message-ID: <etpwKe5OFHA.3748@TK2MSFTNGP10.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: host217-42-145-128.range217-42.btcentralplus.com
217.42.145.128
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:40305
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Hi,
|
|
|
| I am experiencing unacceptable performance when reading XML files using a
| custom XML data processing extensions with RS on SS 2000.
|
|
|
| The XML data processing extensions I have used is the one on the MSDN
site
|
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/ht
ml/RSDSetEx3.asp)
|
|
|
| This works fine with low volumes of data but as the number of records
| increase in the XML file the time taken to open the file increases
| exponentially. The timings I have recorded are:
|
|
|
| records seconds
|
| 1,000 = 1
|
| 2,500 = 3
|
| 5,000 = 6
|
| 10,000 = 22
|
| 15,000 = 75
|
|
|
|
|
| The largest XML file I have contains 40,000 records and takes about 1200
| secs (20 mins) to open which is a show stopper.
|
|
|
| Has anyone else run into this problem and if so how did you resolve it?
|
|
|
| An example of XML File
|
|
|
| <REPORT>
|
|
<base><a>DETAILED</a><b>Repeat</b><c>www.fourthsite.com</c><d>/music/store/f
iles123/</d><e>pop.asp</e><f>?download=ring498</f><g>12</g><h>1</h><i>2</i><
j>1</j><k>12</k><l>101</l><m>200</m><o>0</o><p>100</p><q>1</q><r>-5</r></bas
e>
|
|
<base><a>DETAILED</a><b>Repeat</b><c>www.fourthsite.com</c><d>/music/store/f
iles123/</d><e>pop.asp</e><f>?download=ring498</f><g>12</g><h>1</h><i>2</i><
j>1</j><k>12</k><l>101</l><m>200</m><o>0</o><p>100</p><q>1</q><r>-5</r></bas
e>
|
|
<base><a>DETAILED</a><b>Repeat</b><c>www.fourthsite.com</c><d>/music/store/f
iles123/</d><e>pop.asp</e><f>?download=ring498</f><g>12</g><h>1</h><i>2</i><
j>1</j><k>12</k><l>101</l><m>200</m><o>0</o><p>100</p><q>1</q><r>-5</r></bas
e>
|
| .
|
| </REPORT>
|
|
|
| My thanks,
|
| Jez
|
|
|

Hello Jez,

I included the code from the MSDN site is just to describe that it will
take much time to read/process big xml file into dataset. If csv type
loading is faster, the whole performance might be better. However, if you
import the data into SQL server directly, I believe it will be benefit for
performance.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.




--------------------
| From: "jez" <jnel@nospam.nospam>
| References: <etpwKe5OFHA.3748@TK2MSFTNGP10.phx.gbl>
<yVaWd4$OFHA.4360@TK2MSFTNGXA01.phx.gbl>
| Subject: Re: Performance problem with RS XML data source
| Date: Mon, 11 Apr 2005 10:16:59 +0100
| Lines: 190
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| Message-ID: <#day4cnPFHA.708@TK2MSFTNGP12.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: 213.86.115.1
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:40485
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Peter,
|
| I had a look at the code sample you provided and it appears to be the
same
| as the code on the MSDN site. Therefore using this code for the large
file
| (40,000 rows) will give identical performance. Is this the example you
| meant to send?
|
| Is it the ReadXml method that is causing the problem? If I changed to
file
| format to CSV would I see better performance?
|
| Thanks,
| Jez
|
[quoted text, click to view]
| > Hello Jez,
| >
| > Based on the code for the dataset extension, it uses ReadXml mehtod to
| > read
| > data from xml file.
| >
| > internal void CreateDataSet(string schemaFile)
| > {
| >
| > // Open an XML doc to hold the data.
| > XmlDocument xmlDoc = new XmlDocument();
| > // Create the DataSet.
| > DataSet ds = new DataSet("Customers");
| > // Create the schema for the DataSet.
| > ds.ReadXmlSchema(schemaFile);
| > // Parse the command text string for the files.
| > string[] parameters = this.ParseCmdText();
| > // Get the XML data and
| > // merge it into the DataSet.
| > try
| > {
| > for(int i=0;i<parameters.GetLength(0);i++)
| > {
| > DataSet tempDs = new DataSet();
| > tempDs.ReadXml(parameters[i]);
| > ds.Merge(tempDs);
| > }
| >
| > }
| > catch (Exception e)
| > {
| > throw new Exception(e.Message);
| > }
| >
| > // Set the DataSet variable used in
| > // the rest of the DataReader members
| > // to the one just produced.
| > _ds = ds;
| > // Set the current row to -1
| > // to prepare for reading.
| > _currentRow = -1;
| >
| > }
| >
| > You may want to use this function in your own test if the big file take
| > much time. Also, because reporting services rending all pages before
| > returning HTML stream to the client, it may take a lot of of time. You
may
| > want to use some constraint to query less records or less columns to
make
| > the processing/rendering time less.
| >
| >
| > Regards,
| >
| > Peter Yang
| > MCSE2000/2003, MCSA, MCDBA
| > Microsoft Online Partner Support
| >
| > When responding to posts, please "Reply to Group" via your newsreader so
| > that others may learn and benefit from your issue.
| >
| > =====================================================
| >
| > This posting is provided "AS IS" with no warranties, and confers no
| > rights.
| >
| >
| >
| >
| > --------------------
| > | From: "jez" <jnel@nospam.nospam>
| > | Subject: Performance problem with RS XML data source
| > | Date: Thu, 7 Apr 2005 18:30:47 +0100
| > | Lines: 68
| > | X-Priority: 3
| > | X-MSMail-Priority: Normal
| > | X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| > | X-RFC2646: Format=Flowed; Original
| > | Message-ID: <etpwKe5OFHA.3748@TK2MSFTNGP10.phx.gbl>
| > | Newsgroups: microsoft.public.sqlserver.reportingsvcs
| > | NNTP-Posting-Host: host217-42-145-128.range217-42.btcentralplus.com
| > 217.42.145.128
| > | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
| > | Xref: TK2MSFTNGXA01.phx.gbl
| > microsoft.public.sqlserver.reportingsvcs:40305
| > | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
| > |
| > | Hi,
| > |
| > |
| > |
| > | I am experiencing unacceptable performance when reading XML files
using
| > a
| > | custom XML data processing extensions with RS on SS 2000.
| > |
| > |
| > |
| > | The XML data processing extensions I have used is the one on the MSDN
| > site
| > |
| >
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/ht
| > ml/RSDSetEx3.asp)
| > |
| > |
| > |
| > | This works fine with low volumes of data but as the number of records
| > | increase in the XML file the time taken to open the file increases
| > | exponentially. The timings I have recorded are:
| > |
| > |
| > |
| > | records seconds
| > |
| > | 1,000 = 1
| > |
| > | 2,500 = 3
| > |
| > | 5,000 = 6
| > |
| > | 10,000 = 22
| > |
| > | 15,000 = 75
| > |
| > |
| > |
| > |
| > |
| > | The largest XML file I have contains 40,000 records and takes about
1200
| > | secs (20 mins) to open which is a show stopper.
| > |
| > |
| > |
| > | Has anyone else run into this problem and if so how did you resolve
it?
| > |
| > |
| > |
| > | An example of XML File
| > |
| > |
| > |
| > | <REPORT>
| > |
| > |
| >
<base><a>DETAILED</a><b>Repeat</b><c>www.fourthsite.com</c><d>/music/store/f
| >
iles123/</d><e>pop.asp</e><f>?download=ring498</f><g>12</g><h>1</h><i>2</i><
| >
j>1</j><k>12</k><l>101</l><m>200</m><o>0</o><p>100</p><q>1</q><r>-5</r></bas
| > e>
| > |
| > |
| >
<base><a>DETAILED</a><b>Repeat</b><c>www.fourthsite.com</c><d>/music/store/f
| >
iles123/</d><e>pop.asp</e><f>?download=ring498</f><g>12</g><h>1</h><i>2</i><
| >
j>1</j><k>12</k><l>101</l><m>200</m><o>0</o><p>100</p><q>1</q><r>-5</r></bas
| > e>
| > |
| > |
| >
<base><a>DETAILED</a><b>Repeat</b><c>www.fourthsite.com</c><d>/music/store/f
| >

Peter,

I had a look at the code sample you provided and it appears to be the same
as the code on the MSDN site. Therefore using this code for the large file
(40,000 rows) will give identical performance. Is this the example you
meant to send?

Is it the ReadXml method that is causing the problem? If I changed to file
format to CSV would I see better performance?

Thanks,
Jez

[quoted text, click to view]