Poor perfomance with Spreadsheet OWC 10 with more than 200 columns

M

markatt

We are developping a web application based on SQL Server 8.00 and ASP.

We need to create a Report with OWC Spreadsheet 10 with 210 columns
and 60 rows. It needs 7 minutes to be load. That's very sad!!!! The
html page is 310 kb, so its not so heavy.

We have already created other reports with less number of columns (60
cols) but much more rows (up to 1200). They need something like 1m 1/2
to be load on the page.

Is it the number of columns to cause this low performance?
(The same report with only 60 columns need only 1 minutes to load).

Is there a way to reach more good performance?


We are loading the OWC with the HTML Data Param using Response.Write
to design the html table, reading from a Recordset.


This is an example of code we are using to create the OWC for all our
reports.

<OBJECT id=objMyExcel style="LEFT: 0px; WIDTH: 100%; CURSOR: hand;
TOP: 0px; HEIGHT: 289px"
classid="clsid:0002E551-0000-0000-C000-000000000046" style="WIDTH:
100%" VIEWASTEXT>

<param name="HTMLData" value="

response.write "<table x:str border='0' cellpadding='0'
cellspacing='0' width='2879'
style='border-collapse:collapse;width:2169pt'>"

While Not rs_Rep.EOF
Response.Write "<TR>"
For intCount = 0 To intTotalColumns
arrRowInfo = Split(arrRecordsetInfo(intCount),",",-1,1)

Response.Write "<TD"
Response.Write ">" & Replace(CStr("" &
rs_Rep(Trim(arrRowInfo(2)))),",",".") & "</TD>"

next
Response.Write "</TR>"
wend
response.write "</TABLE>"

Thanks for the help,
Marco
 
A

Alvin Bruney [MVP]

No, that is not normal. Can you eliminate the loop as the performance
bottle-neck? Are you up to date on your service packs for IE, windows, and
OWC?
about 1500 rows, with 15 columns will load in about a second according to my
tests.
 
M

markatt

Thanks for reply.

How can we improve our performance?

Here are our client settings:
All crytical security patches are installed
IE 6.0.2800.1106CO
windows: windows 2000 prof Service Pack 4 512 MB ram
OWC Version: 10.0.0.2621


How can we eliminate the loop?
Referring to our code, the first 'while' is for the row and the 'for'
cycle is for the columns.
We get the values from a recordset (and we need to cycle it) and we
wright these values with the 'Response.Write'. Is there a better way?
Can you give us some examples?

How have you implemented your code to load 1500 rows in 1 second? Do
you have read from a recordet or fix values?

Thanks again,
Marco





Alvin Bruney said:
No, that is not normal. Can you eliminate the loop as the performance
bottle-neck? Are you up to date on your service packs for IE, windows, and
OWC?
about 1500 rows, with 15 columns will load in about a second according to my
tests.

