Hi, I'm hoping some of the wonderful minds here can assist me.
I have written a Windows application that takes user supplied locations, and extracts data from an Access db and puts it into Excel for a few manipulations (calc a percentile, get a few totals).
It works, but it seems very slow. The db runs to an average size of ~500MB, with ~500,000 relevant lines. here's a snippet:

'loop until no more TransName

            'Determine the Event ID for the transaction
            ScCount = TNrCount
            TIDcmd = New OleDbCommand("SELECT [Event ID] FROM Event_map WHERE [Event Name]=""" & TransName & """", LRconnect)
            DataReader = TIDcmd.ExecuteReader
            While DataReader.Read()
                'collect converted Transaction Name to Event ID
                TID = DataReader.GetValue(0)
            End While

            'collect End Times
            SrCount = 11
            ETcmd = New OleDbCommand("SELECT [End Time] FROM Event_meter WHERE [Event ID]=" & TID & "", LRconnect)
            DataReader = ETcmd.ExecuteReader()
            While DataReader.Read()
                'pull end time  and write to sequential rows 
                XlWsPPStaData.Cells(SrCount, ScCount + 1) = DataReader.GetValue(0)
                SrCount += 1
            End While

            'Determine transaction Name quality &  0 out any accumilated measurements for target quality
            BelTargetCount = 0
            BelMapCount = 0
            AbvMapCount = 0

            ' collect each Response Time
            SrCount = 11
            RTcmd = New OleDbCommand("SELECT [Value] FROM Event_meter WHERE [Event ID]=" & TID & "", LRconnect)
            DataReader = RTcmd.ExecuteReader()
            While DataReader.Read()
                'temp variable to determine relationship to target
                RespTime = DataReader.GetValue(0)
                XlWsPPStaData.Cells(SrCount, ScCount) = RespTime
                SrCount += 1
            End While

'do stuff

the first attempt to run this I had this:

RTcmd = New OleDbCommand("SELECT [Value],[End Time] FROM Event_meter WHERE [Event ID]=" & TID & "", LRconnect)

but it threw errors. The only way I could make it work was to extract one value from the table at a time.
TID can have several hundred values to loop through, and each TID can have several thousand rows.
I am using the VS2008 Express IDE (no corp apporval for 10 yet) and Excel and Access 2003(again, updrades aren't yet approved by security). If the proposed solution requires upgraded VS or Office products, I could use it as a business justification for speeding up the approval process.

Any help in getting multiple fields from a single row would speed things up immensely I think. Any other thoughts that would lead to faster processing would be great too!

Edited by RHaynes: n/a

6 Years
Discussion Span
Last Post by RHaynes

I didn't read your code, but I would like to ask if you used Console or it, it makes a big difference.


I'm sorry, I don't understqand your question. What Console? (which probably means no)
I'm reluctant to post the whole thing, as it runs almost 2000 lines at this point, but I can synosize pretty well if anything needs clearing up.


AH! Thanks for the clarification.
It's a Windows Application. Each executioon requires several bits of input from the user, and I was told that the form would give users a familiar look and feel. User specifies a destination (the Excel output file) and a configuration file (set of widely varying data from user to user and between projects for each user) as well as the Source (the Access db) and all 3 with File Browse functionality. There's also text boxes to enter things specific to each execution like date, full report link etc.

This article has been dead for over six months. Start a new discussion instead.
Be sure to adhere to our posting rules.