Этот скрипт выполняется 10 секунд.
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
DECLARE @FindText VARCHAR(4000) = ''
SET NOCOUNT ON
IF @FindText = ''
-- сам по себе этот SELECT выполняется за 1 сек, а в составе ХП - за 10 сек
SELECT TOP 100 caao.City_id, cnt = COUNT(DISTINCT caaa.aut_id)
FROM core_art_ArtOrganisations caao WITH(NOLOCK)
JOIN core_art_ArtAutors caaa WITH(NOLOCK) ON caao.art_id = caaa.art_id
WHERE caao.City_id IS NOT NULL
GROUP BY caao.City_id
ORDER BY cnt desc, 1
ELSE
SELECT TOP 100 City_id, cnt = COUNT(DISTINCT caaa.aut_id)
FROM core_art_ArtIndex i WITH(NOLOCK)
JOIN core_art_ArtOrganisations caao WITH(NOLOCK) ON i.art_id = caao.art_id
JOIN core_art_ArtAutors caaa WITH(NOLOCK) ON caao.art_id = caaa.art_id
WHERE (
CONTAINS(i.[art_title], @FindText)
OR CONTAINS(i.[art_abstract], @FindText)
OR CONTAINS(i.[art_keywords], @FindText)
)
AND City_id IS NOT NULL
GROUP BY City_id
ORDER BY cnt desc, 1
А если запустить просто select, то меньше 1 секунды.
1.
2.
3.
4.
5.
6.
SELECT TOP 100 caao.City_id, cnt = COUNT(DISTINCT caaa.aut_id)
FROM core_art_ArtOrganisations caao WITH(NOLOCK)
JOIN core_art_ArtAutors caaa WITH(NOLOCK) ON caao.art_id = caaa.art_id
WHERE caao.City_id IS NOT NULL
GROUP BY caao.City_id
ORDER BY cnt desc, 1
Как победить?
