Сделайте запрос T-Sql с параметрами БЫСТРЕЕ

Прежде всего, я хочу сказать, что я действительно новичок в базах данных, не осуждайте меня.

Я получаю много параметров от веб-службы, и мне нужно выполнить sql-запрос с этими параметрами. Дело в том, что мой запрос потребляет много процессора из-за моей плохой оптимизации запроса.

  SELECT 
  SUM(t.total_amount) as SumaAmount,
  COUNT(t.id)  as TotalTransaccions

    FROM RealTimeVending.dbo.rtv_turnover_transaction as t, 
    RealTimeVending.dbo.rtv_trans_articles as ta,
    RealTimeVending.dbo.articles as art, RealTimeVending.dbo.groups,
    RealTimeVending.dbo.Clients as s,
    RealTimeVending.dbo.rtv_transactions as tr, 
    RealTimeVending.dbo.tills as till, RealTimeVending.dbo.Ubicacion as u, 
    RealTimeVending.dbo.Operadores as o 

    where t.operador_id=o.ID and t.transaction_id=ta.transaction_id and 
    art.id=ta.article_id and s.id=t.cliente_id and tr.id=t.transaction_id 
    and groups.id=art.group_a_id and t.ubicacio_id=u.id 

    and convert(date,t.trans_date) >='"+globalMap.get("datainici")+"'and 
    convert(date,t.trans_date) <= '"+globalMap.get("datafinal")+"'and 
    and (s.codigo IS NULL or s.codigo like '%"+globalMap.get("client")+"%') 
    and (t.total_amount IS NULL or t.total_amount like'"+globalMap.get("amount")+"%') 

Представьте, что у меня все параметры из веб-сервиса пусты. База данных потратит много времени на эти параметры. Я хотел бы сделать запрос, в котором я мог бы просто искать параметры, которые не равны нулю. Например, если "t.total_amount" было пустым, я не хочу вставлять поиск в запрос.

Надеюсь, вы понимаете мою проблему. Большое спасибо.

Не нужно извиняться за то, что вы новичок - помимо запроса, для того, чтобы дать конкретный совет, потребуется схема таблиц, индексы в таблицах и, в идеале, план запроса. Даже без них s.codigo like '%"+globalMap.get("client")+"%' вызовет проблемы, при таком поиске с использованием подстановочных знаков всегда будут сканироваться данные.

Andrew 10.08.2018 11:50

Совет сегодня: переходите на современный, явный синтаксис JOIN! Легче писать (без ошибок), легче читать и поддерживать, а также при необходимости проще преобразовать во внешнее соединение.

jarlh 10.08.2018 11:56

В этом запросе вместо параметров используются переменные с конкатенацией строк. Я не могу прокомментировать, как правильно параметризовать его с помощью talend, но запрос будет содержать маркеры параметров с фактическими значениями времени выполнения, добавленными к команде отдельно (например, convert(date,t.trans_date) >= ?). Лучше всего провести рефакторинг запроса, чтобы избежать применения таких функций, как convert, к столбцу, чтобы можно было эффективно использовать индексы.

Dan Guzman 10.08.2018 11:58
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
0
3
68
2
Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Ответ принят как подходящий

Вот быстрая переписать ваш запрос, чтобы использовать современный синтаксис JOIN:

SELECT 
  SUM(t.total_amount) AS SumaAmount,
  COUNT(t.id) AS TotalTransaccions
FROM 
    RealTimeVending.dbo.rtv_turnover_transaction t
    INNER JOIN RealTimeVending.dbo.rtv_trans_articles ta ON ta.transaction_id = t.transaction_id
    INNER JOIN RealTimeVending.dbo.articles art ON art.id = ta.article_id
    INNER JOIN RealTimeVending.dbo.groups g ON g.id = art.group_id
    INNER JOIN RealTimeVending.dbo.Clients s ON s.id = t.cliente_id
    INNER JOIN RealTimeVending].dbo.rtv_transactions tr ON tr.id = t.transaction_id
    --INNER JOIN RealTimeVending.dbo.tills till (not used)
    INNER JOIN RealTimeVending.dbo.Ubicacion u ON u.id = t.ubicacio_id 
    INNER JOIN RealTimeVending.dbo.Operadores o ON o.id = t.operador_id
WHERE
    CONVERT(DATE, t.trans_date) >= '"+globalMap.get("datainici")+"' --is this really not already a date?
    AND CONVERT(DATE, t.trans_date) <= '"+globalMap.get("datafinal")+"'
    AND (s.codigo IS NULL or s.codigo LIKE '%"+globalMap.get("client")+"%') 
    AND (t.total_amount IS NULL or t.total_amount LIKE '"+globalMap.get("amount")+"%');

Первое, что мне бросается в глаза, это то, что ваш trans_date конвертируется в DATE для сравнения, и это, вероятно, неэффективно. Было бы лучше, если бы вы могли изменить этот критерий на что-то вроде t.trans_date BETWEEN <dateinici> AND <datefinal>, поскольку это позволило бы использовать индексы.

