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

Откуда:
Сообщений: 36
Есть таблица протоколов событий
USE [srv_intellect]
GO

/****** Object:  Table [dbo].[PROTOCOL]    Script Date: 2/25/2016 1:29:57 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[PROTOCOL](
	[objtype] [nvarchar](50) NULL,  
	[objid] [nvarchar](50) NULL,
	[action] [nvarchar](50) NULL,
	[region_id] [nvarchar](50) NULL,
	[param0] [nvarchar](255) NULL,
	[param1] [nvarchar](60) NULL,
	[param2] [nvarchar](255) NULL,
	[param3] [nvarchar](255) NULL,
	[user_param_double] [float] NULL,
	[date] [datetime] NULL,
	[time] [datetime] NULL,
	[time2] [datetime] NULL,
	[owner] [nvarchar](50) NULL,
	[pk] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_GUID] PRIMARY KEY CLUSTERED 
(
	[pk] 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


где
[objtype] тип объекта по которому записано событие
[objid] ID объекта по которому записано событие
[action] само событие которое произошло по этому объекту
[region_id] не важно)
[param0] текстовое описание события,в случае прохода пишется ФИО проходящего человека из другой таблицы
[param1] ID пользователя который прошёл
[param2] не важно)
[param3] не важно)
[user_param_double] не важно)
[date] дата и время события
[time] дата и время события
[time2] дата и время события
[owner] не важно)
[pk] уникальный идентификатор строки

Таблица заполнена всякими данными, что то типа

INSERT INTO [protocol] ([objtype],[objid],[action],[region_id],[param0],[param1],[param2],[param3],[date],[time],[time2],[owner],[pk],[user_param_double]) 
VALUES('SDK_ORION_ACS_READER','4.3.0.33.1','EVENT0028','32.1',' Цветкова Татьяна  Юрьевна','1400784','','','2016-02-25T13:34:39.000','2016-02-25T13:34:39.000','2016-02-25T13:34:39.000','K23-27031201','{04D8525D-ABDB-E511-ACFA-5404A603730B}', NULL)


в двух словах таблица заполнена РАЗНЫМИ протокольными данными, среди них есть данные о проходах сотрудников их можно идентифицировать по наличию в поле param1 ID-того самого сотрудника.
собственно есть задача выводить первое и последнее событие по пользователю за сутки, с определённой глубиной
мною была сделана следующая хранимая процедура, которая возвращает первое и последнее событие по пропуску с @PersonId (то есть по param1) или с @FIO (то есть по param0) , данные отображаются с глубиной сегодня-@dayback либо если @dayback=0 тол за весь период.


USE [srv_intellect]
GO
/****** Object:  StoredProcedure [dbo].[spGet_Person_In_Out]    Script Date: 2/25/2016 1:25:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[spGet_Person_In_Out] 
	-- Add the parameters for the stored procedure here
	 @PersonId nvarchar(20), 
    @FIO nvarchar(60),
    @dayback int
	
AS
BEGIN
	
	SET NOCOUNT ON;

    -- Insert statements for procedure here


if @PersonId <>''
begin    
IF @dayback= 0
BEGIN
select min(CONVERT(varchar(10),[srv_intellect].[dbo].[PROTOCOL].date, 108) ) as first_in_Day
,max(CONVERT(varchar(10),[srv_intellect].[dbo].[PROTOCOL].date, 108) ) as Last_in_Day
,CONVERT(varchar(10),[srv_intellect].[dbo].[PROTOCOL].date, 110) as Day

  FROM [srv_intellect].[dbo].[PROTOCOL]  
  where [srv_intellect].[dbo].[PROTOCOL].param1 = @PersonId
  group by CONVERT(varchar(10),[srv_intellect].[dbo].[PROTOCOL].date, 110)
  order by CONVERT(varchar(10),[srv_intellect].[dbo].[PROTOCOL].date, 110)
END
ELSE
Begin
select min(CONVERT(varchar(10),[srv_intellect].[dbo].[PROTOCOL].date, 108) ) as first_in_Day
,max(CONVERT(varchar(10),[srv_intellect].[dbo].[PROTOCOL].date, 108) ) as Last_in_Day
,CONVERT(varchar(10),[srv_intellect].[dbo].[PROTOCOL].date, 110) as Day
  FROM [srv_intellect].[dbo].[PROTOCOL]
  where ([srv_intellect].[dbo].[PROTOCOL].param1 = @PersonId) 
  and ([srv_intellect].[dbo].[PROTOCOL].date between getdate()-@dayback and getdate() )
  
  group by CONVERT(varchar(10),[srv_intellect].[dbo].[PROTOCOL].date, 110)
  order by CONVERT(varchar(10),[srv_intellect].[dbo].[PROTOCOL].date, 110)
 end 

end
else
begin
IF @dayback= 0
BEGIN
select min(CONVERT(varchar(10),[srv_intellect].[dbo].[PROTOCOL].date, 108) ) as first_in_Day
,max(CONVERT(varchar(10),[srv_intellect].[dbo].[PROTOCOL].date, 108) ) as Last_in_Day
,CONVERT(varchar(10),[srv_intellect].[dbo].[PROTOCOL].date, 110) as Day

  FROM [srv_intellect].[dbo].[PROTOCOL]  
  where [srv_intellect].[dbo].[PROTOCOL].param0 = @FIO
  group by CONVERT(varchar(10),[srv_intellect].[dbo].[PROTOCOL].date, 110)
  order by CONVERT(varchar(10),[srv_intellect].[dbo].[PROTOCOL].date, 110)
END
ELSE
Begin
select min(CONVERT(varchar(10),[srv_intellect].[dbo].[PROTOCOL].date, 108) ) as first_in_Day
,max(CONVERT(varchar(10),[srv_intellect].[dbo].[PROTOCOL].date, 108) ) as Last_in_Day
,CONVERT(varchar(10),[srv_intellect].[dbo].[PROTOCOL].date, 110) as Day
  FROM [srv_intellect].[dbo].[PROTOCOL]
  where ([srv_intellect].[dbo].[PROTOCOL].param0 = @FIO) 
  and ([srv_intellect].[dbo].[PROTOCOL].date between getdate()-@dayback and getdate() )
  
  group by CONVERT(varchar(10),[srv_intellect].[dbo].[PROTOCOL].date, 110)
  order by CONVERT(varchar(10),[srv_intellect].[dbo].[PROTOCOL].date, 110)
 end 

end
end


в последнее время размер таблицы вырос и интенсивность её использования так же, посмотрел в SQL Server Profiler что при выполнение данной процедуры много накладных расходов.
но понимаю, что моих знаний уже недостаточно вот и обращаюсь к гуру, СПАСИБО.
25 фев 16, 14:34    [18862415]     Ответить | Цитировать Сообщить модератору
 Re: Пожалуйста помогите с оптимизацией запроса  [new]
Speshuric
Member

Откуда: г. Москва
Сообщений: 129
shilll1,

А другие индексы на таблице есть?
25 фев 16, 14:37    [18862440]     Ответить | Цитировать Сообщить модератору
 Re: Пожалуйста помогите с оптимизацией запроса  [new]
shilll1
Member

Откуда:
Сообщений: 36
Speshuric,

да простите забыл,
вот как раз с индексами у меня беда..
у меня их много и могу предположить что именно из за них у меня все беды, это будет второй вопрос, какие из них нужны..
USE [srv_intellect]
GO

/****** Object:  Index [IX_PROTOCOL_112]    Script Date: 2/25/2016 2:46:50 PM ******/
CREATE NONCLUSTERED INDEX [IX_PROTOCOL_112] ON [dbo].[PROTOCOL1]
(
	[objtype] ASC,
	[objid] ASC,
	[action] ASC
)
INCLUDE ( 	[region_id],
	[param0],
	[param1],
	[param2],
	[param3],
	[user_param_double],
	[date],
	[time],
	[time2],
	[owner],
	[pk]) 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


