Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Где истина — ORM или SQL? | Владимир Кальсков, Илья Рачинский | AzovDevMeetup 2018

Где истина — ORM или SQL? | Владимир Кальсков, Илья Рачинский | AzovDevMeetup 2018

Хранилище данных — один из важнейших компонентов программных продуктов. Выбор способа работы с данными является основополагающим фактором развития и поддержки проекта. Подход ORM предоставляет удобный интерфейс для манипуляции данными. Но всегда ли выбор ORM оправдан? Нужен ли программисту SQL или ему поможет DBA?
• ORM — мо(д/ж)но, SQL — ну(д/ж)но.
• Всегда ли оправдан выбор в пользу ORM/SQL?
• Как сравнивать? Что сравнивать?
• Одновременное использование SQL и ORM.
• Так Д или Ж?

JSC “Arcadia Inc.”

October 13, 2018
Tweet

More Decks by JSC “Arcadia Inc.”

Other Decks in Programming

Transcript

  1. 2018 О себе 2 • 10 лет в IT •

    Full stack разработчик. • Проект онлайн аукцион, под капотом .Net, Angular 6, Angular material, NgRx • Люблю Frontend: Angular, Rx, Redux и остальной зоопарк • Js => AngularJs => Angular(6) => Js
  2. 2018 О себе 3 • Работаю в IT более 10

    лет • В Аркадии старший программист в команде по разработке приложения для обмена мультимедиа данными • AngularJS, PHP, MySQL, MongoDB, GO lang
  3. 2018 Вступление • Задачи • Хранить данные • Получение и

    обработка данных • Время, силы, финансы • Проблемы • Доступ к данным • Выбор инструмента • Примеры • Entity Framework, Sequelize • MySQL 4
  4. 2018 Disclaimer Все нижесказанное основывается на опыте авторов, любое сходство

    с реальными событиями, скорее всего, не случайно. 5
  5. 2018 И пришел спаситель - ORM • Модели - круто!

    7 • Инъекциям - нет!​ • Быстро писать - хорошо!​ • Ошибки - нету!
  6. 2018 Критерии ж/д • Безопасность • Сложность получения/выборки данных •

    Производительность • Порог вхождения • Переносимость/поддержка модели 9
  7. 2018 Безопасность (SQL инъекции) 11 myQuery = `select secret_data from

    users where userid = ${userid_param}`; context.User.Where(c => c.userid == userid) SQL: ORM:
  8. 2018 Сложность получения/выборки данных 18 INSERT INTO /*{$comment}*/ tarticles (id_article,

    id_type, id_brand) SELECT p2ai.id_article, p2a.id_type, p2a.id_brand FROM {$table_info} AS p2ai INNER JOIN price2_wh AS p2w ON (p2ai.id_wh=p2w.id_wh AND p2ai.is_availability = 1) INNER JOIN price2_article AS p2a ON (p2a.id_article=p2ai.id_article) WHERE $condition_field IN (" . join(', ', $whs) . ") AND p2w.internal = 1 $condition_field_ex GROUP BY p2ai.id_article; SELECT /*{$comment}*/ tarticles.*,price2_article.id_article,price2_article.id_brand,price2_article.id_group, price2_article.active,price2_article.id_type,{$table_info}.id_wh,{$table_info}.price_buy, {$table_info}.price_base,{$table_info}.price_diff,{$table_info}.avg_price,{$table_info}.price_seb, {$table_info}.currency,{$table_info}.availability,{$table_info}.id_action,price2_wh.currate_eur, price2_wh.currate_usd,price2_wh.currate_krw, price2_wh.nacenka_price_seb,price2_wh.adr, price2_wh.id_ws,price2_wh.id_type AS wh_id_type, price2_wh.smart_punkt,price2_wh.max_client_skidka, price2_article_shina.id_article as tyres_label, price2_article_wheels.id_article as whls_label, price2_article_moreinfo.price_not_up, price2_article_moreinfo.RRP_price, price2_article_moreinfo.RRP_currency, price_group_art2.no_rrp, price_group_art2.no_rrp_adr,price_group_art2.is_seb_algorithm_json, price_group_art2.koef_seb_json,price_group_art2.pricebase_basis, ac.su_exist ac_su_exist,ac.mc_exist ac_mc_exist, ac.su_id_price ac_su_id_price,ac.su_id_curprice ac_su_id_curprice, ac.su_price ac_su_price,ac.su_date1 ac_su_date1, ac.su_date2 ac_su_date2,ac.mc_id_price ac_mc_id_price, ac.mc_id_curprice ac_mc_id_curprice,ac.mc_price ac_mc_price, ac.mc_date1 ac_mc_date1, ac.mc_date2 ac_mc_date2, agc.su_exist agc_su_exist, agc.mc_exist agc_mc_exist, agc.su_id_price agc_su_id_price, agc.su_id_curprice agc_su_id_curprice, agc.su_price agc_su_price, agc.su_date1 agc_su_date1, agc.su_date2 agc_su_date2, agc.mc_id_price agc_mc_id_price, agc.mc_id_curprice agc_mc_id_curprice, agc.mc_price agc_mc_price, agc.mc_date1 agc_mc_date1, agc.mc_date2 agc_mc_date2, ga2c.su_exist ga2c_su_exist, ga2c.mc_exist ga2c_mc_exist, ga2c.su_id_price ga2c_su_id_price, ga2c.su_id_curprice ga2c_su_id_curprice, SQL:
  9. 2018 19 ga2gc2.su_date2 ga2gc2_su_date2, ga2gc2.mc_id_price ga2gc2_mc_id_price, ga2gc2.mc_id_curprice ga2gc2_mc_id_curprice, ga2gc2.mc_price ga2gc2_mc_price,

    ga2gc2.mc_date1 ga2gc2_mc_date1, ga2gc2.mc_date2 ga2gc2_mc_date2, ga2gc2.su_add_id_price ga2gc2_su_add_id_price, ga2gc2.su_add_exist ga2gc2_su_add_exist, punr.no_rrp AS no_rrp_user, punr.rrp_discount AS user_rrp_discount FROM tarticles INNER JOIN {$table_info} ON (tarticles.id_article={$table_info}.id_article) INNER JOIN price2_wh ON (price2_wh.id_wh = {$table_info}.id_wh) INNER JOIN price2_article ON (price2_article.id_article = {$table_info}.id_article) LEFT JOIN price2_article_moreinfo ON (price2_article_moreinfo.id_article = {$table_info}.id_article) LEFT JOIN price_group_art2 ON (price2_article.id_brand = price_group_art2.id_groupart2) LEFT JOIN price2_article_shina ON (price2_article_shina.id_article = price2_article.id_article) LEFT JOIN price2_article_wheels ON (price2_article_wheels.id_article = price2_article.id_article) left join price_art_contr ac on (ac.id_art = tarticles.id_article and ac.id_contr='$discounts[id_contr2]' and tarticles.id_type = ac.id_type) LEFT JOIN (SELECT su_exist, mc_exist, su_id_price, su_id_curprice, su_price, su_date1, su_date2, mc_id_price, mc_id_curprice, mc_price, mc_date1, mc_date2, id_art, id_type FROM price_art_grcontr WHERE id_grcontr='$discounts[id_grcontr]' and quant = 1) AS agc ON (agc.id_art = tarticles.id_article and tarticles.id_type = agc.id_type) left join price_grart2_contr ga2c on (ga2c.id_groupart2 = tarticles.id_brand and ga2c.id_contr = '$discounts[id_contr2]' and tarticles.id_type = ga2c.id_type) LEFT JOIN (select * from price_grart2_contr WHERE id_groupart2 = 0 and id_contr = '$discounts[id_contr2]') AS ga2c2 ON (tarticles.id_type = ga2c2.id_type) left join price_grart2_grcontr ga2gc on (ga2gc.id_groupart2 = tarticles.id_brand and (ga2gc.id_grcontr = '$discounts[id_grcontr]') and tarticles.id_type = ga2gc.id_type) LEFT JOIN (select * from price_grart2_grcontr WHERE id_groupart2 = 0 AND id_grcontr='$discounts[id_grcontr]') AS ga2gc2 ON (tarticles.id_type=ga2gc2.id_type) LEFT JOIN price_users_no_rrp AS punr ON (punr.id_user=‘$discounts[id_contr2]' AND punr.id_brand=price2_article.id_brand) WHERE price2_wh.internal=1 {$condition_wh} AND {$table_info}.is_availability=1 $select_condition_ex; Сложность получения/выборки данных SQL:
  10. 2018 20 Сложность получения/выборки данных User.findAll({ include: [{ model: Tool,

    include: [{ model: Teacher, include:[ model: nextLevel, include:[ model: nexLevel1 .... // we need to go deeper ] .... ]} ] }] }) ORM:
  11. 2018 Производительность (Hint) 24 public class HintInterceptor : DbCommandInterceptor {

    private static readonly Regex _tableAliasRegex = new Regex(@"(?<tableAlias>AS \[Extent\d+\](?! WITH \(*HINT*\)))", RegexOptions.Multiline | RegexOptions.IgnoreCase); [ThreadStatic] public static string HintValue; public override void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext) { if (!String.IsNullOrWhiteSpace(HintValue)) { command.CommandText = _tableAliasRegex.Replace( command.CommandText, "${tableAlias} WITH (*HINT*)"); command.CommandText = command.CommandText.Replace("*HINT*", HintValue); } HintValue = String.Empty; } } ORM:
  12. 2018 Производительность (Hint) 25 public class PbsContextConfig : DbConfiguration {

    public PbsContextConfig() { this.AddInterceptor(new HintInterceptor()); } } public static class HintExtension { public static DbSet<T> WithHint<T>(this DbSet<T> set, string hint) where T : class { HintInterceptor.HintValue = hint; return set; } } context.Persons .WithHint("INDEX(XI_DOWNTIME_LOCK)") .Where(x => x.ID == 1); ORM:
  13. 2018 Производительность • ORM - неконтролируемый результируемый SQL 28 Nullable<Int32>

    propValue = null; var filterQuery = testContext.Logs.Where(log => log.Id > 1 && (propValue.HasValue && log.Prop3 > propValue)); await filterQuery.ToListAsync(); ORM:
  14. 2018 29 SELECT [Extent1].[Id] AS [Id], [Extent1].[Text] AS [Text], [Extent1].[Prop1]

    AS [Prop1], [Extent1].[Prop2] AS [Prop2], [Extent1].[Prop3] AS [Prop3] FROM [dbo].[Logs] AS [Extent1] WHERE ([Extent1].[Id] > 1) AND (@p__linq__0 IS NOT NULL) AND ( CAST( [Extent1].[Prop3] AS int) >@p__linq__1) Производительность SQL: • ORM - неконтролируемый результируемый SQL
  15. 2018 Порог вхождения 32 Обучение ORM = ORM + SQL

    Сложность Объём знаний SQL ORM
  16. 2018 Сложность получения/выборки данных 37 INSERT INTO /*{$comment}*/ tarticles (id_article,

    id_type, id_brand) SELECT p2ai.id_article, p2a.id_type, p2a.id_brand FROM {$table_info} AS p2ai INNER JOIN price2_wh AS p2w ON (p2ai.id_wh=p2w.id_wh AND p2ai.is_availability = 1) INNER JOIN price2_article AS p2a ON (p2a.id_article=p2ai.id_article) WHERE $condition_field IN (" . join(', ', $whs) . ") AND p2w.internal = 1 $condition_field_ex GROUP BY p2ai.id_article; SELECT /*{$comment}*/ tarticles.*,price2_article.id_article,price2_article.id_brand,price2_article.id_group, price2_article.active,price2_article.id_type,{$table_info}.id_wh,{$table_info}.price_buy, {$table_info}.price_base,{$table_info}.price_diff,{$table_info}.avg_price,{$table_info}.price_seb, {$table_info}.currency,{$table_info}.availability,{$table_info}.id_action,price2_wh.currate_eur, price2_wh.currate_usd,price2_wh.currate_krw, price2_wh.nacenka_price_seb,price2_wh.adr, price2_wh.id_ws,price2_wh.id_type AS wh_id_type, price2_wh.smart_punkt,price2_wh.max_client_skidka, price2_article_shina.id_article as tyres_label, price2_article_wheels.id_article as whls_label, price2_article_moreinfo.price_not_up, price2_article_moreinfo.RRP_price, price2_article_moreinfo.RRP_currency, price_group_art2.no_rrp, price_group_art2.no_rrp_adr,price_group_art2.is_seb_algorithm_json, price_group_art2.koef_seb_json,price_group_art2.pricebase_basis, ac.su_exist ac_su_exist,ac.mc_exist ac_mc_exist, ac.su_id_price ac_su_id_price,ac.su_id_curprice ac_su_id_curprice, ac.su_price ac_su_price,ac.su_date1 ac_su_date1, ac.su_date2 ac_su_date2,ac.mc_id_price ac_mc_id_price, ac.mc_id_curprice ac_mc_id_curprice,ac.mc_price ac_mc_price, ac.mc_date1 ac_mc_date1, ac.mc_date2 ac_mc_date2, agc.su_exist agc_su_exist, agc.mc_exist agc_mc_exist, agc.su_id_price agc_su_id_price, agc.su_id_curprice agc_su_id_curprice, agc.su_price agc_su_price, agc.su_date1 agc_su_date1, agc.su_date2 agc_su_date2, agc.mc_id_price agc_mc_id_price, agc.mc_id_curprice agc_mc_id_curprice, agc.mc_price agc_mc_price, agc.mc_date1 agc_mc_date1, agc.mc_date2 agc_mc_date2, ga2c.su_exist ga2c_su_exist, ga2c.mc_exist ga2c_mc_exist, ga2c.su_id_price ga2c_su_id_price, ga2c.su_id_curprice ga2c_su_id_curprice, SQL:
  17. 2018 38 ga2gc2.su_date2 ga2gc2_su_date2, ga2gc2.mc_id_price ga2gc2_mc_id_price, ga2gc2.mc_id_curprice ga2gc2_mc_id_curprice, ga2gc2.mc_price ga2gc2_mc_price,

    ga2gc2.mc_date1 ga2gc2_mc_date1, ga2gc2.mc_date2 ga2gc2_mc_date2, ga2gc2.su_add_id_price ga2gc2_su_add_id_price, ga2gc2.su_add_exist ga2gc2_su_add_exist, punr.no_rrp AS no_rrp_user, punr.rrp_discount AS user_rrp_discount FROM tarticles INNER JOIN {$table_info} ON (tarticles.id_article={$table_info}.id_article) INNER JOIN price2_wh ON (price2_wh.id_wh = {$table_info}.id_wh) INNER JOIN price2_article ON (price2_article.id_article = {$table_info}.id_article) LEFT JOIN price2_article_moreinfo ON (price2_article_moreinfo.id_article = {$table_info}.id_article) LEFT JOIN price_group_art2 ON (price2_article.id_brand = price_group_art2.id_groupart2) LEFT JOIN price2_article_shina ON (price2_article_shina.id_article = price2_article.id_article) LEFT JOIN price2_article_wheels ON (price2_article_wheels.id_article = price2_article.id_article) left join price_art_contr ac on (ac.id_art = tarticles.id_article and ac.id_contr='$discounts[id_contr2]' and tarticles.id_type = ac.id_type) LEFT JOIN (SELECT su_exist, mc_exist, su_id_price, su_id_curprice, su_price, su_date1, su_date2, mc_id_price, mc_id_curprice, mc_price, mc_date1, mc_date2, id_art, id_type FROM price_art_grcontr WHERE id_grcontr='$discounts[id_grcontr]' and quant = 1) AS agc ON (agc.id_art = tarticles.id_article and tarticles.id_type = agc.id_type) left join price_grart2_contr ga2c on (ga2c.id_groupart2 = tarticles.id_brand and ga2c.id_contr = '$discounts[id_contr2]' and tarticles.id_type = ga2c.id_type) LEFT JOIN (select * from price_grart2_contr WHERE id_groupart2 = 0 and id_contr = '$discounts[id_contr2]') AS ga2c2 ON (tarticles.id_type = ga2c2.id_type) left join price_grart2_grcontr ga2gc on (ga2gc.id_groupart2 = tarticles.id_brand and (ga2gc.id_grcontr = '$discounts[id_grcontr]') and tarticles.id_type = ga2gc.id_type) LEFT JOIN (select * from price_grart2_grcontr WHERE id_groupart2 = 0 AND id_grcontr='$discounts[id_grcontr]') AS ga2gc2 ON (tarticles.id_type=ga2gc2.id_type) LEFT JOIN price_users_no_rrp AS punr ON (punr.id_user=‘$discounts[id_contr2]' AND punr.id_brand=price2_article.id_brand) WHERE price2_wh.internal=1 {$condition_wh} AND {$table_info}.is_availability=1 $select_condition_ex; Сложность получения/выборки данных SQL:
  18. 2018 Переносимость 39 const Sequelize = require('sequelize');​ const sequelize =

    new Sequelize('database’, 'username', 'password', {​ host: 'localhost’,​ dialect: 'mysql'|'sqlite'|'postgres'|'mssql’,​ pool: {​ max: 5,​ min: 0,​ acquire: 30000,​ idle: 10000 },​ });​ ORM:
  19. 2018 Вывод SQL => ORM => SQL + ORM 41

    lim проектов→∞ Ж/Д = 50/50