How to speed up sending emails via Oracle PL/SQL?

user_2635209 0
Viewed 517

I'm sending thousands of emails via Oracle 18c to an SMTP server. The emails are different.

I'm using the following code in a loop that I put inside a job. This code works great, however it's quite slow. (I'm talking about the execution time.)

I was wondering if there is any way to speed it up. For example, openning the SMTP connection only once and use it for the entire loop.

Has anyone tried something like that please ?

Thanks. Cheers,

Thanks.

    declare
        Connexion   UTL_SMTP.connection;
        l_boundary  VARCHAR2(50) := '----=*#abc1234321cba#*=';
        -- other variables are declared somewhere else...
    Begin
        Connexion := UTL_SMTP.open_connection(Host, Port);

        UTL_SMTP.AUTH(Connexion,KeyName,KeyValue, 'PLAIN');
        UTL_SMTP.helo(Connexion, Host);
        UTL_SMTP.mail(Connexion, MailFrom_);

        -- Add email
        UTL_SMTP.rcpt(Connexion, EmailAddress);  

        -- Open reader to write data
        UTL_SMTP.open_data(Connexion);

        -- Date
        UTL_SMTP.write_data(Connexion, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);

        -- Mail to
        UTL_SMTP.write_data(Connexion, 'To: ' || EmailAddress || UTL_TCP.crlf);   

        -- Subject
        UTL_SMTP.write_raw_data(Connexion, utl_raw.cast_to_raw('Subject:' || Subject_));
        UTL_SMTP.WRITE_DATA(Connexion, UTL_TCP.CRLF);      

        -- Message html  
        UTL_SMTP.write_data(Connexion, '--' || l_boundary || UTL_TCP.crlf);
        UTL_SMTP.write_data(Connexion, 'Content-Type: text/html; charset="UTF-8"' || UTL_TCP.crlf || UTL_TCP.crlf);     
        UTL_SMTP.write_raw_data(Connexion, utl_raw.cast_to_raw(MessageInHTML));                    
        UTL_SMTP.write_data(Connexion, UTL_TCP.crlf || UTL_TCP.crlf);  


        UTL_SMTP.write_data(Connexion, '--' || l_boundary || '--' || UTL_TCP.crlf);
        UTL_SMTP.close_data(Connexion);
        UTL_SMTP.quit(Connexion);   
    end;
smtp plsql oracle
1 Answers

Basically here is the answer :

I combined two things :

  • Reduce the number of new connections.
  • Multithreading. For me, each loop is independant so I can use multithreading as @Justin Cave sugested in the comment.

Basically I created a procedure that, first creates a connexion (Part A), then loops over my query to send the emails (Part B), and finally closes the connection (Part C).

This procedure is parametrized such that it runs only on a part of the query so that I can use multithreading. For example to run on 3 threads, I added a condition in my query like :

  • mod(identifier,3) = 0 or
  • mod(identifier,3) = 1 or
  • mod(identifier,3) = 2

(identifier is a numerical primary key...)

Then I insert this procedure in different jobs to run it in several threads.

There is a watchout here is that, I don't know if there is a connection maximum life time at the smtp server level. So I'm using the connection for 50 mails only, then I create a new one. So the connection time was divided by 50.

Results in performance for my case :

  • Single thread and reconnect/disconnect to smtp server for each loop : 184 seconds
  • Single thread and reconnect every 50 mail : 78 seconds
  • 3 Threads and reconnect every 50 mail : 26 seconds which is -86%

A part : Create the connection

    Connexion := UTL_SMTP.open_connection(Host, Port);

    UTL_SMTP.AUTH(Connexion,KeyName,KeyValue, 'PLAIN');
    UTL_SMTP.helo(Connexion, Host);

B part : Send the email (main loop)

    UTL_SMTP.mail(Connexion, MailFrom_);

    -- Add email
    UTL_SMTP.rcpt(Connexion, EmailAddress);  

    -- Open reader to write data
    UTL_SMTP.open_data(Connexion);

    -- Date
    UTL_SMTP.write_data(Connexion, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);

    -- Mail to
    UTL_SMTP.write_data(Connexion, 'To: ' || EmailAddress || UTL_TCP.crlf);   

    -- Subject
    UTL_SMTP.write_raw_data(Connexion, utl_raw.cast_to_raw('Subject:' || Subject_));
    UTL_SMTP.WRITE_DATA(Connexion, UTL_TCP.CRLF);      

    -- Message html  
    UTL_SMTP.write_data(Connexion, '--' || l_boundary || UTL_TCP.crlf);
    UTL_SMTP.write_data(Connexion, 'Content-Type: text/html; charset="UTF-8"' || UTL_TCP.crlf || UTL_TCP.crlf);     
    UTL_SMTP.write_raw_data(Connexion, utl_raw.cast_to_raw(MessageInHTML));                    
    UTL_SMTP.write_data(Connexion, UTL_TCP.crlf || UTL_TCP.crlf);  


    UTL_SMTP.write_data(Connexion, '--' || l_boundary || '--' || UTL_TCP.crlf);
    UTL_SMTP.close_data(Connexion);

C part : Close the connection

    UTL_SMTP.quit(Connexion);
user_26352090
Related