Noman Suleman Ответов: 3

Как оптимизировать SQL запрос его выполнение занимает много времени

У меня есть запрос, когда я выполняю его на сервере разработки и производства, это требует увеличения потребления процессора до 100%.

это из-за внешнего применения я также попытался с подзапросом и с внутренним соединением, но возникает же вопрос

Что я уже пробовал:

declare @fJno varchar (50)=820,
	@noOfRows int =10,
	@toJno varchar (50)=828


select top (@noOfRows) z.AgentName Nick,
upper(agnt.AgentName)AgentName
,sum (z.[Local])[Local]
,sum(z.[Foreign])[Foreign]
,(sum (z.[Local])+sum(z.[Foreign]))Total




 from (
select * from(	SELECT  

Substring(LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(upper(AgentName), '&', ''), '(', ''), ')', ''), ',', ''), '.', ''), '-', ''))), 1,Charindex(' ', LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(upper(AgentName), '&', ''), '(', ''), ')', ''), ',', ''), '.', ''), '-', '')))+' ')-1) [AgentName]


,( [Local])[Local]
,( [Foreign])[Foreign]

,  [Local]+[Foreign] as Total

FROM   
(
    SELECT 
		LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(upper(p.AgentName), '&', ''), '(', ''), ')', ''), ',', ''), '.', ''), '-', '')))AgentName
   
		,(case when CompanyCountry like '%pakistan%' then 'Local' when CompanyCountry != '%pakistan%' then 'Foreign'  else '' end) as CompanyCountry
		--,JournalNo
		,FORMAT( JournalDate,'MMMyy')JournalDate


		




    FROM 
      [TheLegalFiles-Dev].dbo.SearchProductGoodsClass p
	 

		where 
		
		  LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(upper(p.AgentName), '&', ''), '(', ''), ')', ''), ',', ''), '.', ''), '-', ''))) like ''+Substring(LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(upper(p.AgentName), '&', ''), '(', ''), ')', ''), ',', ''), '.', ''), '-', ''))), 1,Charindex(' ', LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(upper(p.AgentName), '&', ''), '(', ''), ')', ''), ',', ''), '.', ''), '-', '')))+' ')-1)+'%'
		and
		 JournalNo >= @fJno and JournalNo<=@toJno

		
) t 

PIVOT(
    count(CompanyCountry) FOR CompanyCountry IN ([Local], [Foreign])
) AS pivot_table )pvt



)z


 outer apply (


 select top 1  agentname  from [TheLegalFiles-Dev].dbo.SearchProductGoodsClass where DataEntryStatusid=1 and JournalNo> = @fJno and JournalNo<=@toJno and agentname is not null and  Substring(LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(upper(AgentName), '&', ''), '(', ''), ')', ''), ',', ''), '.', ''), '-', ''))), 1,Charindex(' ', LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(upper(AgentName), '&', ''), '(', ''), ')', ''), ',', ''), '.', ''), '-', '')))+' ')-1)  like ''+ z.AgentName +''
 ) agnt
 
group by z.AgentName--,LTRIM(RTRIM( z.JournalDate))
,agnt.AgentName
order by (sum (z.[Local])+sum(z.[Foreign])) desc

Richard MacCutchan

Удалите все эти команды обрезки и замены, вы создаете огромные накладные расходы в своем запросе.

Noman Suleman

если я удалю команду replace, то получу дубликаты записей, после того как команда replace вернет свою уникальную запись

Richard MacCutchan

Ну, это будет медленно делать таким образом.

Noman Suleman

не, когда я удалить наружный применить, этот запрос будет возвращать в течение 1 сек, после космического применить его идет на большой расход, его из-за условия where параметр @ФНО и @Tjno , и эта внешняя предложении return тысяч записей, поэтому ее становится медленным, я хочу знать, есть ли способ, чтобы заменить с другими apprach?

Sinisa Hajnal

Просто слепо...делать замены, как высоко, как вы можете так, что они запускаются на меньшее количество строк
Сделать памяти или временных таблиц, заполнить их самостоятельно, а потом присоединиться к ним
явно выпишите соединения вместо локальных, внешних
подумайте о том, чтобы иметь столбец в таблице, который уже имеет "поисковую" версию имени, чтобы вы могли делать все эти замены при вставке или обновлении вместо этого в предложении WHERE (плохая практика)

Noman Suleman

хорошо, спасибо за предложение. дай мне попробовать

3 Ответов

Рейтинг:
2

Patrice T

Я не являюсь пользователем SQL, но у меня есть пара идей:
- будет заменить

