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

Откуда:
Сообщений: 60
Добрый день! есть вот такой фрагмент запроса к БД, который использует приложение. раньше его не было и задача выполнялась удовлетворительно по времени. Сейчас он появился с целью присвоить передаваемому параметру процедуры значение (очередной номер документа). для этого в таблице "hdr" базы данных "wsklad" происходит выборка данных и операции сравнения. приложение стало работать значительно медленнее,т.к. размер таблицы сравнительно большой - данные занимают около 1.5 гб. скрипт писал не я, но видно,что он лопатит всё подряд. нам же нужно выбрать данные, которые сопряжены со столбцом с параметром "skd_unicode" (то есть просмотреть даты только для документов касаемо склада №2) а не все сплошняком. как правильно добавить это условие либо как ещё дополнительно оптимизировать данный запрос,чтобы он работал быстрее?

USE [Wsklad]
GO
/****** Объект:  StoredProcedure [dbo].[usp_Rule_Hdr_1]    Дата сценария: 03/27/2013 13:36:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************/
/*                    Расчёт заголовка документа "Приход"                     */
/******************************************************************************/
ALTER PROCEDURE [dbo].[usp_Rule_Hdr_1] 
  @Hdr_UniCode   uniqueidentifier,
  @Skd_Unicode	 int,
  @Prm_unicode   int,
  @Hdr_Date      datetime,
  @Hdr_Sum01     money out, -- суммы производителя
  @Hdr_Sum02     money out, -- суммы поставщика
  @Hdr_Sum03     money out, -- суммы розничные
  @Hdr_Sum04     money out, -- суммы по чистой розничной цене
  @Hdr_Sum05     money out, -- суммы по таре
  @Hdr_Sum06     money out, -- и вспом.материалам
  @Hdr_Sum07     money out, -- наценка розничная руб.
  @Hdr_Sum08     money out, -- НДС аптеки руб.
  @Hdr_Sum09     money out, -- суммы реестровых цен
  @Hdr_Sum10     money out, -- торг.надбавка опт.звена
  @Hdr_Sum11     money out, -- НСП аптеки руб.
  @Hdr_Sum12     money out, -- торг.надбавка импортёра
  @Hdr_ExchSum01 money out,
  @Hdr_ExchSum02 money out,
  @Hdr_ExchSum03 money out,
  @Hdr_ExchSum04 money out,
  @Hdr_ExchSum05 money out,
  @Hdr_ExchSum06 money out,
  @Hdr_ExchSum07 money out,
  @Hdr_ExchSum08 money out, -- сумма НДС поставщика
  @Hdr_ExchSum09 money out, -- сумма поставщика с НДС
  @Hdr_ExchSum10 money out,
  @Hdr_ExchSum11 money out,
  @Hdr_ExchSum12 money out,
  @Hdr_Value2_01 decimal(10,2) out, 
  @Hdr_Value2_02 decimal(10,2) out, -- сумма к оплате с учетом тары и вспом.материалов
  @Hdr_Value2_03 decimal(10,2) out, 
  @Hdr_Value2_04 decimal(10,2) out, 
  @Hdr_Value2_05 decimal(10,2) out, -- ручная сумма поставщика
  @Hdr_Value2_06 decimal(10,2) out, -- суммы НДС пост.10%
  @Hdr_Value2_07 decimal(10,2) out, -- суммы НДС пост.20%
  @Hdr_Value2_08 decimal(10,2) out, -- суммы пост.10%
  @Hdr_Value2_09 decimal(10,2) out, -- суммы пост.20%
  @Hdr_Value2_10 decimal(10,2) out, 
  @Hdr_Value4_01 decimal(10,4) out, 
  @Hdr_Value4_02 decimal(10,4) out, 
  @Hdr_Value4_03 decimal(10,4) out, 
  @Hdr_Value4_04 decimal(10,4) out, 
  @Hdr_Value4_05 decimal(10,4) out, 
  @Hdr_Value4_06 decimal(10,4) out, 
  @Hdr_Value4_07 decimal(10,4) out, 
  @Hdr_Value4_08 decimal(10,4) out, 
  @Hdr_Value4_09 decimal(10,4) out, 
  @Hdr_Value4_10 decimal(10,4) out, -- Внутренний номер
  @Hdr_QntBoxAll int out -- кол-во по документу