USE [srv_intellect]
GO

/****** Object:  Index [IX_PROTOCOL_115]    Script Date: 2/25/2016 2:47:00 PM ******/
CREATE NONCLUSTERED INDEX [IX_PROTOCOL_115] ON [dbo].[PROTOCOL1]
(
	[objtype] ASC
)
INCLUDE ( 	[objid],
	[action],
	[region_id],
	[param0],
	[param1],
	[param2],
	[param3],
	[user_param_double],
	[date],
	[time],
	[time2],
	[owner],
	[pk]) 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


USE [srv_intellect]
GO

/****** Object:  Index [IX_PROTOCOL_119]    Script Date: 2/25/2016 2:47:03 PM ******/
CREATE NONCLUSTERED INDEX [IX_PROTOCOL_119] ON [dbo].[PROTOCOL1]
(
	[objtype] ASC,
	[objid] ASC,
	[time] ASC
)
INCLUDE ( 	[action],
	[param0],
	[owner]) 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

USE [srv_intellect]
GO

/****** Object:  Index [IX_PROTOCOL_139]    Script Date: 2/25/2016 2:47:06 PM ******/
CREATE NONCLUSTERED INDEX [IX_PROTOCOL_139] ON [dbo].[PROTOCOL1]
(
	[objtype] ASC,
	[objid] ASC,
	[action] ASC,
	[time] ASC
)
INCLUDE ( 	[param0],
	[owner]) 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


USE [srv_intellect]
GO

/****** Object:  Index [IX_PROTOCOL_163]    Script Date: 2/25/2016 2:47:09 PM ******/
CREATE NONCLUSTERED INDEX [IX_PROTOCOL_163] ON [dbo].[PROTOCOL1]
(
	[objtype] ASC,
	[objid] ASC,
	[action] ASC,
	[time] ASC
)
INCLUDE ( 	[param0],
	[owner]) 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


USE [srv_intellect]
GO

/****** Object:  Index [IX_PROTOCOL_183]    Script Date: 2/25/2016 2:47:13 PM ******/
CREATE NONCLUSTERED INDEX [IX_PROTOCOL_183] ON [dbo].[PROTOCOL1]
(
	[date] 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


USE [srv_intellect]
GO

/****** Object:  Index [IX_PROTOCOL_19]    Script Date: 2/25/2016 2:47:16 PM ******/
CREATE NONCLUSTERED INDEX [IX_PROTOCOL_19] ON [dbo].[PROTOCOL1]
(
	[param1] ASC,
	[date] 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


USE [srv_intellect]
GO

/****** Object:  Index [IX_PROTOCOL_251]    Script Date: 2/25/2016 2:47:18 PM ******/
CREATE NONCLUSTERED INDEX [IX_PROTOCOL_251] ON [dbo].[PROTOCOL1]
(
	[action] 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


USE [srv_intellect]
GO

/****** Object:  Index [IX_PROTOCOL_273]    Script Date: 2/25/2016 2:47:21 PM ******/
CREATE NONCLUSTERED INDEX [IX_PROTOCOL_273] ON [dbo].[PROTOCOL1]
(
	[pk] ASC
)
INCLUDE ( 	[objtype],
	[objid],
	[action],
	[region_id],
	[param0],
	[param1],
	[param2],
	[param3],
	[user_param_double],
	[date],
	[time],
	[time2],
	[owner]) 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


USE [srv_intellect]
GO

/****** Object:  Index [IX_PROTOCOL_69]    Script Date: 2/25/2016 2:47:24 PM ******/
CREATE NONCLUSTERED INDEX [IX_PROTOCOL_69] ON [dbo].[PROTOCOL1]
(
	[objtype] ASC,
	[objid] ASC,
	[action] ASC,
	[time] ASC
)
INCLUDE ( 	[param0],
	[owner]) 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
25 фев 16, 14:49    [18862554]     Ответить | Цитировать Сообщить модератору
 Re: Пожалуйста помогите с оптимизацией запроса  [new]
o-o
Guest
вы зачем поля юникодами объявили, если в них все равно *неюникод* кладете?
значит, сама база кириллическая и если вашими инсертами, как они сейчас написаны, вставить китайство,
в юникоде вопросики будут все равно.
не переделать ли все nvarchar на varchar,
сэкономив чуть не половину размера.
тем более, что все это еще и в индексы включено
25 фев 16, 15:02    [18862676]     Ответить | Цитировать Сообщить модератору
 Re: Пожалуйста помогите с оптимизацией запроса  [new]
o-o
Guest
да еще вроде и индексы продублированы.
чисто на первый взгляд IX_PROTOCOL_69 = IX_PROTOCOL_163
25 фев 16, 15:05    [18862696]     Ответить | Цитировать Сообщить модератору
 Re: Пожалуйста помогите с оптимизацией запроса  [new]
Glory
Member

Откуда:
Сообщений: 104751
shilll1
собственно есть задача выводить первое и последнее событие по пользователю за сутки, с определённой глубиной

И сколько записей должно выводиться то при этом ?
25 фев 16, 15:05    [18862697]     Ответить | Цитировать Сообщить модератору
 Re: Пожалуйста помогите с оптимизацией запроса  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8577
Группировать (а это означает сортировку) по конверту вообще плохая идея. Пусть конвертацией клиент занимается, отдавайте ему дату.
25 фев 16, 15:10    [18862737]     Ответить | Цитировать Сообщить модератору
 Re: Пожалуйста помогите с оптимизацией запроса  [new]
shilll1
Member

Откуда:
Сообщений: 36
o-o,

да как я далёк ещё.
ну что касается инсёртов, то так они видны в профайлере.
если с Вшей точки зрения это не повлечёт за сбой других изменений, то я могу обратиться к производителю софта с такой инициативой.
а подскажите реально ли на живой базе поменять тип?
25 фев 16, 15:10    [18862743]     Ответить | Цитировать Сообщить модератору
 Re: Пожалуйста помогите с оптимизацией запроса  [new]
shilll1
Member

Откуда:
Сообщений: 36
Glory,

от нуля до 70 строк, т.к. глубина протокола 70 дней
25 фев 16, 15:14    [18862776]     Ответить | Цитировать Сообщить модератору
 Re: Пожалуйста помогите с оптимизацией запроса  [new]
shilll1
Member

Откуда:
Сообщений: 36
o-o
да еще вроде и индексы продублированы.
чисто на первый взгляд IX_PROTOCOL_69 = IX_PROTOCOL_163


увидел спасибо
25 фев 16, 15:15    [18862783]     Ответить | Цитировать Сообщить модератору
 Re: Пожалуйста помогите с оптимизацией запроса  [new]
o-o
Guest
shilll1
o-o
да еще вроде и индексы продублированы.
чисто на первый взгляд IX_PROTOCOL_69 = IX_PROTOCOL_163

увидел спасибо

там 3 одинаковых: IX_PROTOCOL_139, IX_PROTOCOL_163, IX_PROTOCOL_69
и 2 почти одинаковых:
имея IX_PROTOCOL_112, не нужен IX_PROTOCOL_115
лишние надо убрать.
25 фев 16, 15:24    [18862867]     Ответить | Цитировать Сообщить модератору
 Re: Пожалуйста помогите с оптимизацией запроса  [new]
shilll1
Member

Откуда:
Сообщений: 36
o-o,
Спасибо, уже)
25 фев 16, 15:30    [18862917]     Ответить | Цитировать Сообщить модератору
 Re: Пожалуйста помогите с оптимизацией запроса  [new]
aleks2
Guest
Владислав Колосов
Группировать (а это означает сортировку) по конверту вообще плохая идея. Пусть конвертацией клиент занимается, отдавайте ему дату.

Группировать - ваще плохая идея. Независимо по чему.
25 фев 16, 15:34    [18862953]     Ответить | Цитировать Сообщить модератору
 Re: Пожалуйста помогите с оптимизацией запроса  [new]
shilll1
Member

Откуда:
Сообщений: 36
Владислав Колосов
Группировать (а это означает сортировку) по конверту вообще плохая идея. Пусть конвертацией клиент занимается, отдавайте ему дату.

По поводу конвертации понял, спасибо
но я скорее к самому подходу построения запроса, м.б. я коне верно это делаю
25 фев 16, 15:34    [18862960]     Ответить | Цитировать Сообщить модератору
 Re: Пожалуйста помогите с оптимизацией запроса  [new]
shilll1
Member

Откуда:
Сообщений: 36
aleks2,

а подскажите альтернативы для моего случая, пожалуйста.
25 фев 16, 15:35    [18862967]     Ответить | Цитировать Сообщить модератору
 Re: Пожалуйста помогите с оптимизацией запроса  [new]
Speshuric
Member

Откуда: г. Москва
Сообщений: 129
shilll1,

1. Навести порядок в индексах (как минимум не хватает индекса по param1 и куча лишних)
2. Если возможно, вынести готовую дату (без времени) в отдельное поле и время (без даты) в отдельное. Допилить индексы.
3. Не конвертить зря в запросе.
4. Если производительности будет всё равно не хватать, то сделать заранее расчитанную таблицу или индексированное представление с готовыми результатами за предыдущие дни (тогда в запросе можно будет сильно ограничить глубину).
25 фев 16, 15:35    [18862969]     Ответить | Цитировать Сообщить модератору
 Re: Пожалуйста помогите с оптимизацией запроса  [new]
shilll1
Member

Откуда:
Сообщений: 36
Speshuric,

1. да, конечно. спасибо
2. к сожалению нет, это не мой софт а сторонний
3. я могу ошибаться, но это было чем то обусловлено
4. пожалуйста. подскажите, что такое "заранее расчитанную таблицу" ?
и "индексированное представление с готовыми результатами за предыдущие дни" я же не знаю, по каким пользователям (с каким ID будут запрашивать данные).
25 фев 16, 15:42    [18863022]     Ответить | Цитировать Сообщить модератору
 Re: Пожалуйста помогите с оптимизацией запроса  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Какая версия SQL Server?
25 фев 16, 15:43    [18863024]     Ответить | Цитировать Сообщить модератору
 Re: Пожалуйста помогите с оптимизацией запроса  [new]
o-o
Guest
индекс IX_PROTOCOL_273 вообще бред какой-то.
у вас ето [pk] уже кластерный ключ.
т.е. сама таблица и есть такой офигительный индекс со всеми полями.
----
может, у вас вообще они ВСЕ не нужны?
(просто интересно, кто и зачем такое наделал,
похоже, вообще от балды делали)
почитайте про sys.dm_db_index_usage_stats,
можно помониторить, что вообще не используется.
25 фев 16, 15:45    [18863049]     Ответить | Цитировать Сообщить модератору
 Re: Пожалуйста помогите с оптимизацией запроса  [new]
Speshuric
Member

Откуда: г. Москва
Сообщений: 129
shilll1
я же не знаю, по каким пользователям (с каким ID будут запрашивать данные).

По всем и строить. Если это на 10 лет и на 30000 человек каждый день, то всего 10 млн строк на 10 лет. Если будут индексы, то всё быстро будет
25 фев 16, 15:48    [18863064]     Ответить | Цитировать Сообщить модератору
 Re: Пожалуйста помогите с оптимизацией запроса  [new]
Speshuric
Member

Откуда: г. Москва
Сообщений: 129
100 млн. ну да один фиг. столько не будет
25 фев 16, 15:49    [18863071]     Ответить | Цитировать Сообщить модератору
 Re: Пожалуйста помогите с оптимизацией запроса  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
На скорую руку... Хотя сделать можно лучше

ALTER TABLE dbo.PROTOCOL
	ADD [day] AS DATEADD(dd, DATEDIFF(dd, 0, [DATE]), 0)
GO

CREATE NONCLUSTERED INDEX ix
    ON dbo.PROTOCOL ([day], param0, param1, [date]) WITH (DROP_EXISTING=ON)
GO

ALTER PROCEDURE [dbo].[spGet_Person_In_Out]
(
    @PersonId NVARCHAR(20),
    @FIO NVARCHAR(60),
    @dayback INT
)
AS BEGIN

    SET NOCOUNT ON;

    DECLARE
          @StartDate DATETIME
        , @EndDate DATETIME

    SET @EndDate = DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)
    SET @StartDate = DATEADD(DAY, -@dayback, @EndDate)

    SELECT
        first_in_Day = CONVERT(VARCHAR(10), first_in_Day, 108),
        Last_in_Day = CONVERT(VARCHAR(10), Last_in_Day, 108),
        [day] = CONVERT(VARCHAR(10), [day1], 110)
    FROM (
        SELECT
              first_in_Day = MIN([DATE])
            , Last_in_Day = MAX([DATE])
            , [day1] = [day]
        FROM dbo.PROTOCOL
        WHERE (
                    (ISNULL(@PersonId, '') != '' AND param1 = @PersonId)
                OR
                    (ISNULL(@PersonId, '') = '' AND param0 = @FIO)
            )
            AND (
                    @dayback = 0 
                OR 
                    (@dayback != 0 AND [day] BETWEEN @StartDate AND @EndDate)
           )
        GROUP BY [day]
    ) t
    ORDER BY [day1]
    OPTION(RECOMPILE)

END
GO
25 фев 16, 16:00    [18863161]     Ответить | Цитировать Сообщить модератору
 Re: Пожалуйста помогите с оптимизацией запроса  [new]
shilll1
Member

Откуда:
Сообщений: 36
AlanDenton,
sql server 2012 ent
25 фев 16, 16:07    [18863220]     Ответить | Цитировать Сообщить модератору
 Re: Пожалуйста помогите с оптимизацией запроса  [new]
shilll1
Member

Откуда:
Сообщений: 36
o-o
индекс IX_PROTOCOL_273 вообще бред какой-то.
у вас ето [pk] уже кластерный ключ.
т.е. сама таблица и есть такой офигительный индекс со всеми полями.
----
может, у вас вообще они ВСЕ не нужны?
(просто интересно, кто и зачем такое наделал,
похоже, вообще от балды делали)
почитайте про sys.dm_db_index_usage_stats,
можно помониторить, что вообще не используется.

ну конечно я, на основе вот этой статьи

где приведён запрос
--------------------------------------------------------------------------------------------

-- Создание недостающих индексов баз данных на SQL Server 2005, 2008

--

-- Скрипт анализирует статистику, собранную сервером баз данных об отсутствующих индексах в базах данных и

-- предлагает создать индексы, которые могут обеспечить значительное повышение производительности. 

SET NOCOUNT ON

DECLARE @dbid int

IF (object_id('tempdb..##IndexAdvantage') IS NOT NULL) DROP TABLE ##IndexAdvantage

CREATE TABLE ##IndexAdvantage ([Преимущество индекса] float, [База данных] varchar(64), [Transact SQL код для создания индекса] varchar(512), 

[Число компиляций] int, [Количество операций поиска] int, [Количество операций просмотра] int,

[Средняя стоимость ] int, [Средний процент выигрыша] int );

DECLARE DBases CURSOR FOR

SELECT database_id FROM sys.master_files -- Получаем список ID баз данных

WHERE state = 0 AND -- ONLINE

has_dbaccess(db_name(database_id)) = 1 -- Only look at databases to which we have access

GROUP BY database_id

OPEN DBases

FETCH NEXT FROM DBases

INTO @dbid

WHILE @@FETCH_STATUS = 0

BEGIN -- Выполняем для каждой базы данных --------------------------------------------------

INSERT INTO ##IndexAdvantage

SELECT [Преимущество индекса] = user_seeks * avg_total_user_cost * (avg_user_impact * 0.01),

      [База данных] = DB_NAME(mid.database_id),

      [Transact SQL код для создания индекса] = 'CREATE INDEX [IX_' + OBJECT_NAME(mid.object_id,@dbid) + '_' + 

      CAST(mid.index_handle AS nvarchar) + '] ON ' + 

      mid.statement + ' (' + ISNULL(mid.equality_columns,'') + 

      (CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ', ' 

ELSE '' END) + 

      (CASE WHEN mid.inequality_columns IS NOT NULL THEN + mid.inequality_columns ELSE '' END) + ')' + 

      (CASE WHEN mid.included_columns IS NOT NULL THEN ' INCLUDE (' + mid.included_columns + ')' 

ELSE '' END) +      ';', 

      [Число компиляций] = migs.unique_compiles,

      [Количество операций поиска] = migs.user_seeks,

      [Количество операций просмотра] = migs.user_scans,

      [Средняя стоимость ] = CAST(migs.avg_total_user_cost AS int),

      [Средний процент выигрыша] = CAST(migs.avg_user_impact AS int)

FROM  sys.dm_db_missing_index_groups mig

JOIN  sys.dm_db_missing_index_group_stats migs 

ON    migs.group_handle = mig.index_group_handle

JOIN  sys.dm_db_missing_index_details mid 

ON    mig.index_handle = mid.index_handle

AND   mid.database_id = @dbid

    FETCH NEXT FROM DBases

    INTO @dbid

END ----------------------------------------------------------------------------------------

CLOSE DBases

DEALLOCATE DBases

GO

SELECT * FROM ##IndexAdvantage ORDER BY 1 DESC

-- Значение ''Преимущество индекса'' выше 5000 в промышленных системах означает, что следует рассмотреть возможность создания этих индексов.

-- Если же значение превышает 10000, это обычно означает, что индекс может обеспечить значительное повышение производительности для операций чтения.

--------------------------------------------------------------------------------------------

-- Отправляем email с предложением создать индекс

IF (object_id('tempdb..##IndexAdvantage2') IS NOT NULL) DROP TABLE ##IndexAdvantage2

SELECT * INTO ##IndexAdvantage2 FROM ##IndexAdvantage WHERE [Преимущество индекса] >= 5000 ORDER BY 1 DESC

IF ((SELECT COUNT(*) FROM ##IndexAdvantage2) >= 1) BEGIN

DECLARE @subject_str varchar(255),

@message_str varchar(1024),

@separator_str varchar(1),

@email varchar(128)

SET @separator_str=CHAR(9) -- Символ табуляции

SET @email = 'email_address@webzavod.ru'

-- Подготовим текст сообщения

SET @subject_str = 'SQL Server '+@@SERVERNAME+': Предложение создать индексы в базе данных.'

SET @message_str = 'Сервер '+@@SERVERNAME + '. Выявлена необходимость создать индексы в базе данных!

Во вложении - таблица с кодом предлагаемых индексов.

Значение "Преимущество индекса" выше 5000 в промышленных системах означает, что следует рассмотреть возможность создания этих индексов.

Если же значение превышает 10000, это обычно означает, что индекс может обеспечить значительное повышение производительности для операций чтения.

Динамические административные представления, которые помогли нам получить информацию об отсутствующих индексах, не являются заменой помощника по настройке ядра СУБД, который также рассматривает индексированные представления и секции и обеспечивает более всесторонний анализ индексов, но они могут быть очень эффективны на начальном уровне анализа.'

-- Отправляем email

EXEC msdb.dbo.sp_send_dbmail

@recipients = @email,

@query = 'SELECT * FROM ##IndexAdvantage2',

@subject = @subject_str,

@body = @message_str,

@attach_query_result_as_file = 1,

@query_result_separator = @separator_str,

@query_result_width = 7000

END

-- Удаляем временную таблицу

IF (object_id('tempdb..##IndexAdvantage') IS NOT NULL) DROP TABLE ##IndexAdvantage

IF (object_id('tempdb..##IndexAdvantage2') IS NOT NULL) DROP TABLE ##IndexAdvantage2
--------------------------------------------------------------------------------------------
25 фев 16, 16:16    [18863301]     Ответить | Цитировать Сообщить модератору
 Re: Пожалуйста помогите с оптимизацией запроса  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
ALTER TABLE dbo.PROTOCOL
	ADD [day_] AS CAST([DATE] AS DATE)
GO
ALTER TABLE dbo.PROTOCOL
	ADD [time_] AS CAST([DATE] AS TIME)
GO

CREATE NONCLUSTERED INDEX ix
    ON dbo.PROTOCOL ([day_], param0, param1) INCLUDE([time_]) WITH (DROP_EXISTING=ON)
GO

ALTER PROCEDURE [dbo].[spGet_Person_In_Out]
(
    @PersonId NVARCHAR(20),
    @FIO NVARCHAR(60),
    @dayback INT
)
AS BEGIN

    SET NOCOUNT ON;

    DECLARE
          @StartDate DATE = DATEADD(DAY, -@dayback, GETDATE())
        , @EndDate DATE = GETDATE()

    SELECT
          first_in_Day = MIN([time_])
        , Last_in_Day = MAX([time_])
        , [day_]
    FROM dbo.PROTOCOL
    WHERE (
                (ISNULL(@PersonId, N'') != N'' AND param1 = @PersonId)
            OR
                (ISNULL(@PersonId, N'') = N'' AND param0 = @FIO)
        )
        AND (
                @dayback = 0 
            OR 
                (@dayback != 0 AND [day_] BETWEEN @StartDate AND @EndDate)
        )
    GROUP BY [day_]
    ORDER BY [day_]
    OPTION(RECOMPILE)

END
GO


Если хочется еще быстрее, то делайте изменения в структуре Вашей таблицы. ИМХО она не сильно оптимальная с точки зрения типов данных как уже сказали ранее.

А тот чудо скрипт, который использует sys.dm_db_missing_index_details можете выкинуть в топку. Эта вьюха иногда такой бред содержит...
25 фев 16, 16:24    [18863381]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить