views:

41

answers:

2

We are using it to return a file for an export. When we run this export on a lot of records, it takes close to 10 minutes to run. Here is a code snippet of the code that actually calls the File() method and returns the result.

Public Function Export(ByVal ID As Integer) As FileContentResult
  Dim str As String = String.Empty
  Dim data() As Byte
  Dim r As New ExportResult
  Dim Test As New TestConnection(WebUtil.UserToken)

  'This line is important coz IE download was prevented without this.
  ControllerContext.HttpContext.Response.ClearHeaders()

  r = Test.ExportFile(ID)
  data = Encoding.ASCII.GetBytes(r.ResponseString)

  Return File(data, "text/plain", r.DefaultFileName)

End Function

The actual ExportFile method takes in an ID, calls another method, which gets a bunch of records from the database, performs a bunch of calculations on each row, and then creates a StringBuilder, and for each row populates the StringBuilder and then pops it into a List(Of String), after doing a .ToString(). Then this method returns the List(Of String) to the ExportFile method, and this method creates another StringBuilder, appends all the Strings from that list, converts it to one large String, and sets it to the ResponseString property of the result ('r' in the code above).

So that's how it works. Is there anyway to speed this process up, like, a lot?

-Scott

Edit: More Code

Public Function ExportFile(ByVal ID As Integer) As ExportResult
            Dim result As New ExportResult
            Dim s As New StringBuilder

            'Get all Records
            Dim dt As New DataTable
            Using dal As New SQL
                dal.Parameters.AddWithValue("@ID", ID)
                dal.Execute("[dbo].[uspGet]", dt)
                dal.Parameters.Clear()
            End Using

            Dim dataobj As New DataObj(dt, ID)

            'Create FileName
            If dt.Rows.Count > 0 Then
                Dim StartDate As DateTime = DateTime.Parse(dt.Rows(0).Item("StartDate"))
                Dim EndDate As DateTime = DateTime.Parse(dt.Rows(0).Item("EndDate"))
                result.DefaultFileName = String.Format("HMDA_{0}_{1}.dat", String.Format("{0:MMyyyy}", StartDate), String.Format("{0:MMyyyy}", EndDate))
            End If

            'Add Title Line
            s.AppendLine(dataobj.CreateTitleLine())

            'Add all Record Lines
            Dim records As List(Of String) = dataobj.CreateRecordLines()
            Dim last As Integer = records.Count - 1
            For i = 0 To last
                If i = last Then
                    s.Append(records(i))
                Else
                    s.AppendLine(records(i))
                End If
            Next

            result.ResponseString = s.ToString

            Return result
End Function
+1  A: 

How performant is your SQL? I'd start checking there for your problems.

Then consider how big your file is. The data still needs to be downloaded at the client.

jfar
When I run the SQL queries that are used in the operations, they run instantly. The outputted file is 1.16MB in size (all text). I think the slow down is coming in from the massive size of the StringBuilder list, and then when that list is appended into a single StringBuilder and then has .ToString() run on it. I just don't know how to make everything run faster.
Scott
@Scott, Post more code then. Nobody can optimize something they cannot see.
jfar
Just posted the code of the ExportFile() Function. This works with the class that is actually creating the object, and then this method returns the result to the method I posted first.
Scott
@Scott, I see the code, something is fishy here. 1.1MB of strings shouldn't be that slow.
jfar
A: 

Avoid extra copies? Maybe you can avoid the conversion to bytes and / or the creation if the file. Just write the string to the response without this extra work.

Mike
So your saying instead of including it with the result, just return it directly? Byte conversion would still have to happen because of the way FileContentResult works, but I technically only need the String value. The main reason we stored it in result was code readability. You think it will help drastically?
Scott