AS
Declare

@Hdr_ExchSum08C		money,
@Hdr_Sum02C			money,
@Hdr_ExchSum09C		money,
@Date1				datetime,
@Date2				Datetime,
@Idn				int

/* Внутренняя нумерация по ОГФ */


declare 
@y int,
@m int,
@Num int,
@Last int

set @y = (select max(year(hdr_date)) from hdr where isnull(Hdr_Value4_10, 0) > 0 and dcmstate = 0 and prm_unicode =1)

set @m = (select max(month(hdr_date)) from hdr where isnull(Hdr_Value4_10, 0) > 0 and dcmstate = 0 and prm_unicode =1 and year(hdr_date) = @y)

set @num = (select max(Hdr_Value4_10)from hdr 
where year(hdr_date) = @y and month(hdr_date) = @m and isnull(hdr_Value4_10, 0) > 0 and dcmstate = 0) +1

set @Last = (select month(hdr_date)from hdr
             where hdr_idn = (select max(hdr_idn) from hdr
             where skd_unicode = 2))

if @m <> @last set @num = 1

set @Hdr_Value4_10 = @Num

end


return @@error




я хочу переделать проблемный кусок так: это мне поможет???

set @y = (select max(year(hdr_date)) from hdr where isnull(Hdr_Value4_10, 0) > 0 and dcmstate = 0 and prm_unicode =1 and skd_unicode = 2)

set @m = (select max(month(hdr_date)) from hdr where isnull(Hdr_Value4_10, 0) > 0 and dcmstate = 0 and prm_unicode =1 and year(hdr_date) = @y and skd_unicode = 2)

set @num = (select max(Hdr_Value4_10)from hdr 
where year(hdr_date) = @y and skd_unicode = 2 and month(hdr_date) = @m and isnull(hdr_Value4_10, 0) > 0 and dcmstate = 0) +1

set @Last = (select month(hdr_date)from hdr
             where hdr_idn = (select max(hdr_idn) from hdr
             where skd_unicode = 2))

if @m <> @last set @num = 1

set @Hdr_Value4_10 = @Num

end


Сообщение было отредактировано: 28 мар 13, 08:42
27 мар 13, 15:51    [14102644]     Ответить | Цитировать Сообщить модератору
 Re: оптимизировать запрос  [new]
johnjob
Member

Откуда:
Сообщений: 60
то есть добавить в условия выборок skd_unicode=2 дабы сузить пределы поиска
27 мар 13, 15:53    [14102663]     Ответить | Цитировать Сообщить модератору
 Re: оптимизировать запрос  [new]
PaulYoung
Member

Откуда: Москва
Сообщений: 2565
johnjob
нам же нужно выбрать данные, которые сопряжены со столбцом с параметром "skd_unicode" (то есть просмотреть даты только для документов касаемо склада №2) а не все сплошняком
смотрите планы внутрипроцедурных SELECT'ов, возможно станет ясно, какого индекса не хватает, а возможно и то, что нужно скрипт переписать...
27 мар 13, 16:02    [14102736]     Ответить | Цитировать Сообщить модератору
 Re: оптимизировать запрос  [new]
johnjob
Member

Откуда:
Сообщений: 60
в плане выполнения изначальная стоимость поддерева была равна 155, во втором случае - 59... что это значит, что в три раза запрос стал легче или я ошибаюсь? не смейтесь...это моя неосновная работа, как работать с планами и анализировать их ещё не разобрался...)
27 мар 13, 16:03    [14102738]     Ответить | Цитировать Сообщить модератору
 Re: оптимизировать запрос  [new]
johnjob
Member

Откуда:
Сообщений: 60
индексы добавлять нельзя т.к. структуру БД трогать нельзя.
27 мар 13, 16:04    [14102746]     Ответить | Цитировать Сообщить модератору
 Re: оптимизировать запрос  [new]
PaulYoung
Member

