SQL-based email composition and query synthesis in RMX

The Rule-based e-Mail eXchange system, also known as RMX, is an email transfer agent that transfers email based on user-defined delivery rules which are expressed as parameterized SQL queries. In this paper, we introduce insertion rules, which are also expressed as parameterized SQL queries based on the recipient’s email address to derive values such as recipient’s name or the list of purchase records to be embedded in the email body and header. By using the full expressive power of SQL, RMX allows arbitrary information in a relational database to be embedded at the moment of delivery. A straightforward implementation of insertion rules, however, invokes numerous SQL executions which are proportional both to the number of inserted items K and the number of recipients N. We have developed a query synthesis algorithm which derives a single SQL query from the delivery rule query and the K insertion rule queries. The 1 + N × K original query executions will be replaced by a single execution of the resulting query. Furthermore, we introduce a heuristic query simplification algorithm to reduce redundant references to the same relation and the joins between them generated by the naive synthesis algorithm. As an example, delivering 5000 emails each containing four embedded items with the straightforward implementation required 20001 SQL query executions that took 650.50 seconds to complete. This overhead was reduced to a single execution taking 304.69 milliseconds to complete with the naive query synthesis. The query simplification further reduced the time to 148.74 milliseconds by reducing four joins to one.