Performance Tuning for Row-by-Row Update Statement

hi

For an unavoidable reason, I have to use row-by-row processing
(update) on a temporary table to update a history table every day.
I have around 60,000 records in temporary table and about 2 million in
the history table.

Could any one please suggest different methods to imporve the runtime
of the query?

Would highly appreciate!Is the row-by-row processing done in a cursor? Must you update exactly one
row at a time (if so, why?) or would it be acceptable to update 2,3 or 50
rows at a time?

You can use SET ROWCOUNT and a loop to fine-tune the batch size of rows to
be updated. Bigger batches should improve performance over updating single
rows.

SET ROWCOUNT 50

WHILE 1=1
BEGIN

UPDATE SomeTable
SET ...
WHERE /* row not already updated */

IF @.@.ROWCOUNT=0
BREAK

END

SET ROWCOUNT 0

--
David Portas
SQL Server MVP
--|||Is the row-by-row processing done in a cursor? Must you update exactly one
row at a time (if so, why?) or would it be acceptable to update 2,3 or 50
rows at a time?

You can use SET ROWCOUNT and a loop to fine-tune the batch size of rows to
be updated. Bigger batches should improve performance over updating single
rows.

SET ROWCOUNT 50

WHILE 1=1
BEGIN

UPDATE SomeTable
SET ...
WHERE /* row not already updated */

IF @.@.ROWCOUNT=0
BREAK

END

SET ROWCOUNT 0

--
David Portas
SQL Server MVP
--|||"Muzamil" <muzamil@.hotmail.com> wrote in message
news:5a998f78.0405211023.24b40513@.posting.google.c om...
> hi
> For an unavoidable reason, I have to use row-by-row processing
> (update) on a temporary table to update a history table every day.
> I have around 60,000 records in temporary table and about 2 million in
> the history table.

Not much you can do if you absolutely HAVE to do row-by-row updating.

You might want to post DDL, etc. so others can take a crack at it. I've
seen many times someone will say, "I have to use a cursor", "I have to
update one row at a time" and then someone posts a much better/faster
solution.

Also, how are you handling transactions? Explicitly or implicitely? If
you're doing them implicitely, are you wrapping each update in its own, or
can up batch say 20 updates?

Finally, where's your log files? Separate physical drives?

> Could any one please suggest different methods to imporve the runtime
> of the query?
> Would highly appreciate!|||Hi
Thanks for your reply.

The row-by-row update is mandatory becuase the leagacy system is
sending us the information such as "Add", "Modify" or "delete" and
this information HAS to be processed in the same order otherwise we'll
get the erroneous data.
I know it's a dumb way of doing things but this is what our and their
IT department has chosen to be correct way of action after several
meetings. Hence the batch idea will not work here.

I am not using Cursors, instead I am using the loop based on the
primary key.

The log files are on different drives.

I've also tried using "WITH (ROWLOCK)" in the update statement but
it's not helping much.

Can you please still throw in some idea? Would be great help!

Thanks

"Greg D. Moore \(Strider\)" <mooregr_deleteth1s@.greenms.com> wrote in message news:<tOxrc.234090$M3.65389@.twister.nyroc.rr.com>...
> "Muzamil" <muzamil@.hotmail.com> wrote in message
> news:5a998f78.0405211023.24b40513@.posting.google.c om...
> > hi
> > For an unavoidable reason, I have to use row-by-row processing
> > (update) on a temporary table to update a history table every day.
> > I have around 60,000 records in temporary table and about 2 million in
> > the history table.
> Not much you can do if you absolutely HAVE to do row-by-row updating.
> You might want to post DDL, etc. so others can take a crack at it. I've
> seen many times someone will say, "I have to use a cursor", "I have to
> update one row at a time" and then someone posts a much better/faster
> solution.
> Also, how are you handling transactions? Explicitly or implicitely? If
> you're doing them implicitely, are you wrapping each update in its own, or
> can up batch say 20 updates?
> Finally, where's your log files? Separate physical drives?
>
> > Could any one please suggest different methods to imporve the runtime
> > of the query?
> > Would highly appreciate!|||Muzamil (muzamil@.hotmail.com) writes:
> The row-by-row update is mandatory becuase the leagacy system is
> sending us the information such as "Add", "Modify" or "delete" and
> this information HAS to be processed in the same order otherwise we'll
> get the erroneous data.

Ouch. Life is cruel, sometimes.

I wonder what possibilities there could be to find parallel streams,
that is updates that could be performed independently. Maybe you
can modify 10 rows at a time then. But it does not sound like a very
easy thing to do.

Without knowing the details of the system, it is difficult to give
much advice. But any sort of pre-aggregation you can do, is probably
going to pay back.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Details of the system:
The leagcy system sends us records flagged with "Add", "modify" or
"delete".
The purpose of these flags is self-explnatory. But the fun began when
we noticed that within same file , legacy system sends us "Add" and
then "Modify". Thus, we were left with no other option except to do
row-by-row processing.
We came up with the following logic:

a)If records StatusFlag is A' and records key does not exist in
DataWareHouse's Table, then the record is inserted into
DataWareHouse's Table.

b)If records StatusFlag is A', but records key exists in
DataWareHouse's Table, then the record is marked as invalid and will
be inserted into InvalidTable..

c)If records StatusFlag is M' and records key exists in
DataWareHouse's Table and record is active, then the corresponding
record in DataWareHouse's Table will be updated.

d)If records StatusFlag is M' and records key exists in
DataWareHouse's Table but record is inactive, then the record is
marked as invalid and will be inserted into InvalidTable.

