Manage finite SQL resources better #150

Закрито
5 роки тому відкрито jking · 0 коментарів
jking прокоментував(ла) 5 роки тому
Власник

While we already have problems using too much memory (see #134), an even more significant problem is the finite resources available for constructing complex SQL queries.

There are two such resources to be concerned with: the maximum number of parameter markers, and the maximum length of a query.

The first was already identified as a problem for updating very large feeds (see #71), but as criteria for article selection become more numerous and complex, SQLite's limit of 999 parameters quickly appears small.

One solution is to embed values in IN() clauses and LIKE matches into the query, but this must be balanced against SQLite's 1M-byte query length limit (MySQL and PostgreSQL also have limits, but they are significantly larger). Integers should not be problematic, but strings could hypothetically be quite large.

It would probably be sufficient to embed sets larger than five elements, while still parameterizing strings longer than 255 bytes in embedded sets.

While we already have problems using too much memory (see #134), an even more significant problem is the finite resources available for constructing complex SQL queries. There are two such resources to be concerned with: the maximum number of parameter markers, and the maximum length of a query. The first was already identified as a problem for updating very large feeds (see #71), but as criteria for article selection become more numerous and complex, SQLite's limit of 999 parameters quickly appears small. One solution is to embed values in `IN()` clauses and `LIKE` matches into the query, but this must be balanced against SQLite's 1M-byte query length limit (MySQL and PostgreSQL also have limits, but they are significantly larger). Integers should not be problematic, but strings could hypothetically be quite large. It would probably be sufficient to embed sets larger than five elements, while still parameterizing strings longer than 255 bytes in embedded sets.
jking додано
bug
з міткою 5 роки тому
jking додав(ла) до 0.7.0 етапу 5 роки тому
jking закрив цю проблему 5 роки тому
Підпишіться щоб приєднатися до обговорення.
Етап відсутній
Немає виконавеця
1 учасників
Сповіщення
Дата завершення

Термін виконання не встановлений.

Залежності

Ця проблема в даний час не має залежностей.

Завантаження…
Тут ще немає жодного змісту.