Bankir.Ru
6 декабря, вторник 20:58

Объявление

Свернуть
Пока нет объявлений.

Ф-ия PosList_Rest. Как ускорить ?

Свернуть
X
  • Фильтр
  • Время
  • Показать
Очистить всё
новые сообщения

  • Ф-ия PosList_Rest. Как ускорить ?

    Очень медленно работает PosList_Rest (по главе "Д" - депозитарий). Есть ли способы ускорить работу ?

  • #2
    RedPank
    Если используется like по dephash, после уже вызова poslist_rest, то немного ускорить можно методом уже когдато предлагавшемся на этом форуме:

    -- начитка остатков на счетах депо
    delete pResource where SPID = @@spid

    insert pResource (SPID, ResourceID)
    select distinct @@SPID, DepoID from #resource_depo

    exec PosList_Rest @DateReport, 1

    -- выполним неблагодарную работу - установим FundID (бумага)
    update pResList
    set FundID = t.ObjectID
    from tDepartment t (NOLOCK)
    where pResList.SPID = @@spid
    and t.ParentID = 7
    and pResList.DepHash like t.DepHash

    insert into #rest_security (DepoID, I_Quantity, O_Quantity, SecurityID)
    select
    DepoID = rl.ResourceID
    , I_Quantity = sum(-rl.Rest - rl.TurnCre + rl.TurnDeb)
    , O_Quantity = sum(-rl.Rest)
    , SecurityID = rl.FundID
    from pResList rl (nolock)
    where rl.SPID = @@spid
    group by rl.ResourceID, rl.FundID

    или вообще не пользоваться poslist_rest, а расчет вести в ручную собирая остатки из оборота (за закрытые дни) и из таблицы проводок за открытые.

    Комментарий


    • #3
      Ну предложенный совет тот-же самый LIKE - в результате которого и у меня тоже постоянно задница. Не говоря уже о всех отчетах ДЕПО, включая отчет брокера.

      А вообще выход в кардинальной отмене такой глупости как построчное сканирования

      И вообще сделать к tOperPart - tOperPart_Dep и без всяких дэпхашей. Все в индексах и все у меня к этому идет.
      С уважением, Максаев Андрей.

      Комментарий


      • #4
        andrey_seek
        И вообще сделать к tOperPart - tOperPart_Dep и без всяких дэпхашей. Все в индексах и все у меня к этому идет
        Для депозитарных проводок это лишнее. Там и так есть поле SecurityID.

        Комментарий


        • #5
          Но в pResList такого поля нет...

          А строка DepHash имеет фиксированную по позициям структуру или нет ?
          (Субконто для одного типа объектов стоят на одом и том-же месте)

          Возможно ли DepHash "распотрошить" на кусочки по разным полям, а потом искать в tDepartment не по Like а оператором "="

          update #AccRest
          set SecurityID = D.ObjectID
          from tDepartment D
          where
          #AccRest.DepHash7 = D.DepHash
          and D.SubcontoType = 7

          Где в #AccRest.DepHash7 это самое 7-ое субконто и лежит ...
          Последний раз редактировалось RedPank; 04.10.2002, 16:45.

          Комментарий


          • #6
            RedPank
            Но в pResList такого поля нет...
            разговор велся именно о tOperPart
            Возможно ли DepHash "распотрошить" на кусочки по разным полям, а потом искать в tDepartment не по Like а оператором "="
            В принципе substring-ами можно, но прироста скорости не получите это точно.

            Комментарий


            • #7
              2 kvit

              Почему не получиться ?
              Операция Like вызывает полное сканирование таблицы tDepartment. А операция "=" выполниться по индексу. (по DepHash в tDepartment индекс есть).

              Другое дело, что я сильно сомневаюсь (наткнулся в одном месте), что DepHash имеет фиксированную структуру для объектов одного типа. (Т.е. все субконто занимают постояное место в строке).

              Но даже если нельзя точно узнать какое субконто на каком месте находиться, то несколько операций сравнения соединенные OR все равно быстрее.
              (Насколько я разумею числовые коды в субконто в DepHash имеют уникальный номер)

              Тогда можно выпонить запрос

              update #AccRest
              set SecurityID = D.ObjectID
              from tDepartment D
              where #AccRest.DepHash1 = D.DepHash
              or #AccRest.DepHash2 = D.DepHash
              ....
              or #AccRest.DepHashN = D.DepHash
              and D.SubcontoType = 7 (или другой номер ...)

              где #AccRest.DepHashN = D.DepHash - Код субконто стоящий на N-ом месте.

              Комментарий


              • #8
                kvit

                Для депозитарных проводок это лишнее. Там и так есть поле SecurityID
                Иногда этого маловато. Нужно и место хранения и ЦБ и тип счета и т.д.

                Распотрошить на кусочки этого нет. Мало того - есть неписанная беда - депхэши должны быть в строке в порядке возрастания. Иначе PosList_Rest - вернет несколько строк содержащих один и тот же набор субконт. (Например DepHash = ';1057;;5002346;' и ';5002346;;1057;' и '; 1057;;5002346;'. Все это будет каждой строкой.
                Еще раз о DepHash - нельзя зажиматься на место в строке. Там числа в порядке возрастания.


                Предлагаю следующее:

                drop proc psb_PosList_Rest
                print 'create proc psb_PosList_Rest'
                go

                create proc psb_PosList_Rest
                @Date smalldatetime,
                @TurnCalc tinyint = NULL,
                @DepHash varchar(120) = NULL,
                @Confirmed tinyint = NULL,
                @DateStart smalldatetime = NULL,
                @LastDateCalc tinyint = NULL,
                @CalcType tinyint = NULL,

                @TestMode int = 0
                as
                declare @RetVal int

                if @TestMode>0 select getdate() 'Вызов PosList_Rest'

                exec @RetVal = PosList_Rest
                @Date = @Date,
                @TurnCalc = @TurnCalc,
                @DepHash = @DepHash,
                @Confirmed = @Confirmed,
                @DateStart = @DateStart,
                @LastDateCalc = @LastDateCalc,
                @CalcType = @CalcType

                if @RetVal = 0 begin
                if @TestMode>0 select getdate() 'Вызов psb_PosList_DepPrepare'
                exec @RetVal = psb_PosList_DepPrepare @TestMode = @TestMode
                end

                go

                grant exec on psb_PosList_Rest to dia_public
                go



                drop table psb_pDepHash
                print 'create table psb_pDepHash'
                go

                create table psb_pDepHash (
                SPID int default @@spid,
                DepHashIn DSHASH,
                DepHash DSHASH,
                DepMask varchar(25) default ''
                primary key (SPID, DepHash)
                )
                create index i1 on psb_pDepHash (SPID, DepMask, DepHash)
                go
                grant all on psb_pDepHash to dia_public
                go






                drop table psb_pResList_Dep
                print 'create table psb_pResList_Dep'
                go

                create table psb_pResList_Dep (
                SPID int default @@spid,
                ResourceID numeric(15),
                DepHash DSHASH,
                DepartmentID numeric(15) default 0,
                SubcontoType int default 0

                primary key (SPID, ResourceID, DepHash, DepartmentID)
                )
                go
                grant all on psb_pResList_Dep to dia_public
                go






                drop proc psb_PosList_DepPrepare
                print 'create proc psb_PosList_DepPrepare'
                go

                create proc psb_PosList_DepPrepare
                @TestMode int = 0
                as

                set nocount on

                delete from psb_pResList_Dep where SPID=@@spid
                delete from psb_pDepHash where SPID=@@spid

                insert into psb_pDepHash (DepHashIn, DepHash, DepMask)
                select distinct DepHash, DepHash, ''
                from pResList
                where SPID=@@spid
                and DepHash > ''


                while exists (Select 1 from psb_pDepHash where SPID=@@spid) begin -- цикл по срезам

                if @TestMode>0 select count(*) from psb_pDepHash where SPID=@@spid

                update p set -- Проставляем DepMask
                DepMask = '%'+ltrim(rtrim(substring(DepHashIn,1,charindex(';',DepHashIn,2))))+'%',
                DepHashIn = substring(DepHashIn,charindex(';',DepHashIn,2)+1,120)
                from psb_pDepHash p (NOLOCK)
                where SPID=@@spid

                delete from psb_pDepHash
                where SPID=@@spid
                and DepMask = '%%'

                -- if @TestMode>0 select DepMask, * from psb_pDepHash where SPID=@@spid


                insert into psb_pResList_Dep (SPID, ResourceID, DepHash, DepartmentID, SubcontoType)
                select @@spid, p.ResourceID, p.DepHash, d.DepartmentID, d.SubcontoType
                from psb_pDepHash ph (NOLOCK)
                join pResList p (NOLOCK)
                on p.SPID=@@spid and p.DepHash = ph.DepHash
                join tDepartment d (NOLOCK INDEX=XIE2tDepartment)
                on d.DepHash = ph.DepMask
                where ph.SPID=@@spid

                delete from psb_pDepHash
                where SPID=@@spid
                and DepHashIn = ''


                -- Зачищаем DepHashIn
                update ph set
                DepMask = ''
                from psb_pDepHash ph
                where ph.SPID=@@spid

                end
                if @@error>0 return @@error

                if @TestMode>0 select getdate() 'Ставим SecurityID'
                update p set
                p.SecurityID = d.ObjectID
                from pResList p
                join psb_pResList_Dep pd (NOLOCK)
                on pd.SPID=@@spid and pd.ResourceID = p.ResourceID and pd.DepHash = p.DepHash and pd.SubcontoType = 7
                join tDepartment d (NOLOCK)
                on d.DepartmentID = pd.DepartmentID and d.SubcontoType = pd.SubcontoType
                where p.SPID=@@spid

                if @TestMode>0 select getdate() 'Ставим Портфель DepartmentID'
                update p set
                p.DepartmentID = pd.DepartmentID
                from pResList p
                join psb_pResList_Dep pd (NOLOCK)
                on pd.SPID=@@spid and pd.ResourceID = p.ResourceID and pd.DepHash = p.DepHash and pd.SubcontoType = 1
                where p.SPID=@@spid

                return 0

                go
                grant exec on psb_PosList_DepPrepare to dia_public
                go


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

                В результате в psb_pResList_Dep имеем весь набор субконт. Со связью на pResList - SPID, ResourceID, DepHash. А в pResList - проставленные SecurityID, DepartmentID.

                К сожалению все заточено под MS SQL и использование роли - Dia_Public

                На остатках в 3000 записей - у меня этот скрипт отрабатывает около 30-45 сек. В то время как like - приводит к TimeOut (выдержка установлена в 10 мин)
                С уважением, Максаев Андрей.

                Комментарий


                • #9
                  Ну с Dia_Public можно разобраться, а кроме MS SQL у нас ничего нет.

                  P.S. Спасибо за текст. Разберусь "на досуге".

                  Комментарий


                  • #10
                    Ускорение поразительное....

                    P.S.
                    Про ф-ию PosList_Rest.
                    Как мне кажется ... многократный вызов ф-ии PosList_Rest в одном отчете сам по себе тормозит серврер.

                    Комментарий


                    • #11
                      Вот чем сердце успокоилось ...


                      [SQL]
                      @SQL@=SQL{
                      -- Парамеиры ф-ии PosList_Rest
                      Declare
                      @Date smalldatetime,
                      @PrvDate smalldatetime

                      Declare
                      @DepHash char(255),
                      @DepHashSave char(255)

                      -- Период расчета
                      Declare
                      @BeginDate smalldatetime,
                      @EndDate smalldatetime,
                      @AccNum varchar(25),
                      @nDays numeric(15,0) -- Количество дней в периоде
                      -- @Recalc int -- 0 = FALSE - Не пересчитывать отчет заново
                      -- 1 = TRUE - Пересчитать отчет
                      Select
                      @BeginDate = %date1!,
                      @EndDate = %date2!
                      -- ,@Recalc %RecalcFlag!
                      -- @BeginDate = '20020901',
                      -- @EndDate = '20020930'

                      Select
                      @nDays = DateDiff(day, @BeginDate, @EndDate) + 1
                      Select @Date = @BeginDate

                      -- Временная табллица для хранения остатков по лицевым счетам за каждый день периода
                      CREATE TABLE #AcRest
                      ( Date smalldatetime -- Дата остатка
                      ,ResourceID numeric(15,0) -- Раздел ДЕПО
                      ,SecurityID numeric(15,0) -- Ценная бумага
                      ,ActResourceID numeric(15,0) -- Место хранения
                      ,DepHash char(255) --
                      ,Rest money null -- Исходящий остаток
                      )

                      -- Временная таблица для хранения средних остатков
                      CREATE TABLE #AvgRest
                      ( ResourceID numeric(15,0) -- Раздел ДЕПО
                      ,SecurityID numeric(15,0) -- Ценная бумага
                      ,ActResourceID numeric(15,0) -- Место хранения
                      ,Rest money null -- Средний остаток
                      ,DepHash char(255) -- субконо
                      )

                      -- Временная таблица для ускорения работы с DepHash
                      DECLARE @tDepHash TABLE
                      ( ObjectID numeric(15),
                      SubcontoType int default 0,
                      DepHash char(255)
                      ,Primary Key (DepHash, SubcontoType)
                      )

                      -- Очищаем служебные таблицы
                      delete from pResource where SPID=@@SPID
                      delete from pResList where SPID=@@SPID

                      delete from NM_ActRest
                      --TRUNCATE TABLE NM_ActRest
                      -- Формируем список счетов

                      insert into pResource(SPID, ResourceID)
                      select @@spid, A.ResourceID
                      from tDepartment D, tAccDivType AD, tAccPosition AP, Account A
                      where D.SubcontoType in (23,100)
                      and D.ObjectID = AD.AccDivTypeID
                      and AD.BalFlag = 1
                      and AP.DepartmentID = D.DepartmentID
                      and AP.ResourceID = A.ResourceID
                      and A.CharType = 2

                      exec PosList_Rest @Date

                      insert NM_ActRest ([Date], ResourceID, SecurityID, ActResourceID, DepHash, Rest)
                      select @Date, p.ResourceID, 0, 0, p.DepHash,
                      Abs(Sum(p.Rest))
                      from pResList p
                      where p.SPID = @@spid
                      and p.Date = @Date
                      and Rest > 0
                      group by p.ResourceID, p.DepHash

                      select @PrvDate = @Date
                      select @Date = DATEADD(day, 1, @Date)

                      -- Цикл по датам
                      WHILE @Date = @EndDate
                      BEGIN
                      -- print Convert(varchar, getdate()) + ' [' + Convert(varchar, @Date ) + ']'
                      -- Расчет оборотов по дням

                      insert NM_ActRest ([Date], ResourceID, SecurityID, ActResourceID, DepHash, Rest)
                      select @Date, ResourceID, 0, 0, DepHash, Sum(Rest)
                      from (
                      select ResourceID, DepHash, Rest
                      from NM_ActRest
                      where [Date] = @PrvDate AND Rest > 0

                      union all

                      select ResourceID, DepHash,
                      (CASE CharType
                      WHEN 1 THEN Sum(Qty) * (-1) -- Дебетовая часть (Подобная схема - только для пассивных счетов)
                      WHEN -1 THEN Sum(Qty)
                      END) Rest
                      from tOperPart
                      where InstitutionID = 2000 -- По НОМОСу
                      and OperDate = @Date -- За дату
                      and Confirmed = 1 -- Только фактические проводки
                      and BalanceID = 10055017386 -- Глава учета "Д"
                      Group By ResourceID, DepHash, CharType

                      ) R
                      group by ResourceID, DepHash

                      select @PrvDate = @Date
                      select @Date = DATEADD(day, 1, @Date)

                      END

                      insert into #AcRest select * from NM_ActRest N where N.[Date] Between @BeginDate and @EndDate

                      -- Считаем средние остатки
                      insert #AvgRest (ResourceID, SecurityID, ActResourceID, Rest, DepHash)
                      select A.ResourceID, Max(A.SecurityID), 0, Sum(A.Rest)/@nDays, A.DepHash
                      from #AcRest A
                      group by A.ResourceID, A.DepHash


                      -- Разбираемся с DepHash
                      DECLARE crHash CURSOR FAST_FORWARD FOR
                      SELECT DISTINCT DepHash
                      FROM #AvgRest

                      OPEN crHash
                      FETCH crHash INTO @DepHash
                      WHILE (@@FETCH_STATUS = 0)
                      Begin
                      Select @DepHashSave = @DepHash

                      WHILE(@DepHash > ';')
                      Begin
                      Insert Into @tDepHash(ObjectID, SubcontoType, DepHash)

                      Select D.ObjectID, D.SubcontoType, @DepHashSave
                      From tDepartment D
                      where D.DepHash = char(37) + SubString(@DepHash, 1, CharIndex(';', @DepHash, 2)) + char(37)
                      AND Not Exists( select * from @tDepHash DH where DH.DepHash = @DepHashSave AND DH.SubcontoType = D.SubcontoType )

                      Select @DepHash = SubString(@DepHash, Len(SubString(@DepHash, 2, CharIndex(';', @DepHash, 2)))+1, 255)
                      End

                      FETCH NEXT FROM crHash INTO @DepHash
                      end

                      CLOSE crHash
                      DEALLOCATE crHash

                      -- Бумага
                      Update #AvgRest
                      Set SecurityID = D.ObjectID
                      From @tDepHash D
                      Where #AvgRest.DepHash = D.DepHash
                      and D.SubcontoType = 7

                      -- Место хранения
                      Update #AvgRest
                      Set ActResourceID = D.ObjectID
                      From @tDepHash D
                      Where #AvgRest.DepHash = D.DepHash
                      and D.SubcontoType = 12



                      SELECT AnA.Brief AS AccNum, -- Счет Депо
                      AnA.Name AS AccName, -- Наименование счета Депо
                      A.Brief AS RazdNum, -- Номер Раздела
                      A.Name AS RazdName, -- Наименование Раздела
                      S.Name AS SecName, -- ЦБ
                      S.RegNumber AS RegNumber, -- Рег.номер
                      Iss.Name AS Issuer, -- Эмитент
                      #AvgRest.Rest AS Rest, -- Среднее кол-во
                      SK.Name AS SecKind, -- Тип ЦБ
                      D.Brief AS SynAcc, -- Синтетический счет
                      S.Nominal AS nominal, -- Номинал
                      Act.Name AS ActName, -- Место хранения
                      @BeginDate AS D1,
                      @EndDate AS D2


                      FROM #AvgRest
                      INNER JOIN Account A ON #AvgRest.ResourceID = A.ResourceID
                      INNER JOIN tSecurity S ON #AvgRest.SecurityID = S.SecurityID
                      INNER JOIN tResource Act ON #AvgRest.ActResourceID = Act.ResourceID
                      INNER JOIN tResource AnA ON A.ParentID = AnA.ResourceID
                      INNER JOIN tAccPosition Ap ON A.ResourceID = Ap.ResourceID
                      INNER JOIN tSecKind SK ON S.Category = SK.SecKindID
                      INNER JOIN tDepartment D ON Ap.DepartmentID = D.DepartmentID
                      INNER JOIN tInstitution Iss ON S.InstitutionID = Iss.InstitutionID


                      WHERE D.SubcontoType in (5,121)
                      ORDER BY AnA.Brief, A.Brief, S.[Name]


                      -- Очищаем служебные таблицы
                      delete from pResource where SPID=@@SPID
                      delete from pResList where SPID=@@SPID

                      delete from NM_ActRest

                      drop table #AcRest
                      drop table #AvgRest


                      }

                      [Field]
                      @PS@=PrinterSetup{LANDSCAPE, , , , ,Courier New,8,Bold,CYRILLIC} -- Принтер
                      @AFC@ = AfterFileClose{WinExec("StartEXEL.bat",," avg_lim.xls",,""), @s1t}

                      @Date1...@=Field{D1,@d2} -- Период расчета
                      @Date2...@=Field{D2,@d2}

                      @AccNum............@ = Field{AccNum, @s20} -- Счет Депо
                      @AccName......................@ = Field{AccName, @s255t} -- Наименование счета Депо
                      @RazdNum...........@ = Field{RazdNum, @s20} -- Номер Раздела
                      @RazdName.....................@ = Field{RazdName, @s30} -- Наименование Раздела
                      @SecName......................@ = Field{SecName, @s30} -- ЦБ
                      @RegNumber.........@ = Field{RegNumber, @s20} -- Рег.номер
                      @Issuer.......................@ = Field{Issuer, @s30} -- Эмитент
                      @Rest..........@ = Field{Rest, @n_15.4} -- Среднее кол-во
                      @SecKind@ = Field{SecKind, @s60t} -- Тип ЦБ
                      @SynAcc@ = Field{SynAcc, @s25t} -- Синтетические счета
                      @money@ = Field{nominal, @n_15.4} -- Номинал
                      @ActName@ = Field{ActName, @s120t} -- Место хранения



                      &&
                      #@SQL@
                      #@PS@
                      #@AFC@
                      Расчет средних остатков на счетах Депо
                      за период с @Date1...@ по @Date2...@
                      Счет Депо;Наименование счета Депо;Номер Раздела; Наименование Раздела;ЦБ;Рег.номер;Эмитент;Среднее кол-во;Тип ЦБ;Синтетический Счет;Номинал;Место хранения
                      $T
                      @AccNum............@;@AccName......................@;@RazdNum...........@;@RazdName.....................@;@SecName......................@;@RegNumber.........@;@Issuer.......................@;@Rest..........@;@SecKind@;@SynAcc@;@money@;@ActName@
                      $



                      &&

                      Комментарий

                      Пользователи, просматривающие эту тему

                      Свернуть

                      Присутствует 1. Участников: 0, гостей: 1.

                      Обработка...
                      X