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) TRTQual(RespTime) XlWsPPStaData.Cells(SrCount, ScCount) = RespTime SrCount += 1 End While 'do stuff 'loop
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!