Как оптимизировать запрос?

@Kill100 · Регистрация: 11.12.2010

Студворк — интернет-сервис помощи студентам

Есть запрос который по фильтру возвращает записи разбивая по страницам и выбирая только указанную страницу. А так же возвращает общее количество записей
Смущает то что выборка делается дважды для общего количества и постраничного. (Вложенные выборки пока что не считаю)
Возможно ли упростить и ускорить скрипт. Пока записей немного в бд. но уже сей час выполняется достаточно медленно. При выборке 200 странице по 20 на каждой уже запрос намного медленнее выполняется чем выборка 1 страницы

T-SQL
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
29
30
31
32
ALTER PROCEDURE [dbo].[GetPayByFilter] 
@PaySystemId        NVARCHAR(16) = NULL,
@PayStateId         NVARCHAR(16) = NULL,
@StartDate          DATETIME = NULL,
@EndDate            DATETIME = NULL,
@ShowPage           INT,
@CountByPage        INT,
@COUNTROW           BIGINT OUTPUT
AS
BEGIN
  IF @CountByPage < 1
    SET @CountByPage = 20;
  IF @ShowPage < 1
    SET @ShowPage = 1;
 
  SELECT @COUNTROW = COUNT(p.PayId)
  FROM [dbo].[Pay] p
  WHERE (p.CreateDate >= @StartDate OR @StartDate IS NULL)
        AND (p.CreateDate <= @EndDate OR @EndDate IS NULL)
        AND (p.PaySystemId = @PaySystemId OR @PaySystemId IS NULL)
        AND (p.PayStateId = @PayStateId OR @PayStateId IS NULL)
 
  SELECT TOP (@CountByPage) pa.*
  FROM (SELECT TOP (@CountByPage * @ShowPage) p.*
  FROM [dbo].[Pay] p
  WHERE (p.CreateDate >= @StartDate OR @StartDate IS NULL)
        AND (p.CreateDate <= @EndDate OR @EndDate IS NULL)
        AND (p.PaySystemId = @PaySystemId OR @PaySystemId IS NULL)
        AND (p.PayStateId = @PayStateId OR @PayStateId IS NULL)
  ORDER BY p.PayId DESC) AS pa
  ORDER BY pa.PayId
END

@invm · 11.03.2014, 20:33

Сообщение от Kill100

Возможно ли упростить и ускорить скрипт

Можно запихнуть число строк в возвращаемый набор:

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
ALTER PROCEDURE [dbo].[GetPayByFilter] 
@PaySystemId        NVARCHAR(16) = NULL,
@PayStateId         NVARCHAR(16) = NULL,
@StartDate          DATETIME = NULL,
@EndDate            DATETIME = NULL,
@ShowPage           INT,
@CountByPage        INT
AS
BEGIN
  IF @CountByPage < 1
    SET @CountByPage = 20;
  IF @ShowPage < 1
    SET @ShowPage = 1;
 
  SELECT TOP (@CountByPage) pa.*
  FROM (SELECT TOP (@CountByPage * @ShowPage) p.*, count(*) over () as countrow
  FROM [dbo].[Pay] p
  WHERE (p.CreateDate >= @StartDate OR @StartDate IS NULL)
        AND (p.CreateDate <= @EndDate OR @EndDate IS NULL)
        AND (p.PaySystemId = @PaySystemId OR @PaySystemId IS NULL)
        AND (p.PayStateId = @PayStateId OR @PayStateId IS NULL)
  ORDER BY p.PayId DESC) AS pa
  ORDER BY pa.PayId
END

Сообщение от Kill100

но уже сей час выполняется достаточно медленно

При таком запросе будет еще хуже, даже избавление от повторных выборок не поможет. Лучше покажите DDL таблицы и индексов.

@asd24 · 11.03.2014, 20:50

Я бы попробывал обернуть всё во временную таблицу, чтобы дважды не тянуть данные из таблиц..
Также не совсем понял ваш "маневр" с NULL. Еще избавьтесь от * в запросе - надо перечислить все колонки

Для какой версии SQL Server написана процедура? Вот, как вариант, для SQL Server 2012(OFFSET, FETCH):

