Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Долгое выполнение запроса  [new]
Алексаша
Member

Откуда: Левый берег
Сообщений: 374
Здравствуйте!

MS SQL SERVER 2017.
Хранимая процедура в студии выполняется мгновенно а с клиента посредством компонентов ADO в разы дольше.

Имеется таблица вида
CREATE TABLE [dbo].[FaceActivity](
	[FaceActivityID] [int] IDENTITY(1,1) NOT NULL,
	[FaceMainGUID] [uniqueidentifier] NOT NULL,
	[ActualS] [date] NULL,
	[ActualPo] [date] NULL,
	[Post] [varchar](250) NULL,
	[OrgTreeID] [int] NULL,
	[OrgDicTypeLinkID] [int] NULL,
	[BasisInfoID] [int] NULL,
 CONSTRAINT [PK_FaceActivity] PRIMARY KEY CLUSTERED 
(
	[FaceActivityID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_FaceActivity_FaceMainGUID_OrgTreeID] ON [dbo].[FaceActivity]
(
	[FaceMainGUID] ASC,
	[OrgTreeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_FaceActivity_OrgTreeID_FaceMainGUID] ON [dbo].[FaceActivity]
(
	[OrgTreeID] ASC,
	[FaceMainGUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
END

Хранимая процедура
ALTER PROCEDURE [tblRead].[sp_FaceActivity] 
	@FaceMainGUID		UNIQUEIDENTIFIER = NULL
	,@FaceActivityID	INT = NULL
AS
BEGIN
	SET NOCOUNT ON;

	SELECT [FaceActivityID]
		  ,[FaceMainGUID]
		  ,[ActualS] = CAST([ActualS] AS DATETIME)
		  ,[ActualPo] = CAST([ActualPo] AS DATETIME)
		  ,[Post]
		  ,[OrgTreeID]
		  ,fa.OrgDicTypeLinkID
		  ,OrgNam = org.ssf_OrgName([OrgTreeID], 1, ', ')
		  ,BasisInfoID
		  ,BasisInfo = dbo.ssf_BasisInfo(fa.BasisInfoID)
	  FROM [dbo].[FaceActivity] fa
	  WHERE --fa.FaceMainGUID = @FaceMainGUID если оставить таким образом условие а ниже удалить тоже быстро выполняется
		(fa.FaceMainGUID = @FaceMainGUID OR @FaceMainGUID IS NULL)
		AND (fa.FaceActivityID = @FaceActivityID	OR @FaceActivityID IS NULL)
		AND (@FaceMainGUID IS NOT NULL OR @FaceActivityID IS NOT NULL)


С клиента идет запрос
exec sp_executesql N'exec [tblRead].[sp_FaceActivity] @P1
',N'@P1 varchar(39)','{18CA254E-395C-4840-88F5-21DA9AFCF539}'


Выполняется долго от 10 до 15 сек. профайлере.
И тот же запрос в студии менее секунды.
Кэш планов чистил не помогает.
Экспериментальным путем выяснил следующее если на таблице удалить индексы, то запрос выполняется за 1 сек. Но тогда проблемы возникают в других запросах там где индекс необходим. Делал процедуру в виде динамического запроса тогда время выполнения менее 1 сек., но тогда проблемы с предоставлением прав.
И такая проблема на всех подобных табл и процедурах.
На данный момент как решение приходится в процедуре делать условие в зависимости от аргументов делать запросы с различным разделом where
Уважаемые знатоки MS SQL SERVER, почему компактное решение быстро выполняется в студии и так долго с клиентского приложения???
1 июл 19, 14:45    [21918198]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение запроса  [new]
Shakill
Member

Откуда: мск
Сообщений: 1880
Алексаша,

Медленно в приложении, быстро в SSMS
1 июл 19, 14:48    [21918202]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение запроса  [new]
Focha
Member

Откуда: Москва
Сообщений: 499
Может сеть, вы же возвращаете select?
1 июл 19, 14:50    [21918206]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение запроса  [new]
Sergey Syrovatchenko
Member

Откуда:
Сообщений: 134
У вас очевидные проблемы из-за прослушивания параметров + план никогда не будет параллельным потому что скалярные функции используются. План покажите в момент когда запрос медленный + какой размер таблицы?
1 июл 19, 15:46    [21918281]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение запроса  [new]
Sergey Syrovatchenko
Member

Откуда:
Сообщений: 134
Можно так:
ALTER PROCEDURE [tblRead].[sp_FaceActivity]
(
      @FaceMainGUID UNIQUEIDENTIFIER = NULL
    , @FaceActivityID INT = NULL
)
AS BEGIN

    SET NOCOUNT ON

    IF @FaceMainGUID IS NOT NULL AND @FaceActivityID IS NOT NULL
        SELECT FaceActivityID
             , FaceMainGUID
             , ActualS = CAST(ActualS AS DATETIME)
             , ActualPo = CAST(ActualPo AS DATETIME)
             , Post
             , OrgTreeID
             , OrgDicTypeLinkID
             , OrgNam = org.ssf_OrgName(OrgTreeID, 1, ', ')
             , BasisInfoID
             , BasisInfo = dbo.ssf_BasisInfo(BasisInfoID)
        FROM dbo.FaceActivity
        WHERE FaceActivityID = @FaceActivityID
            AND FaceMainGUID = @FaceMainGUID
    ELSE IF @FaceMainGUID IS NULL AND @FaceActivityID IS NOT NULL
        SELECT FaceActivityID
             , FaceMainGUID
             , ActualS = CAST(ActualS AS DATETIME)
             , ActualPo = CAST(ActualPo AS DATETIME)
             , Post
             , OrgTreeID
             , OrgDicTypeLinkID
             , OrgNam = org.ssf_OrgName(OrgTreeID, 1, ', ')
             , BasisInfoID
             , BasisInfo = dbo.ssf_BasisInfo(BasisInfoID)
        FROM dbo.FaceActivity
        WHERE FaceActivityID = @FaceActivityID
    ELSE IF @FaceMainGUID IS NOT NULL AND @FaceActivityID IS NULL
        SELECT FaceActivityID
             , FaceMainGUID
             , ActualS = CAST(ActualS AS DATETIME)
             , ActualPo = CAST(ActualPo AS DATETIME)
             , Post
             , OrgTreeID
             , OrgDicTypeLinkID
             , OrgNam = org.ssf_OrgName(OrgTreeID, 1, ', ')
             , BasisInfoID
             , BasisInfo = dbo.ssf_BasisInfo(BasisInfoID)
        FROM dbo.FaceActivity
        WHERE FaceMainGUID = @FaceMainGUID

END

Можно через OPTION(RECOMPILE):
ALTER PROCEDURE [tblRead].[sp_FaceActivity]
(
      @FaceMainGUID UNIQUEIDENTIFIER = NULL
    , @FaceActivityID INT = NULL
)
AS BEGIN

    SET NOCOUNT ON

    SELECT FaceActivityID
         , FaceMainGUID
         , ActualS = CAST(ActualS AS DATETIME)
         , ActualPo = CAST(ActualPo AS DATETIME)
         , Post
         , OrgTreeID
         , OrgDicTypeLinkID
         , OrgNam = org.ssf_OrgName(OrgTreeID, 1, ', ')
         , BasisInfoID
         , BasisInfo = dbo.ssf_BasisInfo(BasisInfoID)
    FROM dbo.FaceActivity
    WHERE (FaceActivityID = @FaceActivityID OR @FaceActivityID IS NULL)
        AND (FaceMainGUID = @FaceMainGUID OR @FaceMainGUID IS NULL)
    OPTION(RECOMPILE)

END
1 июл 19, 15:54    [21918292]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение запроса  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Sergey Syrovatchenko,

а отчего там первый вариант спасает? Разве что переназначить переменные, а так может ничего не изменить
1 июл 19, 15:57    [21918295]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение запроса  [new]
Sergey Syrovatchenko
Member

Откуда:
Сообщений: 134
TaPaK
отчего там первый вариант спасает?

Формально не спасет. На план смотреть надо, которого у нас увы нет. Мое подозрение что из-за скалярок план последовательный + неверный индекс выбирается с фулл сканом.
1 июл 19, 16:04    [21918303]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение запроса  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Sergey Syrovatchenko
TaPaK
отчего там первый вариант спасает?

Формально не спасет. На план смотреть надо, которого у нас увы нет. Мое подозрение что из-за скалярок план последовательный + неверный индекс выбирается с фулл сканом.

а в "студии" будет параллельный что ли?

а так да, если прото то recompile
1 июл 19, 16:07    [21918304]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение запроса  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Sergey Syrovatchenko
TaPaK
отчего там первый вариант спасает?

Формально не спасет. На план смотреть надо, которого у нас увы нет. Мое подозрение что из-за скалярок план последовательный + неверный индекс выбирается с фулл сканом.

ну и прослушивание не даст "не верный индекс", а тот который скомпилирован и тут больше вопросы к ошибке ввыбора соединений
1 июл 19, 16:10    [21918308]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение запроса  [new]
aleks222
Member

Откуда:
Сообщений: 923
Такой тривиальный запрос, как у тредстартера, лечится выставлением одинаковых опций подключения

см.
DBCC USER_OPTIONS.
1 июл 19, 16:15    [21918316]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение запроса  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
aleks222
Такой тривиальный запрос, как у тредстартера, лечится выставлением одинаковых опций подключения

см.
DBCC USER_OPTIONS.

тривиальный бред
1 июл 19, 16:17    [21918318]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение запроса  [new]
Megabyte
Member

Откуда: ближайшее заМКАДье
Сообщений: 4901
aleks222
Такой тривиальный запрос, как у тредстартера, лечится выставлением одинаковых опций подключения

см.
DBCC USER_OPTIONS.

Это справедливо только для конкретных случаев, в общем случае - нет.
1 июл 19, 16:34    [21918333]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение запроса  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7663
Что там можно обсуждать, во втором сообщении есть ответ.
1 июл 19, 18:52    [21918463]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить