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

Откуда:
Сообщений: 416
Добрый день, форумчане.

У меня ms-sql 2012. После долгой паузы запрос выполняется до 60 секунд. Следующие выполнения данного запроса занимают 2-4 секунды. Проходит какое-то продолжительное время и снова очень долго выполняется запрос.

Помогите разобраться в чём причина!? Есть ли у скл сервера какое-то кэширование ?
24 мар 16, 13:03    [18972715]     Ответить | Цитировать Сообщить модератору
 Re: При первом выполнении запроса выполняется тратится большое время  [new]
Glory
Member

Откуда:
Сообщений: 104751
stinggga
Помогите разобраться в чём причина!?

io statistics смотрели

stinggga
Есть ли у скл сервера какое-то кэширование ?

Есть. Иначе бы ваш запрос всегда выполнялся 60 секунд. Просто кэш не бесконечен по размеру.
24 мар 16, 13:06    [18972726]     Ответить | Цитировать Сообщить модератору
 Re: При первом выполнении запроса выполняется тратится большое время  [new]
Владислав Колосов
Member

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

какой-то кэш есть и не один.
24 мар 16, 14:54    [18973549]     Ответить | Цитировать Сообщить модератору
 Re: При первом выполнении запроса выполняется тратится большое время  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Покажите запрос и план выполнения.
24 мар 16, 14:57    [18973573]     Ответить | Цитировать Сообщить модератору
 Re: При первом выполнении запроса выполняется тратится большое время  [new]
stinggga
Member

Откуда:
Сообщений: 416
AlanDenton
Покажите запрос и план выполнения.



+
 declare @TargetAgentID int set @TargetAgentID = '210070'
	 declare @OffsetRows INT  set @OffsetRows = 0
     declare @FetchRows INT  set @FetchRows = 400
     declare @SearchField NVARCHAR(150)  set @SearchField =''
	 declare @SortField SYSNAME  set @SortField = 'PointName'
	 declare @SortFieldDest int  set @SortFieldDest  = 1
	 declare @isall bit  set @isall = 1
	 declare @User_ID int set @User_ID = 0

-- EXEC Portal3UA.dbo.MonitorGetTerminals @OffsetRows = '0',@FetchRows = '400', @SearchField='', @TargetAgentID = '210070', @SortField='PointName' 
--declare @TargetAgentID int set @TargetAgentID = 4619364
	declare @moment datetime set @moment =getdate()
	declare @day datetime set @day = convert(datetime,convert(varchar, @moment,112))
	declare @day_pred datetime select @day_pred = max(convert(datetime,convert(varchar,CreateTime,112)))	FROM [Main].dbo.[Payment] ppp (nolock)	where convert(datetime,convert(varchar, CreateTime,112)) < @day

	
select * from
(
 select p.PointID
		,p.PointName
		,p.DealerID
		,Location.FullAddress AS city
		,p.PointAddress --одним полем
		,t.SimProviderID
		--,SignalLevel = case when isnull(t.SignalLevel,0) = 200 then 100 else t.SignalLevel end
		,SignalLevel = isnull(t.SignalLevel,0)
		,LastSignal = LastUpdate
		,LastPayDateTime
		,Traffik = isnull(ReceivedKBytes,0) + isnull(SendKBytes,0)
		,TraffikMonth = 0
		,t.CashInModel

		,(
			select CashUnitsCount from [Main].dbo.[TerminalCashSum] tcs (nolock) where tcs.PointID = p.PointID and tcs.MoneyType = 1
		
		) as CashCount

		,(
			select CashUnitsSum from [Main].dbo.[TerminalCashSum] tcs (nolock) where tcs.PointID = p.PointID and tcs.MoneyType = 1
		
		) as CashSum

		,(			select CASE WHEN tcs.MoneyType = 1
                                      AND p.ATL_StackerSize IS NOT NULL
                                      AND p.ATL_StackerSize <> 0
                                 THEN CAST(100 * CAST((
                                                       SELECT   SUM(cs.CashUnitsCount)
                                                       FROM     [Main].dbo.TerminalCashSum cs
                                                       WHERE    cs.PointID = p.PointID
                                                                AND cs.MoneyType = 1
                                                      ) AS DECIMAL(18, 2)) / p.ATL_StackerSize AS DECIMAL(18, 2))
                                 WHEN tcs.MoneyType = 2
                                      AND p.ATL_CoinVaultSize IS NOT NULL
                                      AND p.ATL_CoinVaultSize <> 0
                                 THEN CAST(100 * CAST((
                                                       SELECT   SUM(cs.CashUnitsCount)
                                                       FROM     [Main].dbo.TerminalCashSum cs
                                                       WHERE    cs.PointID = p.PointID
                                                                AND cs.MoneyType = 2
                                                      ) AS DECIMAL(18, 2)) / p.ATL_CoinVaultSize AS DECIMAL(18, 2))
                                 ELSE 0
                            END AS filling
                     FROM   [Main].dbo.TerminalCashSum AS tcs
                            
                     WHERE  tcs.PointID = p.PointID and MoneyType=1) as filling
		 
		  , (	select Count(PaymentID) as coltrans
				FROM [Main].dbo.[Payment] ppp (nolock)
				where convert(datetime,convert(varchar, CreateTime,112)) = @day 
				and ppp.PointID = p.PointID and ppp.[status]=2) as TransactionToday

		 	, (	select Count(PaymentID) as coltrans
				FROM [Main].dbo.[Payment] ppp (nolock)
				where convert(datetime,convert(varchar, CreateTime,112)) = @day_pred
						and ppp.PointID = p.PointID and ppp.[status]=2) as TransactionYestarday


			, (	select isnull(sum(PayValue),0) as coltsum
				FROM [Main].dbo.[Payment] ppp (nolock)
				where convert(datetime,convert(varchar, CreateTime,112)) = @day 
				and ppp.PointID  = p.PointID and ppp.[status]=2) as TransactionSumToday

		 	, (	select isnull(sum(PayValue),0) as colsum
				FROM [Main].dbo.[Payment] ppp (nolock)
				where convert(datetime,convert(varchar, CreateTime,112)) = @day_pred
						and ppp.PointID = p.PointID and ppp.[status]=2) as TransactionSumYestarday
				
			,  
			
			ROW_NUMBER() OVER (ORDER BY  PointName,			 p.PointID) AS RowNumber

			,	(select isnull(reparation,0) as reparation
						from
						(
							select sum(comis) as reparation ,PointID

							from
							(

								select sum(isnull(PayValue,0)-isnull(ServiceValue,0)) as comis,DailyReport.PointID, ReportDate
								FROM [Main].dbo.DailyReport  as DailyReport
									
								where ReportDate between @day and dateadd(day,1,@day)
										and DailyReportTimeTypeID=1 and [Status]=2
								group by DailyReport.PointID, ReportDate
	
								union all

								select sum(isnull(ProfitValue,0)+isnull(TransactionCostInValue,0)) as comis,DailyProfit.PointID, ReportDate
								FROM [Main].dbo.DailyProfit as DailyProfit 
									
								where ReportDate between @day and dateadd(day,1,@day)
										and DailyReportTimeTypeID=1 and [Status]=2
								group by DailyProfit.PointID, ReportDate
							) a
							group by a.PointID
						)a

						where a.PointID = p.PointID)as IncomeToday






			,	(select isnull(reparation,0) as reparation
						from
						(
							select sum(comis) as reparation ,PointID

							from
							(

								select sum(isnull(PayValue,0)-isnull(ServiceValue,0)) as comis,DailyReport.PointID, ReportDate
								FROM [Main].dbo.DailyReport  as DailyReport
									
								where ReportDate between @day_pred and dateadd(day,1,@day_pred)
										and DailyReportTimeTypeID=1 and [Status]=2
								group by DailyReport.PointID, ReportDate
	
								union all

								select sum(isnull(ProfitValue,0)+isnull(TransactionCostInValue,0)) as comis,DailyProfit.PointID, ReportDate
								FROM [Main].dbo.DailyProfit as DailyProfit 
									
								where ReportDate between @day_pred and dateadd(day,1,@day_pred)
										and DailyReportTimeTypeID=1 and [Status]=2
								group by DailyProfit.PointID, ReportDate
							) a
							group by a.PointID
						)a

						where a.PointID = p.PointID)as IncomeYestarday
			,KPDToday = ( select case when col > 100 then 100 else col end as col
							from
							( 
							select case when sum(MinutesActive)>0 then ( sum(MinutesActive) * 100 ) /datediff (minute,  @day, @moment ) else 0 end as col
							from [dbo].[_3_TerminalActive] ppp
							where ppp.moment = @day and ppp.PointID = p.PointID
							)q
							)
			,KPDYestarday =(
							select case when col > 100 then 100 else col end as col
							from
							(
							select  case when sum(MinutesActive)>0 then ( sum(MinutesActive) * 100 ) /1440 else 0 end as col
							 from [dbo].[_3_TerminalActive] ppp
							 where ppp.moment = @day_pred and ppp.PointID = p.PointID
							 )q
							)


			,[Description] = case when  datediff (hour,  LastUpdate, @moment ) >12 then 'MonitorTerminalDesc3'
								  when  datediff (minute,  LastPayDateTime, @moment ) >60 then 'MonitorTerminalDesc2'
								  when PrinterErrorID = 3 then 'MonitorTerminalDesc1'
								end

			, isChosePoint = isnull(ms.Point_ID,0)
		
	from [Main].dbo.Point p (nolock)
	inner join [Main].dbo.[Terminal] t (nolock) on t.PointID = p.PointID
	INNER JOIN [Main].dbo.DealerChilds(@TargetAgentID) AS Childs ON p.DealerID = Childs.DealerID
	LEFT JOIN [Main].dbo.Location ON p.LocationID = Location.LocationID
	INNER JOIN[Main].dbo.Dealer AS Dealer ON p.DealerID = Dealer.DealerID
	left join [_3_PointMonitoringSet] ms on [UserID]=@User_ID and [ActDEalerID]=@TargetAgentID and p.PointID=ms.[Point_ID]

	WHERE  (p.[Enabled] = 1)
	and( @isall = 1 or p.PointID in ( select [Point_ID] from [_3_PointMonitoringSet] where [UserID]=@User_ID and [ActDEalerID]=@TargetAgentID)  )

	and ( @SearchField = '' or (p.PointID like '%'+@SearchField+'%' or p.PointName like '%'+@SearchField+'%') )
)a
	
