Прежде всего, я хочу сказать, что я действительно новичок в базах данных, не осуждайте меня.
Я получаю много параметров от веб-службы, и мне нужно выполнить 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" было пустым, я не хочу вставлять поиск в запрос.
Надеюсь, вы понимаете мою проблему. Большое спасибо.
Совет сегодня: переходите на современный, явный синтаксис JOIN
! Легче писать (без ошибок), легче читать и поддерживать, а также при необходимости проще преобразовать во внешнее соединение.
В этом запросе вместо параметров используются переменные с конкатенацией строк. Я не могу прокомментировать, как правильно параметризовать его с помощью talend
, но запрос будет содержать маркеры параметров с фактическими значениями времени выполнения, добавленными к команде отдельно (например, convert(date,t.trans_date) >= ?
). Лучше всего провести рефакторинг запроса, чтобы избежать применения таких функций, как convert
, к столбцу, чтобы можно было эффективно использовать индексы.
Вот быстрая переписать ваш запрос, чтобы использовать современный синтаксис 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-инъекций, но и может привести к неправильному определению параметров. Простое лекарство - использовать ПАРАМЕТРЫ.
Затем последовали бы другие оптимизации.
Не нужно извиняться за то, что вы новичок - помимо запроса, для того, чтобы дать конкретный совет, потребуется схема таблиц, индексы в таблицах и, в идеале, план запроса. Даже без них
s.codigo like '%"+globalMap.get("client")+"%'
вызовет проблемы, при таком поиске с использованием подстановочных знаков всегда будут сканироваться данные.