Very slow macro performance in Outlook 2003

Printable View

12-02-2005, 09:01 AM
dewdrop_worl

Very slow macro performance in Outlook 2003

Sorry if this has been asked before... I searched this forum for "slow" but didn't see anything relevant.

In Outlook 2000, I set up a bunch of macros that help me organize my folders and delete messages I don't need anymore. I just upgraded to Office 2003 and I find that the same macros are running about 20 times slower. :eek:

Did a little tracing in the VBA editor... whenever I'm trying to get items out of a MAPIFolder, there's a noticeable delay. In 2000, I could hit F8 on a line like this in the response would be practically immediate, some small number of milliseconds.

[VBA] If InStr(myFolder.Items(i).Subject, SubjText) > 0 Then[/VBA]
In 2003, there's a noticeable lag, sometimes 0.1s, sometimes 0.25s, once or twice it might take five or more seconds. Multiply that by 80 messages in a folder and you have a macro that used to take 2-3 seconds that now takes 30-60 seconds. Totally unusable. One of them is Application_NewMail(), so it really really hurts.

I really depend on these macros, but I have no idea why the performance is so crappy. Does anyone know?

Here's the new mail macro:

[VBA]Private Sub Application_NewMail()
Call DeleteOldPendings
End Sub

Sub DeleteOldPendings()
Call DeleteOldContaining("Hourly Pendings")
Call DeleteOldContaining("Hourly Teams")
End Sub

Private Sub DeleteOldContaining(SubjText As String)
Dim myFolder As Outlook.MAPIFolder
Dim HourlyPendings(200) As MailItem
Dim LatestTime As Date
Set myFolder = CreateObject("Outlook.application").GetNamespace("MAPI").GetDefaultFolder(o lFolderInbox)

'Debug.Print Time, "Executing DeleteOldPendings."

' search for latest "hourly pendings"
j = 0
LatestTime = #1/1/1950#
For i = 1 To myFolder.Items.Count

' this line is slow!!

If InStr(myFolder.Items(i).Subject, SubjText) > 0 Then
j = j + 1
Set HourlyPendings(j) = myFolder.Items(i)

' this one too

If myFolder.Items(i).ReceivedTime > LatestTime Then LatestTime = myFolder.Items(i).ReceivedTime
End If
Next i

' now delete all except latest
For i = 1 To j
' I'm pretty sure these lines will be slow also, but I haven't traced them
If HourlyPendings(i).ReceivedTime < LatestTime Then
HourlyPendings(i).Delete
End If
Next i
'Debug.Print Time, "Finished DeleteOldPendings."
End Sub[/VBA]
Thanks!

hjh
12-07-2005, 10:38 PM
Howard Kaikow

Quote:

Originally Posted by dewdrop_worl

Sorry if this has been asked before... I searched this forum for "slow" but didn't see anything relevant.

In Outlook 2000, I set up a bunch of macros that help me organize my folders and delete messages I don't need anymore. I just upgraded to Office 2003 and I find that the same macros are running about 20 times slower. :eek:

Did a little tracing in the VBA editor... whenever I'm trying to get items out of a MAPIFolder, there's a noticeable delay. In 2000, I could hit F8 on a line like this in the response would be practically immediate, some small number of milliseconds.

[VBA] If InStr(myFolder.Items(i).Subject, SubjText) > 0 Then[/VBA]
In 2003, there's a noticeable lag, sometimes 0.1s, sometimes 0.25s, once or twice it might take five or more seconds. Multiply that by 80 messages in a folder and you have a macro that used to take 2-3 seconds that now takes 30-60 seconds. Totally unusable. One of them is Application_NewMail(), so it really really hurts.

I really depend on these macros, but I have no idea why the performance is so crappy. Does anyone know?

Here's the new mail macro:

[VBA]Private Sub Application_NewMail()
Call DeleteOldPendings
End Sub

Sub DeleteOldPendings()
Call DeleteOldContaining("Hourly Pendings")
Call DeleteOldContaining("Hourly Teams")
End Sub

Private Sub DeleteOldContaining(SubjText As String)
Dim myFolder As Outlook.MAPIFolder
Dim HourlyPendings(200) As MailItem
Dim LatestTime As Date
Set myFolder = CreateObject("Outlook.application").GetNamespace("MAPI").GetDefaultFolder(o lFolderInbox)

'Debug.Print Time, "Executing DeleteOldPendings."

' search for latest "hourly pendings"
j = 0
LatestTime = #1/1/1950#
For i = 1 To myFolder.Items.Count

' this line is slow!!

If InStr(myFolder.Items(i).Subject, SubjText) > 0 Then
j = j + 1
Set HourlyPendings(j) = myFolder.Items(i)

' this one too

If myFolder.Items(i).ReceivedTime > LatestTime Then LatestTime = myFolder.Items(i).ReceivedTime
End If
Next i

' now delete all except latest
For i = 1 To j
' I'm pretty sure these lines will be slow also, but I haven't traced them
If HourlyPendings(i).ReceivedTime < LatestTime Then
HourlyPendings(i).Delete
End If
Next i
'Debug.Print Time, "Finished DeleteOldPendings."
End Sub[/VBA]
Thanks!

hjh

If you use some efficiency techniques, the could will run faster.
For example, remove unnecessary object references by using With and End With statements.

To start with, there are too maby unnecessary references to myfolder.
12-08-2005, 08:36 AM
dewdrop_worl

Quote:

Originally Posted by Howard Kaikow

If you use some efficiency techniques, the could will run faster.
For example, remove unnecessary object references by using With and End With statements.

OK, so repeatedly calling methods on objects is bad. That will help me speed up some other, less frequently used macros.

The newmail macro is still unusable, though. I'm now doing a Set myItem = .Items(i), but I still have to get the .Subject once when going through the loop. So the code has gone from 20-30 times slower to only 10 times slower... not quite enough.

Leave it to MS to take something that was easy to use before and make it worse. :doh:

Thanks... any other suggestions?

hjh
12-08-2005, 02:23 PM
Howard Kaikow

Quote:

Originally Posted by dewdrop_worl

OK, so repeatedly calling methods on objects is bad. That will help me speed up some other, less frequently used macros.

The newmail macro is still unusable, though. I'm now doing a Set myItem = .Items(i), but I still have to get the .Subject once when going through the loop. So the code has gone from 20-30 times slower to only 10 times slower... not quite enough.

Leave it to MS to take something that was easy to use before and make it worse. :doh:

Thanks... any other suggestions?

hjh

i'd have to study the code, i would have to be compensated.
12-08-2005, 03:44 PM
dewdrop_worl

Quote:

Originally Posted by Howard Kaikow

i'd have to study the code, i would have to be compensated.

Never mind then... perhaps there are some volunteers out there who have some ideas.

I actually eliminated the need for the new mail script by turning off the service that generates the e-mails (which is no great loss, really).

:thumb

hjh