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

Откуда:
Сообщений: 53
Добрый день, уважаемые форумчане помогите разобраться с проблемой.
Может где-то туплю или не знаю, заранее извиняюсь может быть за глупые вопросы.
Краткая предыстория:
У меня есть таблица "Остатки клиентов", назовём её OPERClient, в ней есть поля
Id - идентификатор записи
acc - счет клиента
dat - дата транзакции
summ - остаток 

Каждый день в данную таблицу подгружаются данные из файла mdb
Мне нужно в одном из отчетов подсчитать за определенный промежуток времени средний остаток, чтобы найти среднее нужно сложить за каждый день остаток и разделить на количество дней в периоде.
есть такой запрос, считающий остаток за каждый день.
        select r.ACC,c.dt,a.RESTRUB from (
		select a.ACC,a.max(dat) maxd,a.min(dat) mind from OPERClient a group by a.ACC) r
	join 
	CALENDAR c on c.dt between '20130101' and '20170310'
	cross apply (select MAX(dat) dt from OPERClient where ACC=r.ACC and dat<=c.dt ) dt
	join OPERClient a on a.dat=dt.dt and a.ACC=r.ACC

Calendar - календарь с датами, почему первичная дата 20130101 - потому что история всех оборотов/остатков по счету ведется с той даты, а так как счет может "спать", соответственно оборот по счету "стоит на месте". Таким образом если мне нужны остатки по счетам с 01.03.2017 по 10.03.2017, то результатом запроса будут, остатки за каждый день из периода.
Уже при накоплении больших объемов инфы, запрос стал выполняться достаточно долго.
Вопрос 1. Как можно данный запрос оптимизировать?
Вопрос 2. Заметил еще такой момент. В какое то время базу данных перенес на более свежий сервер, но на нем проявились тупняки в расчете, на старом сервере выполняется гораздо быстрее. Конфигурация обоих серверов:

"Старый"
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4276.0 (X64)
Feb 8 2013 10:37:00 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
Проц- Intel Xeon CPU 3.4 (Cores - 2, Logical Processor - 4)
ОП - 4Gb

"Новый"
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4276.0 (X64)
Feb 8 2013 10:37:00 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
Проц - Intel Xeon CPU 3.4 (Cores - 4, Logical Processor - 8)
ОП - 6 GB

На "новом" запрос выполняется более часа, затем беру делаю бэкап базы, восстанавливаю на "Старом", запускаю запрос- выполняется за 20 минут.
Куда копать? Что исправить? Что можно оптимизировать?
15 мар 17, 12:38    [20297341]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация работы запроса или проблемы с сервером  [new]
Rankatan
Member

Откуда:
Сообщений: 250
Добавьте некластерный индекс


CREATE NONCLUSTERED INDEX inx_name on Sales.OPERClient(ACC,dat) 
15 мар 17, 12:57    [20297454]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация работы запроса или проблемы с сервером  [new]
TaPaK
Member

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

это ему точно не поможет.

_den89

автор
считающий остаток за каждый день.
для понимания: в каком месте вашего запроса это происходит?
да что он вообще делает?? кроме как насилует OPERClient многократно
15 мар 17, 13:04    [20297510]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация работы запроса или проблемы с сервером  [new]
Rankatan
Member

Откуда:
Сообщений: 250
TaPaK
Rankatan,

это ему точно не поможет.


Поможет
15 мар 17, 13:05    [20297527]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация работы запроса или проблемы с сервером  [new]
TaPaK
Member

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

это ему точно не поможет.


Поможет
к всей хрени получить ещё и лишний лукап? правда скорее всего ещё и придётся заставить скл использовать ваш гений
15 мар 17, 13:08    [20297545]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация работы запроса или проблемы с сервером  [new]
Rankatan
Member

Откуда:
Сообщений: 250
TaPaK
Rankatan
пропущено...

Поможет
к всей хрени получить ещё и лишний лукап? правда скорее всего ещё и придётся заставить скл использовать ваш гений

Какой лукап? Это покрывающий индекс получится
15 мар 17, 13:11    [20297567]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация работы запроса или проблемы с сервером  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Rankatan
TaPaK
пропущено...
к всей хрени получить ещё и лишний лукап? правда скорее всего ещё и придётся заставить скл использовать ваш гений

Какой лукап? Это покрывающий индекс получится

a.RESTRUB
15 мар 17, 13:13    [20297579]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация работы запроса или проблемы с сервером  [new]
Rankatan
Member

Откуда:
Сообщений: 250
CREATE NONCLUSTERED INDEX inx_name on OPERClient(ACC,dat) INCLUDE (RESTRUB )


+ Избавляться от затроения OPERClient
15 мар 17, 13:15    [20297596]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация работы запроса или проблемы с сервером  [new]
TaPaK
Member

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

угу, генерьте ему индексы пока он будет показывать куски запроса.
15 мар 17, 13:18    [20297620]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация работы запроса или проблемы с сервером  [new]
_den89
Member

Откуда:
Сообщений: 53
TaPaK, как раз происходит замедление в куске кода, который я предоставил.
для понимания буду рассматривать разрез одного счета:
Допустим есть счет 40702%01, по нему изменялся остаток 28.02,04.03,06.03, мне чтобы подсчитать средний остаток с 01.03 по 06.03, необходимо взять остаток за каждый день с 01.03 по 06.03 проссумировать и разделить на количество дней (6). К примеру остаток за 01.03 будет = 28.02
Код который привел, показывает остаток на каждый день по счету.
Если есть другой вариант, как можно это сделать, я с удовольствием посмотрел бы.
Была другая идея реализации, если нужно взять средний остаток за период с 01.03 по 06.03:
Так как в таблице остатков может и не быть движения средств, то выявляю самую максимальную дату с остатком, к примеру узнаю остаток за 01.03, затем смотрю все транзакции в этом периоде, к примеру их было 2, соответственно суммирую операции и делю на количество операций. (данный метод не проверял)
15 мар 17, 14:16    [20297954]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация работы запроса или проблемы с сервером  [new]
_den89
Member

Откуда:
Сообщений: 53
TaPaK
Rankatan,

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


Проблема именно с этим куском. я никаких других кусков кода не показывал, поэтому не вводите в заблуждение.
15 мар 17, 14:19    [20297977]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация работы запроса или проблемы с сервером  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
_den89
TaPaK
Rankatan,

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


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

хорошо, зачем вам в нём a.max(dat) maxd,a.min(dat) mind и вообще подзапрос?
15 мар 17, 14:43    [20298122]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация работы запроса или проблемы с сервером  [new]
_den89
Member

Откуда:
Сообщений: 53
Rankatan
CREATE NONCLUSTERED INDEX inx_name on OPERClient(ACC,dat) INCLUDE (RESTRUB )


+ Избавляться от затроения OPERClient


Спасибо! Честно сказать с индексами так не работал, могу глупость говорить, сори.
Я правильно понимаю, создаем индекс по столбцами. А затем, как его использовать? просто добавить в запрос?
15 мар 17, 14:52    [20298181]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация работы запроса или проблемы с сервером  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
_den89,
CREATE TABLE #calendar (
  [dat] DATE )
;
WITH
ct ( [dat] ) AS (
  SELECT
    CONVERT( DATE, '20130101' )
  UNION ALL
  SELECT
    DATEADD( DAY, 1, [dat] )
  FROM
    ct
  WHERE
    [dat] < '20170310'
)
INSERT 
INTO
  #calendar
SELECT
  *
FROM
  ct
OPTION (
  MAXRECURSION 0 )
;
CREATE TABLE #oper_client (
  [id] BIGINT NOT NULL IDENTITY(1,1) PRIMARY KEY,
  [acc] VARCHAR(32) NOT NULL,
  [dat] DATE NOT NULL,
  [sum] MONEY )
;
INSERT
INTO
  #oper_client (
    [acc],
    [dat],
    [sum] )
VALUES
  ( '11111', '20170101', 100.0 ),
  ( '11111', '20170301', 200.0 ),
  ( '22222', '20160101', 50.0 ),
  ( '22222', '20170301', 300.0 )
;
WITH
opc AS (
  SELECT
    [acc],
    [prev] = [dat],
    [next] = LEAD( [dat], 1, '99991231' ) OVER ( PARTITION BY [acc] ORDER BY [dat] ),
    [sum]
  FROM
    #oper_client
)
SELECT
  opc.[acc],
  c.[dat],
  opc.[sum]
FROM
  #calendar c
  INNER JOIN opc ON (
        opc.[prev] <= c.[dat]
    AND opc.[next]  > c.[dat] )
ORDER BY
  1, 2
15 мар 17, 15:13    [20298314]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация работы запроса или проблемы с сервером  [new]
_den89
Member

Откуда:
Сообщений: 53
TaPaK
_den89
пропущено...


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

хорошо, зачем вам в нём a.max(dat) maxd,a.min(dat) mind и вообще подзапрос?

в другом отчете нужны были данные о максимальной и минимальной дате движения средств, затем оставил, для того чтобы была одна запись на счет, конечно можно было сделать top 1.
15 мар 17, 15:27    [20298429]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация работы запроса или проблемы с сервером  [new]
_den89
Member

Откуда:
Сообщений: 53
Руслан Дамирович,
Спасибо! Поразбираюсь
15 мар 17, 15:28    [20298437]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация работы запроса или проблемы с сервером  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
_den89,

А у вас в OPERClient - summ - это остаток на счету на дату, или это сумма поступления/снятия на дату? если второе, то там нужно еще нарастающий итог по полю сделать в WITH opc:
[sum] = SUM( [sum] ) OVER ( PARTITION BY [acc] ORDER BY [dat] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
15 мар 17, 17:27    [20299020]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация работы запроса или проблемы с сервером  [new]
_den89
Member

Откуда:
Сообщений: 53
Руслан Дамирович
_den89,

А у вас в OPERClient - summ - это остаток на счету на дату, или это сумма поступления/снятия на дату? если второе, то там нужно еще нарастающий итог по полю сделать в WITH opc:
[sum] = SUM( [sum] ) OVER ( PARTITION BY [acc] ORDER BY [dat] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )


поле summ - остаток на дату.
Тестирую пока на ваших данных, так как функция LEAD появилась с 2012, а у меня версия 2008 R2, переписал следующим образом:
[next] =(select MIN(t.dat) OVER (PARTITION BY t.acc ORDER BY t.dat) from #oper_client where t.dat>dat) 
/*LEAD( [dat], 1, '99991231' ) OVER ( PARTITION BY [acc] ORDER BY [dat] ),*/

Но результат поля next = null
и результат итогового запроса = Null
16 мар 17, 09:32    [20300464]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация работы запроса или проблемы с сервером  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
_den89,
Ну ясное дело, думать-то мы не хотим, поиск в google тоже использовать желания нет.
Вынь да положь? Положим, но в последний раз.
И в качестве домашнего задания - это ни разу не оптимальный запрос.
WITH
opc0 AS (
  SELECT
    [acc],
    [dat],
    [sum],
    [rn] = ROW_NUMBER() OVER ( PARTITION BY [acc] ORDER BY [dat] )
  FROM
    #oper_client
),
opc AS (
  SELECT
    t1.[acc],
    [prev] = t1.[dat],
    [next] = ISNULL( t2.[dat], '99991231' ),
    t1.[sum]
  FROM
    opc0 t1
    LEFT JOIN opc0 t2 ON (
          t2.[acc] = t1.[acc]
      AND t2.[rn] = t1.[rn] + 1 )
)
SELECT
  opc.[acc],
  c.[dat],
  opc.[sum]
FROM
  #calendar c
  INNER JOIN opc ON (
        opc.[prev] <= c.[dat]
    AND opc.[next]  > c.[dat] )
ORDER BY
  1, 2
16 мар 17, 10:26    [20300705]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация работы запроса или проблемы с сервером  [new]
_den89
Member

Откуда:
Сообщений: 53
Руслан Дамирович,
Спасибо за помощь! Учту.
16 мар 17, 10:34    [20300750]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация работы запроса или проблемы с сервером  [new]
_den89
Member

Откуда:
Сообщений: 53
Руслан Дамирович,
по поводу оптимизации, можете подсказать (из сложившегося опыта) где можно почитать (ресурс/книга/...) про оптимизацию запросов?
16 мар 17, 10:38    [20300774]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация работы запроса или проблемы с сервером  [new]
Rankatan
Member

Откуда:
Сообщений: 250
_den89
Руслан Дамирович,
по поводу оптимизации, можете подсказать (из сложившегося опыта) где можно почитать (ресурс/книга/...) про оптимизацию запросов?


Забей на другие предложения, оставь свой кривой запрос и выполни:
CREATE CLUSTERED INDEX clust_inx_OPERClient on OPERClient(ACC,dat)

если будет ошибка, что кластерный уже существует, сделай
CREATE INDEX inx_OPERClient on OPERClient(ACC,dat) INCLUDE (RESTRUB )

Когда выполнишь, сообщи насколько изменилось время после добавления индекса с оригинальным запросом.
Индексы сами по себе работают, ничего дополнительно делать не нужно (только фрагментировать раз в неделю). Плюс памяти добавь на сервер, 6 ГБ это мало.
16 мар 17, 11:12    [20301029]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация работы запроса или проблемы с сервером  [new]
TaPaK
Member

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

упорно продвигает индексы не видя всего запроса... ну ну
16 мар 17, 11:13    [20301036]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация работы запроса или проблемы с сервером  [new]
Rankatan
Member

Откуда:
Сообщений: 250
TaPaK
Rankatan,

упорно продвигает индексы не видя всего запроса... ну ну


Ты прав, профита нет

create table dbo.OPERClient 
(
	Id int IDENTITY (1,1) PRIMARY KEY,
	acc int NOT NULL,
	dat date NOT NULL,
	RESTRUB money NOT NULL, 
) 
create table dbo.CALENDAR 
(
	dt date NOT NULL PRIMARY KEY
) 
--заполняем  dbo.CALENDAR
insert into dbo.CALENDAR (dt)
SELECT DATEADD(DAY,ROW_NUMBER() OVER(ORDER BY 1/0)-1,d1) ND FROM
(
	SELECT d1,CAST(REPLICATE('<r/>',DATEDIFF(DAY,d1,d2)) AS XML) X FROM
	(SELECT CAST('20130101' AS DATE) d1, CAST('20170316'AS date) d2) Z
) Z
CROSS APPLY X.nodes('r') Y(b)

--заполняем dbo.OPERClient
insert into dbo.OPERClient (acc,dat,RESTRUB)
select ABS(CHECKSUM(NEWID()))%4000,DATEADD(day,ABS(CHECKSUM(NEWID()))%600*-1,GETDATE()),ABS(CHECKSUM(NEWID()))%4000
GO 200000


--запрос
select r.ACC,c.dt,a.RESTRUB from (
	select a.ACC,max(dat) maxd,min(dat) mind from OPERClient a group by a.ACC) r
join 
CALENDAR c on c.dt between '20130101' and '20170310'
cross apply (select MAX(dat) dt from OPERClient where ACC=r.ACC and dat<=c.dt ) dt
join OPERClient a on a.dat=dt.dt and a.ACC=r.ACC

CREATE NONCLUSTERED INDEX inx_name on OPERClient(ACC,dat) INCLUDE (RESTRUB )

select r.ACC,c.dt,a.RESTRUB from (
	select a.ACC,max(dat) maxd,min(dat) mind from OPERClient a group by a.ACC) r
join 
CALENDAR c on c.dt between '20130101' and '20170310'
cross apply (select MAX(dat) dt from OPERClient where ACC=r.ACC and dat<=c.dt ) dt
join OPERClient a on a.dat=dt.dt and a.ACC=r.ACC
16 мар 17, 11:33    [20301145]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация работы запроса или проблемы с сервером  [new]
TaPaK
Member

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

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

+

Привозит папа сына-дебила на море, привел его на пляж и объясняет:
П: Вот видишь, сынок, это море..
С: (тупо) Где?
П: Ну вот это плещется-это море...
С: (по прежнему тупо)Где?
П:(распаляясь)Ну вот мы сидим на таком желтом-это песок, а вон то
синее-это море..
С:...Где?
П:(в бешенстве)Что ты идиот не понимаешь, вот это сухое, желтое-это
песок, а вон то синее, плещется, мокрое-ЭТО МОРЕ!!!
С:(тупо)Где?
Папины нервы не выдерживают, он в крайней ярости хватает сына,
и макает его головой в воду с криком "Вот море!!! Вот оно!!! Вот
это море!! Вот!" Сын захлебывается, кое-как вырывается и с трудом
дыша спрашивает
-Папа, что это было?
-Море, %*"!!!
-ГДЕ?
16 мар 17, 11:36    [20301179]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить