SQL-based email composition and query synthesis in RMX

Moeko Deguchi, Yasushi Doi, Motomichi Toyama

研究成果: Conference contribution

抄録

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.

元の言語English
ホスト出版物のタイトル20th International Conference on Information Integration and Web-Based Applications and Services, iiWAS 2018 - Proceedings
編集者Gabriele Anderst-Kotsis, Eric Pardede, Matthias Steinbauer, Maria Indrawan-Santiago, Ivan Luiz Salvadori, Ivan Luiz Salvadori, Ismail Khalil
出版者Association for Computing Machinery
ページ207-215
ページ数9
ISBN(電子版)9781450364799
DOI
出版物ステータスPublished - 2018 11 19
イベント20th International Conference on Information Integration and Web-Based Applications and Services, iiWAS 2018 - Yogyakarta, Indonesia
継続期間: 2018 11 192018 11 21

Other

Other20th International Conference on Information Integration and Web-Based Applications and Services, iiWAS 2018
Indonesia
Yogyakarta
期間18/11/1918/11/21

Fingerprint

Electronic mail
Chemical analysis
Ion exchange

ASJC Scopus subject areas

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

これを引用

Deguchi, M., Doi, Y., & Toyama, M. (2018). SQL-based email composition and query synthesis in RMX. : G. Anderst-Kotsis, E. Pardede, M. Steinbauer, M. Indrawan-Santiago, I. L. Salvadori, I. L. Salvadori, & I. Khalil (版), 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

SQL-based email composition and query synthesis in RMX. / Deguchi, Moeko; Doi, Yasushi; Toyama, Motomichi.

20th International Conference on Information Integration and Web-Based Applications and Services, iiWAS 2018 - Proceedings. 版 / Gabriele Anderst-Kotsis; Eric Pardede; Matthias Steinbauer; Maria Indrawan-Santiago; Ivan Luiz Salvadori; Ivan Luiz Salvadori; Ismail Khalil. Association for Computing Machinery, 2018. p. 207-215.

研究成果: Conference contribution

Deguchi, M, Doi, Y & Toyama, M 2018, SQL-based email composition and query synthesis in RMX. : G Anderst-Kotsis, E Pardede, M Steinbauer, M Indrawan-Santiago, IL Salvadori, IL Salvadori & I Khalil (版), 20th International Conference on Information Integration and Web-Based Applications and Services, iiWAS 2018 - Proceedings. Association for Computing Machinery, pp. 207-215, 20th International Conference on Information Integration and Web-Based Applications and Services, iiWAS 2018, Yogyakarta, Indonesia, 18/11/19. https://doi.org/10.1145/3282373.3282403
Deguchi M, Doi Y, Toyama M. SQL-based email composition and query synthesis in RMX. : Anderst-Kotsis G, Pardede E, Steinbauer M, Indrawan-Santiago M, Salvadori IL, Salvadori IL, Khalil I, 編集者, 20th International Conference on Information Integration and Web-Based Applications and Services, iiWAS 2018 - Proceedings. Association for Computing Machinery. 2018. p. 207-215 https://doi.org/10.1145/3282373.3282403
Deguchi, Moeko ; Doi, Yasushi ; Toyama, Motomichi. / SQL-based email composition and query synthesis in RMX. 20th International Conference on Information Integration and Web-Based Applications and Services, iiWAS 2018 - Proceedings. 編集者 / Gabriele Anderst-Kotsis ; Eric Pardede ; Matthias Steinbauer ; Maria Indrawan-Santiago ; Ivan Luiz Salvadori ; Ivan Luiz Salvadori ; Ismail Khalil. Association for Computing Machinery, 2018. pp. 207-215
@inproceedings{745b3cf07e67488fbab1217ab8fb8c94,
title = "SQL-based email composition and query synthesis in RMX",
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.",
keywords = "Email, Mailing lists, Query optimization, Query synthesis, RMX",
author = "Moeko Deguchi and Yasushi Doi and Motomichi Toyama",
year = "2018",
month = "11",
day = "19",
doi = "10.1145/3282373.3282403",
language = "English",
pages = "207--215",
editor = "Gabriele Anderst-Kotsis and Eric Pardede and Matthias Steinbauer and Maria Indrawan-Santiago and Salvadori, {Ivan Luiz} and Salvadori, {Ivan Luiz} and Ismail Khalil",
booktitle = "20th International Conference on Information Integration and Web-Based Applications and Services, iiWAS 2018 - Proceedings",
publisher = "Association for Computing Machinery",

}

TY - GEN

T1 - SQL-based email composition and query synthesis in RMX

AU - Deguchi, Moeko

AU - Doi, Yasushi

AU - Toyama, Motomichi

PY - 2018/11/19

Y1 - 2018/11/19

N2 - 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.

AB - 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.

KW - Email

KW - Mailing lists

KW - Query optimization

KW - Query synthesis

KW - RMX

UR - http://www.scopus.com/inward/record.url?scp=85061152565&partnerID=8YFLogxK

UR - http://www.scopus.com/inward/citedby.url?scp=85061152565&partnerID=8YFLogxK

U2 - 10.1145/3282373.3282403

DO - 10.1145/3282373.3282403

M3 - Conference contribution

AN - SCOPUS:85061152565

SP - 207

EP - 215

BT - 20th International Conference on Information Integration and Web-Based Applications and Services, iiWAS 2018 - Proceedings

A2 - Anderst-Kotsis, Gabriele

A2 - Pardede, Eric

A2 - Steinbauer, Matthias

A2 - Indrawan-Santiago, Maria

A2 - Salvadori, Ivan Luiz

A2 - Salvadori, Ivan Luiz

A2 - Khalil, Ismail

PB - Association for Computing Machinery

ER -