T-SQL
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
29
ALTER PROCEDURE [dbo].[GetPayByFilter] 
@PaySystemId        NVARCHAR(16) = NULL,
@PayStateId         NVARCHAR(16) = NULL,
@StartDate          DATETIME = NULL,
@EndDate            DATETIME = NULL,
@ShowPage           INT,
@CountByPage        INT,
@COUNTROW           BIGINT OUTPUT
AS
BEGIN
  IF @CountByPage < 1
    SET @CountByPage = 20;
  IF @ShowPage < 1
    SET @ShowPage = 1;
 
  SELECT p.* -- убрать звездочку и перечислить все колонки
  INTO #result 
  FROM [dbo].[Pay] p
  WHERE p.CreateDate BETWEEN @StartDate AND @EndDate
        AND p.PaySystemId = @PaySystemId
        AND p.PayStateId = @PayStateId
 
  SELECT @COUNTROW = @@ROWCOUNT
  
  SELECT * -- убрать звездочку и перечислить все колонки
  FROM #result
  ORDER BY PayId DESC
  OFFSET @CountByPage * ShowPage ROWS FETCH NEXT @CountByPage ROWS ONLY
END

Также не мешало бы посмотреть План выполнения, может нужно просто добавить индекс

@Kill100 · 11.03.2014, 20:59 **[ТС]**

Сообщение от asd24

Также не совсем понял ваш "маневр" с NULL.

Пользователь может указать только 1 критерий поиска а может сразу все. От туда и такой манёвр. так как писать 8 с лишнем хранимок для каждого варианта это не вариант. Да и в asp код будет громоздкий и уродливый.

Еще избавьтесь от * в запросе - надо перечислить все колонки

А разве это влияет на производительность? Что он сам их перечислит все что я их сам перечислю.

Сообщение от asd24

Для какой версии SQL Server написана процедура

Для 2008

Сообщение от asd24

Я бы попробывал обернуть всё во временную таблицу

Была такая мысль возможно так и сделаю. Просто еще хостинг не самый быстрый. Может в обще просто переедем.

Всем спасибо за ответы. Завтра посмотрю план выполнения. И буду уже тогда думать.

Новые блоги и статьи Все статьи Все блоги /
Functional First Web Framework Suave DevAlt 30.03.2026 Sauve. IO Апнулись до NET10. Из зависимостей один пакет, работает одинаково хорошо как в режиме проекта так и в интерактивном режиме. из сложностей - чисто функциональный подход. Решил. . .	Автоматическое создание документа при проведении другого документа Maks 29.03.2026 Реализация из решения ниже выполнена на нетиповых документах, разработанных в конфигурации КА2. Есть нетиповой документ "ЗаявкаНаРемонтСпецтехники" и нетиповой документ "ПланированиеСпецтехники". В. . .	Настройка движения справочника по регистру сведений Maks 29.03.2026 Решение ниже реализовано на примере нетипового справочника "ТарифыМобильнойСвязи" разработанного в конфигурации КА2, с целью учета корпоративной мобильной связи в коммерческом предприятии. . . .	Автозаполнение реквизита при выборе элемента справочника Maks 27.03.2026 Программный код из решения ниже на примере нетипового документа "ЗаявкаНаРемонтСпецтехники" разработанного в конфигурации КА2. При выборе "Спецтехники" (Тип Справочник. Спецтехника), заполняется. . .
Сумматор с применением элементов трёх состояний. Hrethgir 26.03.2026 Тут. https:/ / fips. ru/ EGD/ ab3c85c8-836d-4866-871b-c2f0c5d77fbc Первый документ красиво выглядит, но без схемы. Это конечно не даёт никаких плюсов автору, но тем не менее. . . всё может быть. . .	Автозаполнение реквизитов при создании документа Maks 26.03.2026 Программный код из решения ниже размещается в модуле объекта документа, в процедуре "ПриСозданииНаСервере". Алгоритм проверки заполнения реализован для исключения перезаписи значения реквизита,. . .	Команды формы и диалоговое окно Maks 26.03.2026 1. Команда формы "ЗаполнитьЗапчасти". Программный код из решения ниже на примере нетипового документа "ЗаявкаНаРемонтСпецтехники" разработанного в конфигурации КА2. В качестве источника данных. . .	Кому нужен AOT? DevAlt 26.03.2026 Решил сделать простой ланчер Написал заготовку: dotnet new console --aot -o UrlHandler var items = args. Split(":"); var tag = items; var id = items; var executable = args;. . .