ORDER BY 
	CASE WHEN @SortField = 'PointName' and @SortFieldDest = 1 then PointName end,
	CASE WHEN @SortField = 'PointName' and @SortFieldDest = -1 then PointName end desc,
	CASE WHEN @SortField = 'PointID' and @SortFieldDest = 1 then PointID end,
	CASE WHEN @SortField = 'PointID' and @SortFieldDest = -1 then PointID end desc,
	CASE WHEN @SortField = 'SignalLevel' and @SortFieldDest = 1 then SignalLevel end,
	CASE WHEN @SortField = 'SignalLevel' and @SortFieldDest = -1 then SignalLevel end desc,
	CASE WHEN @SortField = 'LastSignal' and @SortFieldDest = 1 then LastSignal end,
	CASE WHEN @SortField = 'LastSignal' and @SortFieldDest = -1 then LastSignal end desc,

	CASE WHEN @SortField = 'LastPayDateTime' and @SortFieldDest = 1 then LastPayDateTime end,
	CASE WHEN @SortField = 'LastPayDateTime' and @SortFieldDest = -1 then LastPayDateTime end desc,
	CASE WHEN @SortField = 'Traffik' and @SortFieldDest = 1 then Traffik end,
	CASE WHEN @SortField = 'Traffik' and @SortFieldDest = -1 then Traffik end desc,
	CASE WHEN @SortField = 'CashCount' and @SortFieldDest = 1 then CashCount end,
	CASE WHEN @SortField = 'CashCount' and @SortFieldDest = -1 then CashCount end desc,
	CASE WHEN @SortField = 'CashSum' and @SortFieldDest = 1 then CashSum end,
	CASE WHEN @SortField = 'CashSum' and @SortFieldDest = -1 then CashSum end desc,
	CASE WHEN @SortField = 'filling' and @SortFieldDest = 1 then filling end,
	CASE WHEN @SortField = 'filling' and @SortFieldDest = -1 then filling end desc,
	CASE WHEN @SortField = 'TransactionToday' and @SortFieldDest = 1 then TransactionToday end,
	CASE WHEN @SortField = 'TransactionToday' and @SortFieldDest = -1 then TransactionToday end desc,
	CASE WHEN @SortField = 'TransactionYestarday' and @SortFieldDest = 1 then TransactionYestarday end,
	CASE WHEN @SortField = 'TransactionYestarday' and @SortFieldDest = -1 then TransactionYestarday end desc,
	CASE WHEN @SortField = 'TransactionSumToday' and @SortFieldDest = 1 then TransactionSumToday end,
	CASE WHEN @SortField = 'TransactionSumToday' and @SortFieldDest = -1 then TransactionSumToday end desc,
	CASE WHEN @SortField = 'TransactionSumYestarday' and @SortFieldDest = 1 then TransactionSumYestarday end,
	CASE WHEN @SortField = 'TransactionSumYestarday' and @SortFieldDest = -1 then TransactionSumYestarday end desc,


	CASE WHEN @SortField = 'IncomeYestarday' and @SortFieldDest = 1 then IncomeYestarday end,
	CASE WHEN @SortField = 'IncomeYestarday' and @SortFieldDest = -1 then IncomeYestarday end desc,
	CASE WHEN @SortField = 'IncomeToday' and @SortFieldDest = 1 then IncomeToday end,
	CASE WHEN @SortField = 'IncomeToday' and @SortFieldDest = -1 then IncomeToday end desc,
	CASE WHEN @SortField = 'KPDYestarday' and @SortFieldDest = 1 then KPDYestarday end,
	CASE WHEN @SortField = 'KPDYestarday' and @SortFieldDest = -1 then KPDYestarday end desc,
	CASE WHEN @SortField = 'KPDToday' and @SortFieldDest = 1 then KPDToday end,
	CASE WHEN @SortField = 'KPDToday' and @SortFieldDest = -1 then KPDToday end desc
	
	 --TransactionToday TransactionYestarday TransactionSumToday 


	--CashCount CashSum filling
    OFFSET COALESCE(@OffsetRows, 0) ROWS
			FETCH NEXT COALESCE(@FetchRows, 100) ROWS ONLY;