Откуда: Москва
Сообщений: 2565
johnjob
в плане выполнения изначальная стоимость поддерева была равна 155, во втором случае - 59... что это значит, что в три раза запрос стал легче или я ошибаюсь? не смейтесь...это моя неосновная работа, как работать с планами и анализировать их ещё не разобрался...)
смотрите, где там у вас table/index scan и стрелки пожирнее, как правило это и есть проблемные места
27 мар 13, 16:04    [14102749]     Ответить | Цитировать Сообщить модератору
 Re: оптимизировать запрос  [new]
johnjob
Member

Откуда:
Сообщений: 60
попробовал работу запроса второй вариант значительно быстрее. пока пойдёт. всем спасибо.
27 мар 13, 16:30    [14102931]     Ответить | Цитировать Сообщить модератору
 Re: оптимизировать запрос  [new]
hoha_ftf
Member

Откуда: ЕКБ
Сообщений: 42
johnjob,

а так?

select top 1 @y = year(hdr_date)
           , @m = month(hfr_date)
  from from hdr
  where hdr_Value4_10 > 0
    and dcmstate = 0
    and prm_unicode =1
    and skd_unicode = 2 
  order by hdr_date desc

select top 1 @Last = month(hdr_date)
  from hdr
  where skd_unicode = 2
  order by hdr_idn desc

if @m <> @last 
  set @num = 1
else
  select @num = max(Hdr_Value4_10) + 1
    from hdr
    where hdr_Value4_10 > 0
      and dcmstate = 0
      and skd_unicode = 2
      and hdr_date >= cast(@y as varchar(8)) + cast(@m   as varchar(8)) + '01'
      and hdr_date <  cast(@y as varchar(8)) + cast(@m+1 as varchar(8)) + '01'


set @Hdr_Value4_10 = @Num
28 мар 13, 08:11    [14105219]     Ответить | Цитировать Сообщить модератору
 Re: оптимизировать запрос  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
hoha_ftf
johnjob,

а так?

      and hdr_date >= cast(@y as varchar(8)) + cast(@m   as varchar(8)) + '01'
      and hdr_date <  cast(@y as varchar(8)) + cast(@m+1 as varchar(8)) + '01'


set @Hdr_Value4_10 = @Num

А если @m = 12 ?
28 мар 13, 11:24    [14106074]     Ответить | Цитировать Сообщить модератору
 Re: оптимизировать запрос  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
попробуйте как-нибудь так, хотя я не проверял. Попробуйте этот вариант или вариант hoha_ftf, только ошибки там поправьте.
;with lastop as(
select 
y.val y
,m.val m
,MB.val MB
,NextMB.val NextMB
,rnbydate = ROW_NUMBER() over(order by 
									case	when hdr_Value4_10 > 0
											and dcmstate = 0
											 and prm_unicode =1 
											 then 	hdr_date 
										else cast('17530101'as datetime) end	 desc)
,rnbyidn = ROW_NUMBER() over (order by hdr_idn desc)		
						
 
from	hdr
		cross apply(select year(hdr_date)) y(val)
		cross apply (select month(hdr_date))m(val)
		cross apply (select dateadd(M,DATEDIFF(M,0,hdr_date),0)) MB(val)
		cross apply (select dateadd(M,DATEDIFF(M,0,hdr_date)+1,0)) NextMB(val)
where skd_unicode = 2 		
	)
select 
@m= c1.m
,@y = c1.y
,@MB = c1.MB
,@NextMB = c1.NextMB
,@last = (select c2.m from lastop c2 where rnbyidn = 1 )
from lastop c1 
where rnbydate	=1

if @@ROWCOUNT<1
set @num = 1

if @m <> @last  or @last is null
  set @num = 1
else
  select @num = max(Hdr_Value4_10) + 1
    from hdr
    where hdr_Value4_10 > 0
      and dcmstate = 0
      and skd_unicode = 2
      and hdr_date >= @MB
      and hdr_date <  @NextMB


set @Hdr_Value4_10 = @Num
28 мар 13, 11:31    [14106129]     Ответить | Цитировать Сообщить модератору
 Re: оптимизировать запрос  [new]
hoha_ftf
Member

Откуда: ЕКБ
Сообщений: 42
Мистер Хенки,

да, погорячился немного
28 мар 13, 12:11    [14106368]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить