Takin A LOT of CPU time & not even working

[RhythmAddict112]

Hey all, good morning. I have two main problems concerning this script. Firstly, it consumes massive amounts of my CPU (we're talking 98/99%) I'm not entirely sure why that is, I don't see any endless loop type of scenario, and I think I closed everything up.
The second problem is, in the function FindZone, I'm calling the function above (sendMail) sendMail works fine on its own...however, when I call sendMail from the findZone function it does not work....Could someone give me a hand on this? Your help is appreciated.

Function SendMail(tmpBody, tmpSubject, strEmails) 
    Set objMessage = CreateObject("CDO.Message") 
    objMessage.Sender = "First.Last@Company.com" 
    'objMessage.To = Mid( emails, 2 ) ' the mid chops off first ; 
    objMessage.To = strEmails 
    objMessage.TextBody = tmpbody     
    objMessage.Subject = tmpSubject 
'This section provides the configuration information for the remote SMTP server. 
    objMessage.Configuration.Fields.Item _ 
    ("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendusing")[/URL] = 2 
'Name/IP of SMTP Server 
    objMessage.Configuration.Fields.Item _ 
    ("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserver")[/URL] = "server.com" 
'Server port   
    objMessage.Configuration.Fields.Item _ 
    ("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserverport")[/URL] = 25 
    objMessage.Configuration.Fields.Update 
    objMessage.Send 
    Set objMessage = nothing 
End Function 


Function FindZone(iCity,bSend,tBody,tSubject) 

Set DB = CreateObject("ADODB.Connection") 
Set RS = CreateObject("ADODB.Recordset") 
DB.Open ("Provider=OraOLEDB.Oracle;Data Source=$$$$;Persist Security Info=True;user id=$$$$$;password=$$$$$$$;dbDBType=Oracle;PLSQLRSet=1;") 

SearchString =       "SELECT notify.Contact_Address "_ 
                      & " FROM     tbl_Notify Notify " _ 
                      & " WHERE    notify.Zone_ID = " _ 
                      & " (SELECT locations.Zone_ID " _ 
                      & " FROM     tbl_zone_locations locations"_ 
                      & " WHERE  locations.txt_city = '" & iCity & "')" 
Set RS = DB.Execute(SearchString) 




Dim ZoneEmails 
Do Until Rs.EOF 
ZoneEmails = ZoneEmails & ";" & RS.Fields("contact_address") 
Loop 
ZoneEmails = Mid( ZoneEmails, 2 ) 

Set DB = nothing 
Set RS = nothing 

Call SendMail("body","subject",ZoneEmails) 



End Function 

Call FindZone("hicksville","1","body","subject")

 

[FesterSXS]

Do Until Rs.EOF 
  ZoneEmails = ZoneEmails & ";" & RS.Fields("contact_address") 
  [COLOR=red]Rs.MoveNext[/color]
Loop

Tony
________________________________________________________________________________

http://www.SpiraxSarco.com

 

[PHV]

And to avoid subquery:
SearchString = _
"SELECT notify.Contact_Address" _
& " FROM tbl_Notify Notify INNER JOIN tbl_zone_locations locations" _
& " ON notify.Zone_ID = locations.Zone_ID" _
& " WHERE locations.txt_city = '" & iCity & "'"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[RhythmAddict112]

Hey guys, thank you both for the tips...the moveNext which I forgot in my silly haste definitely accounted for my CPU time being off the charts on this....

PHV; do subqueries slow things down that much? I had no idea (kind of new to the DB world, really) But from now on I won't be using them anymore...If there are any other tips you can pick out that I should be doing in this script I'd lvoe to hear them - thanks!