Сообщение было отредактировано: 24 мар 16, 19:10
24 мар 16, 15:44    [18973932]     Ответить | Цитировать Сообщить модератору
 Re: При первом выполнении запроса выполняется тратится большое время  [new]
stinggga
Member

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

Может у кого есть какие-то сображения?
24 мар 16, 15:49    [18973970]     Ответить | Цитировать Сообщить модератору
 Re: При первом выполнении запроса выполняется тратится большое время  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2423
stinggga
stinggga,

Может у кого есть какие-то сображения?


дак какие тут соображения,
винт медленный(потому что медленно вычитывается в память),
памяти мало(потому что быстро выкидывается из кеша)
24 мар 16, 16:06    [18974071]     Ответить | Цитировать Сообщить модератору
 Re: При первом выполнении запроса выполняется тратится большое время  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
stinggga
Может у кого есть какие-то сображения?

Запрос мягко говоря не оптимизированный... Например, вот такой кусок можно переписать:

        select isnull(reparation,0) as reparation
	FROM (
		select sum(comis) as reparation ,PointID
        FROM (
			select sum(isnull(PayValue,0)-isnull(ServiceValue,0)) as comis,DailyReport.PointID, ReportDate
			FROM [Main].dbo.DailyReport  as DailyReport
									
			where ReportDate between @day and dateadd(day,1,@day)
					and DailyReportTimeTypeID=1 and [Status]=2
			group by DailyReport.PointID, ReportDate
	
			union all

			select sum(isnull(ProfitValue,0)+isnull(TransactionCostInValue,0)) as comis,DailyProfit.PointID, ReportDate
			FROM [Main].dbo.DailyProfit as DailyProfit 
									
			where ReportDate between @day and dateadd(day,1,@day)
					and DailyReportTimeTypeID=1 and [Status]=2
			group by DailyProfit.PointID, ReportDate
		) a
		group by a.PointID
	) a
	where a.PointID = p.PointID

на:

SELECT ISNULL(SUM(
    ISNULL(PayValue, 0) -
    ISNULL(ServiceValue, 0) +
    ISNULL(ProfitValue, 0) +
    ISNULL(TransactionCostInValue, 0)), 0)
FROM dbo.DailyReport d
WHERE d.ReportDate BETWEEN @day AND DATEADD(DAY, 1, @day)
    AND d.DailyReportTimeTypeID = 1
    AND d.[Status] = 2
    AND d.PointID = a.PointID


таких примеров у Вас много можно найти. Но опять же... без плана выполнения как без рук.
24 мар 16, 16:15    [18974124]     Ответить | Цитировать Сообщить модератору
 Re: При первом выполнении запроса выполняется тратится большое время  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Такой кусок кода:

select Count(PaymentID) as coltrans
FROM [Main].dbo.[Payment] ppp (nolock)
where convert(datetime,convert(varchar, CreateTime,112)) = @day 
    and ppp.PointID = p.PointID and ppp.[status]=2) as TransactionToday

select isnull(sum(PayValue),0) as coltsum
FROM [Main].dbo.[Payment] ppp (nolock)
where convert(datetime,convert(varchar, CreateTime,112)) = @day 
    and ppp.PointID  = p.PointID and ppp.[status]=2) as TransactionSumToday

select Count(PaymentID) as coltrans
FROM [Main].dbo.[Payment] ppp (nolock)
where convert(datetime,convert(varchar, CreateTime,112)) = @day_pred
	and ppp.PointID = p.PointID and ppp.[status]=2

select isnull(sum(PayValue),0) as colsum
FROM [Main].dbo.[Payment] ppp (nolock)
where convert(datetime,convert(varchar, CreateTime,112)) = @day_pred
	and ppp.PointID = p.PointID and ppp.[status]=2) as TransactionSumYestarday

на вот такой:

SELECT
    COUNT(CASE WHEN CAST(CreateTime AS DATE) = @day THEN PaymentID END) AS coltrans1,
    SUM(CASE WHEN CAST(CreateTime AS DATE) = @day THEN PayValue ELSE 0 END) AS coltsum1,
    COUNT(CASE WHEN CAST(CreateTime AS DATE) = @day_pred THEN PaymentID END) AS coltrans1,
    SUM(CASE WHEN CAST(CreateTime AS DATE) = @day_pred THEN PayValue ELSE 0 END) AS coltsum1,
FROM dbo.Payment ppp
WHERE CAST(CreateTime AS DATE) IN (@day, @day_pred)
    AND ppp.PointID = p.PointID
    AND ppp.[status] = 2

и то это все не предел. В Вашем запросе есть где разгуляться :)
24 мар 16, 16:27    [18974227]     Ответить | Цитировать Сообщить модератору
 Re: При первом выполнении запроса выполняется тратится большое время  [new]
stinggga
Member

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

Спасибо
24 мар 16, 19:03    [18974975]     Ответить | Цитировать Сообщить модератору
 Re: При первом выполнении запроса выполняется тратится большое время  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Пожалуйста. Попробуйте переписать запрос по аналогии. Если не поможет, то план выполнения все же приложите.
24 мар 16, 19:07    [18974988]     Ответить | Цитировать Сообщить модератору
 Re: При первом выполнении запроса выполняется тратится большое время  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Передаю эстафетную палочку Вам :) думаю, что Вы разберетесь что еще можно допилить по аналогии:

+
USE [Main]
GO

ALTER TABLE dbo.Payment
	ADD CreateTimeOnlyDate AS CAST(CreateTime AS DATE)
GO

CREATE /*UNIQUE*/ NONCLUSTERED INDEX ix
    ON dbo.Payment (CreateTimeOnlyDate DESC, PointID, [status])
GO

ALTER TABLE dbo.DailyReport
	ADD Val AS ISNULL(PayValue, 0) - ISNULL(ServiceValue, 0) + ISNULL(ProfitValue, 0) + ISNULL(TransactionCostInValue, 0)
GO

CREATE /*UNIQUE*/ NONCLUSTERED INDEX ix
    ON dbo.DailyReport (ReportDate, PointID) INCLUDE (Val) WHERE DailyReportTimeTypeID = 1 AND [Status] = 2
GO

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

DECLARE @TargetAgentID INT = '210070'
      , @OffsetRows INT = 0
      , @FetchRows INT = 400
      , @SearchField NVARCHAR(150) = ''
      , @SortField SYSNAME = 'PointName'
      , @SortFieldDest INT = 1
      , @isall BIT = 1
      , @User_ID INT = 0

DECLARE @moment DATETIME = GETDATE()
DECLARE @day DATETIME = CAST(@moment AS DATE)

DECLARE @day_pred DATETIME = (
        SELECT TOP(1) CreateTimeOnlyDate
        FROM dbo.Payment
        WHERE CreateTimeOnlyDate < @day
        ORDER BY CreateTimeOnlyDate DESC
    )
	
SELECT *
FROM (
    SELECT 
          p.PointID
		, p.PointName
		, p.DealerID
		, Location.FullAddress AS city
		, p.PointAddress
		, t.SimProviderID
		, SignalLevel = isnull(t.SignalLevel,0)
		, LastSignal = LastUpdate
		, LastPayDateTime
		, Traffik = isnull(ReceivedKBytes,0) + isnull(SendKBytes,0)
		, TraffikMonth = 0
		, t.CashInModel
          
		, tcs.CashUnitsCount as CashCount
		, tcs.CashUnitsSum as CashSum

		,(			select CASE WHEN tcs.MoneyType = 1
                                      AND p.ATL_StackerSize IS NOT NULL
                                      AND p.ATL_StackerSize <> 0
                                 THEN CAST(100 * CAST((
                                                       SELECT   SUM(cs.CashUnitsCount)
                                                       FROM     dbo.TerminalCashSum cs
                                                       WHERE    cs.PointID = p.PointID
                                                                AND cs.MoneyType = 1
                                                      ) AS DECIMAL(18, 2)) / p.ATL_StackerSize AS DECIMAL(18, 2))
                                 WHEN tcs.MoneyType = 2
                                      AND p.ATL_CoinVaultSize IS NOT NULL
                                      AND p.ATL_CoinVaultSize <> 0
                                 THEN CAST(100 * CAST((
                                                       SELECT   SUM(cs.CashUnitsCount)
                                                       FROM     dbo.TerminalCashSum cs
                                                       WHERE    cs.PointID = p.PointID
                                                                AND cs.MoneyType = 2
                                                      ) AS DECIMAL(18, 2)) / p.ATL_CoinVaultSize AS DECIMAL(18, 2))
                                 ELSE 0
                            END AS filling
                     FROM   [Main].dbo.TerminalCashSum AS tcs
                            
                     WHERE  tcs.PointID = p.PointID and MoneyType=1) as filling
		 
		    , TransactionToday = ISNULL(TransactionToday, 0)
		 	, TransactionYestarday = ISNULL(TransactionYestarday, 0)
			, TransactionSumToday = ISNULL(TransactionSumToday, 0)
		 	, TransactionSumYestarday = ISNULL(TransactionSumYestarday, 0)
			, RowNumber = ROW_NUMBER() OVER (ORDER BY PointName, p.PointID) 

			, IncomeToday = (
                SELECT ISNULL(SUM(
                    ISNULL(PayValue, 0) -
                    ISNULL(ServiceValue, 0) +
                    ISNULL(ProfitValue, 0) +
                    ISNULL(TransactionCostInValue, 0)), 0)
                FROM dbo.DailyReport d
                WHERE d.ReportDate BETWEEN @day AND DATEADD(DAY, 1, @day)
                    AND d.DailyReportTimeTypeID = 1
                    AND d.[Status] = 2
                    AND d.PointID = p.PointID) 

			, IncomeYestarday = (
                SELECT ISNULL(SUM(
                    ISNULL(PayValue, 0) -
                    ISNULL(ServiceValue, 0) +
                    ISNULL(ProfitValue, 0) +
                    ISNULL(TransactionCostInValue, 0)), 0)
                FROM dbo.DailyReport d
                WHERE d.ReportDate BETWEEN @day_pred AND DATEADD(DAY, 1, @day_pred)
                    AND d.DailyReportTimeTypeID = 1
                    AND d.[Status] = 2
                    AND d.PointID = p.PointID
             )
			,KPDToday = ( select case when col > 100 then 100 else col end as col
							from
							( 
							select case when sum(MinutesActive)>0 then ( sum(MinutesActive) * 100 ) /datediff (minute,  @day, @moment ) else 0 end as col
							from [dbo].[_3_TerminalActive] ppp
							where ppp.moment = @day and ppp.PointID = p.PointID
							)q
							)
			,KPDYestarday =(
							select case when col > 100 then 100 else col end as col
							from
							(
							select  case when sum(MinutesActive)>0 then ( sum(MinutesActive) * 100 ) /1440 else 0 end as col
							 from [dbo].[_3_TerminalActive] ppp
							 where ppp.moment = @day_pred and ppp.PointID = p.PointID
							 )q
							)


			,[Description] = case when  datediff (hour,  LastUpdate, @moment ) >12 then 'MonitorTerminalDesc3'
								  when  datediff (minute,  LastPayDateTime, @moment ) >60 then 'MonitorTerminalDesc2'
								  when PrinterErrorID = 3 then 'MonitorTerminalDesc1'
								end

			, isChosePoint = ISNULL(ms.Point_ID, 0)
	FROM dbo.Point p
	JOIN dbo.Terminal t on t.PointID = p.PointID
    LEFT JOIN (
        SELECT
            COUNT(CASE WHEN CreateTimeOnlyDate = @day THEN PaymentID END) AS TransactionToday,
            SUM(CASE WHEN CreateTimeOnlyDate = @day THEN PayValue END) AS TransactionSumToday,
            COUNT(CASE WHEN CreateTimeOnlyDate = @day_pred THEN PaymentID END) AS TransactionYestarday,
            SUM(CASE WHEN CreateTimeOnlyDate = @day_pred THEN PayValue END) AS TransactionSumYestarday,
            PointID
        FROM dbo.Payment
        WHERE CreateTimeOnlyDate IN (@day, @day_pred)
            AND [status] = 2
        GROUP BY PointID
    ) ppp ON ppp.PointID = p.PointID
    OUTER APPLY (
        SELECT TOP(1) CashUnitsCount, CashUnitsSum
        FROM dbo.[TerminalCashSum] tcs
        WHERE tcs.PointID = p.PointID
            AND tcs.MoneyType = 1
    ) tcs
	--JOIN dbo.DealerChilds(@TargetAgentID) AS Childs ON p.DealerID = Childs.DealerID
	LEFT JOIN dbo.Location ON p.LocationID = Location.LocationID
	JOIN dbo.Dealer AS d ON p.DealerID = d.DealerID
	LEFT JOIN dbo.[_3_PointMonitoringSet] ms on ms.[UserID] = @User_ID AND ms.ActDEalerID = @TargetAgentID AND p.PointID = ms.Point_ID
	WHERE p.[Enabled] = 1
	    AND (@isall = 1 OR ms.PointID IS NOT NULL)
	    AND (
                @SearchField = ''
            OR
                p.PointID LIKE '%' + @SearchField + '%'
            OR
                p.PointName LIKE '%' + @SearchField + '%'
        )
) a
/*...... без изменений  */
OPTION(RECOMPILE)


Сообщение было отредактировано: 24 мар 16, 19:48
24 мар 16, 19:43    [18975078]     Ответить | Цитировать Сообщить модератору
 Re: При первом выполнении запроса выполняется тратится большое время  [new]
МуМу
Member

Откуда:
Сообщений: 1134
Планировщик запросов это экспертная система. Она по определению не может работать на 100% эффективно - в яблочко. Есть много неизвестных, наличие свободной оперативной памяти, актуальная статистика, наличие индексов, структура запроса,хинты и т.п. В вашем случае найдите индексы, либо структуру запроса которые его сделают НАДЕЖНЫМ(как минимум).
25 мар 16, 23:37    [18980272]     Ответить | Цитировать Сообщить модератору
 Re: При первом выполнении запроса выполняется тратится большое время  [new]
МуМу
Member

Откуда:
Сообщений: 1134
А по нашим кейсам- пересчет статистики с full scan ;)
25 мар 16, 23:38    [18980275]     Ответить | Цитировать Сообщить модератору
 Re: При первом выполнении запроса выполняется тратится большое время  [new]
stinggga
Member

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

План запроса прилагаю

К сообщению приложен файл (plan zaprosa.rar - 31Kb) cкачать
28 мар 16, 10:38    [18985326]     Ответить | Цитировать Сообщить модератору
 Re: При первом выполнении запроса выполняется тратится большое время  [new]
stinggga
Member

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

Переписал так
+
USE [Portal3UA]
GO
/****** Object:  StoredProcedure [dbo].[MonitorGetTerminals]    Script Date: 28.03.2016 10:37:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[MonitorGetTerminals] (
      @TargetAgentID int
	 ,@OffsetRows INT = NULL
     ,@FetchRows INT = NULL
     ,@SearchField NVARCHAR(150) = NULL
	 ,@SortField SYSNAME  = null
	 ,@SortFieldDest int = 1
	 ,@isall bit = 1
	 ,@User_ID int = 0
	 )
as
--MonitorGetTerminals 4619364

/* declare @TargetAgentID int set @TargetAgentID = '210139'--4619364
	 declare @OffsetRows INT  set @OffsetRows = 0
     declare @FetchRows INT  set @FetchRows = 400
     declare @SearchField NVARCHAR(150)  set @SearchField =''
	 declare @SortField SYSNAME  set @SortField = 'PointName'
	 declare @SortFieldDest int  set @SortFieldDest  = 1
	 declare @isall bit  set @isall = 1
	 declare @User_ID int set @User_ID = 0*/

-- EXEC Portal3UA.dbo.MonitorGetTerminals @OffsetRows = '0',@FetchRows = '4', @SearchField='', @TargetAgentID = '210070', @SortField='PointName' 
--declare @TargetAgentID int set @TargetAgentID = 4619364
	declare @moment datetime set @moment =getdate()
	declare @day datetime set @day = convert(datetime,convert(varchar, @moment,112))
	declare @day_pred datetime select @day_pred = max(convert(datetime,convert(varchar,CreateTime,112)))	FROM [Main].dbo.[Payment] ppp (nolock)	where convert(datetime,convert(varchar, CreateTime,112)) < @day

	
select  * from
(
 select distinct p.PointID
		,p.PointName
		,p.DealerID
		,Location.FullAddress AS city
		,p.PointAddress --одним полем
		,t.SimProviderID
		--,SignalLevel = case when isnull(t.SignalLevel,0) = 200 then 100 else t.SignalLevel end
		,SignalLevel = isnull(t.SignalLevel,0)
		,LastSignal = LastUpdate
		,LastPayDateTime
		,Traffik = isnull(ReceivedKBytes,0) + isnull(SendKBytes,0)
		,TraffikMonth = 0
		,t.CashInModel

		,CashCount = tcs.CashUnitsCount
		,CashSum = tcs.CashUnitsSum

	

		,(			select CASE WHEN tcs.MoneyType = 1
                                      AND p.ATL_StackerSize IS NOT NULL
                                      AND p.ATL_StackerSize <> 0
                                 THEN CAST(100 * CAST((
                                                       SELECT   SUM(cs.CashUnitsCount)
                                                       FROM     [Main].dbo.TerminalCashSum cs
                                                       WHERE    cs.PointID = p.PointID
                                                                AND cs.MoneyType = 1
                                                      ) AS DECIMAL(18, 2)) / p.ATL_StackerSize AS DECIMAL(18, 2))
                                 WHEN tcs.MoneyType = 2
                                      AND p.ATL_CoinVaultSize IS NOT NULL
                                      AND p.ATL_CoinVaultSize <> 0
                                 THEN CAST(100 * CAST((
                                                       SELECT   SUM(cs.CashUnitsCount)
                                                       FROM     [Main].dbo.TerminalCashSum cs
                                                       WHERE    cs.PointID = p.PointID
                                                                AND cs.MoneyType = 2
                                                      ) AS DECIMAL(18, 2)) / p.ATL_CoinVaultSize AS DECIMAL(18, 2))
                                 ELSE 0
                            END AS filling
                     FROM   [Main].dbo.TerminalCashSum AS tcs
                            
                     WHERE  tcs.PointID = p.PointID and MoneyType=1) as filling

		  ,payy.TransactionToday
		  ,payy.TransactionYestarday
		  ,payy.TransactionSumToday
		  ,payy.TransactionSumYestarday
		 
		  
			, ROW_NUMBER() OVER (ORDER BY  PointName,			 p.PointID) AS RowNumber

			/*,	(select isnull(reparation,0) as reparation
						from
						(
							select sum(comis) as reparation ,PointID

							from
							(

								select sum(isnull(PayValue,0)-isnull(ServiceValue,0)) as comis,DailyReport.PointID, ReportDate
								FROM [Main].dbo.DailyReport  as DailyReport
									
								where ReportDate between @day and dateadd(day,1,@day)
										and DailyReportTimeTypeID=1 and [Status]=2
								group by DailyReport.PointID, ReportDate
	
								union all

								select sum(isnull(ProfitValue,0)+isnull(TransactionCostInValue,0)) as comis,DailyProfit.PointID, ReportDate
								FROM [Main].dbo.DailyProfit as DailyProfit 
									
								where ReportDate between @day and dateadd(day,1,@day)
										and DailyReportTimeTypeID=1 and [Status]=2
								group by DailyProfit.PointID, ReportDate
							) a
							group by a.PointID
						)a

						where a.PointID = p.PointID)as IncomeToday0,*/
						,IncomeToday


						


			/*,	(select isnull(reparation,0) as reparation
						from
						(
							select sum(comis) as reparation ,PointID

							from
							(

								select sum(isnull(PayValue,0)-isnull(ServiceValue,0)) as comis,DailyReport.PointID, ReportDate
								FROM [Main].dbo.DailyReport  as DailyReport
									
								where ReportDate between @day_pred and dateadd(day,1,@day_pred)
										and DailyReportTimeTypeID=1 and [Status]=2
								group by DailyReport.PointID, ReportDate
	
								union all

								select sum(isnull(ProfitValue,0)+isnull(TransactionCostInValue,0)) as comis,DailyProfit.PointID, ReportDate
								FROM [Main].dbo.DailyProfit as DailyProfit 
									
								where ReportDate between @day_pred and dateadd(day,1,@day_pred)
										and DailyReportTimeTypeID=1 and [Status]=2
								group by DailyProfit.PointID, ReportDate
							) a
							group by a.PointID
						)a

						where a.PointID = p.PointID)as IncomeYestarday0*/
						,IncomeYestarday
			,yu.KPDToday
			,yu.KPDYestarday			
			
			,[Description_n] = isnull(case when  datediff (hour,  LastUpdate, @moment ) >12 then 3
								  when  datediff (minute,  LastPayDateTime, @moment ) >60 then 2
								  when PrinterErrorID = 3 then 1								  
								end,0)

			,[Description] = case when  datediff (hour,  LastUpdate, @moment ) >12 then 'MonitorTerminalDesc3'
								  when  datediff (minute,  LastPayDateTime, @moment ) >60 then 'MonitorTerminalDesc2'
								  when PrinterErrorID = 3 then 'MonitorTerminalDesc1'								
								end

			, isChosePoint = isnull(ms.Point_ID,0)
		
	from [Main].dbo.Point p (nolock)
	inner join [Main].dbo.[Terminal] t (nolock) on t.PointID = p.PointID
	INNER JOIN [Main].dbo.DealerChilds(@TargetAgentID) AS Childs ON p.DealerID = Childs.DealerID
		
	
	inner join [Main].dbo.[TerminalCashSum] tcs (nolock) on tcs.PointID = p.PointID		and MoneyType=1
	left join (--колличество и сумма транзакций
		        SELECT
					COUNT(CASE WHEN CAST(CreateTime AS DATE) = @day THEN PaymentID END) AS TransactionToday,
					SUM(CASE WHEN CAST(CreateTime AS DATE) = @day THEN PayValue ELSE 0 END) AS TransactionSumToday,
					COUNT(CASE WHEN CAST(CreateTime AS DATE) = @day_pred THEN PaymentID END) AS TransactionYestarday,
					SUM(CASE WHEN CAST(CreateTime AS DATE) = @day_pred THEN PayValue ELSE 0 END) AS TransactionSumYestarday,
					PointID
				FROM [Main].dbo.Payment ppp (nolock)
				WHERE CAST(CreateTime AS DATE) IN (@day, @day_pred)
					
					AND ppp.[status] = 2 
			group by PointID
	) payy on payy.PointID = p.PointID

	left join -- КПД
	(
		select case when KPDToday > 100 then 100 else col end as KPDToday
			, case when KPDYestarday > 100 then 100 else col end as KPDYestarday
			,PointID
		from
			( 
				select case when sum(CASE WHEN CAST(moment AS DATE) = @day THEN MinutesActive END)>0 
								then ( sum(CASE WHEN CAST(moment AS DATE) = @day THEN MinutesActive END) * 100 ) /datediff (minute,  @day, @moment ) else 0 end as KPDToday
							 ,case when sum(MinutesActive)>0 then ( sum(MinutesActive) * 100 ) /1440 else 0 end as col
						
						,case when sum(CASE WHEN CAST(moment AS DATE) = @day_pred THEN MinutesActive END)>0 
								then ( sum(CASE WHEN CAST(moment AS DATE) = @day_pred THEN MinutesActive END) * 100 ) /1440 else 0 end as KPDYestarday
					
					,ppp.PointID
				from [dbo].[_3_TerminalActive] ppp (nolock)
				where CAST(ppp.moment AS DATE) IN (@day, @day_pred)
				group by ppp.PointID
		)q
	)yu on yu.PointID = p.PointID

	--income
	left join (						
							select   sum(comis_today+proftit_today) as IncomeToday 
									,sum(comis_pred+proftit_pred) as IncomeYestarday
									,PointID

							from
							(

								select   comis_today = sum(isnull(CASE WHEN CAST(ReportDate AS DATE) = @day THEN PayValue END,0)-isnull(CASE WHEN CAST(ReportDate AS DATE) = @day THEN ServiceValue END,0)) 
								        ,comis_pred = sum(isnull(CASE WHEN CAST(ReportDate AS DATE) = @day_pred THEN PayValue END,0)-isnull(CASE WHEN CAST(ReportDate AS DATE) = @day_pred THEN ServiceValue END,0)) 
										,proftit_today = 0	
										,proftit_pred = 0
										,DailyReport.PointID
								FROM [Main].dbo.DailyReport  as DailyReport (nolock)
									
								where CAST(ReportDate AS DATE) IN (@day, @day_pred) 
										and DailyReportTimeTypeID=1 and [Status]=2
								group by DailyReport.PointID
	
								union all

								select   comis_today = 0
										,comis_pred = 0
										,proftit_today = sum(isnull(CASE WHEN CAST(ReportDate AS DATE) = @day THEN ProfitValue END,0)+isnull(CASE WHEN CAST(ReportDate AS DATE) = @day THEN TransactionCostInValue END,0)) 
								        ,proftit_pred = sum(isnull(CASE WHEN CAST(ReportDate AS DATE) = @day_pred THEN ProfitValue END,0)+isnull(CASE WHEN CAST(ReportDate AS DATE) = @day_pred THEN TransactionCostInValue END,0)) 
										,DailyProfit.PointID
								FROM [Main].dbo.DailyProfit as DailyProfit  (nolock)
									
								where CAST(ReportDate AS DATE) IN (@day, @day_pred) 
										and DailyReportTimeTypeID=1 and [Status]=2
								group by DailyProfit.PointID
							) aa
							group by aa.PointID				
								
	) inc on inc.PointID = p.PointID



	LEFT	JOIN [Main].dbo.Location ON p.LocationID = Location.LocationID
	INNER	JOIN[Main].dbo.Dealer AS Dealer ON p.DealerID = Dealer.DealerID
	left	join [_3_PointMonitoringSet] ms on [UserID]=@User_ID and [ActDEalerID]=@TargetAgentID and p.PointID=ms.[Point_ID]

	WHERE  (p.[Enabled] = 1)
	and( @isall = 1 or p.PointID in ( select [Point_ID] from [_3_PointMonitoringSet] where [UserID]=@User_ID and [ActDEalerID]=@TargetAgentID)  )

	and ( @SearchField = '' or (p.PointID like '%'+@SearchField+'%' or p.PointName like '%'+@SearchField+'%') )
)a
	