e)If records StatusFlag is M' and records key does not exist in
DataWareHouse's Table, then the record is marked as invalid and will
be inserted into InvalidTable.

f)If records StatusFlag is D' and records key exists in
DataWareHouse's Table and record is active, then the corresponding
record in DataWareHouse's Table will be updated as inactive.

g)If records StatusFlag is D' and records key exists in
DataWareHouse's Table but record is inactive, then the record is
marked as invalid and will be inserted into InvalidTable.

h)If records StatusFlag is D' and records key does not exist in
DataWareHouse's Table, then the record is marked as invalid and will
be inserted into InvalidTable.

This logic takes care of ALL the anomalies we were facing before but
at the cost of long processing time.

I await your comments.

Thanks

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns94F53BF51111Yazorman@.127.0.0.1>...
> Muzamil (muzamil@.hotmail.com) writes:
> > The row-by-row update is mandatory becuase the leagacy system is
> > sending us the information such as "Add", "Modify" or "delete" and
> > this information HAS to be processed in the same order otherwise we'll
> > get the erroneous data.
> Ouch. Life is cruel, sometimes.
> I wonder what possibilities there could be to find parallel streams,
> that is updates that could be performed independently. Maybe you
> can modify 10 rows at a time then. But it does not sound like a very
> easy thing to do.
> Without knowing the details of the system, it is difficult to give
> much advice. But any sort of pre-aggregation you can do, is probably
> going to pay back.|||Muzamil (muzamil@.hotmail.com) writes:
> Details of the system:
> The leagcy system sends us records flagged with "Add", "modify" or
> "delete".
> The purpose of these flags is self-explnatory. But the fun began when
> we noticed that within same file , legacy system sends us "Add" and
> then "Modify". Thus, we were left with no other option except to do
> row-by-row processing.
> We came up with the following logic:

Hm, you might be missing a few cases. What if you get an Add, and record
exists in DW, but is marked inactive? With your current logic, the
input record moved to the Invalid table.

And could that feediug system be as weird as to send Add, Modify, Delete,
and Add again? Well, for a robust solution this is what we should assume.

It's a tricky problem, and I was about to defer the problem, when I
recalled a solution that colleague did for one of our stored procedures.
The secret word for tonight is bucketing! Assuming that there are
only a couple of input records for each key value, this should be
an excellent solution. You create buckets, so that each bucket has
at most one row per key value. Here is an example on how to do it:

UPDATE inputtbl
SET bucket = (SELECT count(*)
FROM inputtbl b
WHERE a.keyval = b.keyval
AND a.rownumber < b.rownumber) + 1
FROM inputtbl a

input.keyval is the keys for the records in the DW table. Rownumber
is a column which as describes the processing order. I assume that
you have such a column.

So now you can iterate over the buckets, and for each bucket, you can do
set- based processing. You still have to iterate, but instead over 60000
rows, only over a couple of buckets.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I think I was not articulate enough to convey the logic properly.
Anyways, thanks to everyone for your help.
By using the ROWLOCK and proper indexes, I was ale to reduce the time considerably.

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns94F6821D6ABYazorman@.127.0.0.1>...
> Muzamil (muzamil@.hotmail.com) writes:
> > Details of the system:
> > The leagcy system sends us records flagged with "Add", "modify" or
> > "delete".
> > The purpose of these flags is self-explnatory. But the fun began when
> > we noticed that within same file , legacy system sends us "Add" and
> > then "Modify". Thus, we were left with no other option except to do
> > row-by-row processing.
> > We came up with the following logic:
> Hm, you might be missing a few cases. What if you get an Add, and record
> exists in DW, but is marked inactive? With your current logic, the
> input record moved to the Invalid table.
> And could that feediug system be as weird as to send Add, Modify, Delete,
> and Add again? Well, for a robust solution this is what we should assume.
> It's a tricky problem, and I was about to defer the problem, when I
> recalled a solution that colleague did for one of our stored procedures.
> The secret word for tonight is bucketing! Assuming that there are
> only a couple of input records for each key value, this should be
> an excellent solution. You create buckets, so that each bucket has
> at most one row per key value. Here is an example on how to do it:
> UPDATE inputtbl
> SET bucket = (SELECT count(*)
> FROM inputtbl b
> WHERE a.keyval = b.keyval
> AND a.rownumber < b.rownumber) + 1
> FROM inputtbl a
> input.keyval is the keys for the records in the DW table. Rownumber
> is a column which as describes the processing order. I assume that
> you have such a column.
> So now you can iterate over the buckets, and for each bucket, you can do
> set- based processing. You still have to iterate, but instead over 60000
> rows, only over a couple of buckets.|||Muzamil (muzamil@.hotmail.com) writes:
> I think I was not articulate enough to convey the logic properly.
> Anyways, thanks to everyone for your help. By using the ROWLOCK and
> proper indexes, I was ale to reduce the time considerably.

Good indexes is always useful, and of course for iterative processing
it is even more imperative, since the cost a less-than-optimal plan
is multiplied.

I'm just curious, would my bucketing idea be applicable to your problem?
It should give you even more speed, but if you have good-ebough now, there
is of course no reason to spend more time on it.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Performance problem with RS XML data source

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,
JezHello 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
|
|
||||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
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:yVaWd4$OFHA.4360@.TK2MSFTNGXA01.phx.gbl...
> 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
|
| "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
| news:yVaWd4$OFHA.4360@.TK2MSFTNGXA01.phx.gbl...
| > 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
| > |
| > |
| > |
| >
|
|
|