Вам действительно следует правильно использовать параметры, так как это помогает избежать SQL-инъекции.

В зависимости от оптимизатора он должен понимать, что нет смысла сравнивать значение, если это NULL, а у вас есть ограничение x IS NULL OR x LIKE <something>.

Возможно, стоит взять образец запроса и посмотреть, какой план выполнения он использует?

Используя современный синтаксис JOIN, также становится очевидным, что ваша таблица tills не использовалась, поэтому, по сути, это была CROSS JOIN, которая сильно замедлила бы все, если это большая таблица?

Ваш SQL нуждается в серьезной корректировке.

Давайте сначала немного разберем ваш SQL:

FROM RealTimeVending.dbo.rtv_turnover_transaction as t, 
RealTimeVending.dbo.rtv_trans_articles as ta,
RealTimeVending.dbo.articles as art, RealTimeVending.dbo.groups,
RealTimeVending.dbo.Clients as s,
RealTimeVending].dbo.rtv_transactions as tr, 
RealTimeVending.dbo.tills as till, RealTimeVending.dbo.Ubicacion as u, 
RealTimeVending.dbo.Operadores as o 
where t.operador_id=o.ID and t.transaction_id=ta.transaction_id and 
art.id=ta.article_id and s.id=t.cliente_id and tr.id=t.transaction_id 
and groups.id=art.group_a_id and t.ubicacio_id=u.id 
and convert(date,t.trans_date) >='"+globalMap.get("datainici")+"'and 
convert(date,t.trans_date) <= '"+globalMap.get("datafinal")+"'and 
and (s.codigo IS NULL or s.codigo like '%"+globalMap.get("client")+"%') 
and (t.total_amount IS NULL or t.total_amount like'"+globalMap.get("amount")+"%') 

Вы используете объединение в старом стиле, что затрудняет чтение. Вы можете переписать его как (предположим, что RealTimeVending была текущей базой данных):

FROM rtv_turnover_transaction as t
inner join rtv_trans_articles as ta on t.transaction_id=ta.transaction_id 
inner join articles as art on art.id=ta.article_id
inner join groups on groups.id=art.group_a_id
inner join Clients as s on s.id=t.cliente_id
inner join rtv_transactions as tr on tr.id=t.transaction_id  
cross join tills as till 
inner join Ubicacion as u on t.ubicacio_id=u.id
inner join Operadores as o on t.operador_id=o.ID

where
--convert(date,t.trans_date) >='"+globalMap.get("datainici")+"'and 
--convert(date,t.trans_date) <= '"+globalMap.get("datafinal")+"'and 
--and (s.codigo IS NULL or s.codigo like '%"+globalMap.get("client")+"%') 
--and (t.total_amount IS NULL or t.total_amount like'"+globalMap.get("amount")+"%') 

Здесь у вас есть внутренние соединения со многими таблицами и одно CROSS JOIN, которое совершенно (не только) бесполезно. Это перекрестное соединение само по себе является причиной медлительности. С помощью перекрестного соединения произнесите:

t1 cross join t2

Если t1 имеет 1000 строк, а t2 имеет 10000 строк, вы получите 1000 * 10000 = 10000000 строк, где каждая строка на t1 повторяется 10 000 раз (и строки t2 1000 раз).

Следующий вопрос: когда вам нужны поля только из rtv_turnover_transaction, зачем вы присоединяетесь к другим таблицам? Это может быть намеренно невозможно узнать, не зная схемы и потребностей. Скорее всего они использовались только для проверки (если ЕСТЬ). Если да, вы можете добавить их как запросы EXISTS в WHERE.

Затем идут ваши предложения WHERE:

where
 convert(date,t.trans_date) >='"+globalMap.get("datainici")+"'and 
 convert(date,t.trans_date) <= '"+globalMap.get("datafinal")+"'and 
 and (s.codigo IS NULL or s.codigo like '%"+globalMap.get("client")+"%') 
 and (t.total_amount IS NULL or t.total_amount like'"+globalMap.get("amount")+"%') 

Вот почему:

convert(date,t.trans_date)

Это аннулирует использование существующего индекса для trans_date и замедлит его. Вместо:

 t.trans_date >='"+globalMap.get("datainici")+"'and

вполне нормально, если globalMap.get ("datainici") возвращает дату или дату и время, где часть времени равна 00:00:00.

Затем следует самый важный:

where
 convert(date,t.trans_date) >='"+globalMap.get("datainici")+"'and 
 convert(date,t.trans_date) <= '"+globalMap.get("datafinal")+"'and 
 and (s.codigo IS NULL or s.codigo like '%"+globalMap.get("client")+"%') 
 and (t.total_amount IS NULL or t.total_amount like'"+globalMap.get("amount")+"%') 

НИКОГДА не следует строить подобные SQL-запросы путем объединения строк. Это не только хорошо известная причина атаки SQL-инъекций, но и может привести к неправильному определению параметров. Простое лекарство - использовать ПАРАМЕТРЫ.

Затем последовали бы другие оптимизации.

Другие вопросы по теме