Когда речь заходит об использовании SQL, экосистеме Rust есть что нам предложить: к счастью, нам доступны несколько отличных готовых к использованию решений. SQLx — это чисто асинхронный, независящий от рантайма SQL-крейт, позволяющий использовать проверяемые во время компиляции запросы без какого-либо DSL. Являясь одним из наиболее популярных способов использования SQL в Rust, он обладает следующими преимуществами:
Он совместим со всеми вашими любимыми разновидностями SQL (MySQL, SQLite, Postgres).
Проверка запросов во время компиляции обеспечивает корректность этих самых запросов и типов, которые в них задействованы.
Поддержка дополнительных функций, таких как listen/notify из Postgres.
Множество различных способов построения и использования запросов.
С помощью SQLx вы также можете создать свой собственный конструктор запросов!
Давайте же посмотрим на SQLx в действии!
Начало работы
Для начала вам необходимо добавить sqlx в вашу Rust-программу:
cargo add sqlxТакже необходимо установить sqlx-cli, официальный CLI SQLx, который, помимо всего прочего, облегчает управление миграциями. Установить его можно, выполнив приведенную ниже команду:
cargo install sqlx-cliМиграции
Первый шаг — миграция. При желании можно просто создать таблицы вручную — но это потребует много времени и усилий... и нужно будет постоянно помнить, что конкретно мы сделали на этом этапе! К счастью, мы можем написать .sql-файлы, представляющие наши миграции, и затем переносить их в любую используемую нами базу данных либо с помощью sqlx-cli, либо с помощью команды sqlx::execute. Простая SQL-схема может выглядеть следующим образом:
-- таблица здесь будет создана только в том случае, если она еще
-- не существует, что позволяет избежать проблемы с перезатиранием таблиц
CREATE TABLE IF NOT EXISTS foo (
id SERIAL PRIMARY KEY,
message TEXT
);Если это корректный SQL, то какой бы метод вы ни использовали, он успешно выполнится, создав в вашей базе данных таблицу _sqlx_migrations с примененным списком миграций.
Ваша встроенная команда миграции может выглядеть следующим образом:
В качестве рекомендации скажу вам, что сам я использую sqlx-cli, где выполняю команду sqlx migrate -r add <filename>. Эта команда, по сути, добавляет новую миграцию, но флаг -r позволяет в любой момент отменить при возникновении каких-либо ошибок. Это удобный способ, позволяющий откатить все назад, если что-то пойдет не так после развертывания новой миграции в продакшене.
Запросы
Мы по умолчанию можем использовать чистые SQL-запросы, просто выполняя запрос на нашем пуле соединений:
let query = sqlx::query("SELECT * FROM TABLE")
.execute(&pool)
.await
.unwrap();По умолчанию SQLx поощряет использование связанных параметров, которые очень важны для предотвращения SQL-инъекций — достаточно просто добавить их в запрос (подробнее об этом можно прочитать здесь):
sqlx::query("INSERT INTO TABLE (foo) VALUES ($1)")
.bind("bar".to_string())
.execute(&pool)
.await
.unwrap();Теперь предположим, что вы пишете запрос, который возвращает что-то. Когда вы получаете строки из этого запроса, вам, скорее всего, придется брать каждое значение по отдельности — в небольших масштабах это еще куда ни шло, но когда вы используете fetch_all, вам придется задействовать итератор, чтобы получить то, что вам нужно из каждой строки. К счастью, создатели SQLx понимали это и предусмотрели макрос для извлечения вектора со структурами из вектора строк SQL — вы можете использовать query_as для привязки возвращаемых результатов к структуре, которая использует #[derive(Sqlx::FromRow)].
Выглядеть это будет следующим образом:
#[derive(sqlx::FromRow)]
struct Foo {
id: i32,
message: String
}
async fn foo(pool: PgPool) -> Vec<Foo> {
let res = sqlx::query_as::<_, Foo>("SELECT * FROM FOO")
.fetch_all(&pool).await.unwrap();
Ok(res)
}Вам нужно сделать что-то более сложное? Для построения запросов можно использовать тип QueryBuilder. Хоть он отлично подходит для программного добавления динамических фрагментов в запросы, следует быть осторожным при его использовании, поскольку в нем есть методы для добавления значений, не являющихся связанными параметрами, — в идеале, если вы не уверены в безопасности используемого метода, лучше использовать push_bind.
Пример использования:
const BIND_LIMIT: usize = 65535;
// Значения здесь могут выдаваться бесконечно!
let records = (0..).map(|i| Foo {
id: i,
message: format!("This is note {i}"),
});
let mut query_builder: QueryBuilder<MySql> = QueryBuilder::new(
// Обратите внимание на завершающий пробел; большинство вызовов
// `QueryBuilder` не добавляют пробелы автоматически, так как это может
// помешать идентификаторам или строкам с кавычками, где могут быть
// важны точные значения.
"SELECT * FROM users WHERE (id, username, email, password) in"
);
// Отмечу, что .into_iter() здесь не понадобилось, поскольку users
// уже является итератором. Если бы вы хотели связать их по ссылке,
// а не по значению, вам бы понадобился итератор, который бы выдавал
// ссылки, живущие столько же, сколько и `query_builder`, для чего,
// например, можно было бы сначала собрать их в `Vec`.
bound.push_bind(foo.id)
.push_bind(foo.username);
});
let mut query = query_builder.build();
let res = query.fetch_all(&pool).await.unwrap();Если вы попробуете выполнить этот запрос, то вы получите вектор структур Foo! Однако следует помнить, что этот метод имеет свои недостатки, о которых будет сказано ниже: вы не сможете воспользоваться макросами проверки времени компиляции SQLx, а также этот метод генерации запросов может быть несколько небезопасным, если вы не будете проявлять особую осторожность. Однако в рамках задач, предполагающих динамическую генерацию запросов с использованием SQL в Rust, этот метод является достаточно мощным.
Последний тип запроса, который мы также можем использовать, — это скалярный подзапрос, который возвращает результат в виде кортежа. Если при выполнении запроса (например) SELECT * FROM TABLE мы не знаем, сколько в нем полей, мы можем использовать query_scalar, что позволит нам ссылаться на столбцы просто по порядку их следования, а не по заданному имени. Смотрите пример ниже:
let query = sqlx::query_scalar("SELECT * FROM FOO LIMIT 1").fetch_one(&pool).await.unwrap();
println!("{:?}", query.0);Макросы
Мы наконец подошли к одному из достоинств SQLx: проверке запросов во время компиляции. Если вы используете чистый (raw) SQL, наличие некоторой гарантии того, что ваш SQL корректен, практически никогда не будет лишним: если вы не администратор базы данных, то при выполнении запроса с несколькими джоинами вам обязательно захочется убедиться в его корректности до того, как он будет запущен. Следует отметить, что для использования этой возможности необходимо установить sqlx-cli: если его нет, то вам придется полагаться только на предыдущие методы.
Простой запрос с использованием макроса query! может выглядеть следующим образом:
// обратите внимание, что в макрос query добавляются связанные параметры
let query = query!("SELECT * FROM FOO WHERE ID = $1", 1).fetch_one(&pool).await.unwrap();Аналогично, эквивалентный запрос, использующий созданную ранее структуру Foo, может быть использован для прямого связывания результатов с вектором структур:
#[derive(sqlx::FromRow)]
struct Foo {
id: i32,
message: String
}
let query = query_as!(Foo, "SELECT * FROM FOO").fetch_all(&pool).await.unwrap();При использовании макроса query! или query_as! необходимо использовать программу cargo sqlx prepare, которая будет генерировать JSON-файлы для ваших запросов. Когда вы компилируете свою программу, она будет автоматически проверять ее во время компиляции: если что-то не так, она автоматически проверит это за вас.
Существует одна особенность, которая может поставить вас в тупик при использовании макросов проверки во время компиляции, особенно в Postgres: если вы используете as _ для переименования полей SQL, то тип будет автоматически обернут в Option, если вы не зададите его явно как ненулевое (non-nullable) значение. В SQLx есть ответ на этот вопрос — возможность использования чистых строк (raw strings) для явного объявления значений в качестве ненулевых столбцов. Например, возьмем следующий оператор:
let query = query_as!(Foo, "SELECT id, message as message from foo").fetch_all(&pool).await.unwrap();Если бы у нас по-прежнему String был в качестве типа Message, то этот запрос не скомпилировался бы, поскольку message теперь является типом Option<String>, а не String. Однако, преобразовав приведенный выше запрос в чистую строку, мы можем заставить поле снова стать ненулевым:
// // обратите внимание, что теперь message —— это "message!"
let query = query_as!(Foo, r#"SELECT id, message as "message!" from foo"#).fetch_all(&pool).await.unwrap();Подробнее об этом можно прочитать здесь.
Подобным образом, конечно, query_scalar также имеет связанный макрос, который можно использовать аналогично макросу query!, возвращая при этом кортежи.
Еще одна замечательная возможность — хранить SQL-запрос в файле и запускать макрос для выполнения содержимого SQL-файла, сохраняя при этом привязку наших параметров. Смотрите пример ниже:
SELECT * FROM FOO WHERE id = $1;let query = query_file!("query.sql", 1i32).fetch_one(&pool).await.unwrap();Разумеется, этот макрос также поддерживает связывание со структурами и скалярные подзапросы с помощью query_file_as! и query_file_scalar!.
Следует отметить, что если вам требуется только проверка синтаксиса во время компиляции, а корректность входов и выходов базы данных для макроса запроса не важна, то в конце макроса можно добавить unchecked. Например: query! превратится в query_unchecked!. Это полезно в тех случаях, когда база данных еще не настроена или нет удобного способа получения URL базы данных (или в других подобных случаях, когда вы не хотите предоставлять SQLx прямой доступ к вашей базе данных).
Listen/Notify PostgreSQL
При таком количестве фич, как у Postgres, впечатляет, что SQLx способен их поддерживать — хотя SQLx в первую очередь предназначен для написания чистого SQL, нет каких-либо ограничений, заставляющих нас писать все только на нем. SQLx поддерживает каналы, LISTEN и, что еще важнее, pg_notify, который является для нас отличным способом обработки уведомлений от Postgres при обновлении записей. Давайте посмотрим на примере ниже, как мы можем настроить слушатель событий:
// заранее подготавливаем пул
let mut listener = PgListener::connect_with(&pool).await.unwrap();
listener.listen("testNotify").await.unwrap();
// создаем цикл для получения уведомлений
tokio::spawn(async move || {
while let Some(notification) = listener.try_recv().await.unwrap() {
println!("{notification:?}");
}
});
loop {
sqlx::query("SELECT pg_notify('testNotify', 'Hello world!')").execute(&pool).await;
}Как вы можете видеть здесь, мы породили задачу Tokio для асинхронного цикла получения уведомлений и их последующей печати — тем временем внутри основного потока выполнения мы также создали цикл для непрерывной отправки запроса "Hello world!" по каналу, который получает наш PgListener.
Для более продвинутой реализации в веб-сервисе, реализующем в качестве конечной точки поток изменений базы данных, следует использовать метод .into_stream(), поскольку фреймворки обычно принимают поток данных, который затем оборачивается в соответствующий тип из фреймворка. Например, в Axum вы бы использовали тип axum::response::Sse (обратите внимание, что это предполагает, что у вас уже есть настроенный веб-сервис):
use axum::{Extension, response::{Sse, sse::Event}};
use tokio_stream::StreamExt as _ ;
use futures_util::stream::{self, Stream};
use std::convert::Infallbile;
async fn return_stream(Extension(listener): Extension<PgListener>) -> Sse<impl Stream<Item = Result<Event, Infallible>>> {
let stream = listener.into_stream();
Sse::new(stream
.map(|msg| {
let msg = msg.uwnrap();
let json = json!(msg).to_string();
Event::default().data(json)
}).map(Ok),
).keep_alive(KeepAlive::default())
}При настройке веб-сервиса мы можем создавать уведомления одним из двух способов:
Используя SQL
Используя
pg_notifyдля определенных событий
Сама работа с pg_notify довольно проста, хотя тут можно обойтись и без SQL, просто используя каналы Tokio. Давайте поднимемся на ступеньку выше и используем SQL для настройки наших каналов, чтобы нам не пришлось вручную генерировать их в коде.
CREATE TABLE IF NOT EXISTS test_table (
id SERIAL PRIMARY KEY,
message TEXT NOT NULL
);
CREATE TRIGGER "testNotify"
AFTER INSERT ON test_table
FOR EACH ROW EXECUTE PROCEDURE testNotify();
CREATE OR REPLACE FUNCTION testNotify()
RETURNS TRIGGER AS $$
DECLARE
BEGIN
PERFORM pg_notify('testNotify', ROW_TO_JSON(NEW)::text);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;Теперь, если мы добавим это в файл миграции SQL, затем запустим приложение и обратимся к конечной точке, которую мы используем для нашего потока, то мы сможем получать поток уведомлений!
Использование SQLx вместе с Shuttle
В настоящее время Shuttle предлагает SQLx в качестве соединения по умолчанию через наши аннотационные макросы, которые экономят время, позволяя создавать инфраструктуру прямо из кода. Все, что вам нужно сделать, это объявить макрос в коде, как, например, здесь:
use sqlx::PgPool;
#[shuttle_runtime::main]
async fn main(
#[shuttle_shared_db::Postgres] db: PgPool // gets объявляется здесь
) -> shuttle_axum::ShuttleAxum {
sqlx::migrate!().run(&db).await.map_err(|e| format!("Oh no! Migrations failed :( {e}");
... the rest of your code
}Наши бесплатные базы данных предоставляются через общий сервер баз данных (при этом пользователи имеют отдельные базы данных для каждого приложения). Однако теперь мы предлагаем 100% изолированные базы данных AWS RDS в нашем плане Pro, о котором вы можете узнать здесь, с поддержкой MySQL, Postgres и MariaDB.
Пару слов в завершение
Спасибо, что прочитали эту статью! Я надеюсь, что вы получили хорошее представление о том, как можно использовать SQL в Rust, а также о том, насколько полезным инструментом является SQLx, когда нам нужно заставить мощь чистых, проверяемых во время компиляции SQL-запросов работать на вас при использовании SQL в Rust.
Если эта статья была для вас полезна, то не стесняйтесь поставить нам звезду на GitHub.
В заключение приглашаем всех Rust-разработчиков на ближайшие открытые уроки:
Многопоточность без страха, 8 ноября
Распределённый Ledger на Rust, 27 ноября
Комментарии (18)
SpiderEkb
01.11.2023 17:38+2А вот сразу пара вопросов.
Номер раз: в какой момент происходит построение плана запроса? Вот пишем:
let query = sqlx::query("SELECT TBL1FLD1, TBL2FLD2 FROM TBL1 JOIN TBL2 ON TBL2FLD1 = TBL1FLD1 WHERE TBL1FLD3 < 5") .execute(&pool) .await .unwrap();Когда будет план запроса построен? Во время компиляции (и где он сохранится при этом?) или в рантайме?
Запрос ведь может быть достаточно сложным. Например:
with CLIENTTYPE as ( select C4CTP, cast( case when C4CBQ = 'C' and C4IND = 'N' and C4CIGR <> '12' then 'U' else 'F' end as char(1)) CLTP from C4PF where ((C4IND = 'Y' and C4CBQ in ('B', 'C', 'N')) or (C4IND = 'N' and C4CBQ = 'C')) ), NMCCHANGED as ( select distinct NMCCUS, NMCCLC, GFCPNC NMCCPNC, CLTP NMCCLTP from NMCPF join GFPF on (GFCUS, GFCLC) = (NMCCUS, NMCCLC) join CLIENTTYPE on GFCTP = C4CTP where NMCDT >= 1230405 and NMCNTP not in ('B') ), DULCHANGED as ( select distinct RDKCUS, RDKCLC, GFCPNC RDKCPNC, CLTP RDKCLTP from RDKPF join GFPF on (GFCUS, GFCLC) = (RDKCUS, RDKCLC) join CLIENTTYPE on GFCTP = C4CTP where RDKDTM >= 1230405 ) select coalesce(GFCUS, RDKCUS, NMCCUS) CUS, coalesce(GFCLC, RDKCLC, NMCCLC) CLC, coalesce(GFCPNC, RDKCPNC, NMCCPNC) CPNC, coalesce(GFCRF, '0') CRF, coalesce(BGDTBR, 0) DTBR, coalesce(CLTP, RDKCLTP, NMCCLTP) CLTP, cast( case when GFDCC > 0 or GFDEL = 'Y' then 'X' when GFCOD >= 1230405 then 'N' else ' ' end as char(1)) CHK, case when GFDLM >= 1230405 then 'Y' else '' end CHKGFBG, case when RDKCUS is not NULL then 'Y' else '' end CHKRDK, case when NMCCUS is not NULL then 'Y' else '' end CHKNMC from GFPF join CLIENTTYPE on (C4CTP) = (GFCTP) left join BGPF on (BGCUS, BGCLC) = (GFCUS, GFCLC) and CLTP = 'F' full join NMCCHANGED on (NMCCUS, NMCCLC) = (GFCUS, GFCLC) full join DULCHANGED on (RDKCUS, RDKCLC) = (GFCUS, GFCLC) and CLTP = 'F' where GFDLM >= 1200325 or RDKCUS is not NULL or NMCCUS is not NULLИ план для него будет тоже достаточно сложен:
Построение плана в рантайме занимает определенное время...
Номер два: допустим используем конструкцию типа
#[derive(sqlx::FromRow)] struct Foo { id: i32, message: String }Что там нужно написать, если в структуре записи есть поля типов varchar, decimal, numeric, date, time, timestmp? В языке эти типы как-то поддерживаются?
mayorovp
01.11.2023 17:38+1Учитывая, что программа компилируется для запуска везде, а не на конкретном сервере - варианта "строить план запроса при компиляции" просто нет.
Что же до типов данных - в языке уж точно найдётся куда эти типы отобразить, всё же это современный ЯП где можно вводить свои типы данных. Вопрос надо задавать не "есть ли в языке эти типы", а "есть ли в драйвере поддержка этих типов"?
И ответ тут- да, есть. Вот тут есть список что тут куда отображается: https://docs.rs/sqlx-postgres/0.7.2/sqlx_postgres/types/index.html
SpiderEkb
01.11.2023 17:38Учитывая, что программа компилируется для запуска везде,
Что значит "везде"? Я так понимаю, что подобные программы пишутся под конкретную БД и завязаны на структуру конкретной БД.
Построение плана запроса в рантайме кратно снижает производительность. Из личной практики - в использумом у нас языке есть т.н. "embedded sql" - возможность вставлять в код sql запросы. Не какими-то инстансами, на напрямую:
exec sql declare curRDMS12Clients cursor for with Holders as ( select F0UCUS1, F0UCLC1, F0UCUS2, F0UCLC2 from F0UPF F0U join FPKPF FPK on (FPKSCON, FPKCTP) = (F0U.F0USCON, 'F') where (F0UCUS2 <> F0UCUS1 or F0UCLC2 <> F0UCLC1) and F0UCD >= :$PDate and F0USCID in ('00', '01', '02', '03', '14', '16') and F0USCON like 'P%' ), CheckOwner as ( select RDKCUS OWNCUS, RDKCLC OWNCLC, RDKSER OWNSER, RDKNUM OWNNUM, RDKOPN OWNOPN, RDKEDT OWNEDT from RDKPF RDK1 where (RDKUCD, RDKSDL, RDKOSN) = ('001', 'Y', 'Y') and RDKEDT < :$PDate ), CheckHolder as ( select RDKCUS HLDCUS, RDKCLC HLDCLC, RDKSER HLDSER, RDKNUM HLDNUM, RDKOPN HLDOPN, RDKEDT HLDEDT from RDKPF RDK2 where (RDKUCD, RDKSDL, RDKOSN) = ('001', 'Y', 'Y') and RDKEDT <= :$FDate and not exists ( select SCAN from SCPF where (SCAN, SCAI17, SCAI30, SCAI80) = (RDK2.RDKCUS, 'N', 'N', 'N') and SCNANC in ('40817', '40820', '42301', '42601') ) and ( (RDK2.RDKEDT >= :$PDate and not exists ( select RDKMCUS from RDKMPF where (RDKMCUS, RDKMCLC, RDKMSER, RDKMNUM, RDKMUCD, RDKMTP) = (RDK2.RDKCUS, RDK2.RDKCLC, RDK2.RDKSER, RDK2.RDKNUM, '001', 'I') ) ) or (RDK2.RDKEDT < :$PDate and not exists ( select RDKMCUS from RDKMPF where (RDKMCUS, RDKMCLC, RDKMSER, RDKMNUM, RDKMUCD, RDKMTP) = (RDK2.RDKCUS, RDK2.RDKCLC, RDK2.RDKSER, RDK2.RDKNUM, '001', 'J') ) ) ) ) select F0UCUS1, F0UCLC1, F0UCUS2, F0UCLC2, coalesce(HLDSER, ''), coalesce(HLDNUM, ''), coalesce(HLDOPN, 0), coalesce(HLDEDT, 0), coalesce(OWNSER, ''), coalesce(OWNNUM, ''), coalesce(OWNOPN, 0), coalesce(OWNEDT, 0), cast (case when (HLDEDT is not null and HLDEDT >= :$PDate) then 'Y' else 'N' end as char(1)) FLAGI, cast (case when (HLDEDT is not null and HLDEDT < :$PDate) then 'Y' else 'N' end as char(1)) FLAGJ, cast (case when OWNEDT is not null then 'Y' else 'N' end as char(1)) FLAGH from Holders left join CheckHolder on (HLDCUS, HLDCLC) = (Holders.F0UCUS2, Holders.F0UCLC2) left join CheckOwner on (OWNCUS, OWNCLC) = (Holders.F0UCUS1, Holders.F0UCLC1) where HLDSER is not null or (OWNSER is not null and not exists ( select RDKMCUS from RDKMPF where (RDKMCUS, RDKMCLC, RDKMSER, RDKMNUM, RDKMUCD, RDKMTP) = (Holders.F0UCUS2, Holders.F0UCLC2, CheckOwner.OWNSER, CheckOwner.OWNNUM, '001', 'H') ));далее
exec sql open curRDMS12Clients;и затем
exec sql fetch curRDMS12Clients for :sqlRows rows into :dsSQLData;где dsSQLData массив структур
dcl-ds t_dsSQLData qualified template; CUS1 char(6) inz; CLC1 char(6) inz; CUS2 char(6) inz; CLC2 char(6) inz; HSER char(10) inz; HNUM char(35) inz; HOPN zoned(7: 0) inz; HEDT zoned(7: 0) inz; OSER char(10) inz; ONUM char(35) inz; OOPN zoned(7: 0) inz; OEDT zoned(7: 0) inz; FLGI char(1) inz; FLGJ char(1) inz; FLGH char(1) inz; end-ds; dcl-ds dsSQLData likeds(t_dsSQLData) dim(sqlRows);Куда блоками по sqlRows элементов читаются данные в цикле.
Тип zoned в нашем языке соответствует типу numeric в SQL (есть и остальные - packed соответствует decimal, поддерживаются date, time, varchar, timestamp).
Так вот, есть два варианта - "статический", как выше, где план запроса готовится на этапе компиляции и где-то там сохраняется, и "динамический", когда формируем строку запроса в коде, а потом
exec sql declare SqlStatement1 statement; exec sql prepare SqlStatement1 from :sqlTxt1; exec sql declare currACY1 cursor for SqlStatement1;Недавно как раз столкнулся - динамический запрос выполнялся 15 минут, при этом данные начинали поступать только через 13 минут. Аналогичный статический запрос выполнялся 5 минут, данные начали поступать через 5-6 секунд.
Ну или вот сегодняшнее внедрение. Оптимизация sql - там как раз первод с динамики на статику и еще немного шаманства
Последний столбец - время выполнения. Вчера была старая версия, на динамике, сегодня новая, на статике. Разница в три раза.
Это к вопросу о производительности (кстати, процессорных ресурсов оно тоже потребляет меньше).
Что же до типов данных - в языке уж точно найдётся куда эти типы отобразить, всё же это современный ЯП где можно вводить свои типы данных.
Во что обходится "введение своего типа и отображение в него"? Это ведь тоже не бесплатно.
Одно дело когда у нас в языке есть поддержка нужных типов - достаточно просто прочитать запись в байтовый буфер (структуру) и затем сразу работать с ее полями напрямую, и совсем другое, когда нужно что-то куда-то "отображать" с затратой на это дополнительного времени и ресурсов процессора. И, кстати, numeric и decimal, при внешней схожести, все-таки разные типы данных - в памяти они по разному предстваляются.
Собственно к чему все это... К тому, что в качестве workaraund для небольших приложений это сгодится, но не для высоконагруженных систем, плотно работающих с БД. Там стоит все-таки, поискать какой-то специализированный язык, ориентированный на эффективное выполнение именно такого рода задач.
То что описано выше для RUST есть и в других языках (SQLite для С, для Borland Delphi и C++ Builder были наборы компонент для работы с InterBase/FireBird...) и для небольших БД все это вполне удовлетворительно работало. Но как только речь заходит от терабайтных объемах где в таблице может быть несколько сотен миллионов записей а в выборку запросто может "влететь" несколько десятков миллионов записей и все это один из тысяч одновременно работающих на сервере процессов - тут уже нужно что-то посерьезнее.
mayorovp
01.11.2023 17:38+4Что значит "везде"? Я так понимаю, что подобные программы пишутся под конкретную БД и завязаны на структуру конкретной БД.
Они завязаны на структуру конкретной БД (точнее, на конкретную структуру БД), но не на саму БД.
Типичный современный процесс разработки подразумевает наличие как минимум двух окружений со своими БД одинаковой структуры: "стейджинг" и "прод". Также, скорее всего, будут свои БД на рабочем компе каждого разработчика, отдельная БД будет создаваться при каждой сборке для запуска автотестов, и отдельная БД будет в превью-окружении привязанном к Pull/Merge Request. В сложных проектах будут свои БД в интеграционных окружениях разного уровня.
И всё что выше относится к случаю, когда с СУБД "общается" бекенд. У десктопных и у мобильных приложений свой отдельный мир, в котором обычно никакой СУБД нет, но иногда и у них появляется встроенная СУБД. В таком случае отдельная БД появляется на каждом устройстве.
Построение плана запроса в рантайме кратно снижает производительность.
Нет никакой разницы строится ли план при компиляции, при установке или при первом появлении запроса - времени в любом случае будет потрачено одинаково.
Разница появляется лишь в том случае, когда план строится при каждом запросе. Это лечится кешированием планов запроса. Если ваша СУБД не умеет так делать - ну что же, грустно.
Ещё производительность может просесть если запрос формируется не один и тот же, а реально разный. Но тут и вовсе ничего поделать не получится, автоматически такое на статику не переписать.
Одно дело когда у нас в языке есть поддержка нужных типов - достаточно просто прочитать запись в байтовый буфер (структуру) и затем сразу работать с ее полями напрямую, и совсем другое, когда нужно что-то куда-то "отображать" с затратой на это дополнительного времени и ресурсов процессора.
На фоне обработки JSON или, уж тем более, XML, которая в программе наверняка будет, все эти затраты на раскладывание байт по полям - копейки.
Тем более что использование нативных типов данных порой само по себе даёт ускорение, которое с лихвой окупает две конвертации.
SpiderEkb
01.11.2023 17:38Типичный современный процесс разработки подразумевает наличие как минимум двух окружений со своими БД одинаковой структуры: "стейджинг" и "прод". Также, скорее всего, будут свои БД на рабочем компе каждого разработчика, отдельная БД будет создаваться при каждой сборке для запуска автотестов, и отдельная БД будет в превью-окружении привязанном к Pull/Merge Request. В сложных проектах будут свои БД в интеграционных окружениях разного уровня.
Не поверите, но я в курсе.
Нет никакой разницы строится ли план при компиляции, при установке или при первом появлении запроса - времени в любом случае будет потрачено одинаково.
Но в первом случае это время будет потрачено в процессе установки один раз, во втором - в процессе работы при каждом запуске.
Это лечится кешированием планов запроса.
Кеш хранится ограниченное время. У нас есть достаточно емкие по объему данных задачи, которые запускаются раз в день, раз в неделю-две, раз в месяц, раз в год. Будет ваша БД хранить кеш запросов в течении года? Особенно с учетом того, что запросов разных в ней 100500 штук (реально так).
Наша система много чего умеет. И не только запросы хранить, но и сохранять данные между запусками программы в рамках одного задания (точнее, в рамках жизни группы активации (activation group), явлюяющейся подмножеством задания (job)).
И, тем не менее, разница между временем выполнения статического и динамического sql видна даже простыми замерами времени (не говоря уже про специализированные инструменты типа PEX (Performance EXplorer).
На фоне обработки JSON или, уж тем более, XML, которая в программе наверняка будет, все эти затраты на раскладывание байт по полям - копейки.
Вот поэтому мы не используем ни то ни другое кроме тех случаев, когда без этого совсем никак. Но это обычно касается оправки каких-то данных во внешние системы через очередь. Если же данные возвращаются в вебсервис через ESB шину, то там проще вернуть ResultSet:
EXEC SQL declare Cursor2 cursor with return to client for SELECT S01PS AS $$PS, S01DSC AS $$DSC FROM S01PF WHERE S01PS = :iParms.PS AND S01EXF = :iParms.EXF AND TRIM(S01DSC) like TRIM(:iParms.DSC ) fetch first 9999 rows only; EXEC SQL open Cursor2;Все. Данные ушли тому, кто нас вызвал.
Тем более что использование нативных типов данных порой само по себе даёт ускорение, которое с лихвой окупает две конвертации.
О каких двойных конвертациях речь? Для нас типы данных zoned (numeric) и packed (decimal) - нативные. Равно как и остальные SQL типы данных.
Там нет никакой конвертации. Описали структуру (это просто статическая структура - там нет никаких конструкторов, просто буфер с размеченными в нем полями), прочитали в этот буфер запись (вот просто с диска, бат в байт) и все. Дальше работаем с полями. Если это чистовые поля - вся арифметика с ним работает (а для полей с фиксированной точкой еще и операции с округлением - присвоение, деление, умножение и т.п.).
Я понимаю, что это так сразу в голову не заходит, но оно вот так вот... Никаких "пакетов", никаких "зависимостей", никаких "отображений", "конвертаций"...
mayorovp
01.11.2023 17:38Не поверите, но я в курсе.
А если в курсе что могут быть несколько БД с одной структурой - почему задаёте глупые вопросы?
У нас есть достаточно емкие по объему данных задачи, которые запускаются раз в день, раз в неделю-две, раз в месяц, раз в год.
Чем более ёмкая задача по объёму данных, тем меньше влияние подготовительного этапа.
А уж пытаться избежать повторного построения плана запроса спустя год - это даже не экономия на спичках...
И, тем не менее, разница между временем выполнения статического и динамического sql видна даже простыми замерами времени
Это приколы вашей СУБД.
У той же Постгри планы запросов нужно периодически обновлять, потому что оптимизатор слишком сильно завязывается на текущую статистику по таблицам. Как следствие, статические запросы попросту не имеют смысла.
О каких двойных конвертациях речь? Для нас типы данных zoned (numeric) и packed (decimal) - нативные. Равно как и остальные SQL типы данных.
Да вот пусть даже из zoned в packed, а потом снова в zoned.
Zoned decimal - это настолько ужасный формат, что ему даже аппаратное ускорение что мёртвому припарки. От этой гадости надо избавляться при первой возможности.
Я понимаю, что это так сразу в голову не заходит, но оно вот так вот... Никаких "пакетов", никаких "зависимостей", никаких "отображений", "конвертаций"...
Вот это и плохо.
SpiderEkb
01.11.2023 17:38+1Чем более ёмкая задача по объёму данных, тем меньше влияние подготовительного этапа.
Увы, но это не совсем так. Если запрос достаточно сложен, он может отбирать, скажем, 50 записей из 50 000 000. И тогда "подготовительный этап" может оказаться доллше самой обработки.
Я уже писал - задача, запускаемая каждый день. Запрос
exec sql declare curRDKCHK1Clients cursor for select RDKCUS, RDKCLC, GFP3R, GFCTP, RDKSER, RDKNUM, RDKSERNUM, RDKOPN, RDKEDT from RDKPF RDK join GFPF on (GFCUS, GFCLC) = (RDK.RDKCUS, RDK.RDKCLC) left join HDA1PF HDA1 on (HDA1CUS, HDA1CLC, HDA1TYP) = (RDK.RDKCUS, RDK.RDKCLC, 'DOC') where (RDKUCD, RDKSDL, RDKOSN) = ('001', 'Y', 'Y') and (HDA1.HDA1DAT is null or HDA1.HDA1DAT < :minDA) and exists ( select CAFCUS from CAFPF where (CAFCUS, CAFCLC, CAFATR1) = (RDK.RDKCUS, RDK.RDKCLC, 'Y') ) and not exists ( select RDKMCUS from RDKMPF where (RDKMCUS, RDKMCLC, RDKMUCD, RDKMSER, RDKMNUM, RDKMOPN, RDKMTP) = (RDK.RDKCUS, RDK.RDKCLC, '001', RDK.RDKSER, RDK.RDKNUM, RDK.RDKOPN, '3') );выбирает порядка 1 200 записей из около 50 000 000. При динамическим подходе выполняется 15 минут из которых 13 уходит на "подготовительный этап". В статическом - 5 минут, на подготовку уходит несколько секунд.
И это далеко не самый сложный запрос из того, с чем псотоянно приходится работать.
А уж пытаться избежать повторного построения плана запроса спустя год - это даже не экономия на спичках...
А если речь идет о запросе (точнее, там 5 разных запросов), который выбирает 25 000 000 записей из 50 000 000, которые потом обрабатываются параллельно в 10 потоков и все это занимает 2+ часа?
У той же Постгри планы запросов нужно периодически обновлять, потому что оптимизатор слишком сильно завязывается на текущую статистику по таблицам. Как следствие, статические запросы попросту не имеют смысла.
Но, почему-то, они стабильно работают быстрее и потребляют меньше процессорных ресурсов.
Тут хочется задать вопрос - то, что вы разрабатывает проходит обязательное нагрузочное тестирование? Вам говорят "вот тут у вас идет излишнее обращение к БД, а тут много времени уходит на динамическое выделение/освобождение памяти"? Подозреваю, что нет... Смею полагать, что вам никогда не прилетает от сопровождение "дефект производительности":
Коллеги, сервис *** за последние 5 недель увеличил потребление процессорных ресурсов в 3 раза!!!
Он уже является 2-м по величине сервисом после *****.
В качестве альтернативы мы рассматриваем перенос запуска сервиса на резервный сервер, но там есть лаг по отставанию до 10 мин.
Заказчикам сервиса это может не понравиться :(Да вот пусть даже из zoned в packed, а потом снова в zoned.
Зачем???
Я же писал:
dcl-ds t_dsSQLData qualified template; CUS char(6) inz; CLC char(6) inz; Packet char(3) inz; CTP char(2) inz; SER char(10) inz; NUM char(35) inz; SERNUM char(45) inz; OPN zoned(7: 0) inz; EDT zoned(7: 0) inz; end-ds;Это описание структуры куда читается строка запроса
select RDKCUS, RDKCLC, GFP3R, GFCTP, RDKSER, RDKNUM, RDKSERNUM, RDKOPN, RDKEDTПоля RDKOPN и RDKEDT в БД описаны как NUMERIC(7,0). У нас этому полностью соответствует нативный тип данных zoned(7:0). Никаких конвертаций, никаких преобразований. Просто двоичное чтение с диска в буфер. Дальше с этим полем работаем как с обычной числовой переменной - можем складывать, умножать, делить, вычитать...
Zoned decimal - это настолько ужасный формат, что ему даже аппаратное ускорение что мёртвому припарки.
Это потому что вам приходится его куда-то конвертировать прежде че с ним работать.
Вот это и плохо.
Что плохо? Что вам приходится следить на не поменялось ли что-то в зависимости? Фиксировать версии пакетов? Плохо, что вместо одной строки (например, прочистка работой таблицы от старых записей)
dcl-s now timestamp;
now = %timestamp(*sys);
exec sql delete from table where dte < :now;
вам придется тянуть зависимости и городить огород с sqlx::query и chrono::NaiveDateTime?
Мне не кажется что это плохо.
Плохо когда обработка выборки сводился к простому и понятному
dou lastBlock; exec sql fetch curRDKCHK1Clients for :sqlRows rows into :dsSQLData; lastBlock = sqlGetRows(rowsRead); for row = 1 to rowsRead; procData(dsSQLData(row)); endfor; enddo;где sqlGetRows возвращает количество реально причитанных записей и признак того, что это блок является последним в выборке:
dcl-proc sqlGetRows; dcl-pi *n ind; sqlRowsRead int(10); end-pi; dcl-s sqlRowCount packed(31 : 0) inz(*zero); dcl-s sqlDB2LstRow int(10) inz(*zero); exec sql GET DIAGNOSTICS :sqlRowCount = ROW_COUNT, :sqlDB2LstRow = DB2_LAST_ROW; sqlRowsRead = sqlRowCount; return (sqlDB2LstRow = 100); end-proc;Я вот не уверен что это так плохо...
mayorovp
01.11.2023 17:3813 минут на построение плана динамического запроса - это пиздец какой-то, не распространяйте этот опыт на другие СУБД. Такого просто не должно быть.
Дальше с этим полем работаем как с обычной числовой переменной - можем складывать, умножать, делить, вычитать...
И каждая из этих операций выполняется в 2 раза медленнее чем в packed формате.
Нет, если у вас 128-битная архитектура и полная аппаратная поддержка zoned формата, то конкретно 7-разрядное число в zoned формате будет работать нормально, но вот уже 64 битах упакованый формат будет выигрывать на математических операциях, просто потому что будет влезать в регистр процессора в то время когда zoned формат туда не влезает.
И даже на 128 битах упакованный формат станет выигрывать как только число разрядов увеличится до 9.
А если у вас архитектура процессора amd64, где на поддержку двоично-десятичных чисел давным-давно забили - то оба формата хрень и быстрее всего работают обычные i64.
Это потому что вам приходится его куда-то конвертировать прежде че с ним работать.
Нет, преобразование в более упакованный формат как раз ускоряет расчёты, если расчётов достаточно много, а преобразование сделано заранее (желательно - ещё до записи в БД).
Что плохо? Что вам приходится следить на не поменялось ли что-то в зависимости? Фиксировать версии пакетов?
Плохо то, что вы ограничены исключительно тем кодом, который вам "с барского плеча" предоставил производитель, и тем который вы написали сами.
Пакеты - это возможность, а не необходимость.
Вы понимаете, что вы сейчас пытаетесь поставить минус sqlx просто за то, что sqlx не предустановлен на ваше железо?
Плохо когда обработка выборки сводился к простому и понятному
Вот вообще не вижу ни одного отличия от sqlx в плане простоты и понятности.
SpiderEkb
01.11.2023 17:3813 минут на построение плана динамического запроса - это пиздец какой-то, не распространяйте этот опыт на другие СУБД. Такого просто не должно быть.
СУБД на самая плохая - DB2 for i
Но с динамическми скулем
95% утилизации - выполнение запроса SQL
Из 1015 сек выполнения программы - 975 сек - это выполнение SQL запроса
Переход на статику сразу в три раза уменьшил время выполнения запроса и потребление ресурсов. Это объективные цифры.
После перехода на статику
И каждая из этих операций выполняется в 2 раза медленнее чем в packed формате.
Нет, если у вас 128-битная архитектура и полная аппаратная поддержка zoned формата, то конкретно 7-разрядное число в zoned формате будет работать нормально, но вот уже 64 битах упакованый формат будет выигрывать на математических операциях, просто потому что будет влезать в регистр процессора в то время когда zoned формат туда не влезает.
И даже на 128 битах упакованный формат станет выигрывать как только число разрядов увеличится до 9.
Вы понимаете, что все это работает только когда у вас язык нативно поддерживает эти типы? В вашем случае вы больше времени будет тратить на конвертацию из в обычный float и обратно
А если у вас архитектура процессора amd64
У нас архитектура процессора IBM Power9 Конкретно - Power E980 (120 процессорных ядер, каждое по 8 потоков)
Вы понимаете, что вы сейчас пытаетесь поставить минус sqlx просто за то, что sqlx не предустановлен на ваше железо?
Нет. Потому что это натягивание совы на глобус. Попытка прикрутить то, чего не предусмотрено на нативном уровне.
Вот вообще не вижу ни одного отличия от sqlx в плане простоты и понятности.
А теперь попробуйте написать программу, которая возвращает SQL resultset в вызывающую ее программу. Т.е. вас вызвали, вы что-то сделали и вернул резалтсет, который на той стороне воспринимается как обычная SQL выборка. И он может быть сформирован не обязательно как результат какого курсора, а, например, из таблицы в памяти. Т.е. что-то там посчитали, сложили в массив структур и вернули в виде SQL resultset вызывающему.
Тут вопрос в том, что все SQL типы данных совпадают с типами данных языка. Для них реализована вся арифметика. SQL поддерживается "насквозь", а не как ограниченный вызов через библиотечные функции которые что-то там внутри себя крутят.
Вот еще раз. Читаете запись. В ней есть поле в формате DATA. Просто набор байт. Что с ним произойдет прежде чем вы сможете с ним работать (ну, скажем, вычесть из нее заданное количество дней)? Ваш язык такой тип не поддерживает. Значит после чтения из БД эти байтики отправятся на какое-то преобразование, будет создан какой-то объект из них... Все это время и такты процессора.
В нашем случае эти байтики просто лягут в область памяти, объявленную как переменная соотв. типа. Безо всяких преобразований.
Вот предствьте себе "минималистический язык" в котором всего один тип данных - массив байт. И вас всегда надо сначала создать переменную типа byte[4], а потом, если вы хотите работа с ней как целым числом, обернуть ее в объект типа int32. И только после этого вы что-то с ней сможете делать. Вот о чем речь.
Плохо то, что вы ограничены исключительно тем кодом, который вам "с барского плеча" предоставил производитель, и тем который вы написали сами.
А вы чем ограничены? Каким-то кодом, неизвестно кем написанным и неизвестно как протестированым? Обнаружили в нем ошибку и что? Кто и в какие сроки вам ее исправит? И как вы будет оправдываться перед клиентами "ой, извините, у вас тут со счета 10 лямов куда-то ушло, но это не мы виноваты - это ошибка в пакете который мы из интернета скачали..." Серьезно?
Есть у нас на платформе С, есть С++. Но вся бизнес-логика намного проще и эффективнее реализуется на специально разработанном для этого языке. Это, опять же, результаты измерений.
Да, там есть не все что нужно для, например, низкоуровневых вещей. Но их мы пишем на С/С++. А вот работа с БД, коммерческие расчеты на нем объективно быстрее идут. У нас достаточное количество разработчиков, которые хорошо владеют как С/С++, так специальным языком. И, главное, имеют достаточно опыта для правильного выбора наиболее подходящего с точки зрения эффективности инструмента для решения той или иной задачи. Понимает, это как винты крестовые - есть PH, есть PZ и для каждого типа есть соответствующая бита. Можно заменить, но эффективность уже не та будет.
mayorovp
01.11.2023 17:38Вы понимаете, что все это работает только когда у вас язык нативно поддерживает эти типы? В вашем случае вы больше времени будет тратить на конвертацию из в обычный float и обратно
Работает что?
Для того что язык не поддерживает, в Rust на крайний случай есть ассемблерные вставки.
Нет. Потому что это натягивание совы на глобус. Попытка прикрутить то, чего не предусмотрено на нативном уровне.
Моя ваша не понимать.
Какое отношение пакеты имеют к архитектуре процессора?
А теперь попробуйте написать программу, которая возвращает SQL resultset в вызывающую ее программу. Т.е. вас вызвали, вы что-то сделали и вернул резалтсет, который на той стороне воспринимается как обычная SQL выборка. И он может быть сформирован не обязательно как результат какого курсора, а, например, из таблицы в памяти. Т.е. что-то там посчитали, сложили в массив структур и вернули в виде SQL resultset вызывающему.
Реализовать нужные трейты для вашей структуры данных, чтобы она сошла за SQL resultset - вообще не проблема, вопрос лишь в "нафига". Обычно решают прямо противоположную задачу.
Но вы куда-то отклонились от темы. Вы сначала говорили про простоту и понятность программы, а теперь пошли какие-то формирования resultset в памяти.
Вот еще раз. Читаете запись. В ней есть поле в формате DATA. Просто набор байт. Что с ним произойдет прежде чем вы сможете с ним работать (ну, скажем, вычесть из нее заданное количество дней)? Ваш язык такой тип не поддерживает.
А что в таком случае будете делать вы?
Сейчас вы, наверное, скажете что у вас-то язык всё поддерживает. Только вот у нас тоже язык всё что нужно поддерживает, так в чём же, блин, разница?
Вот предствьте себе "минималистический язык" в котором всего один тип данных - массив байт. И вас всегда надо сначала создать переменную типа byte[4], а потом, если вы хотите работа с ней как целым числом, обернуть ее в объект типа int32. И только после этого вы что-то с ней сможете делать. Вот о чем речь.
Ага, а потом выясняется что такая переменная обязана оставаться в памяти как часть массива, и не может быть помещена в регистр процессора целиком с целью оптимизации.
Ваша концепция не является чем-то новым, она стара, и не просто так от подобной концепции отказались.
А вы чем ограничены? Каким-то кодом, неизвестно кем написанным и неизвестно как протестированым? Обнаружили в нем ошибку и что? Кто и в какие сроки вам ее исправит?
Так я сам и исправлю, если будет необходимо - код-то открыт.
Да, это может быть долго, так ведь и без сторонних решений писать всё самому - тоже долго.
И как вы будет оправдываться перед клиентами "ой, извините, у вас тут со счета 10 лямов куда-то ушло, но это не мы виноваты - это ошибка в пакете который мы из интернета скачали..." Серьезно?
Вы, скорее всего, даже не смотрели код пакета sqlx, но уже обвиняете его в наличии ошибок которые могут списать со счёта 10 лямов.
А ещё вы не учитываете, что ошибка может быть как в коде IBM, так и в вашем собственном. За эти ошибки как оправдываться будете?
SpiderEkb
01.11.2023 17:38Реализовать нужные трейты для вашей структуры данных, чтобы она сошла за SQL resultset - вообще не проблема, вопрос лишь в "нафига". Обычно решают прямо противоположную задачу.
Самое частое - есть мобильное приложение. У него есть REST API, которое дергает вебсервис, который через ESB шину вызывает сервисмодуль на центральном сервере и ждет от него выборку в виде резалсета.
Это может быть и не мобильное приложение, а какая-то внешняя система или еще что-то. Но вот так оно работает. Сервера полностью изодлированы от мира, общать с яними можно только запросами через ESB или через очередь. Б - безопасность.
Можно накрутить трейтов и прочего. Но все это решается куда более простыми методами на самом деле.
Сервисмодлуль, кстати, может разом возвращать и 2 и 3 резалтсета. Одним ответом.
Выглядит это, например так - из окна интерактвного SQL вызываем единую сервисную процедуру с нужными параметрам, в ответ открывается три окошка с результатами выборок.
Ага, а потом выясняется что такая переменная обязана оставаться в памяти как часть массива, и не может быть помещена в регистр процессора целиком с целью оптимизации.
Вы говорите о "регистровой оптимизации" так, как будто ваш код уже оптимизирован на 146%. А на практике обычно бывает так, что просто изменеием алгоритма можно выморщить прирос в скорости и эффективности если не в разы, то на 15-20%
Вы, скорее всего, даже не смотрели код пакета sqlx
На С я начал писать где-то в 89-90-м году. И до 17-го года это был основной язык. И с БД поработал изрядно (начиная с Paradox через ParadoxEngine, позже BDE и до InterBase/FireBird с использование компонент очень похожих на sqlx идеологически). Даже немного пришлось с dbVista (которая нынче Raima DataManager - RDM). Так что у меня есть возможность сравнивать SQL с прямым доступом к БД (у нас он тоже есть в языке и в ряде случаев он быстрее и эффективнее чем SQL что тоже проверено PEX-статистиками), между работой через компоненты и библиотеки и возможностью использовать встроенные средства...
Так что все что пишу - результат многолетнего опыта, а сейчас еще и результаты множества нагрузочных тестов.
Специализированный инструмент для решения конкретный задач всегда будет лучше универсального. Раскроить листы фанеры на большие прямоугольники циркулярной пилой быстрее, ровнее и проще чем лобзиком. А пилить дрова лучше цепной.
А вот эти решения - они от того, что нет специального инструмента. И задачи не столько объемные - вряд ли вам нужно в рамках одного сервера реализовать десятки тысяч одновременно работающих бизнес-процессов которые прокручивают сотни миллионов бизнес-операций в день и совершают терабайты изменений БД в сутки. И все это не должно тормозить и, более того, у сервера всегда должен быть ощутимый запас производительности на случай периодов пиковых нагрузок (которые да, случаются достаточно регулярно и от нас никак не зависят).
mayorovp
01.11.2023 17:38Самое частое - есть мобильное приложение. У него есть REST API, которое дергает вебсервис, который через ESB шину вызывает сервисмодуль на центральном сервере и ждет от него выборку в виде резалсета.
Эта задача вообще не требует sql resultset.
Общение с ESB же через сеть идёт, верно? В таком случае всё что требуется на стороне сервисмодуля - это передать в сеть данные в таком формате, в каком ESB может их прочитать. Это решается сериализацией.
Поскольку типы данных тут вы выбираете сами, вы всегда можете выбрать их так, чтобы сериализация была бесплатной (учитывая что передачи данных в сеть всё равно не избежать, а значит её стоимость при сравнении разных решений можно не учитывать).
Но даже если я ошибся и ESB вызывает сервисмодуль как подпрограмму - вам всё равно нет необходимости возвращать какой-то абстрактный sql resultset. Вам нужно вернуть условный esb resultset, который совершенно не обязан бинарно совпадать с условным pgSQL resultset.
Можно накрутить трейтов и прочего. Но все это решается куда более простыми методами на самом деле.
Трейты - это и есть простое решение на самом деле.
Вы говорите о "регистровой оптимизации" так, как будто ваш код уже оптимизирован на 146%. А на практике обычно бывает так, что просто изменеием алгоритма можно выморщить прирос в скорости и эффективности если не в разы, то на 15-20%
Вот именно, алгоритм важен в первую очередь.
А регистровую оптимизацию я упомянул потому, что её делает компилятор, если ему не мешать лишними указаниями на то где и как должны храниться переменные.
Специализированный инструмент для решения конкретный задач всегда будет лучше универсального.
Но он не может заменить универсальный. А вы пришли в пост, рассказывающий про универсальный и бесплатный инструмент, и упорно рекламируете специализированный и дорогой.
SpiderEkb
01.11.2023 17:38Но даже если я ошибся и ESB вызывает сервисмодуль как подпрограмму - вам всё равно нет необходимости возвращать какой-то абстрактный sql resultset. Вам нужно вернуть условный esb resultset, который совершенно не обязан бинарно совпадать с условным pgSQL resultset.
Несколько не так. Ситуация таков - есть АБС (Автоматизированная Банковская Система), которая работает на центральном сервере и обеспечивает всю банковскую логику (а это очень много чего - десятки тысяч одновременно работающих бизнес-процессов, там все нереально сложно и необъятно разуму). Это mission-critical мастер-система.
Веб-сервис - один из способов для внешней системы получить какие-то данные (второй способ - очередь - у нас это IBM MQ на нашей стороне стыкующаяся с WBI на "той" стороне). Фактически вебсервис крайне тупая штука - это просто преобразователь интерфейсов и типов данных из/в внешних в/из наши и обратно. Есть два типа сервисов - геттеры (получающие ответ на конкретный запрос) и A/M/D (Add/Maintain/Delete) - изменение данных.
Поскольку у нас "своих забот хватает" - у нас и без этого много чего крутится постоянно, сервис-модуль не должен занимать много времени и ресурсов. Посему мы максимально быстро и с минимальными затратами ресурсов просто отдаем нужные данные а там уже пусть с ними сами разбираются. И максимально быстро и минимально затратно для нас - отдать sql resulset там где требуется выборка или просто одиночный результат.
Но наша основная задача - посильно снизить нагрузку на нашу систему. Поэтому все что можно делать не у нас - выносим. Оставляем только ту логику, которая обеспечивает собственно работу банка.
Но он не может заменить универсальный.
Не может. Как и универсальный проиграет в конкретной задаче специализированному. На самом деле, у нас и универсальны есть - С/С++ (и там тоже можно SQL в код встраивать и есть библиотека RECIO для прямой работы с БД, но т.к. там не поддержки SQL типов данных кроме decimal, работать с БД там мало смысла и много заморочек). Более того, у нас есть возможность написать кусок кода на С/С++, кусок на нашем специальном языке, а потом собрать это в одну программу :-)
А пришел я с одним вопросом - как это работает в Rust. Может там что-то реально интересное придумали. Выяснилось что нет - все то же самое, что было в С/С++ с незапамятных времен (начиная с библиотек ParadoxEngine и dbVista еще под DOS и далее до SQLite и борландовских компонент для Delphi/C++ Builder для InterBase/FireBird с которыми когда-то приходилось работать).
mayorovp
01.11.2023 17:38Так, проглядел середину комментария, отвечу отдельно.
А если речь идет о запросе (точнее, там 5 разных запросов), который выбирает 25 000 000 записей из 50 000 000, которые потом обрабатываются параллельно в 10 потоков и все это занимает 2+ часа?
Я не понял, они обрабатываются 2 часа в 10 потоков средствами запроса или уже после выборки?
Если первое - то да, тут план надо готовить аккуратнее.
Если второе - то на фоне этих 2 часов обработки в 10 потоков лишнее построение плана запроса вот вообще незаметно. Если меня попросят оптимизировать подобное - я начну не со спичек, а с поиска лучшего алгоритма, потом перейду к форматам данных и всяким локальностям в кешах.
Тут хочется задать вопрос - то, что вы разрабатывает проходит обязательное нагрузочное тестирование? Вам говорят "вот тут у вас идет излишнее обращение к БД, а тут много времени уходит на динамическое выделение/освобождение памяти"? Подозреваю, что нет... Смею полагать, что вам никогда не прилетает от сопровождение "дефект производительности":
Обязательное - нет (заказчик почему-то думает что оно ему не нужно и за него не платит). Но вот дефекты производительности я встречал и чинил. Причины у этих дефектов были самые разные, некоторые даже совсем "весёлые", только вот ни разу этой причиной не было слишком длительное формирование плана запроса.
SpiderEkb
01.11.2023 17:38Я не понял, они обрабатываются 2 часа в 10 потоков средствами запроса или уже после выборки?
Я бы сказал в процессе выборки. Стандартный паттерн параллельной обработки выглядит так: есть два программных объекта - головное задание и обработчик. Запускается головное задание, которое запускает (системной командой SBMJOB - Submit Job или функцией spawn) нужное количество обработчиков, каждый в своем фоновом задании (это более устойчивый, безопасный и сопровождаемый вариант нежели posix threads, которые у нас тоже есть, но используются реже).
Когда все "дочки" запустились, головное задание начинает выборку данных для обработки. Обычно мы используем пакетное чтение - читаем сразу 1000 (например) записей выборки. Прочитанные денные выкладываются на "конвейер" (это может быть пайп, но у нас на платформе есть более пригодные для этого системные объекты - очереди DataQueue и UserQueue). Обработчики подхватывают пакеты с конвейера и обрабатывают данные.
Т.е. голова еще читает, а дочки уже обрабатывают.
тут план надо готовить аккуратнее
Естественно. Каждый мало-мальски сложный запрос предварительно гоняется в интерактивном SQL, там можно посмотреть визуализацию плана (картинку выкладывал как это выглядит), посмотреть рекомендации оптимизатора, оценить время выполнения, объемы выборки и т.п. Тут все достаточно основательно делается.
Я просто к тому, что статический SQL всегда быстрее динамического работает.
Кстати, не удивлюсь, если он периодически контролирует план запроса. Система очень мощная - тут даже при переносе программного объекта на другое железо (скажем, купили сервер нового поколения) он будет пересобран при первом запуске под тот процессор, на котором работает (для этого кроме исполняемого кода в программном объекте хранится т.н. TIMI-код - код в Technology Independant Michine Instructions).
Когда-то (я не застал) работали на серверах Power7. Потом перешли на Power8 (это уже помню - 824 на тестовом сервере, 828 на боевом). Сейчас - Power9 (E960 на бою). И перенос системы (наших объектов) просто копированием. А пересборку под новый проц система уже сама делает при первом запуске программы. Это любителям "регистровой оптимизации".
Если второе - то на фоне этих 2 часов обработки в 10 потоков лишнее построение плана запроса вот вообще незаметно.
В данном случае да. Но все что может быть сделано более эффективно малыми затратами, должно делаться более эффективно. А использование статического скуля как раз та вещь, которая не требует никаких затрат, но дает некоторый прирост производительности и сокращений времени. Куда более ощутимое, чем "регистровая оптимизация".
Если меня попросят оптимизировать подобное - я начну не со спичек, а с поиска лучшего алгоритма, потом перейду к форматам данных и всяким локальностям в кешах.
Именно этим и занимаюсь в последнее время. Много старых модулей которые удовлетворительно работали пока было 30 000 000 клиентов, но вносят ощутимую задержку сейчас, когда их стало 50 000 000млн.
Благо у нас для кеширования очень много возможностей. Например, есть такая сущность внутри задания, как "группа активации". Так вот, если в одной ГА вызывать программу несколько раз, то все глобальные и статические переменные сохраняются с прошлого вызова. И этим можно активно пользоваться для кеширования - что-то сохраняем с предыдущих вызовов и проверяем на каждом новом - нужно ли пересчитывать (условно) или нет.
Обязательное - нет (заказчик почему-то думает что оно ему не нужно и за него не платит). Но вот дефекты производительности я встречал и чинил. Причины у этих дефектов были самые разные, некоторые даже совсем "весёлые", только вот ни разу этой причиной не было слишком длительное формирование плана запроса.
У нас НТ - обязательный этап. Сначала компонентное, потом бизнес, потом нагрузочное, интеграционное и техтест на прелайве (последние три на копии промсреды). Только после положительных заключений по всем этапам согласуется внедрение (и с обязательным планом отката).
Ибо заказчиком у нас бизнес-подразделения, но за то, чтобы все работало отвечаем мы (в первую очередь служба сопровождения и дежурная смена, но если что, то они только откатят или WA какой временно организуют, а претензии все к нам пойдут).
Действительно, дефекты производительности по очень многим причинам, главным образом алгоритмического характера, но, повторюсь, если есть возможность на 2-3% ускорится просто переходом на статический скуль - это будет сделано. Более того, есть "нефункциональные требования" где явно прописано, что использования динамического скуля допустимо только в исключительных случаях и с кучей оговорок (например, ручное формирование SQLDA - есть тут такая сущность - которое очень гиморно, но ощутимо ускоряет процесс и снижает нагрузку).
Дело в том, что в ситуациях когда запрос известен заранее и не изменяется от вызова к вызову, статически скуль не только быстрее, но и проще - вместо трех строк
exec sql declare SqlStatement1 statement; exec sql prepare SqlStatement1 from :sqlTxt1; exec sql declare currACY1 cursor for SqlStatement1;пишется одна
exec sql declare curRDKCHK1Clients cursor for select ...которая на самом деле является декларативной, но не исполняемой строкой (т.е. не требует проверки sqlCode на предмет ошибки). Плюсом - корректность синтаксиса запроса будет проверяться на этапе компиляции (для кода, содержащего SQL компиляция идет в два этапа - сначала SQL-препроцессор, потом уже собственно компилятор).
Так что это и проще и быстрее.
Вообще тут SQL это как расширение языка и его часть. Есть возможность напрямую к таблицам обращаться (позиционирование по индексу, чтение записи по значению ключа в заданную структуру-буфер, добавление записи из буфера, удаление, обновление...), так и посредством SQL все тоже самое делать (скажем читать очередную запись или блок записей в структуру-буфер или массив структур).
Более того, прочитать одну (или небольшое количество) записей из одной таблицы проще и эффективнее прямым доступом чем SQL (тоже результаты наших исследований PEX-ом). Проверить наличие записи с заданным значением ключа тоже проще прямым доступом - просто смотрим есть ли такая запись в индексе без собственно чтения ее из таблицы.
Все это дает возможность писать максимально эффективный код малыми затратами сил. Но требует некоторого опыта и понимания как оно работает.
mayorovp
01.11.2023 17:38А использование статического скуля как раз та вещь, которая не требует никаких затрат, но дает некоторый прирост производительности и сокращений времени.
Это у вас использование статического скуля не требует никаких затрат. В других СУБД может быть совсем по-другому (включая отсутствие "статического скуля").
Это у вас использование статического скуля дает прирост производительности.
Дело в том, что в ситуациях когда запрос известен заранее и не изменяется от вызова к вызову, статически скуль не только быстрее, но и проще - вместо трех строк [...] пишется одна
Это у вас пишется вместо трёх строк одна. На других языках это может выглядеть совсем по-другому.
SpiderEkb
01.11.2023 17:38-1Все верно, все правильно. У нас.
Я говорил уже, что опыт работы с различными БД через разные библиотеки и компоненты на С/С++ у меня есть. И прямым доступом и посредством SQL.
И вижу, что специализированный язык с нативной поддержкой всех типов данных, имеющихся в БД и плотной интеграцией с SQL дает больше свободы, более удобен и эффективен чем все эти компоненты и библиотеки.
На прошлой работе БД была в качестве хранилища некой конфигурации системы. Десяток-полтора таблиц, максимум десяток тысяч записей на таблицу. Типичные объемы вуборки - сотня, ну тысяча записей. И программа работала фактически в монопольном режиме (все ресурсы машины мои). Там все эти компоненты вообще не парили.
Тут работа с БД - ключевой момент. Любой бизнес-процесс суть работа с потоком данных который начинается с формирования выборки (собственно потока) и дальше какая-то потоковая обработка результата выборки.
Таблиц десятки тысяч. Количество записей в таблице может запросто дойти до нескольких сотен миллионов. Объемы выборок могут доходить до десятков миллионов записей. И при этом параллельно работают десятки тысяч разных процессов. Т.е. нужно заботится не только о том, чтобы оно работало максимально быстро, но и при этом потребляло минимум ресурсов, прежде всего CPU - с памятью проблем нет - в основном используется одноуровневая модель памяти (SINGLE LEVEL, есть еще TERASPACE, но это для особых случаев когда нужно одним куском выделить большой, до 2Гб, объем в куче - одноуровневая позволяет не более 16Мб выделять) когда ты не знаешь где тебе физически выделили память - в ОЗУ (у нас ее 12Тб) или на диске (400Тб SSD массив). А вот процессор - да. 120 8-потоковых ядер на все задачи. В период пиковых нагрузок утилизация CPU может доходить до 90%, а это уже риски.
Посему (что для меня было некоторым "переходом на новый уровень") приходится всегда держать в голове что "ты тут не один" ну и соответственно подходить к решению задачи - не только быстро, но и с минимальными затратами ресурсов.
Возвращаясь в нашим баранам. Нашел вот одно старое заключение с НТ. Как раз по поводу динамического скуля:
Из PEX статистики работы PBC01U01R видно, что 33% времени и 36% ресурсов CPU тратится на выполнение QSQRPARS в программе STL#CHKN, т.е. парсинг статических выражений при подготовке SQL запроса,
Сократить данные русурсозатраты практически до нуля можно путем описания параметров sql запросов через SQL Descriptor Area (SQLDA).
Поскольку CU130 один из наиболее активно используемых сервис модулей, необоснованное повышенное ресурсопотребление является малодопустимым. Просьба инициировать доработку STL#CHKN.
В том случае проще всего оказалось не возиться руками с SQLDA (на редкость муторное занятие и по кодлу объемное), но вообще отказаться там от SQL в пользу прямой работы с БД т.к. там выборки были невелики (десяток-два записей), выполнялись по двум таблицам и полностью покрывались индексами.
В результате получилось чуть быстрее и существенно экономичнее по потреблению ресурсов
Текущая версия функционала (копия в пром среды от 25.01.2020), а - 7.98 м. сек
Обновленная версия, СМ - 6.71 м. сек
Общий тезис такой - если есть возможность, использование специализированного инструмента для решения специфических задач всегда даст больший эффект чем разного рода "регистровые оптимизации".
И это везде - если вы работает в промавтоматизации (или подобных областях) где требуется жесткий реалтайм, вы не будете использовать обычную ОС, но специализированную ОСРВ - QNX, WindRiver Linux, vxWorks и подобные.
Та платформа, IBM i, и специализированный язык RPG (более 80% кода на этой платформе пишется на нем), что используется у нас, достаточно распространены в мире там, где нужны коммерческие расчеты (банки, страховые и т.п.). Она создана именно для решения таких вот задач и в них они хороши. Это интенсивный путь развития.
Использование универсальных инструментов с обвешиванием их всевозможными "примочками" для решения специфических задач - экстенсивный путь развития, который приводит к необходимости постоянного наращивания ресурсов по мере увеличения нагрузки.
Т.е. наличие возможности работать с БД из ЯП общего назначения хорошо, но до определенного предела, до тех пор, пока работа с большими БД не становится основной задачей. С некоторого момента уже стоит думать в сторону перехода на специализированные системы и языки.
Gorthauer87
А ей все так же нужен целый инстанс postgess, чтобы проверка запросов сработала?