--
Regards,
Alvin Bruney
[ASP.NET MVP http://mvp.support.microsoft.com/default.aspx]
Got tidbits? Get it here... http://tinyurl.com/27cok
markatt said:
We are developping a web application based on SQL Server 8.00 and ASP.

We need to create a Report with OWC Spreadsheet 10 with 210 columns
and 60 rows. It needs 7 minutes to be load. That's very sad!!!! The
html page is 310 kb, so its not so heavy.

We have already created other reports with less number of columns (60
cols) but much more rows (up to 1200). They need something like 1m 1/2
to be load on the page.

Is it the number of columns to cause this low performance?
(The same report with only 60 columns need only 1 minutes to load).

Is there a way to reach more good performance?


We are loading the OWC with the HTML Data Param using Response.Write
to design the html table, reading from a Recordset.


This is an example of code we are using to create the OWC for all our
reports.

<OBJECT id=objMyExcel style="LEFT: 0px; WIDTH: 100%; CURSOR: hand;
TOP: 0px; HEIGHT: 289px"
classid="clsid:0002E551-0000-0000-C000-000000000046" style="WIDTH:
100%" VIEWASTEXT>

<param name="HTMLData" value="

response.write "<table x:str border='0' cellpadding='0'
cellspacing='0' width='2879'
style='border-collapse:collapse;width:2169pt'>"

While Not rs_Rep.EOF
Response.Write "<TR>"
For intCount = 0 To intTotalColumns
arrRowInfo = Split(arrRecordsetInfo(intCount),",",-1,1)

Response.Write "<TD"
Response.Write ">" & Replace(CStr("" &
rs_Rep(Trim(arrRowInfo(2)))),",",".") & "</TD>"

next
Response.Write "</TR>"
wend
response.write "</TABLE>"

Thanks for the help,
Marco
 
A

Alvin Bruney [MVP]

in my last project, i used a dataset obtained from a database. and i push
the dataset to the page where the final worksheet tags are added in on the
client. then i load into the spreadsheet using XMLLOAD. It may be that the
HTML portion is what is slowing it down. as a quick test, you can write it
out as a CSV file and try loading that file instead to see how fast it
loads. If you still have the same problem, the issue lies somewhere else

--
Regards,
Alvin Bruney
[ASP.NET MVP http://mvp.support.microsoft.com/default.aspx]
Got tidbits? Get it here... http://tinyurl.com/27cok
markatt said:
Thanks for reply.

How can we improve our performance?

Here are our client settings:
All crytical security patches are installed
IE 6.0.2800.1106CO
windows: windows 2000 prof Service Pack 4 512 MB ram
OWC Version: 10.0.0.2621


How can we eliminate the loop?
Referring to our code, the first 'while' is for the row and the 'for'
cycle is for the columns.
We get the values from a recordset (and we need to cycle it) and we
wright these values with the 'Response.Write'. Is there a better way?
Can you give us some examples?

How have you implemented your code to load 1500 rows in 1 second? Do
you have read from a recordet or fix values?

Thanks again,
Marco





Alvin Bruney said:
No, that is not normal. Can you eliminate the loop as the performance
bottle-neck? Are you up to date on your service packs for IE, windows,
and
OWC?
about 1500 rows, with 15 columns will load in about a second according to
my
tests.

--
Regards,
Alvin Bruney
[ASP.NET MVP http://mvp.support.microsoft.com/default.aspx]
Got tidbits? Get it here... http://tinyurl.com/27cok
markatt said:
We are developping a web application based on SQL Server 8.00 and ASP.

We need to create a Report with OWC Spreadsheet 10 with 210 columns
and 60 rows. It needs 7 minutes to be load. That's very sad!!!! The
html page is 310 kb, so its not so heavy.

We have already created other reports with less number of columns (60
cols) but much more rows (up to 1200). They need something like 1m 1/2
to be load on the page.

Is it the number of columns to cause this low performance?
(The same report with only 60 columns need only 1 minutes to load).

Is there a way to reach more good performance?


We are loading the OWC with the HTML Data Param using Response.Write
to design the html table, reading from a Recordset.


This is an example of code we are using to create the OWC for all our
reports.

<OBJECT id=objMyExcel style="LEFT: 0px; WIDTH: 100%; CURSOR: hand;
TOP: 0px; HEIGHT: 289px"
classid="clsid:0002E551-0000-0000-C000-000000000046" style="WIDTH:
100%" VIEWASTEXT>

<param name="HTMLData" value="

response.write "<table x:str border='0' cellpadding='0'
cellspacing='0' width='2879'
style='border-collapse:collapse;width:2169pt'>"

While Not rs_Rep.EOF
Response.Write "<TR>"
For intCount = 0 To intTotalColumns
arrRowInfo = Split(arrRecordsetInfo(intCount),",",-1,1)

Response.Write "<TD"
Response.Write ">" & Replace(CStr("" &
rs_Rep(Trim(arrRowInfo(2)))),",",".") & "</TD>"

next
Response.Write "</TR>"
wend
response.write "</TABLE>"

Thanks for the help,
Marco
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top