SQL-based email composition and query synthesis in RMX

Moeko Deguchi, Yasushi Doi, Motomichi Toyama

Research output: Chapter in Book/Report/Conference proceedingConference contribution

Abstract

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.

Original languageEnglish
Title of host publication20th International Conference on Information Integration and Web-Based Applications and Services, iiWAS 2018 - Proceedings
EditorsGabriele Anderst-Kotsis, Eric Pardede, Matthias Steinbauer, Maria Indrawan-Santiago, Ivan Luiz Salvadori, Ivan Luiz Salvadori, Ismail Khalil
PublisherAssociation for Computing Machinery
Pages207-215
Number of pages9
ISBN (Electronic)9781450364799
DOIs
Publication statusPublished - 2018 Nov 19
Event20th International Conference on Information Integration and Web-Based Applications and Services, iiWAS 2018 - Yogyakarta, Indonesia
Duration: 2018 Nov 192018 Nov 21

Other

Other20th International Conference on Information Integration and Web-Based Applications and Services, iiWAS 2018
CountryIndonesia
CityYogyakarta
Period18/11/1918/11/21

    Fingerprint

Keywords

  • Email
  • Mailing lists
  • Query optimization
  • Query synthesis
  • RMX

ASJC Scopus subject areas

  • Human-Computer Interaction
  • Computer Networks and Communications
  • Computer Vision and Pattern Recognition
  • Software

Cite this

Deguchi, M., Doi, Y., & Toyama, M. (2018). SQL-based email composition and query synthesis in RMX. In G. Anderst-Kotsis, E. Pardede, M. Steinbauer, M. Indrawan-Santiago, I. L. Salvadori, I. L. Salvadori, & I. Khalil (Eds.), 20th International Conference on Information Integration and Web-Based Applications and Services, iiWAS 2018 - Proceedings (pp. 207-215). Association for Computing Machinery. https://doi.org/10.1145/3282373.3282403