ORDER BY 
	CASE WHEN @SortField = 'PointName' and @SortFieldDest = 1 then PointName end,
	CASE WHEN @SortField = 'PointName' and @SortFieldDest = -1 then PointName end desc,
	CASE WHEN @SortField = 'PointID' and @SortFieldDest = 1 then PointID end,
	CASE WHEN @SortField = 'PointID' and @SortFieldDest = -1 then PointID end desc,
	CASE WHEN @SortField = 'SignalLevel' and @SortFieldDest = 1 then SignalLevel end,
	CASE WHEN @SortField = 'SignalLevel' and @SortFieldDest = -1 then SignalLevel end desc,
	CASE WHEN @SortField = 'LastSignal' and @SortFieldDest = 1 then LastSignal end,
	CASE WHEN @SortField = 'LastSignal' and @SortFieldDest = -1 then LastSignal end desc,

	CASE WHEN @SortField = 'LastPayDateTime' and @SortFieldDest = 1 then LastPayDateTime end,
	CASE WHEN @SortField = 'LastPayDateTime' and @SortFieldDest = -1 then LastPayDateTime end desc,
	CASE WHEN @SortField = 'Traffik' and @SortFieldDest = 1 then Traffik end,
	CASE WHEN @SortField = 'Traffik' and @SortFieldDest = -1 then Traffik end desc,
	CASE WHEN @SortField = 'CashCount' and @SortFieldDest = 1 then CashCount end,
	CASE WHEN @SortField = 'CashCount' and @SortFieldDest = -1 then CashCount end desc,
	CASE WHEN @SortField = 'CashSum' and @SortFieldDest = 1 then CashSum end,
	CASE WHEN @SortField = 'CashSum' and @SortFieldDest = -1 then CashSum end desc,
	CASE WHEN @SortField = 'filling' and @SortFieldDest = 1 then filling end,
	CASE WHEN @SortField = 'filling' and @SortFieldDest = -1 then filling end desc,
	CASE WHEN @SortField = 'TransactionToday' and @SortFieldDest = 1 then TransactionToday end,
	CASE WHEN @SortField = 'TransactionToday' and @SortFieldDest = -1 then TransactionToday end desc,
	CASE WHEN @SortField = 'TransactionYestarday' and @SortFieldDest = 1 then TransactionYestarday end,
	CASE WHEN @SortField = 'TransactionYestarday' and @SortFieldDest = -1 then TransactionYestarday end desc,
	CASE WHEN @SortField = 'TransactionSumToday' and @SortFieldDest = 1 then TransactionSumToday end,
	CASE WHEN @SortField = 'TransactionSumToday' and @SortFieldDest = -1 then TransactionSumToday end desc,
	CASE WHEN @SortField = 'TransactionSumYestarday' and @SortFieldDest = 1 then TransactionSumYestarday end,
	CASE WHEN @SortField = 'TransactionSumYestarday' and @SortFieldDest = -1 then TransactionSumYestarday end desc,


	CASE WHEN @SortField = 'IncomeYestarday' and @SortFieldDest = 1 then IncomeYestarday end,
	CASE WHEN @SortField = 'IncomeYestarday' and @SortFieldDest = -1 then IncomeYestarday end desc,
	CASE WHEN @SortField = 'IncomeToday' and @SortFieldDest = 1 then IncomeToday end,
	CASE WHEN @SortField = 'IncomeToday' and @SortFieldDest = -1 then IncomeToday end desc,
	CASE WHEN @SortField = 'KPDYestarday' and @SortFieldDest = 1 then KPDYestarday end,
	CASE WHEN @SortField = 'KPDYestarday' and @SortFieldDest = -1 then KPDYestarday end desc,
	CASE WHEN @SortField = 'KPDToday' and @SortFieldDest = 1 then KPDToday end,
	CASE WHEN @SortField = 'KPDToday' and @SortFieldDest = -1 then KPDToday end desc,
	CASE WHEN @SortField = 'Description_n' and @SortFieldDest = 1 then Description_n end,
	CASE WHEN @SortField = 'Description_n' and @SortFieldDest = -1 then Description_n end desc,
	CASE WHEN @SortField = 'Description' and @SortFieldDest = 1 then Description end,
	CASE WHEN @SortField = 'Description' and @SortFieldDest = -1 then Description end desc

	

    OFFSET COALESCE(@OffsetRows, 0) ROWS
			FETCH NEXT COALESCE(@FetchRows, 100) ROWS ONLY;

			--PRINT @SQL
   -- EXEC sys.sp_executesql @SQL

 --MonitorGetTerminals @TargetAgentID =4619364, @SortField='PointName' exec  MonitorGetTerminals @TargetAgentID =4619364, @SortField='SignalLevel' 


Сообщение было отредактировано: 28 мар 16, 10:40
28 мар 16, 10:39    [18985336]     Ответить | Цитировать Сообщить модератору
 Re: При первом выполнении запроса выполняется тратится большое время  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
То что Вы переписали это конечно хорошо. Жаль, что мои труды вообще не брались во внимание. Например, чтобы full scan не было, а быстрый seek:

DECLARE @day_pred DATETIME = (
        SELECT TOP(1) CreateTimeOnlyDate
        FROM dbo.Payment
        WHERE CreateTimeOnlyDate < @day
        ORDER BY CreateTimeOnlyDate DESC
    )

План, собственно говоря, подтверждает мои слова.
28 мар 16, 11:10    [18985480]     Ответить | Цитировать Сообщить модератору
 Re: При первом выполнении запроса выполняется тратится большое время  [new]
stinggga
Member

Откуда:
Сообщений: 416
AlanDenton
То что Вы переписали это конечно хорошо. Жаль, что мои труды вообще не брались во внимание. Например, чтобы full scan не было, а быстрый seek:

DECLARE @day_pred DATETIME = (
        SELECT TOP(1) CreateTimeOnlyDate
        FROM dbo.Payment
        WHERE CreateTimeOnlyDate < @day
        ORDER BY CreateTimeOnlyDate DESC
    )

План, собственно говоря, подтверждает мои слова.


