ASP - Writing Database Info To Excel - Speed Issues 5

[cLFlaVA]

Hi all. I am currently developing an ASP application that communicates with an Oracle 9i database. Throught DotNetGnat's suggestions, I use ASP to change the ContentType of the Response to excel, and then write a simple table to generate an Excel spreadsheet.

I am running into performance issues, however. I receive a script timeout - the script timeout of the server was set to 90 seconds. So, I programatically changed this to 200, generate the output, then set back to 90. However, this is still not enough time - it still times out. I am wondering if any of you can suggest any ways I could speed up my code, or any other ideas you may have. Thanks a lot for your time.

Private Function getOutput( strSql )
    Dim objRS
    Dim strOutput

    Set objRS = objKintanaDatabase.CreateDynaset( strSql, cInt( 0 ) )

    If Not objRS.EOF Then
        strOutput = "<table>"
        
        strOutput = strOutput & "<tr>"
            strOutput = strOutput & "<th>Project Name</th>"
            strOutput = strOutput & "<th>IT Plan #</th>"
            strOutput = strOutput & "<th>Budget Entity</th>"
            strOutput = strOutput & "<th>Work Item Type</th>"
            strOutput = strOutput & "<th>Work Item</th>"
            strOutput = strOutput & "<th>Business Unit</th>"
            strOutput = strOutput & "<th>Resource</th>"
            strOutput = strOutput & "<th>Resource Type</th>"
            strOutput = strOutput & "<th>Time Period</th>"
            strOutput = strOutput & "<th>Actual Time</th>"
        strOutput = strOutput & "</tr>"

        Do While Not objRS.EOF
            strOutput = strOutput & "<tr>"
            strOutput = strOutput & "<td>" & objRS.Fields( "project_name" ) & "</td>"
            strOutput = strOutput & "<td>" & objRS.Fields( "it_plan_number" ) & "</td>"
            strOutput = strOutput & "<td>" & objRS.Fields( "budget_entity" ) & "</td>"
            strOutput = strOutput & "<td>" & objRS.Fields( "work_item_type" ) & "</td>"
            strOutput = strOutput & "<td>" & objRS.Fields( "work_item" ) & "</td>"
            strOutput = strOutput & "<td>" & objRS.Fields( "business_unit" ) & "</td>"
            strOutput = strOutput & "<td>" & objRS.Fields( "user_name" ) & "</td>"
            strOutput = strOutput & "<td>" & objRS.Fields( "resource_type" ) & "</td>"
            strOutput = strOutput & "<td>" & objRS.Fields( "time_period" ) & "</td>"
            strOutput = strOutput & "<td>" & objRS.Fields( "actual_time" ) & "</td>"
            strOutput = strOutput & "</tr>"
            objRS.MoveNext
        Loop

        strOutput = strOutput & "</table>"
    End If
    
    Set objRS = Nothing

    getOutput = strOutput
End Function

p.s. the sql statement I'm running in this instance returns 26,929 rows.

Thanks again,

Cory

*cLFlaVA
----------------------------
[tt]your mom goes to college[/tt]
[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[theniteowl]

One possible solution is to pull down all the data to an array and later feed it to the Excel file.
Not sure if you will run into memory problems with that many rows in an array or not, guess it depends on the content.
I do not know about Oracle but in MS SQL you can use the GetRows command to pull down all of your data at once then you can feed it out to your file rather than looping back and forth to the database.

 

[DotNetGnat]

can you show us your sql statement?

-DNG

 

[cLFlaVA]

sure...

  select p.project_name as project_name
       , v.it_plan as it_plan_number
       , v.budget_entity as budget_entity
       , v.task_type as work_item_type
       , v.task_name as work_item
       , v.prime_bus_unit as business_unit
       , u.full_name as user_name
       , v.emp_type as resource_type
       , t.period_name as time_period
       , sum(v.actual_time) as actual_time
    from gcr_ktmg_actual_time_wit_v v join kdrv_projects p
      on v.project_id = p.project_id and
         p.project_id is not null join knta_users u
      on v.user_id = u.user_id and 
         u.user_id is not null join ktmg_periods t
      on v.period_id = t.period_id and 
         t.period_id is not null 
   where v.period_id >= 30050 and 
         v.period_id <= 30099 
group by p.project_name
       , v.it_plan
       , v.budget_entity
       , v.task_type
       , v.task_name
       , v.prime_bus_unit
       , u.full_name
       , v.emp_type
       , t.period_name

*cLFlaVA
----------------------------
[tt]your mom goes to college[/tt]
[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[DotNetGnat]

try removing the group by clause and see how fast the results show up...let us know if there is any difference...

-DNG

 

[cLFlaVA]

if i remove the grouping i have to also remove the sum() function, and if i do that i will come close to breaking the bank with the excel row limit of about 65000.

*cLFlaVA
----------------------------
[tt]your mom goes to college[/tt]
[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[cLFlaVA]

how would i go about storing the result in an array? i'm not too familiar with asp nor it's communication with oracle...

*cLFlaVA
----------------------------
[tt]your mom goes to college[/tt]
[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[theniteowl]

Check out this link:

http://www.4guysfromrolla.com/webtech/070500-1.shtml

It grabs all the rows as determined in the query and writes it into a two dimensioned array.
This example then displays the rows one page at a time with forward/back buttons but you would just output the data as you were doing but in a loop that occurs after you have closed your recordset.

By getting the data all at once you save the time it takes to process that row in your function before it goes back to retrieve the next row. With 29K+ rows returned that is a lot of tiny delays while processing data before it gets more data.

The best method would be to write a stored procedure on the database end to return your records to you as that is generally much faster. I have not done much with stored procedures and have no experience with Oracle but it is something to look into.

 

[Kendel]

<form name='yourForm' method='post' action='yourExcell.asp'>
<table>
<tr>
<%
Do While Not objRS.EOF
     response.write "<td><input type='text' name='project' value='" & objRS.Fields( "project_name" ) & "'></td>"
     response.write "<td><input type='text' name='plannumber' value='" & objRS.Fields( "it_plan_number" ) & "'></td>"
     response.write "<td><input type='text' name='budget' value='" & objRS.Fields( "budget_entity" ) & "'></td>"
     response.write "<td><input type='text' name='itemtype' value='" & objRS.Fields( "work_item_type" ) & "'></td>"
     response.write "<td><input type='text' name='item' value='" & objRS.Fields( "work_item" ) & "'></td>"
     response.write "<td><input type='text' name='username' value='" & objRS.Fields( "user_name" ) & "'></td>"
     response.write "<td><input type='text' name='resourcetype' value='" & objRS.Fields( "resource_type" ) & "'></td>"
     response.write "<td><input type='text' name='period' value='" & objRS.Fields( "time_period" ) & "'></td>"
     response.write "<td><input type='text' name='actualTime' value='" & objRS.Fields( "actual_time" ) & "'></td>"	

    objRS.MoveNext
Loop
%>
</tr>
<tr><td colspan=10><input type='submit' name='btnSubmit' value='Export to Excell'></td></tr>
</table>

-----

On your Excell page:

project = Request("project")
proName = split(project,", ")

planNumber= Request("plannumber")
planNum = split(planNumber,", ")

.... etc


For i=0 to UBound(proName)
   response.write(proName(i) & ", ")
   response.write(planNum (i) & ", ")
   .......etc

   response.write("<br>")
Next

 

[DotNetGnat]

as far as my experience i did not have any problem displaying 26,929 rows on a excel sheet...i mean to say that my asp page did not timeout..i just was wondering if there is anyway to improve the query...

And yes getrows() method can improve the performance in this situation...

try thsi link for the details:

http://www.asp101.com/samples/viewasp.asp?file=db_getrows.asp

-DNG

 

[cLFlaVA]

am i mistaken, or do the two links above refer to MS SQL Server databases? Will getrows work with the result returned from the CreateDynaset function?

*cLFlaVA
----------------------------
[tt]your mom goes to college[/tt]
[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[DotNetGnat]

Will getrows work with the result returned from the CreateDynaset function?

YES

-DNG

 

[cLFlaVA]

Thanks to all who helped.

After a bit of tweaking and the utilization of the GetRows method, I got this working. Something else that was very helpful:

I changed the storage into immediate Response.Write. In other words, instead of storing the output in the strOutput variable, I just immediately used Response.Write. This seemed to speed things up - which confused me.

I thought it would be the opposite - so I wrote this script:

<%

Dim intStartOne
Dim intEndOne
Dim intStartTwo
Dim intEndTwo
Dim strOut

Dim intOne
Dim intTwo

intStartOne = Timer

For intI = 0 to 100000
    Response.Write " "
Next

Response.Write "<br /><br />"

intEndOne = Timer

intOne = (intEndOne - intStartOne)

intStartTwo = Timer

For intI = 0 to 100000
    strOut = strOut & " "
Next

Response.Write strOut & "<br /><br />"

intEndTwo = Timer

intTwo = (intEndTwo - intStartTwo)

If intTwo > intOne Then
    Response.Write "variable takes " & intTwo / intOne & " longer than immediate write"
Else
    Response.Write "immediate write takes " & intOne / intTwo & "  longer than variable"
End If

%>

Which verified my findings. Does this make sense to you all? Or does it have something to do with the fact that I'm testing on a laptop?

Thanks again everyone.

*cLFlaVA
----------------------------
[tt]your mom goes to college[/tt]
[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[DotNetGnat]

Read this:

http://www.4guysfromrolla.com/webtech/010202-1.shtml

-DNG

 

[cLFlaVA]

thanks again...

*cLFlaVA
----------------------------
[tt]your mom goes to college[/tt]
[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[Kendel]

Very interesting! I will keep this in mind. Thanks for the link DNG.

How can I find out what version of ASP I am using?

 

[DotNetGnat]

cLFlaVA,

Thanks for the code and bringing up this interesting topic...

Star for you.

-DNG

 

[DotNetGnat]

->ASP 2.0 was installed with IIS4 (NT4 & PWS).
->To use ASP3.0 you need IIS5(win2k).
->ASP 1.0 is incapable of executing sql queries on a database.

run this piece of code:

<%= ScriptEngine %>      
<%= ScriptEngineMajorVersion %>  
<%= ScriptEngineMinorVersion %>

this tells you what version of vbscript you are running

-DNG

 

[DotNetGnat]

also take a look at this:

http://www.4guysfromrolla.com/webtech/010700-1.shtml

-DNG

 

[Kendel]

Thanks again DNG.