(case when CompanyCountry like '%pakistan%' then 'Local' when CompanyCountry != '%pakistan%' then 'Foreign'  else '' end) as CompanyCountry

с
(case when CompanyCountry like '%pakistan%' then 'Local' else 'Foreign' end) as CompanyCountry

или создайте пользовательский столбец с ответом, чтобы избежать пересчета при каждом запуске запроса.

- Я бы создал пользовательский столбец, содержащий результат:
RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(upper(AgentName), '&', ''), '(', ''), ')', ''), ',', ''), '.', ''), '-', ''))

Maciej Los

Хороший улов!
Неправильный случай, когда ... Оператор END может быть причиной дублирования записей.

Снимаю шляпу перед пользователем, не являющимся пользователем SQL!

Patrice T

Спасибо.

Noman Suleman

спасибо и ценю, позвольте мне изменить это хорошо указано

Рейтинг:
12

Maciej Los

А как насчет самой короткой версии вашего очень длинного и сложного запроса:

SELECT AgentName, [Local], [Foreign], [Local] + [Foreign] Total
FROM
(
    SELECT upper(p.AgentName) AgentName,
        SUM(CASE WHEN CompanyCountry like '%pakistan%' then 1 ELSE 0 END) [Local], 
        SUM(CASE WHEN CompanyCountry like '%pakistan%' then 0 ELSE 1 END) [Foreign]
    FROM [TheLegalFiles-Dev].dbo.SearchProductGoodsClass p
    WHERE p.DataEntryStatusid=1 and p.JournalNo> = @fJno and p.JournalNo<=@toJno
    GROUP BY upper(p.AgentName)
) T
ORDER BY [Local] + [Foreign] DESC

Noman Suleman

это показывает название компании несколько раз мне нужна сумма всех этих


Имя Агента Местный Иностранный Итого
Объединенная торговая марка & 40 90 130
ОБЪЕДИНЕННАЯ ТОРГОВАЯ МАРКА 0 1 1
ОБЪЕДИНЕННЫЙ ПАТЕНТ НА ТОВАРНЫЙ ЗНАК 0 1 1

Maciej Los

Да, потому что у этих компаний разные названия.

Если вы хотите получить "общее название компании", вам нужно определить вспомогательную таблицу, которая вернет это "общее имя". Например, у компании Coca-Cola есть агенты по всему миру. В Польше, у нас есть 2 компании: "Кока-Кола ЭйчБиСи Польска СП. з О. О. О. О." и "Кока-Колы услуг Польша SP. з О. О. О. О.". Первоначальное название компании - "Кока-Кола Компани". Итак, если вы хотите вернуть названия компаний, которые связаны с этой мировой компанией, вам нужно создать словарь a'ka:

AgentName | CommonName
United Trademark | United Trademark
United Trademark & | United Trademark
United Trademark Patent | United Trademark

Тогда вы сможете использовать это общее имя, объединив вспомогательный стол.

Noman Suleman

это идеально, если бы вы предоставили мне пример ссылки на словарь a'ka, как я могу это создать. будьте добры, ведите меня дальше. заранее спасибо

Maciej Los

Это довольно легкая часть...
1. СОЗДАНИЕ ТАБЛИЦЫ[^]

Create table CompanyCommonNames
(
    AgentName  NVARCHAR(255),
    CommonName NVARCHAR(255)
)


2. ВСТАВИТЬ В[^]
INSERT INTO CompanyCommonNames(AgentName, CommonName)
SELECT DISTINCT upper(y.AgentName), upper(REPLACE(REPLACE(...)...))
FROM YourTableNameHere y
ORDER BY upper(y.AgentName)


Обратите внимание, что REPLACE(REPLACE(...)...) используется для получения общего названия компании. Вы должны использовать свою логику здесь!

3. использовать Присоединяйтесь[^]
SELECT ccn.CommonName, T.[Local], T.[Foreign], T.[Local] + T.[Foreign] Total
FROM
(
    --inner query from solution #4
) T INNER JOIN CompanyCommonNames ccn ON T.AgetName =  ccn.AgentName
ORDER BY T.[Local] + T.[Foreign] DESC


Вот и все!

Noman Suleman

большое спасибо, сэр, я все понял , очень хорошее и простое решение оценил!

Maciej Los

Всегда пожалуйста.

Noman Suleman

Спасибо!

Рейтинг:
0

RickZeeland

Может быть, эти статьи вам помогут:
основных-советы по тюнингу-SQL-сервера-запросы-f834f09bafaf[^]
методы оптимизации запросов-в-sql-сервере-советы и рекомендации/[^]

Maciej Los

5!