Я прошу прощения не понял. Можете разъяснить по подробнее с примером в моём запросе?
28 мар 16, 11:20    [18985524]     Ответить | Цитировать Сообщить модератору
 Re: При первом выполнении запроса выполняется тратится большое время  [new]
stinggga
Member

Откуда:
Сообщений: 416
AlanDenton,
В вашем коде есть ошибке. Например ваш код

,
 IncomeToday = (
                SELECT ISNULL(SUM(
                    ISNULL(PayValue, 0) -
                    ISNULL(ServiceValue, 0) +
                    ISNULL(ProfitValue, 0) +
                    ISNULL(TransactionCostInValue, 0)), 0)
                FROM dbo.DailyReport d
                WHERE d.ReportDate BETWEEN @day AND DATEADD(DAY, 1, @day)
                    AND d.DailyReportTimeTypeID = 1
                    AND d.[Status] = 2
                    AND d.PointID = p.PointID) 

			, IncomeYestarday = (
                SELECT ISNULL(SUM(
                    ISNULL(PayValue, 0) -
                    ISNULL(ServiceValue, 0) +
                    ISNULL(ProfitValue, 0) +
                    ISNULL(TransactionCostInValue, 0)), 0)
                FROM dbo.DailyReport d
                WHERE d.ReportDate BETWEEN @day_pred AND DATEADD(DAY, 1, @day_pred)
                    AND d.DailyReportTimeTypeID = 1
                    AND d.[Status] = 2
                    AND d.PointID = p.PointID
             )


У меня в запросе 2 таблицы DailyReport и DailyProfit. Потому и union all

--income
	left join (						
							select   sum(comis_today+proftit_today) as IncomeToday 
									,sum(comis_pred+proftit_pred) as IncomeYestarday
									,PointID

							from
							(

								select   comis_today = sum(isnull(CASE WHEN CAST(ReportDate AS DATE) = @day THEN PayValue END,0)-isnull(CASE WHEN CAST(ReportDate AS DATE) = @day THEN ServiceValue END,0)) 
								        ,comis_pred = sum(isnull(CASE WHEN CAST(ReportDate AS DATE) = @day_pred THEN PayValue END,0)-isnull(CASE WHEN CAST(ReportDate AS DATE) = @day_pred THEN ServiceValue END,0)) 
										,proftit_today = 0	
										,proftit_pred = 0
										,DailyReport.PointID
								FROM [Main].dbo.DailyReport  as DailyReport (nolock)
									
								where CAST(ReportDate AS DATE) IN (@day, @day_pred) 
										and DailyReportTimeTypeID=1 and [Status]=2
								group by DailyReport.PointID
	
								union all

								select   comis_today = 0
										,comis_pred = 0
										,proftit_today = sum(isnull(CASE WHEN CAST(ReportDate AS DATE) = @day THEN ProfitValue END,0)+isnull(CASE WHEN CAST(ReportDate AS DATE) = @day THEN TransactionCostInValue END,0)) 
								        ,proftit_pred = sum(isnull(CASE WHEN CAST(ReportDate AS DATE) = @day_pred THEN ProfitValue END,0)+isnull(CASE WHEN CAST(ReportDate AS DATE) = @day_pred THEN TransactionCostInValue END,0)) 
										,DailyProfit.PointID
								FROM [Main].dbo.DailyProfit as DailyProfit  (nolock)
									
								where CAST(ReportDate AS DATE) IN (@day, @day_pred) 
										and DailyReportTimeTypeID=1 and [Status]=2
								group by DailyProfit.PointID
							) aa
							group by aa.PointID				
								
	) inc on inc.PointID = p.PointID
28 мар 16, 11:35    [18985576]     Ответить | Цитировать Сообщить модератору
 Re: При первом выполнении запроса выполняется тратится большое время  [new]
stinggga
Member

Откуда:
Сообщений: 416
Как можно оптимизировать запрос ещё ?
28 мар 16, 11:54    [18985658]     Ответить | Цитировать Сообщить модератору
 Re: При первом выполнении запроса выполняется тратится большое время  [new]
Glory
Member

Откуда:
Сообщений: 104751
stinggga
У меня в запросе 2 таблицы DailyReport и DailyProfit. Потому и union all

А зачем по две агрегатные функции, если их результаты вычитаются ?
28 мар 16, 11:57    [18985673]     Ответить | Цитировать Сообщить модератору
 Re: При первом выполнении запроса выполняется тратится большое время  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
stinggga
Как можно оптимизировать запрос ещё ?

имхо вам лучше разбить запрос на логические части через временную таблицу
28 мар 16, 12:07    [18985733]     Ответить | Цитировать Сообщить модератору
 Re: При первом выполнении запроса выполняется тратится большое время  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
stinggga
Как можно оптимизировать запрос ещё ?

Настроения нет критиковать... Вот обратите внимание на CAST(... AS DATE) и CONVERT-ы равнозначные. На данных столбцах индексов нет и даже если бы были, то происходил бы Index Scan. Чтобы были Index Seek я Вам написал что нужно:

ALTER TABLE dbo.Payment
	ADD CreateTimeOnlyDate AS CAST(CreateTime AS DATE)
GO

CREATE /*UNIQUE*/ NONCLUSTERED INDEX ix
    ON dbo.Payment (CreateTimeOnlyDate DESC, PointID, [status])
GO

ALTER TABLE dbo.DailyReport
	ADD Val AS ISNULL(PayValue, 0) - ISNULL(ServiceValue, 0) + ISNULL(ProfitValue, 0) + ISNULL(TransactionCostInValue, 0)
GO

CREATE /*UNIQUE*/ NONCLUSTERED INDEX ix
    ON dbo.DailyReport (ReportDate, PointID) INCLUDE (Val) WHERE DailyReportTimeTypeID = 1 AND [Status] = 2
GO

Индексы возможно не самые идеальные (на реальных данных можно и получше надизайнить), но главное что будет Index Seek... Далее запрос:

DECLARE @day_pred DATETIME = (
        SELECT TOP(1) CreateTimeOnlyDate
        FROM dbo.Payment
        WHERE CreateTimeOnlyDate < @day
        ORDER BY CreateTimeOnlyDate DESC
    )

с учетом индексов работать будет быстрее:

declare @day_pred datetime select @day_pred = max(convert(datetime,convert(varchar,CreateTime,112)))	FROM [Main].dbo.[Payment] ppp (nolock)	where convert(datetime,convert(varchar, CreateTime,112)) < @day

То что я ошибся признаю, но все же пересмотрите ответ еще раз. Там все вполне очевидно.
28 мар 16, 12:24    [18985795]     Ответить | Цитировать Сообщить модератору
 Re: При первом выполнении запроса выполняется тратится большое время  [new]
stinggga
Member

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

Спасибо
28 мар 16, 12:29    [18985817]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить