Меню
Новые сообщения [новые:0]
Дайджест
Горячие темы
Избранное [новые:0]
Форумы
Пользователи
Статистика
Статистика нагрузки
Мод. лог
Поиск
|
Действия ...
Форумы
[новые:0]
/ Sybase ASA, ASE, IQ
[новые:0]
[игнор отключен]
[закрыт для гостей]
/
Как бороться с блокировками в ASE 12.5.0.3
[новые:0]
/
25 сообщений из 42, страница 1 из 2
|
13.05.2004, 17:56
|
|||
|---|---|---|---|
Как бороться с блокировками в ASE 12.5.0.3 |
|||
|
#18+
Стабильная ситуация всех заблокировал 29 две минуты все отдыхают. 29 ничего сложного не делает, он запустил процедуру и должен модифицировать несколько записей в разных таблицах. Отдельно такая процедура выполняется моментально, но как видно не всегда. Как бороться с блокировками? На сервере кроме ASE ничего нет. Все пользовательские обращения к базе через хранимые процедуры. В процедурах частенько используется CREATE TABLE #name_table( ... Система: Microsoft Windows Server 2003 Сервер: Xeon(TM) CPU 2.80 ГГц, ОЗУ 2.00 ГБ Размер базы данных: ak_2004 - 2 GB = (1 GB data + 1 GB Log) Data Spase Usage Data (17.4%) 173.54 MB Indexes (12.8%) 128.43 MB Unused (4.9%) 49.39 MB Unreserved (64.9%) 648.65 MB Options: Check - Allow select into/bulk copy Check - Checkpoint on recovery Check - Free space accounting dump transaction akv_2004 to @path -- каждые 20 минут. Размер tempdb 603 Mb = (3Mb Data and Log + 300 Data + 300 Log) Options: Check - Allow select into/bulk copy Check - Truncate log on checkpoint Check - Checkpoint on recovery Check - Free space accounting SELECT @@version Adaptive Server Enterprise/12.5.0.3/EBF 11449 ESD#4/P/NT (IX86)/OS 4.0/rel12503/1939/32-bit/OPT/Sat Sep 20 22:28:57 2003 sp_cacheconfig 'default data cache' default data cache Active Default 700.00 Mb 700.00 Mb sp_configure 'procedure cache size' procedure cache size 3271 67488 32000 32000 memory pages(2k) dynamic sp_configure "max memory" max memory 33792 1536000 768000 768000 memory pages(2k) dynamic sp_configure "total logical memory" total logical memory 33792 852942 426460 426471 memory pages(2k) read-only sp_configure "total physical memory" total physical memory 0 852920 0 426460 memory pages(2k) read-only sp_who spid..status...........loginame...blk_spid....dbname.....cmd 17....recv sleep.....zaitsev........0.........ak_2004......AWAITING COMMAND 19....lock sleep......maxdrouj...29........ak_2004......DROP TABLE 21....recv sleep.....arapov........0.........ak_2004......AWAITING COMMAND 29....sleeping........chehova......0.........tempdb.......CREATE TABLE 31....running.........sa..............0.........master........SELECT 36....recv sleep.....zaitsev........0........ak_2004.......AWAITING COMMAND 38....lock sleep.....antonov.....29.........ak_2004.......DROP TABLE 40....recv sleep.....grigo...........0........ak_2004.......AWAITING COMMAND 43....lock sleep.....tihonovp....29.........tempdb........CREATE TABLE 44....recv sleep.....piskunov....0..........ak_2004.......AWAITING COMMAND 45....recv sleep.....topchiev....0..........ak_2004.......AWAITING COMMAND 53....lock sleep.....okureva.....29.........tempdb........CREATE TABLE 54....recv sleep.....fedchko.....0..........ak_2004.......AWAITING COMMAND 57....recv sleep.....sheky........0..........ak_2004.......AWAITING COMMAND 62....recv sleep.....lugaay.......0..........ak_2004.......AWAITING COMMAND 64....lock sleep.....beljva........29.........ak_2004.......DROP TABLE Как бороться с блокировками? ... |
|||
|
:
Нравится:
Не нравится:
|
|||
|
|
|
13.05.2004, 18:30
|
|||
|---|---|---|---|
|
|||
Как бороться с блокировками в ASE 12.5.0.3 |
|||
|
#18+
Скорее всего блокируются служебные таблицы в tempdb. На каком-то форуме надыбал полезную ХП. Выполни в БД sybsystemproc. Простой sp_lock непонятные данные дает, а этот вроде ничего. Выполни в момент блокировки sp_dba_locks и посмотри, какие таблицы заблокированы. /*************************************************/ create proc sp_dba_locks @spid int = null as select l.fid, l.spid, loginame=convert(char(12), suser_name(sp1.suid)), locktype = v1.name, "table" = object_name(l.id,l.dbid), l.page, dbname = db_name(l.dbid), l.class, context=v2.name from master..syslocks l, master..spt_values v1, master..spt_values v2, master..sysprocesses sp1 where l.type = v1.number and v1.type = "L" and (l.context+2048) = v2.number and v2.type = "L2" and (l.spid = @spid or l.fid = @spid or @spid is null) and sp1.spid=l.spid order by l.fid, l.spid, l.dbname, l.id, locktype, l.page /*************************************************/ Могу порекомендовать во всех ХП поставить create table #<name> в начало процедуры, а drop table #<name> в самый конец ХП. Еще можно посоветовать использовать не create table... insert, а select into и insert into. Для временных таблиц - самое оно. Выполняется быстрее, чем create table-insert. ... |
|||
|
:
Нравится:
Не нравится:
|
|||
|
|
|
13.05.2004, 18:40
|
|||
|---|---|---|---|
Как бороться с блокировками в ASE 12.5.0.3 |
|||
|
#18+
Код: plaintext блокируются, а что с ними делать? Код: plaintext Код: plaintext ... |
|||
|
:
Нравится:
Не нравится:
|
|||
|
|
|
13.05.2004, 20:41
|
|||
|---|---|---|---|
Как бороться с блокировками в ASE 12.5.0.3 |
|||
|
#18+
Можно изменить уровень блокировки для пользовательских таблиц, перестроить индексы (например, у тебя кластерный индекс по identity полю, блокируется последняя страница, с которой все работают и т.д.) Конкретики не хватает. Владимир ... |
|||
|
:
Нравится:
Не нравится:
|
|||
|
|
|
13.05.2004, 20:57
|
|||
|---|---|---|---|
Как бороться с блокировками в ASE 12.5.0.3 |
|||
|
#18+
Уровень блокировки я менял, это приводит к ошибкам логики :( А конкретнее - всегда вешается на операции CREATE TABLE в tempdb это видно по sh_who Все остальные случаи проходят на ура, значит где-то здесь нужно капать.... ... |
|||
|
:
Нравится:
Не нравится:
|
|||
|
|
|
13.05.2004, 21:11
|
|||
|---|---|---|---|
Как бороться с блокировками в ASE 12.5.0.3 |
|||
|
#18+
У меня было подобное, правда на ASA. В итоге победил подправив клиентскую программу на посылку commit после завершения ХП. Никаких изменений в базе эти ХП у меня не делали, просто сложные выборки через временные таблицы. Кстати, может помочь и вставка коммита в конец самой процедуры, после drop table #t. Третий метод борьбы - клиентская программа может работать в режиме connect->one_request->disconnect :) ... |
|||
|
:
Нравится:
Не нравится:
|
|||
|
|
|
14.05.2004, 10:29
|
|||
|---|---|---|---|
|
|||
Как бороться с блокировками в ASE 12.5.0.3 |
|||
|
#18+
Код: plaintext 1. 2. Лучше все-таки посмотри, что блокируется, потому, как 29 у тебя создает таблицу в tempdb, а блокируются также и те, кто делает что-то в ak_2004. Если блокировки идут из-за тр. лога, то придется менять на select into, т.к. она в тр. лог не пишется. Если все-таки блокируются системные таблицы (sysobjects, например), то скорее всего придется переписывать ХП. Вообще-то, лучше покажи ХП, которую выполняет 29, и рез-т работы sp_dba_locks в момент работы ХП. Как вариант можно поиграться параметрами ASE, в названии которых присутствует lock или spinlock. Но при этом _обязательно_ читать administrator guide и perfomance and tuning guide по значениям этих параметров. Иначе можно такого наизменять... 8))) Код: plaintext Мне, например, помогло изменение уровня блокировок на некоторых (на наиболее используемых, а их процентов 30-40) таблицах с datapages на datarow. Правда, пришлось увеличивать число блокировок и поковырять некоторые параметры, ответственные за блокировки, но жить стало легче. Вот еще что - у тебя create table часом не внутри транзакции выполняется? ... |
|||
|
:
Нравится:
Не нравится:
|
|||
|
|
|
14.05.2004, 10:42
|
|||
|---|---|---|---|
Как бороться с блокировками в ASE 12.5.0.3 |
|||
|
#18+
В принципе, можно сделать разделяемую псевдовременную таблицу в tempdb(и поместить её в model, чтобы она пересоздавалась при restart'е). Пользователей разделять по host_id (например сделать view которая будет показывать данные только текущей сессии). Тогда блокировки системных таблиц не будет, но нужно будет править код. Запусти sp_lock или более user friendly аналог во время блокировки - тогда можно будет говорить конкретнее. 2 Mladov Alexey: А зачем серверу блокировать журнал транзакций? Владимир ... |
|||
|
:
Нравится:
Не нравится:
|
|||
|
|
|
14.05.2004, 10:46
|
|||
|---|---|---|---|
Как бороться с блокировками в ASE 12.5.0.3 |
|||
|
#18+
Код: plaintext процедуры, но мне кажется дело не в этом. Код: plaintext Блокируются те кто делает CREATE TABLE #<...> или DROP TABLE #<...> Это временные таблицы и как я понимаю создаются они на tempdb, а удаляется, почему-то, на ak_2004. ??? Кроме временных таблиц пользователи таблицы не создают и не удаляют, а блокируется именно эта операция, значит тормозит работу создание или удалением временных таблиц. 64....lock sleep.....beljva........29.........ak_2004....... DROP TABLE Как с этим бороться? или я не там копаю... ... |
|||
|
:
Нравится:
Не нравится:
|
|||
|
|
|
14.05.2004, 13:45
|
|||
|---|---|---|---|
|
|||
Как бороться с блокировками в ASE 12.5.0.3 |
|||
|
#18+
2 immutable: А как же иначе??? А если _одновременно_ пишется лог нескольких пользователей? Насколько мне известно (может меня поправят, если я неправ) в ASE работа с тр. логом организована примерно так: каждый пользователь (коннект) имеет свой лог-буфер; его размер по умолчанию 512 байт(если не путаю); Все изменения в транзакции накапливаются в этом буфере; после заполнения буфера он сбрасывается на диск (т.е. вносятся изменения в последнюю страницу тр. лога), в момент записи происходит блокировка последней страницы тр. лога, после записи блокировка снимается. Если лог-буфер маленький (размер средней транзакции больше буфера), то сброс буфера на диск происходит часто, что при активности пользователей ведет к постоянным блокировкам последней страницы тр. лога и высокой конкуренции за доступ к ней. Чтобы этого избежать, надо увеличить размер лог-буфера (параметр user log cache size). Максимальный размер лог-буфера, который был запрошен, можно посмотреть, запустив sp_sysmon <интервал>, где <интервал> - интервал времени в формате 'hh:mm:ss', за который собирать статистику (минут на 5-10 в момент пиковых нагрузок). Если макс. размер user log cache равен установленному, то надо увеличить user log cache size. Затем посмотреть снова. У меня user log cache size равен 8192, а максимальный размер по данным sp_sysmon 7 тыс с небольшим. 2 Badger Ты все-таки помотри, какие таблицы лочатся. И покажи ХП. Не всю (если большая или засекреченная), а хотя бы основной алгоритм - begin tran? create table, drop table... Еще раз повторюсь: у тебя create table часом не внутри транзакции выполняется? ... |
|||
|
:
Нравится:
Не нравится:
|
|||
|
|
|
14.05.2004, 14:00
|
|||
|---|---|---|---|
Как бороться с блокировками в ASE 12.5.0.3 |
|||
|
#18+
На время записи буфера это понятно, но не на несколько минут. Владимир ... |
|||
|
:
Нравится:
Не нравится:
|
|||
|
|
|
14.05.2004, 17:21
|
|||
|---|---|---|---|
Как бороться с блокировками в ASE 12.5.0.3 |
|||
|
#18+
По многочисленным просьбам привожу текст процедуры, которая всех залочила, и список заблокированных таблиц. Процедура "ask_f_deal" запускается всеми пользователями чаще других она хорошо отлажена, в части производительности, и работает быстро. Но один два раза в час происходит подвешивание всего сервера на 2-3 минуты, что ни есть хорошо..... sp_who go fid.....spid.....status.....loginame.....origname.....hostname.....blk_spid.....dbname.....cmd.....block_xloid 0.....2.....sleeping.........[NULL].....[NULL]....................0.........master.....NETWORK.HANDLER......0 0.....3.....sleeping.........[NULL].....[NULL]....................0.........master.....NETWORK.HANDLER......0 0.....4.....sleeping.........[NULL].....[NULL]....................0.........master.....DEADLOCK.TUNE........0 0.....5.....sleeping.........[NULL].....[NULL]....................0.........master.....MIRROR.HANDLER.......0 0.....6.....sleeping.........[NULL].....[NULL]....................0.........master.....SHUTDOWN.HANDLER.....0 0.....7.....sleeping.........[NULL].....[NULL]....................0.........master.....ASTC.HANDLER.........0 0.....8.....sleeping.........[NULL].....[NULL]....................0.........tempdb.....CHECKPOINT.SLEEP.....0 0.....9.....sleeping.........[NULL].....[NULL]....................0.........master.....HK.WASH..............0 0.....10.....sleeping.........[NULL].....[NULL]....................0.........master.....HK.GC................0 0.....11.....sleeping.........[NULL].....[NULL]....................0.........master.....HK.CHORES............0 0.....12.....sleeping.........[NULL].....[NULL]....................0.........master.....DTC.COMMIT.SVC.......0 0.....18.....sleeping.........beljkova.....beljkova.....BELJKOVA.......0.........tempdb.....CREATE.TABLE.........0 0.....19.....recv.sleep.......zaitsev.....zaitsev.....ZOLOTAREV......0.........akv_2004.....AWAITING.COMMAND.....0 0.....23.....recv.sleep.......chehova.....chehova.....CONVERSBAN.....0.........akv_2004.....AWAITING.COMMAND.....0 0.....25.....recv.sleep.......shekersky.....shekersky.....SHEKERSKY2.....0.........akv_2004.....AWAITING.COMMAND.....0 0.....30.....recv.sleep.......topchiev.....topchiev.....TOPCHIEV.......0.........akv_2004.....AWAITING.COMMAND.....0 0.....34.....lock.sleep.......zaitsev.....zaitsev.....ZOLOTAREV......18........tempdb.....CREATE.TABLE.........0 0.....36.....lock.sleep.......sa.....sa.....DRUJININ.......18........akv_2004.....EXECUTE..............0 0.....37.....recv.sleep.......luganskay.....luganskay.....CONVERSBAN.....0.........akv_2004.....AWAITING.COMMAND.....0 0.....42.....recv.sleep.......abramova.....abramova.....CONVERSBAN.....0.........akv_2004.....AWAITING.COMMAND.....0 0.....46.....lock.sleep.......fedchenko.....fedchenko.....CONVERSBAN.....18........tempdb.....CREATE.TABLE.........0 0.....48.....lock.sleep.......maxdrouj.....maxdrouj.....DRUJININ.......18........tempdb.....CREATE.TABLE.........0 0.....49.....lock.sleep.......antonov.....antonov.....ANTONOV......18........tempdb.....CREATE.TABLE.........0 0.....53.....lock.sleep.......tihonovp.....tihonovp.....TIHONOVP.......18........tempdb.....CREATE.TABLE.........0 0.....57.....recv.sleep.......sa.....sa.....MANDRAKE.......0.........master.....AWAITING.COMMAND.....0 0.....61.....running..........sa.....sa.....ANTONOV_A......0.........akv_2004.....SELECT...............0 0.....63.....recv.sleep.......levina.....levina.....LEVINA.........0.........akv_2004.....AWAITING.COMMAND.....0 ............................................. sp_dba_locks go fid.....spid.....loginame.....locktype.....table.....page.....dbname.....class.....context..... 0.....18.....beljkova.....Ex_page.....sysobjects.....3.....tempdb.....Non.Cursor.Lock.....Fam.dur,.Inf.key..... 0.....18.....beljkova.....Ex_page.....sysobjects.....104.....tempdb.....Non.Cursor.Lock.....Fam.dur,.Inf.key..... 0.....18.....beljkova.....Ex_table.....sysobjects.....0.....tempdb.....Non.Cursor.Lock.....LOCK.CONTEXT.VALUES..... 0.....18.....beljkova.....Ex_page.....sysindexes.....29.....tempdb.....Non.Cursor.Lock.....Fam.dur,.Inf.key..... 0.....18.....beljkova.....Ex_table.....sysindexes.....0.....tempdb.....Non.Cursor.Lock.....LOCK.CONTEXT.VALUES..... 0.....18.....beljkova.....Ex_page.....syscolumns.....40.....tempdb.....Non.Cursor.Lock.....Fam.dur,.Inf.key..... 0.....18.....beljkova.....Ex_page.....syscolumns.....323.....tempdb.....Non.Cursor.Lock.....Fam.dur,.Inf.key..... 0.....18.....beljkova.....Ex_page.....syscolumns.....324.....tempdb.....Non.Cursor.Lock.....LOCK.CONTEXT.VALUES..... 0.....18.....beljkova.....Ex_page.....syscolumns.....325.....tempdb.....Non.Cursor.Lock.....LOCK.CONTEXT.VALUES..... 0.....18.....beljkova.....Ex_page.....syscolumns.....326.....tempdb.....Non.Cursor.Lock.....LOCK.CONTEXT.VALUES..... 0.....18.....beljkova.....Ex_table-blk.....syscolumns.....0.....tempdb.....Non.Cursor.Lock.....LOCK.CONTEXT.VALUES..... 0.....18.....beljkova.....Sh_table.....systypes.....0.....tempdb.....Non.Cursor.Lock.....LOCK.CONTEXT.VALUES..... 0.....18.....beljkova.....Ex_page.....sysprocedures.....121.....tempdb.....Non.Cursor.Lock.....Fam.dur,.Inf.key..... 0.....18.....beljkova.....Ex_page.....sysprocedures.....129.....tempdb.....Non.Cursor.Lock.....Fam.dur,.Inf.key..... 0.....18.....beljkova.....Ex_page.....sysprocedures.....130.....tempdb.....Non.Cursor.Lock.....LOCK.CONTEXT.VALUES..... 0.....18.....beljkova.....Ex_table.....sysprocedures.....0.....tempdb.....Non.Cursor.Lock.....LOCK.CONTEXT.VALUES..... 0.....18.....beljkova.....Ex_page.....syscomments.....145.....tempdb.....Non.Cursor.Lock.....Fam.dur,.Inf.key..... 0.....18.....beljkova.....Ex_table-blk.....syscomments.....0.....tempdb.....Non.Cursor.Lock.....LOCK.CONTEXT.VALUES..... 0.....18.....beljkova.....Ex_intent.....sysdepends.....0.....tempdb.....Non.Cursor.Lock.....LOCK.CONTEXT.VALUES..... 0.....18.....beljkova.....Ex_intent.....systabstats.....0.....tempdb.....Non.Cursor.Lock.....LOCK.CONTEXT.VALUES..... 0.....18.....beljkova.....Ex_row.....systabstats.....443.....tempdb.....Non.Cursor.Lock.....LOCK.CONTEXT.VALUES..... 0.....18.....beljkova.....Ex_table.....#f_deal_temp_01000180010283263.....0.....tempdb.....Non.Cursor.Lock.....LOCK.CONTEXT.VALUES..... 0.....18.....beljkova.....Ex_table....._f_deal_te_f_clie_729280942.....0.....tempdb.....Non.Cursor.Lock.....LOCK.CONTEXT.VALUES..... 0.....36.....sa.....Sh_intent.....systabstats.....0.....akv_2004.....Non.Cursor.Lock.....LOCK.CONTEXT.VALUES..... 0.....36.....sa.....Sh_intent.....sysstatistics.....0.....akv_2004.....Non.Cursor.Lock.....LOCK.CONTEXT.VALUES..... 0.....61.....sa.....Sh_intent.....spt_values.....0.....master.....Non.Cursor.Lock.....LOCK.CONTEXT.VALUES..... CREATE.PROCEDURE.dbo.ask_f_deal;1 ( ....@c_membertype_1.........int.........=.NULL, ....@c_membertype_2.........int.........=.NULL, ....@c_membertype_3.........int.........=.NULL, ....@c_membertype_4.........int.........=.NULL, ....@c_inout_1..............int.........=.NULL, ....@c_inout_2..............int.........=.NULL, ....@c_inout_3..............int.........=.NULL, ....@c_sumkind..............int.........=.NULL, ....@f_deal.................int.........=.NULL, ....@dtmake_1...............datetime....=.NULL, ....@dtmake_2...............datetime....=.NULL, ....@date_time_11...........datetime....=.NULL, ....@date_time_12...........datetime....=.NULL, ....@date_time_21...........datetime....=.NULL, ....@date_time_22...........datetime....=.NULL, ....@date_time_31...........datetime....=.NULL, ....@date_time_32...........datetime....=.NULL, ....@date_time_41...........datetime....=.NULL, ....@date_time_42...........datetime....=.NULL, ....@date_time_51...........datetime....=.NULL, ....@date_time_52...........datetime....=.NULL, ....@check_c_classificator..int.........=.NULL, ....@check_c_market.........int.........=.NULL, ....@check_f_client_1.......int.........=.NULL, ....@check_f_client_2.......int.........=.NULL, ....@check_f_instr..........int.........=.NULL, ....@check_f_portf..........int.........=.NULL, ....@check_l_currencyuser...int.........=.NULL, ....@c_market...............int.........=.NULL, ....@c_dealstatus...........int.........=.NULL, ....@f_user.................int.........=.NULL, ....@f_client12.............int.........=.NULL, ....@c_clienttype...........int.........=.NULL, ....@broker.................int.........=.NULL, ....@svich..................int.........=.NULL, ....@commission.............int.........=.NULL, ....@f_client_4.............int.........=.NULL, ....@f_client_3.............int.........=.NULL, ....@check_date_1or2........int.........=.NULL, ....@date_time_1or2.........datetime....=.NULL, ....@c_currency_1...........int.........=.NULL, ....@c_currency_2...........int.........=.NULL, ....@c_currency_12..........int.........=.NULL, ....@number.................varchar(20).=.NULL, ....@f_instr................int.........=.NULL, ....@c_order................int.........=.NULL, ....@f_client_2.............int.........=.NULL, ....@finans.................int.........=.NULL, ....@check_f_mean...........int.........=.NULL, ....@c_jurtype..............int.........=.NULL, ....@c_settype..............int.........=.NULL, ....@c_class_cancel.........int.........=.NULL, ....@c_clientkind...........int.........=.NULL, ....@c_clientgroup..........int.........=.NULL, ....@check_srok.............int.........=.NULL, ....@srok...................int.........=.NULL, ....@c_market_mbk...........int.........=.NULL, ....@c_market_forex.........int.........=.NULL, ....@c_market_netting.......int.........=.NULL, ....@c_market_journal.......int.........=.NULL, ....@c_market_pay...........int.........=.NULL, ....@c_market_note..........int.........=.NULL, ....@c_currency_national....int.........=.NULL, ....@c_dogtype_100..........int.........=.NULL, ....@c_dogtype_200..........int.........=.NULL, ....@zero_portf.............int.........=.NULL ) AS BEGIN /************************************************************************\ *...Процедура:..ask_f_deal * *....Описание:..Получение.записей.из.таблицы.f_deal.(реестры.сделок) * *.....История:.16.07.2003 -.ООО.'Аквилон' * *...Аргументы:..@c_membertype_1.........-.тип.участника:.инициатор *...............@c_membertype_2.........-.тип.участника:.контрагент *...............@c_membertype_3.........-.тип.участника:.свич *...............@c_membertype_4.........-.тип.участника:.брокер *...............@c_inout_1..............-.номер.суммы.1 *...............@c_inout_2..............-.номер.суммы.2 *...............@c_inout_3..............-.номер.суммы.3 *...............@c_sumkind..............-.положительная.сумма *...............@f_deal.................-.сделка *...............@dtmake_1...............-.начальная.дата.создания *...............@dtmake_2...............-.конечная.дата.создания *...............@date_time_11...........-.начальная.дата.валютирования.1 *...............@date_time_12...........-.конечная.дата.валютирования.1 *...............@date_time_21...........-.начальная.дата.валютирования.2 *...............@date_time_22...........-.конечная.дата.валютирования.2 *...............@date_time_31...........-.начальная.дата.договора.3 *...............@date_time_32...........-.конечная.дата.договора.3 *...............@date_time_41...........-.Фактическая.поставка.ЦБ.с.4 *...............@date_time_42...........-.Фактическая.поставка.ЦБ.по.4 *...............@date_time_51...........-.Фактическая.оплата.ЦБ.с.5 *...............@date_time_52...........-.Фактическая.оплата.ЦБ.по.5 *...............@check_c_classificator..-.запрос.по.набору.типов.сделок *...............@check_c_market.........-.запрос.по.набору.рвнков *...............@check_f_client_1.......-.запрос.по.набору.инициаторов *...............@check_f_client_2.......-.запрос.по.набору.контрагентов *...............@check_f_instr..........-.запрос.по.набору.инструментов *...............@check_l_currencyuser...-.запрос.по.набору.пар.валют *...............@c_market...............-.рынок *...............@c_dealstatus...........-.статус.сделки *...............@f_user.................-.пользователь *...............@f_client12.............-.участник *...............@c_clienttype...........-.тип.контрагента *...............@broker.................-.брокер.Да/Нет *...............@svich..................-.свич.Да/Нет *...............@commission.............-.Тип.отправленного.сообщения *...............@f_client_4.............-.брокер.(контрагент) *...............@f_client_3.............-.свич.(контрагент) *...............@check_date_1or2........-.запрос.по.дате.валютирования.1,2 *...............@date_time_1or2.........-.дата.валютирования.1,2 *...............@c_currency_1...........-.валюта.1 *...............@c_currency_2...........-.валюта.2 *...............@c_currency_12..........-.валюта.1,2 *...............@number.................-.номер.сделки.или.идентификатор *...............@f_instr................-.инструмент *...............@c_order................-.условия.расчетов *...............@finans.................-.предоплата *...............@check_f_mean...........-.запрос.по.набору.значений.категорий *...............@c_jurtype..............-.тип.связи *...............@c_settype..............-.набор *...............@c_clientkind...........-.вид.клиента *...............@c_clientgroup..........-.группа.клиента *...............@check_srok.............-.запрос.по.сроку.(знак) *...............@srok...................-.срок.(число) *...............@c_market_mbk...........-.Рынок.МБК *...............@c_market_forex.........-.Рынок.Форекс *...............@c_market_netting.......-.Рынок.Неттинг *...............@c_market_journal.......-.Рынок.Журнал *...............@c_market_pay...........-.Рынок.Платеж *...............@c_market_note..........-.Рынок.ЦБ *...............@c_currency_national....-.Национальная.валюта *...............@c_dogtype_100..........-.договор.по.сделке *...............@c_dogtype_200..........-.заявка.на.сделку * *..Возвращает:..f_deal..............-.идентификатор.сделки *...............f_deal_reference....-.идентификатор.связанной.сделки *...............c_jurtype...........-.тип.связи *...............c_market............-.рынок *...............c_classificator.....-.классификатор *...............c_class_name........-.название.классификатора *...............c_dealstatus........-.статус.сделки *...............f_path..............-.путь.сделки *...............number..............-.номер.сделки *...............number_jur..........-.номер.сделки.журнал *...............descr...............-.примечание *...............rollover............-.флаг.ролловера *...............comission...........-.флаг.комиссии.для.брокера *-----.f_sum.-.первая.сумма.сделки.-------------------------------------------------- *...............f_sum_1.............-.идентификатор *...............f_sumtype_1............-.тип.суммы *...............c_currency_1........-.валюта *...............currency_iso_1......-.код.валюты *...............date_time_1.........-.дата.платежа *...............value_1.................-.сумма *-----.f_member.-.первый.участник.сделки.-------------------------------------------- *...............f_member_1..........-.идентификатор *...............c_membertype_1......-.тип.участника *...............f_client_1..........-.контрагент *...............code_1..............-.код *...............rtscode_1...........-.код.ММВБ *...............rsbcode_1...........-.код.в.Автоматизированной.Банковской.Системе *...............firstname_1.........-.название *...............f_portf_1...........-.портфель *...............f_portf_code_1......-.название.портфеля *-----.f_sum.-.вторая.сумма.сделки.-------------------------------------------------- *...............f_sum_2.............-.идентификатор *...............f_sumtype_2.........-.тип.суммы *...............c_currency_2........-.валюта *...............currency_iso_2......-.код.валюты *...............date_time_2.........-.дата.платежа *...............value_2.............-.сумма *-----.f_member.-.второй.участник.сделки.-------------------------------------------- *...............f_member_2..........-.идентификатор *...............c_membertype_2......-.тип.участника *...............f_client_2..........-.контрагент *...............code_2..............-.код *...............rtscode_2...........-.код.ММВБ *...............rsbcode_2...........-.код.в.Автоматизированной.Банковской.Системе *...............firstname_2.........-.название *...............f_portf_2...........-.портфель *...............f_portf_code_2......-.название.портфеля *-----.f_sum.-.третья.сумма.сделки.-------------------------------------------------- *...............f_sum_3.............-.идентификатор *...............f_sumtype_3.........-.тип.суммы *...............c_currency_3........-.валюта *...............currency_iso_3......-.код.валюты *...............date_time_3.........-.дата.платежа *...............value_3.............-.сумма *-----.f_member.-.третий.участник.сделки.-------------------------------------------- *...............f_member_3..........-.идентификатор *...............c_membertype_3......-.тип.участника *...............f_client_3..........-.контрагент *...............code_3..............-.код *...............rtscode_3...........-.код.ММВБ *...............rsbcode_3...........-.код.в.Автоматизированной.Банковской.Системе *...............firstname_3.........-.название.свича.(контрагента) *-----.f_member.-.четвертый.участник.сделки.----------------------------------------- *...............f_member_4..........-.идентификатор *...............c_membertype_4......-.тип.участника *...............f_client_4..........-.контрагент *...............code_4..............-.код *...............rtscode_4...........-.код.ММВБ *...............rsbcode_4...........-.код.в.Автоматизированной.Банковской.Системе *...............firstname_4.........-.название.брокера.(посредника) *-----.ввод.сделки.----------------------------------------------------------------- *...............f_user_1............-.идентификатор.пользователя *...............usermake_1..........-.имя.пользователя *...............userdescr_1.........-.ФИО.пользователя *...............dt_make_1...........-.дата.ввода.сделки *-----.последняя.модификация.сделки.------------------------------------------------ *...............f_user_2............-.идентификатор.пользователя *...............usermodify_2........-.имя.пользователя *...............userdescr_2.........-.ФИО.пользователя *...............dt_modify_2.........-.дата.модификации *-----.f_rate.-.курс/ставка.-------------------------------------------------------- *...............f_rate..............-.идентификатор.ставки.сделки *...............rate................-.курс/ставка.сделки *...............real_rate...........-.курс/ставка.сделки *...............revers..............-.флаг.обратного.курса *...............finans..............-.флаг.предоплаты *-----.дополнительные.поля.--------------------------------------------------------- *...............market_name.........-.название.рынка *...............c_opertype..........-.типы.переходов *...............c_metod.............-.методы.начисления *...............c_sumkind...........-.положительная.сумма *-----.корр.счета.------------------------------------------------------------------ *...............f_account_1.........-.счет.для.1-й.суммы *...............f_account_2.........-.счет.для.2-й.суммы *...............f_account_3.........-.счет.для.3-й.суммы *-----.позиции.--------------------------------------------------------------------- *...............f_pos_11............-.идентификатор *...............f_pos_12............-.идентификатор *...............f_pos_13............-.идентификатор *...............f_pos_21............-.идентификатор *...............f_pos_22............-.идентификатор *...............f_pos_23............-.идентификатор *-----.неттинг/rollover.------------------------------------------------------------ *...............netting_value_1.....-.включена.в.неттинг.1-я.сумма *...............netting_value_2.....-.включена.в.неттинг.2-я.сумма *...............rollover_value_1....-.включена.в.rollover.1-я.сумма *...............rollover_value_2....-.включена.в.rollover.1-я.сумма *-----.журналы.--------------------------------------------------------------------- *...............journal_l_jurnal....-.идентификатор.связи.с.журналом *...............journal_f_deal......-.сделка.журнала *...............journal_f_d_f_deal..-.сделка,.вошедшая.в.журнал *...............journal_add.........-.флаг=1,.если.сделка.вошла.в.журнал *...............f_instr_1...........-.инструмент *...............f_issue_1...........-.выпуск *...............f_instr_code_1......-.код.инструмента.и.выпуска *...............do_dognum_1.........-.номер.договора *...............do_date_time_1......-.дата.договора *...............do_dogform_name_1...-.форма.договора *...............do_descr_1..........-.описание.договора *...............do_dognum_2.........-.номер.заявки *...............do_date_time_2......-.дата.заявки *...............do_dogform_name_2...-.форма.заявки *...............do_descr_2..........-.описание.заявки *...............rate_pay............-.курс.расчетов *...............st_date_pay_1.......-.дата.расчетов *...............st_date_begin_1.....-.дата.начала.векселя.(не.используется) *...............st_date_end_1.......-.дата.погашения.векселя.(не.используется) *...............fs_date_pay_1.......-.дата.поставки *...............f_pricetype.........-.тип.цены *...............f_pricetype_name....-.название.типа.цены *...............f_client12..........-.запрос.по.участнику *...............c_currency_12.......-.запрос.по.валюте.1,2 *...............rate_pay............-.курс.расчетов *...............selectrow...........-.выделение.строки * \************************************************************************/ ....DECLARE.@selectrow..........int ....DECLARE.@row_count..........int ....DECLARE.@f_user_id..........int ....DECLARE.@c_sumkind_minus....int ....DECLARE.@c_usergroup........int ....DECLARE.@c_category.........int ....DECLARE.@f_mean.............int ....DECLARE.@f_client...........int ....DECLARE.@number_test........char(20) ....DECLARE.@number_acc.........char(20) ....DECLARE.@f_account..........int ....DECLARE.@c_settype_13.......int ....DECLARE.@c_sumstock_1.......int ....DECLARE.@c_sumstock_2.......int ....DECLARE.@c_sumstock_3.......int ....DECLARE.@c_order_1..........int ....DECLARE.@c_order_2..........int ....DECLARE.@c_order_3..........int ....DECLARE.@c_market_stock.....int ....DECLARE.@c_market_bond......int ....DECLARE.@c_market_bill......int ....DECLARE.@c_market_depo_bond.int ....DECLARE.@c_market_depo_bill.int ....DECLARE.@c_market_depo_pay..int ....DECLARE.@c_conftype_53......int ....DECLARE.@c_conftype_54......int ..../*.Глобальные.константы.*/ ....SELECT.@c_sumkind_minus.....=.2 ....SELECT.@c_settype_13........=.13 ....SELECT.@c_order_1...........=.1 ....SELECT.@c_order_2...........=.2 ....SELECT.@c_order_3...........=.3 ....SELECT.@c_sumstock_1........=.1 ....SELECT.@c_sumstock_2........=.2 ....SELECT.@c_sumstock_3........=.3 ....SELECT.@c_market_stock......=.6 ....SELECT.@c_market_bond.......=.7 ....SELECT.@c_market_bill.......=.8 ....SELECT.@c_market_depo_bond..=.10 ....SELECT.@c_market_depo_bill..=.11 ....SELECT.@c_market_depo_pay...=.12 ....SELECT.@c_conftype_53.......=.53 ....SELECT.@c_conftype_54.......=.54 /*-----.f_user_id.-.пользователь.и.его.группа.-------------------------------------*/ ....SELECT ............@f_user_id......=.f_user, ............@c_usergroup....=.c_usergroup, ............@f_client.......=.f_client ......FROM ............f_user .....WHERE ............name.=.suser_name() /*--.Временные.таблицы.-------------------------------------------------------------*/ ....CREATE.TABLE.#f_deal_account ....( ........f_deal..............int.............NULL ) ..../*.Создание.временной.таблицы.сделок.*/ ....CREATE.TABLE.#f_deal_temp ....( /*-----f_deal.-.сделка.-------------------------------------------------------------*/ ........f_deal..............int.............NULL, ........f_deal_reference....int.............NULL, ........c_jurtype...........int.............NULL, ........c_market............int.............NULL, ........c_classificator.....int.............NULL, ........c_classif_name......varchar(30).....NULL, ........c_dealstatus........int.............NULL, ........f_path..............int.............NULL, ........number..............varchar(20).....NULL, ........number_jur..........varchar(20).....NULL, ........descr...............varchar(100)....NULL, ........rollover............int.............NULL, ........commission..........int.............NULL, /*-----f_sum.-.первая.сумма.сделки.-------------------------------------------------*/ ........f_sum_1.............int.............NULL, ........f_sumtype_1.........int.............NULL, ........c_currency_1........int.............NULL, ........currency_iso_1......varchar(5)......NULL, ........date_time_1.........datetime........NULL, ........st_date_pay_1.......datetime........NULL, ........value_1.............decimal(18,6)...NULL, /*-----f_member.-.первый.участник.сделки.-------------------------------------------*/ ........f_member_1..........int.............NULL, ........c_membertype_1......int.............NULL, ........f_client_1..........int.............NULL, ........c_clienttype_1......int.............NULL, ........code_1..............varchar(6)......NULL, ........rtscode_1...........varchar(6)......NULL, ........rsbcode_1...........varchar(6)......NULL, ........firstname_1.........varchar(20).....NULL, ........f_portf_1...........int.............NULL, ........f_portf_code_1......varchar(6)......NULL, /*-----f_sum.-.вторая.сумма.сделки.-------------------------------------------------*/ ........f_sum_2.............int.............NULL, ........f_sumtype_2.........int.............NULL, ........c_currency_2........int.............NULL, ........currency_iso_2......varchar(5)......NULL, ........date_time_2.........datetime........NULL, ........value_2.............decimal(18,6)...NULL, /*-----f_member.-.второй.участник.сделки.-------------------------------------------*/ ........f_member_2..........int.............NULL, ........c_membertype_2......int.............NULL, ........f_client_2..........int.............NULL, ........c_clienttype_2......int.............NULL, ........c_clientgroup_2.....int.............NULL, ........code_2..............varchar(6)......NULL, ........rtscode_2...........varchar(6)......NULL, ........rsbcode_2...........varchar(6)......NULL, ........firstname_2.........varchar(20).....NULL, ........f_portf_2...........int.............NULL, ........f_portf_code_2......varchar(6)......NULL, /*-----f_sum.-.третья.сумма.сделки.-------------------------------------------------*/ ........f_sum_3.............int.............NULL, ........f_sumtype_3.........int.............NULL, ........c_currency_3........int.............NULL, ........currency_iso_3......varchar(5)......NULL, ........date_time_3.........datetime........NULL, ........value_3.............decimal(18,6)...NULL, /*-----f_member.-.третий.участник.сделки.-------------------------------------------*/ ........f_member_3..........int.............NULL, ........c_membertype_3......int.............NULL, ........f_client_3..........int.............NULL, ........c_clienttype_3......int.............NULL, ........code_3..............varchar(6)......NULL, ........rtscode_3...........varchar(6)......NULL, ........rsbcode_3...........varchar(6)......NULL, ........firstname_3.........varchar(20).....NULL, /*-----f_member.-.четвертый.участник.сделки.-------------------------------------------*/ ........f_member_4..........int.............NULL, ........c_membertype_4......int.............NULL, ........f_client_4..........int.............NULL, ........c_clienttype_4......int.............NULL, ........code_4..............varchar(6)......NULL, ........rtscode_4...........varchar(6)......NULL, ........rsbcode_4...........varchar(6)......NULL, ........firstname_4.........varchar(20).....NULL, /*-----создал.----------------------------------------------------------------------*/ ........f_user_1............int.............NULL, ........usermake_1..........varchar(30).....NULL, ........userdescr_1.........varchar(100)....NULL, ........dt_make_1...........datetime........NULL, /*-----последние.изтенения.сделал.--------------------------------------------------*/ ........f_user_2............int.............NULL, ........usermodify_2........varchar(30).....NULL, ........userdescr_2.........varchar(100)....NULL, ........dt_modify_2.........datetime........NULL, /*-----дополнительные.поля.---------------------------------------------------------*/ ........c_currency_sum......int.............NULL, ........f_rate..............int.............NULL, ........rate................decimal(18,6)...NULL, ........real_rate...........decimal(18,6)...NULL, ........revers..............int.............NULL, ........finans..............int.............NULL, ........market_name.........varchar(30).....NULL, ........c_opertype..........int.............NULL, ........c_metod.............int.............NULL, ........c_sumkind...........int.............NULL, ........f_account_1.........int.............NULL, ........f_account_2.........int.............NULL, ........f_account_3.........int.............NULL, ........f_pos_11............int.............NULL, ........f_pos_12............int.............NULL, ........f_pos_13............int.............NULL, ........f_pos_21............int.............NULL, ........f_pos_22............int.............NULL, ........f_pos_23............int.............NULL, /*...не.возвращаемые.поля............*/ ........netting_value_1.....int.............NULL, ........netting_value_2.....int.............NULL, ........rollover_value_1....int.............NULL, ........rollover_value_2....int.............NULL, ........journal_l_jurnal....int.............NULL, ........journal_f_deal......int.............NULL, ........journal_f_d_f_deal..int.............NULL, ........journal_add.........int.............NULL, ........f_mean..............int.............NULL, ........selectrow...........int.............NULL, ........textcolor...........int.............NULL, ........c_membertype........int.............NULL, ........srok................int.............NULL, ........fx_dt_make..........datetime........NULL, ........fx_max_date.........datetime........NULL, /*-----ценные.бумаги.--------------------------------------------------*/ ........f_instr_1...........int.............NULL, ........f_issue_1...........int.............NULL, ........f_instr_code_1......varchar(15).....NULL, ........f_term..............int.............NULL, ........c_order.............int.............NULL, ........do_dognum_1.........varchar(20).....NULL, ........do_date_time_1......datetime........NULL, ........do_c_dogform_1......int.............NULL, ........do_dogform_name_1...varchar(20).....NULL, ........do_descr_1..........varchar(100)....NULL, ........do_dognum_2.........varchar(20).....NULL, ........do_date_time_2......datetime........NULL, ........do_c_dogform_2......int.............NULL, ........do_dogform_name_2...varchar(20).....NULL, ........do_descr_2..........varchar(100)....NULL, ........st_date_begin_1.....datetime........NULL, ........st_date_end_1.......datetime........NULL, ........fs_date_pay_1.......datetime........NULL, ........f_pricetype.........int.............NULL, ........f_pricetype_name....varchar(3)......NULL, ........rate_pay............decimal(18,6)...NULL, ........date_pay............datetime........NULL, ........date_delivery.......datetime........NULL, ........date_min............datetime........NULL, ........fp_client_1.........int.............NULL, ........fp_client_2.........int.............NULL, ........f_client12..........int.............default.0, ........swift_telex.........int.............default.3 ....) ..../*.Проверка.параметров.*/ ....If..@dtmake_1...........IS.NULL.OR ........@dtmake_2...........IS.NULL.OR ........@c_membertype_1.....IS.NULL.OR ........@c_membertype_2.....IS.NULL.OR ........@c_membertype_3.....IS.NULL.OR ........@c_membertype_4.....IS.NULL.OR ........@c_sumkind..........IS.NULL ....BEGIN ........Goto.err ....END ..../*.Увеличиваем.дату.на.один.день.*/ ....SELECT..@dtmake_2.=.DATEADD(DAY,.1,.@dtmake_2) /*----------------------------------------------------------------------------------*/ /*-----.ПОЛУЧЕНИЕ.ВСЕХ.ВОЗМОЖНЫХ.СДЕЛОК.(разными.способами)-------------------------*/ /*----------------------------------------------------------------------------------*/ ........IF.@check_date_1or2.>.0 ........BEGIN ............IF.@check_date_1or2.=.1...../*.=.*/ ............BEGIN ................INSERT.INTO.#f_deal_temp ................( ........................f_deal, ........................c_market, ........................c_classificator, ........................c_dealstatus, ........................f_path, ........................f_user_1, ........................number, ........................descr, ........................dt_make_1, ........................fx_dt_make, ........................usermodify_2, ........................dt_modify_2 ................) ................SELECT.DISTINCT ........................fd.f_deal, ........................fd.c_market, ........................fd.c_classificator, ........................fd.c_dealstatus, ........................fd.f_path, ........................fd.f_user, ........................fd.number, ........................fd.descr, ........................fd.dt_make, ........................fd.dt_make, ........................fd.user_modify, ........................fd.dt_modify ..................FROM ........................f_sum.....fs, ........................f_sumtype.fst, ........................f_deal....fd .................WHERE ........................fs.date_time.=.@date_time_1or2.............AND ........................fs.f_sumtype.=.fst.f_sumtype...............AND ........................fst.c_inout..IN.(.@c_inout_1,.@c_inout_2.).AND ........................fs.f_deal....=.fd.f_deal...................AND ........................fd.dt_make..>=.@dtmake_1...................AND ........................fd.dt_make..<..@dtmake_2 ............END ............ELSE.IF.@check_date_1or2.=.2...../*.>.*/ ............BEGIN ................INSERT.INTO.#f_deal_temp ................( ........................f_deal, ........................c_market, ........................c_classificator, ........................c_dealstatus, ........................f_path, ........................f_user_1, ........................number, ........................descr, ........................dt_make_1, ........................fx_dt_make, ........................usermodify_2, ........................dt_modify_2 ................) ................SELECT.DISTINCT ........................fd.f_deal, ........................fd.c_market, ........................fd.c_classificator, ........................fd.c_dealstatus, ........................fd.f_path, ........................fd.f_user, ........................fd.number, ........................fd.descr, ........................fd.dt_make, ........................fd.dt_make, ........................fd.user_modify, ........................fd.dt_modify ..................FROM ........................f_sum.....fs, ........................f_sumtype.fst, ........................f_deal....fd .................WHERE ........................fs.date_time.>.@date_time_1or2.............AND ........................fs.f_sumtype.=.fst.f_sumtype...............AND ........................fst.c_inout..IN.(.@c_inout_1,.@c_inout_2.).AND ........................fs.f_deal....=.fd.f_deal...................AND ........................fd.dt_make..>=.@dtmake_1...................AND ........................fd.dt_make..<..@dtmake_2 ............END ............ELSE.IF.@check_date_1or2.=.3...../*.<.*/ ............BEGIN ................INSERT.INTO.#f_deal_temp ................( ........................f_deal, ........................c_market, ........................c_classificator, ........................c_dealstatus, ........................f_path, ........................f_user_1, ........................number, ........................descr, ........................dt_make_1, ........................fx_dt_make, ........................usermodify_2, ........................dt_modify_2 ................) ................SELECT.DISTINCT ........................fd.f_deal, ........................fd.c_market, ........................fd.c_classificator, ........................fd.c_dealstatus, ........................fd.f_path, ........................fd.f_user, ........................fd.number, ........................fd.descr, ........................fd.dt_make, ........................fd.dt_make, ........................fd.user_modify, ........................fd.dt_modify ..................FROM ........................f_sum.....fs, ........................f_sumtype.fst, ........................f_deal....fd .................WHERE ........................fs.date_time.<.@date_time_1or2.............AND ........................fs.f_sumtype.=.fst.f_sumtype...............AND ........................fst.c_inout..IN.(.@c_inout_1,.@c_inout_2.).AND ........................fs.f_deal....=.fd.f_deal...................AND ........................fd.dt_make..>=.@dtmake_1...................AND ........................fd.dt_make..<..@dtmake_2 ............END ........END ........ELSE.IF.@f_client12.IS.NOT.NULL BEGIN ................INSERT.INTO.#f_deal_temp ................( ........................f_deal, ........................c_market, ........................c_classificator, ........................c_dealstatus, ........................f_path, ........................f_user_1, ........................number, ........................descr, ........................dt_make_1, ........................fx_dt_make, ........................usermodify_2, ........................dt_modify_2 ................) ................SELECT.DISTINCT ........................fd.f_deal, ........................fd.c_market, ........................fd.c_classificator, ........................fd.c_dealstatus, ........................fd.f_path, ........................fd.f_user, ........................fd.number, ........................fd.descr, ........................fd.dt_make, ........................fd.dt_make, ........................fd.user_modify, ........................fd.dt_modify ..................FROM ........................f_member..fm, ........................f_deal....fd .................WHERE ........................fm.f_client.=.@f_client12..................AND ........................fm.c_membertype.IN.(.@c_membertype_1,.@c_membertype_2.).AND ........................fm.f_deal....=.fd.f_deal...................AND ........................fd.dt_make..>=.@dtmake_1...................AND ........................fd.dt_make..<..@dtmake_2 ........END ........ELSE.IF.@f_instr.IS.NOT.NULL BEGIN ................INSERT.INTO.#f_deal_temp ................( ........................f_deal, ........................c_market, ........................c_classificator, ........................c_dealstatus, ........................f_path, ........................f_user_1, ........................number, ........................descr, ........................dt_make_1, ........................fx_dt_make, ........................usermodify_2, ........................dt_modify_2 ................) ................SELECT.DISTINCT ........................fd.f_deal, ........................fd.c_market, ........................fd.c_classificator, ........................fd.c_dealstatus, ........................fd.f_path, ........................fd.f_user, ........................fd.number, ........................fd.descr, ........................fd.dt_make, ........................fd.dt_make, ........................fd.user_modify, ........................fd.dt_modify ..................FROM ........................f_stock...fs, ........................f_deal....fd .................WHERE ........................fs.f_deal....=.fd.f_deal...................AND ........................fs.f_instr...=.@f_instr....................AND ........................fd.dt_make..>=.@dtmake_1...................AND ........................fd.dt_make..<..@dtmake_2 ........END ........ELSE.IF.@date_time_11.IS.NOT.NULL.OR.@date_time_12.IS.NOT.NULL BEGIN ................INSERT.INTO.#f_deal_temp ................( ........................f_deal, ........................c_market, ........................c_classificator, ........................c_dealstatus, ........................f_path, ........................f_user_1, ........................number, ........................descr, ........................dt_make_1, ........................fx_dt_make, ........................usermodify_2, ........................dt_modify_2 ................) ................SELECT.DISTINCT ........................fd.f_deal, ........................fd.c_market, ........................fd.c_classificator, ........................fd.c_dealstatus, ........................fd.f_path, ........................fd.f_user, ........................fd.number, ........................fd.descr, ........................fd.dt_make, ........................fd.dt_make, ........................fd.user_modify, ........................fd.dt_modify ..................FROM ........................f_sum.....fs, ........................f_sumtype.st, ........................f_deal....fd .................WHERE ........................fd.dt_make......>=.@dtmake_1................AND ........................fd.dt_make......<..@dtmake_2................AND ........................fd.f_deal.......=..fs.f_deal................AND ........................fd.c_market.....=..st.c_market..............AND ........................fs.f_sumtype....=..st.f_sumtype.............AND ........................st.c_inout......=..@c_inout_1...............AND ........................(.fs.date_time..>=.@date_time_11....OR.@date_time_11.is.null.)..AND ........................(.fs.date_time..<=.@date_time_12....OR.@date_time_12.is.null.) ........END ........ELSE.IF.@date_time_21.IS.NOT.NULL.OR.@date_time_22.IS.NOT.NULL BEGIN ................INSERT.INTO.#f_deal_temp ................( ........................f_deal, ........................c_market, ........................c_classificator, ........................c_dealstatus, ........................f_path, ........................f_user_1, ........................number, ........................descr, ........................dt_make_1, ........................fx_dt_make, ........................usermodify_2, ........................dt_modify_2 ................) ................SELECT.DISTINCT ........................fd.f_deal, ........................fd.c_market, ........................fd.c_classificator, ........................fd.c_dealstatus, ........................fd.f_path, ........................fd.f_user, ........................fd.number, ........................fd.descr, ........................fd.dt_make, ........................fd.dt_make, ........................fd.user_modify, ........................fd.dt_modify ..................FROM ........................f_sum.....fs, ........................f_sumtype.st, ........................f_deal....fd .................WHERE ........................fd.dt_make......>=.@dtmake_1................AND ........................fd.dt_make......<..@dtmake_2................AND ........................fd.f_deal.......=..fs.f_deal................AND ........................fd.c_market.....=..st.c_market..............AND ........................fs.f_sumtype....=..st.f_sumtype.............AND ........................st.c_inout......=..@c_inout_2...............AND ........................(.fs.date_time..>=.@date_time_21....OR.@date_time_21.is.null.)..AND ........................(.fs.date_time..<=.@date_time_22....OR.@date_time_22.is.null.) ........END ........ELSE ........BEGIN ............INSERT.INTO.#f_deal_temp ............( ....................f_deal, ....................c_market, ....................c_classificator, ....................c_dealstatus, ....................f_path, ....................f_user_1, ....................number, ....................descr, ....................dt_make_1, ....................fx_dt_make, ....................usermodify_2, ....................dt_modify_2 ............) ............SELECT ....................fd.f_deal, ....................fd.c_market, ....................fd.c_classificator, ....................fd.c_dealstatus, ....................fd.f_path, ....................fd.f_user, ....................fd.number, ....................fd.descr, ....................fd.dt_make, ....................fd.dt_make, ....................fd.user_modify, ....................fd.dt_modify ..............FROM ....................f_deal..........fd .............WHERE ....................fd.dt_make......>=.@dtmake_1................AND ....................fd.dt_make......<..@dtmake_2 ..........ORDER.BY ....................fd.f_deal ........END /*-----.номер.сделки.или.идентификатор.или.счет.------------------------------------*/ ....IF.DATALENGTH(RTRIM.(@number)).>.1 ....BEGIN ........SELECT..@number_test.=.SUBSTRING(@number,1,DATALENGTH(@number).-.2) ........SELECT..@number_acc..=.SUBSTRING(@number,1,DATALENGTH(@number).-.3) ......../*.Проверка.на.наличие.окончание.'id'.-.начало.соответствует.индентификатору.сделки.*/ ........IF..NOT.PATINDEX("%id",@number.).=.0.and ............NOT.(.@number_test.LIKE.'%[:-я]%'.OR.@number_test.LIKE.'%[!-/]%'.) ........BEGIN ............SELECT.@f_deal.=.CONVERT(int,.SUBSTRING(@number,1,DATALENGTH(@number).-.2)), ...................@number.=.NULL IF.@f_deal.>.0 ............BEGIN ................DELETE.FROM.#f_deal_temp ............END ........END ......../*.Проверка.на.окончание.'acc'.начало.соответствует.счету.в.сделке.*/ ........ELSE.IF..NOT.PATINDEX("%acc",@number.).=.0.and ............NOT.(.@number_acc.LIKE.'%[:-я]%'.OR.@number_acc.LIKE.'%[!-/]%'.) ........BEGIN ............SELECT.@f_account.=.CONVERT(int,.SUBSTRING(@number,1,DATALENGTH(@number).-.3)), ...................@number.=.NULL IF.@f_account.>.0 ............BEGIN ................/*.Получаем.список.сделок.с.указанным.счетом.*/ ................INSERT.INTO.#f_deal_account ................( ........................f_deal ................) ................SELECT..DISTINCT ........................fs.f_deal ..................FROM ........................#f_deal_temp....fd, ........................f_sum...........fs .................WHERE ........................fd.f_deal.......=.fs.f_deal.AND ........................fs.f_account....=.@f_account ................/*.Удаляем.сделки.не.соответствующие.указанному.счету.*/ ................DELETE.FROM ........................#f_deal_temp .................WHERE ........................#f_deal_temp.f_deal.NOT.IN.( ....................................................SELECT ............................................................f_deal ....................................................FROM ............................................................#f_deal_account ....................................................) ............END ........END ........ELSE.IF..NOT.PATINDEX("NULLacc",@number.).=.0 ........BEGIN ............SELECT..@f_account..=.NULL, ....................@number.....=.NULL /*.Получаем.список.сделок.с.указанным.счетом.*/ ................INSERT.INTO.#f_deal_account ................( ........................f_deal ................) ................SELECT..DISTINCT ........................fs.f_deal ..................FROM ........................#f_deal_temp....fd, ........................f_sum...........fs .................WHERE ........................fd.f_deal.......=.fs.f_deal.AND ........................fs.f_account....IS.NULL.....AND ........................fs.f_sumtype....IN.( ............................................SELECT ....................................................st.f_sumtype ..............................................FROM ....................................................c_market..cm, ....................................................f_sumtype.st .............................................WHERE ....................................................cm.c_market.=.st.c_market.AND ....................................................cm.c_market.IN.(1,2,5,6,7,8,9,10,11,12,13) ............................................) ................/*.Удаляем.сделки.не.соответствующие.указанному.счету.*/ ................DELETE.FROM ........................#f_deal_temp .................WHERE ........................#f_deal_temp.f_deal.NOT.IN.( ....................................................SELECT ............................................................f_deal ....................................................FROM ............................................................#f_deal_account ....................................................) ........END ....END /*-----.ДОБАВЛЕНИЕ.сделки.по.данному.ID.--------------------------------------------*/ ....IF.@f_deal.IS.NOT.NULL BEGIN ........INSERT.INTO.#f_deal_temp ........( ................f_deal, ................c_market, ................c_classificator, ................c_dealstatus, ................f_path, ................f_user_1, ................number, ................descr, ................dt_make_1, ................fx_dt_make, ................usermodify_2, ................dt_modify_2 ........) ........SELECT ................fd.f_deal, ................fd.c_market, ................fd.c_classificator, ................fd.c_dealstatus, ................fd.f_path, ................fd.f_user, ................fd.number, ................fd.descr, ................fd.dt_make, ................fd.dt_make, ................fd.user_modify, ................fd.dt_modify ..........FROM ................f_deal..........fd .........WHERE ................fd.f_deal...........=.@f_deal ....END /*-----.Удаление.сделок.с.запрещенными.для.пользователя.статусами.----------------*/ ....DELETE ............#f_deal_temp .....WHERE ............#f_deal_temp.c_dealstatus.NOT.IN.(.SELECT .......................................................lm.c_d_c_dealstatus .................................................FROM .......................................................l_movewin.........lm ................................................WHERE .......................................................#f_deal_temp.f_path.=.lm.f_path..AND .......................................................lm.c_usergroup......=.@c_usergroup .............................................) /*-----.Удаление.сделки.по.маске.номера.-------------------------------------------*/ ....IF.DATALENGTH(RTRIM.(@number)).>.0 ....BEGIN ........DELETE ................#f_deal_temp .........WHERE ................number.NOT.LIKE..@number ....END /*----------------------------------------------------------------------------------*/ /*------------.УДАЛЕНИЕ.СДЕЛОК.ПОПАДАЮЩИХ.ПОД.ФИЛЬТР.-------------------------------*/ /*----------------------------------------------------------------------------------*/ /*-----.рынок.----------------------------------------------------------------------*/ ....IF.@c_market.IS.NOT.NULL BEGIN ........DELETE ................#f_deal_temp .........WHERE ................NOT.(@c_market.=.c_market) ....END ..../*.Наборы.по.рынкам.*/ ....IF.@check_c_market.IS.NOT.NULL.AND.@check_c_market.=.1 ....BEGIN ........DELETE ................#f_deal_temp .........WHERE ................#f_deal_temp.c_market.NOT.IN.(...SELECT.DISTINCT ........................................................lm.c_market ...................................................FROM ........................................................l_marketuser.....lm ..................................................WHERE ........................................................lm.c_market........=.#f_deal_temp.c_market.....and ........................................................lm.f_user..........=.@f_user_id................and ........................................................lm.c_settype.......=.@c_settype .................................................) ....END ..../*.Запрос.для.журнала.сделок.*/ ....IF.@check_c_market.IS.NOT.NULL.AND.@check_c_market.=.-1 ....BEGIN ........DELETE ................#f_deal_temp .........WHERE ................#f_deal_temp.c_market.NOT.IN.(...SELECT.DISTINCT ........................................................lm.c_market ...................................................FROM ........................................................l_marketuser.....lm ..................................................WHERE ........................................................lm.c_market........=.#f_deal_temp.c_market.....and ........................................................lm.f_user..........=.@f_user_id................and ........................................................lm.c_settype.......=.@c_settype_13 .................................................) ....END /*-----.статус.---------------------------------------------------------------------*/ ....IF.@c_dealstatus.IS.NOT.NULL BEGIN ........DELETE ................#f_deal_temp .........WHERE ................NOT.(@c_dealstatus.=.c_dealstatus) ....END /*-----.УДАЛЕНИЕ.типы.сделки.-------------------------------------------------------*/ ....IF.@check_c_classificator.=.1 ....BEGIN ........DELETE ................#f_deal_temp .........WHERE ................#f_deal_temp.c_classificator.NOT.IN.(SELECT.DISTINCT ............................................................lc.c_classificator .......................................................FROM ............................................................l_classuser.....lc ......................................................WHERE ............................................................lc.c_classificator.=.#f_deal_temp.c_classificator...and ............................................................lc.f_user..........=.@f_user_id.....................and ............................................................lc.c_settype.......=.@c_settype .....................................................) ....END /*-----.Покупка/привлечиние.--------------------------------------------------------*/ ....IF.@date_time_11.IS.NOT.NULL..OR.@date_time_12.IS.NOT.NULL BEGIN ........IF..@date_time_11.is.not.null BEGIN ............DELETE ....................#f_deal_temp ..............FROM ....................f_sum.......fs, ....................f_sumtype...st .............WHERE ....................#f_deal_temp.f_deal.............=.fs.f_deal.................and ....................fs.date_time....................<..@date_time_11............and ....................fs.f_sumtype....................=..st.f_sumtype.............and ....................st.c_market.....................=..#f_deal_temp.c_market....and ....................st.c_inout......................=..@c_inout_1 ........END ........IF.@date_time_12.is.not.null BEGIN ............DELETE ....................#f_deal_temp ..............FROM ....................f_sum.......fs, ....................f_sumtype...st .............WHERE ....................#f_deal_temp.f_deal.............=.fs.f_deal.................and ....................fs.date_time....................>..@date_time_12............and ....................fs.f_sumtype....................=..st.f_sumtype.............and ....................st.c_market.....................=..#f_deal_temp.c_market....and ....................st.c_inout......................=..@c_inout_1 ........END ....END /*-----.Продажа/Размещение.---------------------------------------------------------*/ ....IF.@date_time_21.IS.NOT.NULL.OR.@date_time_22.IS.NOT.NULL BEGIN ........IF.@date_time_21.is.not.null BEGIN ............DELETE ....................#f_deal_temp ..............FROM ....................f_sum.......fs, ....................f_sumtype...st .............WHERE ....................#f_deal_temp.f_deal.............=..fs.f_deal................and ....................fs.date_time....................<..@date_time_21............and ....................fs.f_sumtype....................=..st.f_sumtype.............and ....................st.c_market.....................=..#f_deal_temp.c_market....and ....................st.c_inout......................=..@c_inout_2 ........END ........IF.@date_time_22.is.not.null BEGIN ............DELETE ....................#f_deal_temp ..............FROM ....................f_sum.......fs, ....................f_sumtype...st .............WHERE ....................#f_deal_temp.f_deal.............=.fs.f_deal.................and ....................fs.date_time....................>..@date_time_22............and ....................fs.f_sumtype....................=..st.f_sumtype.............and ....................st.c_market.....................=..#f_deal_temp.c_market....and ....................st.c_inout......................=..@c_inout_2 ........END ........IF.@date_time_11.IS.NULL.AND.@date_time_12.IS.NULL BEGIN ............DELETE ....................#f_deal_temp ..............FROM ....................f_sum.......fs, ....................f_sumtype...st .............WHERE ....................#f_deal_temp.f_deal.............=..fs.f_deal................and ....................fs.f_sumtype....................=..st.f_sumtype.............and ....................st.c_market.....................=..#f_deal_temp.c_market....and ....................st.c_inout......................=..@c_inout_1...............and ....................st.c_market.....................in.(.@c_market_netting, .........................................................@c_market_journal, .........................................................@c_market_pay ........................................................) ........END ....END /*-----.Дата.договора.---------------------------------------------------------*/ ....IF.(.@date_time_31.IS.NOT.NULL).OR.(@date_time_32.IS.NOT.NULL) ....BEGIN ........DELETE ................#f_deal_temp ..........FROM ................f_dogovor...fd .........WHERE ................#f_deal_temp.f_deal.............=.fd.f_deal.................and.( .............(..@date_time_31...................is.not.null.................and ................fd.date_time....................<..@date_time_31........)...or .............(..@date_time_32...................is.not.null.................and ................fd.date_time....................>..@date_time_32........).....) ....END /*-----.Дата.поставки.--------------------------------------------------------*/ ....IF.@date_time_41.IS.NOT.NULL.OR.@date_time_42.IS.NOT.NULL BEGIN ........DELETE ................#f_deal_temp ..........FROM ................f_stock......fs, ................f_stocktype..st .........WHERE ................#f_deal_temp.f_deal.............=.fs.f_deal.................and.( .............(..@date_time_41...................is.not.null.................and ................fs.date_pay.....................<..@date_time_41............and ................fs.f_stocktype..................=..st.f_stocktype...........and ................st.c_market.....................=..#f_deal_temp.c_market....and ................st.c_inout......................=..@c_inout_1.........).....or .............(..@date_time_42...................is.not.null.................and ................fs.date_pay.....................>..@date_time_42............and ................fs.f_stocktype..................=..st.f_stocktype...........and ................st.c_market.....................=..#f_deal_temp.c_market....and ................st.c_inout......................=..@c_inout_1.........).........) ........DELETE ................#f_deal_temp ..........FROM ................f_sum.......fs, ................f_sumtype...st .........WHERE ................#f_deal_temp.f_deal.............=..fs.f_deal................and ................fs.f_sumtype....................=..st.f_sumtype.............and ................st.c_market.....................=..#f_deal_temp.c_market....and ................st.c_inout......................=..@c_inout_1...............and ................@date_time_11...................IS.NULL.....................and ................@date_time_12...................IS.NULL.....................and ................@date_time_51...................IS.NULL.....................and ................@date_time_52...................IS.NULL.....................and ................st.c_market.....................in.(.@c_market_netting, .....................................................@c_market_journal, .....................................................@c_market_pay, .....................................................@c_market_forex, .....................................................@c_market_mbk ....................................................) ....END /*-----.Дата.оплаты.---------------------------------------------------------*/ ....IF.@date_time_51.IS.NOT.NULL.OR.@date_time_52.IS.NOT.NULL BEGIN ........DELETE ................#f_deal_temp ..........FROM ................f_sum.......fs, ................f_sumtype...st .........WHERE ................#f_deal_temp.f_deal.............=.fs.f_deal...............and.( .............(..@date_time_51...................is.not.null.................and ................fs.date_pay.....................<..@date_time_51............and ................fs.f_sumtype....................=..st.f_sumtype.............and ................st.c_market.....................=..#f_deal_temp.c_market....and ................st.c_inout......................=..@c_inout_2...........)...or .............(..@date_time_52...................is.not.null.................and ................fs.date_pay.....................>..@date_time_52............and ................fs.f_sumtype....................=..st.f_sumtype.............and ................st.c_market.....................=..#f_deal_temp.c_market....and ................st.c_inout......................=..@c_inout_2...........).....) ........DELETE ................#f_deal_temp ..........FROM ................f_sum.......fs, ................f_sumtype...st .........WHERE ................#f_deal_temp.f_deal.............=..fs.f_deal................and ................fs.f_sumtype....................=..st.f_sumtype.............and ................st.c_market.....................=..#f_deal_temp.c_market....and ................st.c_inout......................=..@c_inout_1...............and ................@date_time_11...................IS.NULL.....................and ................@date_time_12...................IS.NULL.....................and ................@date_time_41...................IS.NULL.....................and ................@date_time_42...................IS.NULL.....................and ................st.c_market.....................in.(.@c_market_netting, .....................................................@c_market_journal, .....................................................@c_market_pay, .....................................................@c_market_forex, .....................................................@c_market_mbk ....................................................) ....END /*-----.Запрос.по.пользователю.-------------------------------------------------------*/ ....IF.@f_user.IS.NOT.NULL BEGIN ........DELETE ................#f_deal_temp .........WHERE ................NOT.@f_user.=.f_user_1 ....END /*----------------------------------------------------------------------------------*/ /*------------.ЗАПОЛНЕНИЕ.ПОЛЕЙ.СДЕЛКИ.---------------------------------------------*/ /*----------------------------------------------------------------------------------*/ /*------------.первая.сторона.сделки.(Инициатор).----------------------------------*/ ....UPDATE ............#f_deal_temp .......SET ............#f_deal_temp.f_member_1.........=.fm.f_member, ............#f_deal_temp.c_membertype_1.....=.fm.c_membertype, ............#f_deal_temp.f_client_1.........=.fm.f_client, ............#f_deal_temp.f_portf_1..........=.fm.f_portf ......FROM ............f_member.......fm .....WHERE ............#f_deal_temp.f_deal.............=.fm.f_deal.................and ............fm.c_membertype.................=.@c_membertype_1 ....UPDATE ............#f_deal_temp .......SET ............#f_deal_temp.f_portf_code_1.....=.fp.name ......FROM ............f_portf.fp .....WHERE ............#f_deal_temp.f_portf_1..........=.fp.f_portf....AND ............( ................fp.name.NOT.LIKE.'00%'.OR.@zero_portf.=.1 ............) /*------------.вторая.сторона.сделки.(Контрагент).----------------------------------*/ ....UPDATE ............#f_deal_temp .......SET ............#f_deal_temp.f_member_2.........=.fm.f_member, ............#f_deal_temp.c_membertype_2.....=.fm.c_membertype, ............#f_deal_temp.f_client_2.........=.fm.f_client, ............#f_deal_temp.f_portf_2..........=.fm.f_portf ......FROM ............f_member....fm .....WHERE ............#f_deal_temp.f_deal.............=.fm.f_deal.................and ............fm.c_membertype.................=.@c_membertype_2 ....UPDATE ............#f_deal_temp .......SET ............#f_deal_temp.f_portf_code_2.....=.fp.name ......FROM ............f_portf.fp .....WHERE ............#f_deal_temp.f_portf_2..........=.fp.f_portf....AND ............( ................fp.name.NOT.LIKE.'00%'.OR.@zero_portf.=.1 ............) ..../*.Запрос.по.клиенту.портфеля.*/ ....IF.@f_client_4.>.0 ....BEGIN ......../*.Проставить.клиента.портфеля.*/ ........UPDATE ................#f_deal_temp ...........SET ................fp_client_1.=.lp.f_client ..........FROM ................l_portfclient.lp .........WHERE ................lp.f_portf......=.#f_deal_temp.f_portf_1..AND ................lp.c_accmemtype.=.1 ........UPDATE ................#f_deal_temp ...........SET ................fp_client_2.=.lp.f_client ..........FROM ................l_portfclient.lp .........WHERE ................lp.f_portf......=.#f_deal_temp.f_portf_2..AND ................lp.c_accmemtype.=.1 ........DELETE ................#f_deal_temp .........WHERE ................( ....................fp_client_1.!=.@f_client_4.OR ....................fp_client_1.IS.NULL ).AND ................( ....................fp_client_2.!=.@f_client_4.OR ....................fp_client_2.IS.NULL ) ....END /*-----.УДАЛЕНИЕ.портфелей.----------------------------------------------------------*/ ....IF.@check_f_portf.=.1 ....BEGIN ........DELETE ................#f_deal_temp .........WHERE ................#f_deal_temp.f_portf_1......not.in.(SELECT.DISTINCT ............................................................f_portf ......................................................FROM ............................................................l_portfuser...lp .....................................................WHERE ............................................................lp.f_portf........=.#f_deal_temp.f_portf_1..and ............................................................lp.f_user.........=.@f_user_id..............and ............................................................lp.c_settype......=.@c_settype ....................................................) ................and ................#f_deal_temp.f_portf_2......not.in.(SELECT.DISTINCT ............................................................f_portf ......................................................FROM ............................................................l_portfuser...lp .....................................................WHERE ............................................................lp.f_portf........=.#f_deal_temp.f_portf_2..and ............................................................lp.f_user.........=.@f_user_id..............and ............................................................lp.c_settype......=.@c_settype ....................................................) ....END ....IF.@check_f_portf.=.-1 ....BEGIN ........DELETE ................#f_deal_temp .........WHERE ................#f_deal_temp.f_portf_1.........in..(SELECT.DISTINCT ............................................................f_portf ......................................................FROM ............................................................l_portfuser...lp .....................................................WHERE ............................................................lp.f_portf........=.#f_deal_temp.f_portf_1..and ............................................................lp.f_user.........=.@f_user_id..............and ............................................................lp.c_settype......=.@c_settype ....................................................) ................or ................#f_deal_temp.f_portf_2.........in..(SELECT.DISTINCT ............................................................f_portf ......................................................FROM ............................................................l_portfuser...lp .....................................................WHERE ............................................................lp.f_portf........=.#f_deal_temp.f_portf_2..and ............................................................lp.f_user.........=.@f_user_id..............and ............................................................lp.c_settype......=.@c_settype ....................................................) ....END /*-----.УДАЛЕНИЕ.участник.1.или.2.----------------------------------------------------*/ ....IF.@f_client12.IS.NOT.NULL BEGIN ........DELETE ................#f_deal_temp .........WHERE ................NOT.(#f_deal_temp.f_client_1.=.@f_client12).AND ................NOT.(#f_deal_temp.f_client_2.=.@f_client12) ....END /*-----.УДАЛЕНИЕ.контрагент.----------------------------------------------------------*/ ....IF.not.@f_client_2.=.0.AND.@f_client_2.IS.NOT.NULL BEGIN ........IF.@f_client_2.>.0 ........BEGIN ............DELETE ....................#f_deal_temp .............WHERE ....................NOT.(#f_deal_temp.f_client_1.=.ABS.(@f_client_2)).AND ....................NOT.(#f_deal_temp.f_client_2.=.ABS.(@f_client_2)) ........END ........ELSE.IF.@f_client_2.<.0 ........BEGIN ............DELETE ....................#f_deal_temp .............WHERE ....................#f_deal_temp.f_client_1.=.ABS.(@f_client_2).OR ....................#f_deal_temp.f_client_2.=.ABS.(@f_client_2) ........END ....END /*----------------.Удаление.сделок.с.запрещенными.статусами.----------------------*/ ..../*.Получить.тип.участника.сделки.*/ ....UPDATE ............#f_deal_temp .......SET ............c_membertype.=.fm.c_membertype ......FROM ............f_member.fm .....WHERE ............#f_deal_temp.f_deal.=.fm.f_deal....AND ............fm.f_client.........=.@f_client /*-----.Не.соответствие.статусу.---------------------------------------------------*/ ....IF.@f_deal.IS.NULL BEGIN ........DELETE ................#f_deal_temp .........WHERE ................#f_deal_temp.c_dealstatus.NOT.IN.(.SELECT.DISTINCT ..........................................................lm.c_dealstatus .....................................................FROM ..........................................................l_movewin.......lm ....................................................WHERE ..........................................................lm.f_path.........=.#f_deal_temp.f_path...........AND ..........................................................lm.c_membertype...=.#f_deal_temp.c_membertype.....AND ..........................................................lm.c_usergroup....=.@c_usergroup ....................................................)..AND ................#f_deal_temp.c_membertype.IS.NOT.NULL END /*-----.ставка.по.сделке.-----------------------------------------------------------*/ ....UPDATE ............#f_deal_temp .......SET ............#f_deal_temp.f_rate.............=.fr.f_rate, ............#f_deal_temp.rate...............=.fr.rate, ............#f_deal_temp.real_rate..........=.fr.real_rate, ............#f_deal_temp.revers.............=.fr.revers, ............#f_deal_temp.finans.............=.fr.finans, ............#f_deal_temp.rollover...........=.fr.rollover, ............#f_deal_temp.commission.........=.fr.commission, ............#f_deal_temp.f_pricetype........=.fr.f_pricetype ......FROM ............f_rate......fr ....WHERE ............#f_deal_temp.f_deal.............=.fr.f_deal /*-----.Курс.и.порядок.расчетов.-------------------------------------------------*/ ....UPDATE ............#f_deal_temp .......SET ............f_term...........=.ft.f_term, ............c_order..........=.ft.c_order, ............rate_pay.........=.ft.rate ......FROM ............f_term......ft .....WHERE ............#f_deal_temp.f_deal.....=.ft.f_deal /*-----.Очередность.------------------------------------------------------------*/ ....IF.@c_order.IS.NOT.NULL BEGIN ........DELETE ................#f_deal_temp .........WHERE ................NOT.(c_order.=.@c_order).OR ................c_order.IS.NULL END /*-----.УДАЛЕНИЕ.Рыночная.-------------------------------------------------------*/ ....IF.@finans.IS.NOT.NULL BEGIN ........DELETE ................#f_deal_temp .........WHERE ................NOT.(finans.=.@finans).OR ................finans.IS.NULL END /*-----.УДАЛЕНИЕ.Типы.сообщений.--------------------------------------------------*/ ....IF.@commission.IS.NOT.NULL BEGIN ........UPDATE ................#f_deal_temp ...........SET ................swift_telex.=.2./*.TELEX.*/ ..........FROM ................l_clientrecv.lc .........WHERE ................lc.f_client.=.#f_deal_temp.f_client_2.....AND ................lc.c_telex..IS.NOT.NULL UPDATE ................#f_deal_temp ...........SET ................swift_telex.=.1./*.SWIFT.*/ ..........FROM ................l_clientrecv.lc .........WHERE ................lc.f_client.=.#f_deal_temp.f_client_2.....AND ................lc.c_swift..IS.NOT.NULL /*.Проставляем.наличие.ключа.у.МБК.*/ ........UPDATE ................#f_deal_temp ...........SET ................swift_telex.=.(#f_deal_temp.swift_telex.*.-1) ..........FROM ................f_conf..fc .........WHERE ................#f_deal_temp.f_deal.........=.fc.f_deal.........AND ................#f_deal_temp.swift_telex....=.ABS.(@commission).AND ................fc.c_conftype...............=.@c_conftype_53....AND ................fc.account..................IS.NOT.NULL /*.Проставляем.наличие.ключа.у.ForEx.*/ ........UPDATE ................#f_deal_temp ...........SET ................swift_telex.=.(#f_deal_temp.swift_telex.*.-1) ..........FROM ................f_conf..fc .........WHERE ................#f_deal_temp.f_deal.........=.fc.f_deal.........AND ................#f_deal_temp.swift_telex....=.ABS.(@commission).AND ................fc.c_conftype...............=.@c_conftype_54....AND ................fc.account..................IS.NOT.NULL /*.Удаление.не.соответствующих.*/ ........DELETE ................#f_deal_temp .........WHERE ................NOT.swift_telex.=.@commission ....END /*------------.третья.сторона.сделки.(Свич).-----------------------------------------*/ ....UPDATE ............#f_deal_temp .......SET ............#f_deal_temp.f_member_3.........=.fm.f_member, ............#f_deal_temp.c_membertype_3.....=.fm.c_membertype, ............#f_deal_temp.f_client_3.........=.fm.f_client ......FROM ............f_member..fm .....WHERE ............#f_deal_temp.f_deal.............=.fm.f_deal.................and ............fm.c_membertype.................=.@c_membertype_3...........and ............fm.f_client.....................IS.NOT.NULL /*------------.четвертая.сторона.сделки.(брокер).-----------------------------------------*/ ....UPDATE ............#f_deal_temp .......SET ............#f_deal_temp.f_member_4.........=.fm.f_member, ............#f_deal_temp.c_membertype_4.....=.fm.c_membertype, ............#f_deal_temp.f_client_4.........=.fm.f_client ......FROM ............f_member..fm .....WHERE ............#f_deal_temp.f_deal.............=.fm.f_deal.................and ............fm.c_membertype.................=.@c_membertype_4...........and ............fm.f_client.....................IS.NOT.NULL /*-----.УДАЛЕНИЕ.свич.ДА.-----------------------------------------------------------*/ ....IF.(@svich.=.1) ....BEGIN ........DELETE ................#f_deal_temp ..........FROM ................f_member...fm .........WHERE ................#f_deal_temp.f_deal.........=.fm.f_deal................AND ................#f_deal_temp.f_client_3.....IS.NULL END /*-----.УДАЛЕНИЕ.свич.НЕТ.----------------------------------------------------------*/ ....IF.(@svich.=.0) ....BEGIN ........DELETE ................#f_deal_temp ..........FROM ................f_member...fm .........WHERE ................#f_deal_temp.f_deal.........=.fm.f_deal.............AND ................#f_deal_temp.f_client_3.....>.0 ....END /*-----.УДАЛЕНИЕ.свич.(контрагент).-----------------------------------------------*/ ....IF.(@f_client_3.IS.NOT.NULL) ....BEGIN ........DELETE ................#f_deal_temp ..........FROM ................f_member...fm .........WHERE ................#f_deal_temp.f_deal.............=..fm.f_deal.........and ................(#f_deal_temp.f_client_3.is.null.....................or ................not.(#f_deal_temp.f_client_3....=.@f_client_3)) ....END /*-----.УДАЛЕНИЕ.набор.инициаторов.--------------------------------------------------*/ ....IF.@check_f_client_1.=.1 ....BEGIN ........DELETE ................#f_deal_temp .........WHERE ................#f_deal_temp.f_client_1.NOT.IN.(SELECT.DISTINCT ........................................................lm.f_client ..................................................FROM ........................................................l_memuser........lm .................................................WHERE ........................................................#f_deal_temp.f_client_1.....=.lm.f_client...........and ........................................................#f_deal_temp.c_membertype_1.=.lm.c_membertype.......and ........................................................lm.f_user.=.@f_user_id..............................and ........................................................lm.c_settype.=.@c_settype ................................................) ....................................................AND ................#f_deal_temp.f_client_2.NOT.IN.(SELECT.DISTINCT ........................................................lm.f_client ..................................................FROM ........................................................l_memuser........lm .................................................WHERE ........................................................#f_deal_temp.f_client_2.....=.lm.f_client...........and ........................................................#f_deal_temp.c_membertype_1.=.lm.c_membertype.......and ........................................................lm.f_user.=.@f_user_id..............................and ........................................................lm.c_settype.=.@c_settype ................................................) ....END ....IF.@check_f_client_1.=.-1 ....BEGIN ........DELETE ................#f_deal_temp .........WHERE ................#f_deal_temp.f_client_1.IN.(SELECT.DISTINCT ........................................................lm.f_client ..................................................FROM ........................................................l_memuser........lm .................................................WHERE ........................................................#f_deal_temp.f_client_1.....=.lm.f_client...........and ........................................................#f_deal_temp.c_membertype_1.=.lm.c_membertype.......and ........................................................lm.f_user.=.@f_user_id..............................and ........................................................lm.c_settype.=.@c_settype ...........................................) ................................................AND ................#f_deal_temp.f_client_2.IN.(SELECT.DISTINCT ........................................................lm.f_client ..................................................FROM ........................................................l_memuser........lm .................................................WHERE ........................................................#f_deal_temp.f_client_2.....=.lm.f_client...........and ........................................................#f_deal_temp.c_membertype_1.=.lm.c_membertype.......and ........................................................lm.f_user.=.@f_user_id..............................and ........................................................lm.c_settype.=.@c_settype ...........................................) ....END /*-----.УДАЛЕНИЕ.набор.контрагентов.-------------------------------------------------*/ ....IF.@check_f_client_2.=.1 ....BEGIN ........DELETE ................#f_deal_temp .........WHERE ................#f_deal_temp.f_client_2.NOT.IN.(SELECT.DISTINCT ........................................................lm.f_client ..................................................FROM ........................................................l_memuser.......lm ..................................................WHERE ........................................................#f_deal_temp.f_client_2.....=.lm.f_client...........and ........................................................#f_deal_temp.c_membertype_2.=.lm.c_membertype.......and ........................................................lm.f_user...................=.@f_user_id............and ........................................................lm.c_settype................=.@c_settype ................................................) ....END ....IF.@check_f_client_2.=.-1 ....BEGIN ........DELETE ................#f_deal_temp .........WHERE ................#f_deal_temp.f_client_2.IN.(SELECT.DISTINCT ........................................................lm.f_client ..................................................FROM ........................................................l_memuser.......lm ..................................................WHERE ........................................................#f_deal_temp.f_client_2.....=.lm.f_client...........and ........................................................#f_deal_temp.c_membertype_2.=.lm.c_membertype.......and ........................................................lm.f_user...................=.@f_user_id............and ........................................................lm.c_settype................=.@c_settype ................................................) ....END /*------------.первая.сторона.сделки.-----------------------------------------------*/ ....UPDATE ............#f_deal_temp .......SET ............#f_deal_temp.code_1.............=.fc.code, ............#f_deal_temp.rtscode_1..........=.fc.rtscode, ............#f_deal_temp.rsbcode_1..........=.fc.rsbcode, ............#f_deal_temp.c_clienttype_1.....=.fc.c_clienttype, ............#f_deal_temp.firstname_1........=.fc.firstname ......FROM ............f_client........fc .....WHERE ............#f_deal_temp.f_client_1.........=.fc.f_client /*------------.вторая.сторона.сделки.-----------------------------------------------*/ ....UPDATE ............#f_deal_temp .......SET ............#f_deal_temp.code_2.............=.fc.code, ............#f_deal_temp.rtscode_2..........=.fc.rtscode, ............#f_deal_temp.rsbcode_2..........=.fc.rsbcode, ............#f_deal_temp.c_clienttype_2.....=.fc.c_clienttype, ............#f_deal_temp.c_clientgroup_2....=.fc.c_clientgroup, ............#f_deal_temp.firstname_2........=.fc.firstname ......FROM ............f_client........fc .....WHERE ............#f_deal_temp.f_client_2.........=.fc.f_client /*------------.третья.сторона.сделки.-----------------------------------------------*/ ....UPDATE ............#f_deal_temp .......SET ............#f_deal_temp.code_3.............=.fc.code, ............#f_deal_temp.rtscode_3..........=.fc.rtscode, ............#f_deal_temp.rsbcode_3..........=.fc.rsbcode, ............#f_deal_temp.c_clienttype_3.....=.fc.c_clienttype, ............#f_deal_temp.firstname_3........=.fc.firstname ......FROM ............f_client........fc .....WHERE ............#f_deal_temp.f_client_3.........=.fc.f_client /*------------.четвертая.сторона.сделки.---------------------------------------------*/ ....UPDATE ............#f_deal_temp .......SET ............#f_deal_temp.code_4.............=.fc.code, ............#f_deal_temp.rtscode_4..........=.fc.rtscode, ............#f_deal_temp.rsbcode_4..........=.fc.rsbcode, ............#f_deal_temp.c_clienttype_4.....=.fc.c_clienttype ......FROM ............f_client........fc .....WHERE ............#f_deal_temp.f_client_4.........=.fc.f_client /*-----.УДАЛЕНИЕ.тип,вид,группа.контрагента.-----------------------------------------*/ ....IF(@c_clienttype.IS.NOT.NULL) ....BEGIN ........DELETE ................#f_deal_temp .........WHERE ................NOT.(c_clienttype_2.=.@c_clienttype) ....END ..../*.Удаление.по.виду.клиента.*/ ....IF(@c_clientkind.IS.NOT.NULL) ....BEGIN ........DELETE ................#f_deal_temp ..........FROM ................f_client.fc .........WHERE ................#f_deal_temp.f_client_2.=.fc.f_client.AND ................NOT.fc.c_clientkind.=.@c_clientkind ....END ..../*.Удаление.по.группе.клиента.*/ ....IF.@c_clientgroup.IS.NOT.NULL BEGIN ........DELETE ................#f_deal_temp .........WHERE ................NOT.c_clientgroup_2.=.@c_clientgroup ....END /*------------.первая.сумма.по.сделке.и.id.валюты.----------------------------------*/ ....UPDATE ............#f_deal_temp .......SET ............#f_deal_temp.f_sum_1............=.fs.f_sum, ............#f_deal_temp.f_sumtype_1........=.st.f_sumtype, ............#f_deal_temp.f_account_1........=.fs.f_account, ............#f_deal_temp.value_1............=.CASE.fs.c_sumkind ....................................................WHEN.@c_sumkind.........THEN.fs.value ....................................................WHEN.@c_sumkind_minus...THEN.(-fs.value) ....................................................ELSE.........................NULL END, ............#f_deal_temp.date_time_1........=.fs.date_time, ............#f_deal_temp.fs_date_pay_1......=.fs.date_pay, ............#f_deal_temp.c_currency_1.......=.fs.c_currency ......FROM ............f_sum.......fs, ............f_sumtype...st .....WHERE ............#f_deal_temp.f_deal.....=.fs.f_deal..................and ............#f_deal_temp.c_market...=.st.c_market................and ............fs.f_sumtype............=.st.f_sumtype...............and ............st.c_inout..............=.@c_inout_1 /*------------.поля.по.ценной.бумаге.-----------------------------------------*/ ....UPDATE ............#f_deal_temp .......SET ............#f_deal_temp.f_instr_1..........=.fs.f_instr, ............#f_deal_temp.f_issue_1..........=.fs.f_issue, ............#f_deal_temp.date_time_1........=.fs.date_time, ............#f_deal_temp.st_date_pay_1......=.fs.date_pay, ............#f_deal_temp.st_date_begin_1....=.fs.date_begin, ............#f_deal_temp.st_date_end_1......=.fs.date_end, ............#f_deal_temp.value_3............=.#f_deal_temp.value_1, ............#f_deal_temp.c_currency_3.......=.#f_deal_temp.c_currency_1, ............#f_deal_temp.value_1............=.CASE.fs.c_sumkind ....................................................WHEN.@c_sumkind.........THEN.fs.value ....................................................WHEN.@c_sumkind_minus...THEN.(-fs.value) ....................................................ELSE.........................NULL END ......FROM ............f_stock.........fs, ............f_stocktype.....st .....WHERE ............#f_deal_temp.f_deal.....=.fs.f_deal......and ............#f_deal_temp.c_market...=.st.c_market....and ............fs.f_stocktype..........=.st.f_stocktype.and ............st.c_inout..............=.@c_inout_1 /*------------.Получаем.номер.журнала.и.дату.---------------------------------------*/ ....UPDATE ............#f_deal_temp .......SET ............number_jur..=.fd.number.+.'.'.+.Convert.(varchar(10).,.fd.dt_make,.4.).+.'.'.+.Convert.(varchar(10),.fd.f_deal).+.'id' ......FROM ............l_jurnal....lj, ............f_deal......fd .....WHERE ............NOT.(.#f_deal_temp.c_market.=.@c_market_journal.)....and ............#f_deal_temp.f_deal.........=.lj.f_d_f_deal..........and ............fd.f_deal...................=.lj.f_deal /*------------.Получаем.количество.сделок.в.журнале.-------------------------------*/ ....UPDATE ............#f_deal_temp .......SET ............#f_deal_temp.value_1....=.(.SELECT ................................................COUNT(lj.f_deal) ..........................................FROM ...............................................l_jurnal........lj .........................................WHERE ...............................................#f_deal_temp.f_deal......=.lj.f_deal ......................................) .....WHERE ...........#f_deal_temp.c_market....=.@c_market_journal /*------------.Удаление.по.заданному.инструменту.-----------------------------------*/ ....IF.@f_instr.IS.NOT.NULL BEGIN ........DELETE ................#f_deal_temp .........WHERE ................NOT.#f_deal_temp.f_instr_1.=.@f_instr..OR ................#f_deal_temp.f_instr_1.IS.NULL END ....IF.@check_f_instr.=.1 ....BEGIN ........DELETE ................#f_deal_temp .........WHERE ................#f_deal_temp.f_instr_1.IS.NULL.OR ................#f_deal_temp.f_instr_1.not.in.(SELECT.DISTINCT ........................................................f_instr ..................................................FROM ........................................................l_instruser...lp .................................................WHERE ........................................................lp.f_user.........=.@f_user_id.and ........................................................lp.c_settype......=.@c_settype ................................................) ....END ....IF.@check_f_instr.=.-1 ....BEGIN ........DELETE ................#f_deal_temp .........WHERE ................#f_deal_temp.f_instr_1......in.(SELECT.DISTINCT ........................................................f_instr ..................................................FROM ........................................................l_instruser...lp .................................................WHERE ........................................................lp.f_user.........=.@f_user_id.and ........................................................lp.c_settype......=.@c_settype ................................................) ....END /*------------.Договор.и.заявка.по.сделке.с.ЦБ.-------------------------------------*/ ....UPDATE ............#f_deal_temp .......SET ............#f_deal_temp.do_c_dogform_1.....=.fd.c_dogform, ............#f_deal_temp.do_dognum_1........=.fd.dognum, ............#f_deal_temp.do_date_time_1.....=.fd.date_time, ............#f_deal_temp.do_descr_1.........=.fd.descr ......FROM ............f_dogovor.fd .....WHERE ............#f_deal_temp.f_deal....=.fd.f_deal......and ............fd.c_dogtype...........=.@c_dogtype_100.and ............#f_deal_temp.f_instr_1.IS.NOT.NULL UPDATE ............#f_deal_temp .......SET ............#f_deal_temp.do_dogform_name_1.......=.cd.name ......FROM ............c_dogform.cd .....WHERE ............#f_deal_temp.do_c_dogform_1....=.cd.c_dogform.and ............#f_deal_temp.do_c_dogform_1....IS.NOT.NULL UPDATE ............#f_deal_temp .......SET ............#f_deal_temp.do_c_dogform_2.....=.fd.c_dogform, ............#f_deal_temp.do_dognum_2........=.fd.dognum, ............#f_deal_temp.do_date_time_2.....=.fd.date_time, ............#f_deal_temp.do_descr_2.........=.fd.descr ......FROM ............f_dogovor.fd .....WHERE ............#f_deal_temp.f_deal....=.fd.f_deal......and ............fd.c_dogtype...........=.@c_dogtype_200.and ............#f_deal_temp.c_market.>=.@c_market_stock./*.f_instr_1.IS.NOT.NULL.*/ ..../*.Заполняем.в.пустой.комментарий.Условия.расчетов.*/ ....UPDATE ............#f_deal_temp .......SET ............#f_deal_temp.do_descr_2.=.CASE.ft1.c_order ........................................WHEN.@c_order_1....THEN.'пП.'.+ ............................................Convert.(varchar(4),.ft2.days).+.'-П,'.+ ............................................Convert.(varchar(4),.ft1.days).+.'-О' ........................................WHEN.@c_order_2....THEN.'пО.'.+ ............................................Convert.(varchar(4),.ft2.days).+.'-П,'.+ ............................................Convert.(varchar(4),.ft1.days).+.'-О' ........................................WHEN.@c_order_3....THEN.'DVP.'.+ ............................................Convert.(varchar(4),.ft2.days) ........................................ELSE....................'??.' ......................................END ......FROM ............f_term....ft1, ............f_term....ft2 .....WHERE ............#f_deal_temp.f_deal.....=.ft1.f_deal........and ............#f_deal_temp.f_deal.....=.ft2.f_deal........and ............ft1.c_sumstock..........=.@c_sumstock_1.....and ............ft2.c_sumstock..........=.@c_sumstock_2.....and ............#f_deal_temp.do_descr_2...IS.NULL...........and ............#f_deal_temp.f_instr_1....IS.NOT.NULL UPDATE ............#f_deal_temp .......SET ............#f_deal_temp.do_dogform_name_2.......=.cd.name ......FROM ............c_dogform.cd .....WHERE ............#f_deal_temp.do_c_dogform_2....=.cd.c_dogform.and ............#f_deal_temp.do_c_dogform_2....IS.NOT.NULL /*------------.вторая.сумма.по.сделке.и.краткое.(id).валюты.------------------------*/ ....UPDATE ............#f_deal_temp ......SET ............#f_deal_temp.f_sum_2............=.fs.f_sum, ............#f_deal_temp.f_sumtype_2........=.st.f_sumtype, ............#f_deal_temp.f_account_2........=.fs.f_account, ............#f_deal_temp.value_2............=.CASE.fs.c_sumkind ....................................................WHEN.@c_sumkind.........THEN.fs.value ....................................................WHEN.@c_sumkind_minus...THEN.(-fs.value) ....................................................ELSE....NULL END, ............#f_deal_temp.date_time_2........=.fs.date_time, ............#f_deal_temp.c_currency_2.......=.fs.c_currency ......FROM ............f_sum.......fs, ............f_sumtype...st .....WHERE ............#f_deal_temp.f_deal....=.fs.f_deal.................and ............#f_deal_temp.c_market..=.st.c_market...............and ............fs.f_sumtype...........=.st.f_sumtype..............and ............st.c_inout.............=.@c_inout_2 ..../*.Для.поручений.ДЕПО.сумма_2.=.номинал.*.цену.залога.*/ ....UPDATE ............#f_deal_temp .......SET ............value_2.=.CONVERT(decimal(18,6),.value_1.*.rate./.100.0) .....WHERE ............c_market.IN.(@c_market_depo_bond,.@c_market_depo_bill) ..../*.Для.облигаций.нужно.умножить.на.номинал.*/ ....UPDATE ............#f_deal_temp .......SET ............value_2.=.CONVERT(decimal(18,6),.value_2.*.fi.value) ......FROM ............f_issue.fi .....WHERE ............#f_deal_temp.f_issue_1.=.fi.f_issue.............AND ............#f_deal_temp.c_market..=.@c_market_depo_bond /*-----.Для.депо.платежа,.пустые.счета.заполняем.l_dealacc.--------------------------*/ ....UPDATE ............#f_deal_temp .......SET ............#f_deal_temp.f_account_2....=.ld.f_account ......FROM ............l_dealacc...ld, ............f_sum.......fs .....WHERE ............#f_deal_temp.f_deal.........=.fs.f_deal.............and ............#f_deal_temp.f_deal.........=.ld.f_deal.............and ............#f_deal_temp.c_market.......=.@c_market_depo_pay....and ............#f_deal_temp.f_account_1....IS.NULL.................and ............#f_deal_temp.f_account_2....IS.NULL.................and ............fs.c_sumkind................=.ld.c_sumkind..........and ............fs.f_account................IS.NULL.................and ............ld.f_account................IS.NOT.NULL /*------------.третья.сумма.по.сделке.(%%.или.НКД).--------------------------------*/ ....UPDATE ............#f_deal_temp .......SET ............#f_deal_temp.f_sum_3............=.fs.f_sum, ............#f_deal_temp.f_sumtype_3........=.st.f_sumtype, ............#f_deal_temp.f_account_3........=.fs.f_account, ............#f_deal_temp.value_2.=.(.#f_deal_temp.value_2.+.IsNull..( ....................................CASE.fs.c_sumkind ........................................WHEN.@c_sumkind.........THEN.fs.value ........................................WHEN.@c_sumkind_minus...THEN.(-fs.value) ........................................ELSE.........................NULL END.,.0.0.......................) ...................................) ......FROM ............f_sum.......fs, ............f_sumtype...st .....WHERE ............#f_deal_temp.f_deal.....=.fs.f_deal..................and ............#f_deal_temp.c_market...=.st.c_market................and ............fs.f_sumtype............=.st.f_sumtype...............and ............st.c_inout..............=.@c_inout_3 /*-----.Покупка/привлечиние.--------------------------------------------------------*/ ....IF.@date_time_11.IS.NOT.NULL..OR.@date_time_12.IS.NOT.NULL BEGIN ........DELETE ................#f_deal_temp .........WHERE ..............(.@date_time_11...................is.not.null.................and ................#f_deal_temp.date_time_1........<..@date_time_11..........).or ..............(.@date_time_12...................is.not.null.................and ................#f_deal_temp.date_time_1........>..@date_time_12..........) ....END /*-----.Дата.1.или.2.Неттинг.----------------------------------------------------*/ ....IF.@check_date_1or2.>.0 ....BEGIN ........IF.@check_date_1or2.=.1........./*.=.*/ ........BEGIN ............DELETE ....................#f_deal_temp .............WHERE ....................#f_deal_temp.date_time_2.IS.NULL................AND ....................NOT.#f_deal_temp.date_time_1.=.@date_time_1or2 ............DELETE ....................#f_deal_temp .............WHERE ....................NOT.#f_deal_temp.date_time_1.=.@date_time_1or2...AND ....................NOT.#f_deal_temp.date_time_2.=.@date_time_1or2 ........END ........ELSE.IF.@check_date_1or2.=.2..../*.>.*/ ........BEGIN ............DELETE ....................#f_deal_temp .............WHERE ....................#f_deal_temp.date_time_2.IS.NULL................AND ....................#f_deal_temp.date_time_1.<.@date_time_1or2 ............DELETE ....................#f_deal_temp .............WHERE ....................#f_deal_temp.date_time_1.<.@date_time_1or2......AND ....................#f_deal_temp.date_time_2.<.@date_time_1or2 ........END ........ELSE.IF.@check_date_1or2.=.3..../*.<.*/ ........BEGIN ............DELETE ....................#f_deal_temp .............WHERE ....................#f_deal_temp.date_time_2.IS.NULL................AND ....................#f_deal_temp.date_time_1.>.@date_time_1or2 ............DELETE ....................#f_deal_temp .............WHERE ....................#f_deal_temp.date_time_1.>.@date_time_1or2......AND ....................#f_deal_temp.date_time_2.>.@date_time_1or2 ........END ....END /*-----.УДАЛЕНИЕ.валюта.1.-------------------------------------------------------*/ ....IF(@c_currency_1.IS.NOT.NULL) ....BEGIN ........DELETE ................#f_deal_temp .........WHERE ................NOT.#f_deal_temp.c_currency_1.=.@c_currency_1...OR ................#f_deal_temp.c_currency_1.....IS.NULL END /*-----.УДАЛЕНИЕ.валюта.2.-------------------------------------------------------*/ ....IF(@c_currency_2.IS.NOT.NULL) ....BEGIN ........DELETE ................#f_deal_temp .........WHERE ................NOT.#f_deal_temp.c_currency_2.=.@c_currency_2...OR ................#f_deal_temp.c_currency_2.......IS.NULL END /*-----.УДАЛЕНИЕ.валюта.1.или.2.--------------------------------------------------*/ ....IF(@c_currency_12.IS.NOT.NULL) ....BEGIN ........DELETE ................#f_deal_temp .........WHERE ................((not.#f_deal_temp.c_currency_1.=.@c_currency_12).and ................(not.#f_deal_temp.c_currency_2.=.@c_currency_12)).or ................#f_deal_temp.c_currency_1.is.null.................or ................#f_deal_temp.c_currency_2.is.null END /*------------.iso.валюты.1.или.код.инструмента.-------------------------------------*/ ....UPDATE ............#f_deal_temp .......SET ............#f_deal_temp.currency_iso_1...=.cc.iso ......FROM ............c_currency......cc .....WHERE ............#f_deal_temp.c_currency_1.......=.cc.c_currency ....UPDATE ............#f_deal_temp .......SET ............#f_deal_temp.currency_iso_1...=.fi.code ......FROM ............f_instr.fi .....WHERE ............#f_deal_temp.f_instr_1........=.fi.f_instr..and ............#f_deal_temp.f_instr_1........IS.NOT.NULL /*------------.тип.цены.-----------------------------------------------------------*/ ....UPDATE ............#f_deal_temp .......SET ............#f_deal_temp.f_pricetype_name...=.fp.name ......FROM ............f_pricetype....fp .....WHERE ............#f_deal_temp.f_pricetype......=..fp.f_pricetype /*------------.(iso).валюты.2,3.----------------------------------------------------*/ ....UPDATE ............#f_deal_temp .......SET ............#f_deal_temp.currency_iso_2...=.cc.iso ......FROM ............c_currency......cc .....WHERE ............#f_deal_temp.c_currency_2.......=.cc.c_currency ....UPDATE ............#f_deal_temp .......SET ............#f_deal_temp.currency_iso_3...=.cc.iso ......FROM ............c_currency......cc .....WHERE ............#f_deal_temp.c_currency_3.......=.cc.c_currency ....UPDATE ............#f_deal_temp .......SET ............currency_iso_3...=.currency_iso_2 .....WHERE ............currency_iso_3.IS.NULL /*------------.Код.инструмента.1.+.номер.выпуска.------------------------------*/ ....UPDATE ............#f_deal_temp .......SET ............#f_deal_temp.f_instr_code_1...=.fi.code ......FROM ............f_instr.fi .....WHERE ............#f_deal_temp.f_instr_1........=.fi.f_instr..and ............#f_deal_temp.f_instr_1........IS.NOT.NULL UPDATE ............#f_deal_temp .......SET ............#f_deal_temp.f_instr_code_1...=.#f_deal_temp.f_instr_code_1.+.'.'.+.fis.name ......FROM ............f_issue.fis .....WHERE ............#f_deal_temp.f_issue_1........=.fis.f_issue..and ............#f_deal_temp.f_issue_1........IS.NOT.NULL /*-----.выделенные.поля.------------------------------------------------------------*/ ....UPDATE ............#f_deal_temp .......SET ............#f_deal_temp.c_sumkind..........=.@c_sumkind, ............#f_deal_temp.c_currency_sum.....=.#f_deal_temp.c_currency_1.*.1000.+.#f_deal_temp.c_currency_2, ............#f_deal_temp.netting_value_1....=.0, ............#f_deal_temp.netting_value_2....=.0, ............#f_deal_temp.journal_f_deal.....=.0, ............#f_deal_temp.journal_add........=.0, ............#f_deal_temp.selectrow..........=.0 /*-----.УДАЛЕНИЕ.валюты.------------------------------------------------------------*/ ....IF(@check_l_currencyuser.=.1) ....BEGIN ........DELETE ................#f_deal_temp .........WHERE ................#f_deal_temp.c_currency_sum.not.in.(SELECT.DISTINCT ............................................................lc.c_currency.*.1000.+.lc.c_c_c_currency ......................................................FROM ............................................................l_currencyuser...lc .....................................................WHERE ............................................................#f_deal_temp.c_currency_1.......=.lc.c_currency.........and ............................................................#f_deal_temp.c_currency_2.......=.lc.c_c_c_currency.....and ............................................................lc.c_c_c_currency...............is.not.null.............and ............................................................lc.f_user.......................=.@f_user_id............and ............................................................lc.c_settype.=.@c_settype ....................................................) ................and ................#f_deal_temp.c_currency_1...not.in.(SELECT.DISTINCT ............................................................lc.c_currency ......................................................FROM ............................................................l_currencyuser...lc .....................................................WHERE ............................................................#f_deal_temp.c_currency_1.......=.lc.c_currency.........and ............................................................lc.c_c_c_currency...............is.null.................and ............................................................lc.f_user.......................=.@f_user_id............and ............................................................lc.c_settype....................=.@c_settype ...................................................) ....END /*-----.УДАЛЕНИЕ.Категории.-------------------------------------------------------*/ ....IF.@check_f_mean.IS.NOT.NULL BEGIN ......../*.Кол-во.записей.в.запросе.*/ ........SELECT ...............@f_mean.=.count(*) ..........FROM ...............l_meanuser..lu .........WHERE ...............lu.f_user.....=.@f_user_id.AND ...............lu.c_settype..=.@c_settype ........IF.@f_mean.>.0 ........BEGIN ............/*.Курсор.по.категориям.*/ ............DECLARE.cursorb_category.CURSOR.FOR ............SELECT.DISTINCT ....................c_category ..............FROM ....................l_meanuser .............WHERE ....................f_user.=.@f_user_id.AND ....................c_settype.=.@c_settype ............/*.Открытие.курсора.*/ ............OPEN.cursorb_category ............FETCH.cursorb_category.INTO ....................@c_category ............WHILE.@@sqlstatus.=.0 ............BEGIN ................/*.Обнуление.значений.категорий.*/ ................UPDATE ........................#f_deal_temp ...................SET ........................f_mean.=.NULL /*.Проставить.значения.текущей.категории.*/ ................UPDATE ........................#f_deal_temp ...................SET ........................#f_deal_temp.f_mean.=.lm.f_mean ..................FROM ........................l_meandeal......lm .................WHERE ........................lm.f_deal.....=.#f_deal_temp.f_deal.......AND ........................lm.c_category.=.@c_category ................/*.Удалить.сделки.с.другими.значениями.категорий.*/ ................DELETE ........................#f_deal_temp .................WHERE ........................#f_deal_temp.f_mean.IS.NOT.NULL.AND ........................#f_deal_temp.f_mean.NOT.IN.(SELECT ..........................................................lu.f_mean ......................................................FROM ...........................................................l_meanuser..lu .....................................................WHERE ...........................................................lu.f_user.....=.@f_user_id....AND ...........................................................lu.c_settype..=.@c_settype....AND ...........................................................lu.c_category.=.@c_category...AND ...........................................................lu.f_mean.....IS.NOT.NULL ) ................/*.Кол-во.пустых.записей.в.запросе.*/ ................SELECT .......................@f_mean.=.count(*) ..................FROM .......................l_meanuser..lu .................WHERE .......................lu.f_user.....=.@f_user_id...AND .......................lu.c_settype..=.@c_settype...AND .......................lu.c_category.=.@c_category..AND .......................lu.f_mean.....=.NULL IF.@f_mean.=.0 ................BEGIN ....................DELETE ............................#f_deal_temp .....................WHERE ............................#f_deal_temp.f_mean.IS.NULL END ................FETCH.cursorb_category.INTO ....................@c_category ............END ............CLOSE.cursorb_category ............DEALLOCATE.CURSOR.cursorb_category ........END ....END /*-----.название.рынка.-------------------------------------------------------------*/ ....UPDATE ............#f_deal_temp .......SET ............#f_deal_temp.market_name........=.cm.name ......FROM ............c_market....cm .....WHERE ............#f_deal_temp.c_market...........=.cm.c_market ..../*.SWAP.*/ ....UPDATE ............#f_deal_temp .......SET ............market_name.=.market_name.+.'-S' .....WHERE ............c_market.=.@c_market_forex...AND ............rollover.=.1 /*------------.тип.сделки.----------------------------------------------------------*/ ....UPDATE ............#f_deal_temp .......SET ............#f_deal_temp.c_classif_name.....=.cc.name, ............#f_deal_temp.textcolor..........=.cc.textcolor ......FROM ............c_classificator........cc .....WHERE ............#f_deal_temp.c_classificator....=.cc.c_classificator /*------------.имя.автора.сделки.---------------------------------------------------*/ ....UPDATE ............#f_deal_temp .......SET ............#f_deal_temp.usermake_1..=..fu.name.+.'.'.+ ........................................CONVERT(varchar,.dt_make_1,.111).+.'.'.+ ........................................CONVERT(varchar,.dt_make_1,.111) ......FROM ............f_user........fu .....WHERE ............#f_deal_temp.f_user_1....=.fu.f_user /*-----.подсветка.первой.суммы.если.она.вошла.в.неттинг.----------------------------*/ ....UPDATE..#f_deal_temp .......SET ............netting_value_1.=.ld.l_dealsum ......FROM ............l_dealsum...ld, ............f_deal......fd .....WHERE ............#f_deal_temp.f_sum_1............=.ld.f_sum..........and ............fd.f_deal.......................=.ld.f_deal.........and ............not.fd.c_classificator..........=.@c_class_cancel /*-----.подсветка.второй.суммы.если.она.вошла.в.неттинг.---------------------------*/ ....UPDATE..#f_deal_temp .......SET ............netting_value_2.=.ld.l_dealsum ......FROM ............l_dealsum...ld, ............f_deal......fd .....WHERE ............#f_deal_temp.f_sum_2............=.ld.f_sum..........and ............fd.f_deal.......................=.ld.f_deal.........and ............not.fd.c_classificator..........=.@c_class_cancel /*-----.подсветка.первой.суммы.если.она.вошла.в.ролловер.---------------------------*/ ....UPDATE..#f_deal_temp .......SET ............rollover_value_1.=.lr.l_rollsum ......FROM ............l_rollsum...lr .....WHERE ............#f_deal_temp.f_sum_1........=.lr.f_sum..............AND ............lr.f_deal...................IS.NOT.NULL /*-----.подсветка.второй.суммы.если.она.вошла.в.ролловер.--------------------------*/ ....UPDATE..#f_deal_temp .......SET ............rollover_value_2.=.lr.l_rollsum ......FROM ............l_rollsum...lr .....WHERE ............#f_deal_temp.f_sum_2........=.lr.f_sum..............AND ............lr.f_deal...................IS.NOT.NULL /*-----.флаг.1.сделки.если.она.вошла.в.таблицу.ccылок.журнала.(l_jurnal).-----------*/ ....UPDATE..#f_deal_temp .......SET ............journal_add.=.1 ......FROM ............l_jurnal....lj .....WHERE ............lj.c_jurtype...............=.@c_jurtype.....and ............#f_deal_temp.f_deal........=.lj.f_d_f_deal /*-----.ID.сделки.если.она.вошла.в.сделку.журнал.-----------------------------------*/ ....UPDATE.#f_deal_temp .......SET ............journal_l_jurnal....=.lj.l_jurnal, ............journal_f_deal......=.lj.f_deal, ............journal_f_d_f_deal..=.lj.f_d_f_deal ......FROM ............l_jurnal....lj .....WHERE ............#f_deal_temp.f_deal........=.lj.f_d_f_deal..and ............lj.c_jurtype...............=.@c_jurtype.....and ............lj.f_d_f_deal..............is.not.null /*-----последние.изменения.сделал.--------------------------------------------------*/ ....UPDATE..#f_deal_temp .......SET ............#f_deal_temp.f_user_2.......=.fu.f_user ......FROM ............f_user....fu .....WHERE ............#f_deal_temp.usermodify_2...=.fu.name ..../*.Срок.*/ ....IF.@check_srok.>.0 ....BEGIN ......../*.Проставим.срок.=.0.для.всех,.кроме.Форекс.и.МБК.*/ ........UPDATE ................#f_deal_temp ...........SET ................srok.=.0 .........WHERE ................NOT.c_market.=.@c_market_mbk.AND ................NOT.c_market.=.@c_market_forex ......../*.Проставим.сроки.для.ЦБ.*/ ........UPDATE ................#f_deal_temp ...........SET ................date_pay......=.ISNULL(.fs_date_pay_1,.date_time_2.), ................date_delivery.=.ISNULL(.st_date_pay_1,.date_time_1.) .........WHERE ................c_market.IN.(.@c_market_stock,.@c_market_bond,.@c_market_bill.) ......../*.MIN(.дата.поставки,.дата.оплаты.).*/ ........UPDATE ................#f_deal_temp ...........SET ................date_min.=.CASE ................................WHEN.date_pay.<.date_delivery.THEN.date_pay ................................ELSE.date_delivery ...........................END .........WHERE ................c_market.IN.(.@c_market_stock,.@c_market_bond,.@c_market_bill.) ........UPDATE ................#f_deal_temp ...........SET ................srok.=.DATEDIFF(.dd,.do_date_time_1,.date_min.) .........WHERE ................c_market.IN.(.@c_market_stock,.@c_market_bond,.@c_market_bill.) ......../*.Корректировка.для.учета.выходных.дней.*/ ........UPDATE ................#f_deal_temp ...........SET ................srok.=.srok.-.( ................................SELECT ........................................COUNT(.*.) ..................................FROM ........................................f_holiday.fh .................................WHERE ........................................fh.date_time.BETWEEN.#f_deal_temp.do_date_time_1.and.#f_deal_temp.date_min.AND ........................................c_currency.=.@c_currency_national ..............................) ..........WHERE ................c_market.IN.(.@c_market_stock,.@c_market_bond,.@c_market_bill.).AND ................srok.!=.0 ......../*.Для.МБК.*/ ........UPDATE ................#f_deal_temp ...........SET ................srok.=.datediff(.dd,.date_time_1,.date_time_2.) .........WHERE ................c_market.=.@c_market_mbk ......../*.Для.ForEx.*/ ......../*.Установить.дату.создания.как.дату.перехода.статуса.(.fx_dt_make.-.заполняется.выше.).*/ ........UPDATE..#f_deal_temp ...........SET ................fx_dt_make.=.(.SELECT ........................................max(fo.date_time) .................................FROM ........................................f_oper..fo ................................WHERE ........................................#f_deal_temp.f_deal.=.fo.f_deal.....and ........................................fo.f_move..in.(select ................................................................f_move .........................................................from ................................................................f_move ........................................................where ................................................................c_dealstatus.=.2000.....and .............................................................c_d_c_dealstatus.=.3000 ......................................................) ...........................) .........WHERE ................c_market.=.@c_market_forex.AND ................NOT.#f_deal_temp.c_classificator.IN(.2,.3.) ......../*.Выбрать.максимальную.дату.валютирования.*/ ........UPDATE ................#f_deal_temp ...........SET ................fx_max_date.=.CASE ....................................WHEN.date_time_2.>.date_time_1.THEN.date_time_2 ....................................ELSE.date_time_1 ..............................END .........WHERE ................c_market.=.@c_market_forex ......../*.дата.валютирования.должны.быть.рабочим.днем.*/ ........WHILE.EXISTS( ........................SELECT ................................1 ..........................FROM ................................#f_deal_temp .........................WHERE ................................c_market.=.@c_market_forex.AND ................................fx_max_date.IN.( ........................................SELECT ................................................date_time ..........................................FROM ................................................f_holiday .........................................WHERE ................................................f_holiday.c_currency.=.@c_currency_national ...................................................) ....................) ........BEGIN ............UPDATE ....................#f_deal_temp ...............SET ....................fx_max_date.=.dateadd(.dd,.1,.fx_max_date.) .............WHERE ....................c_market.=.@c_market_forex.AND ....................fx_max_date.IN.( ........................................SELECT ................................................date_time ..........................................FROM ................................................f_holiday .........................................WHERE ................................................f_holiday.c_currency.=.@c_currency_national ......................................) ........END ......../*.Проставим.срок.*/ ........UPDATE ................#f_deal_temp ...........SET ................srok.=.datediff(.dd,.fx_dt_make,.fx_max_date.) .........WHERE ................c_market.=.@c_market_forex ......../* ............Вычесть.из.сроков.выходные.дни.(сроки.считаются.по.базовой.валюте.) ............Для.сделок.Today.этого.делать.не.нужно ........*/ ........UPDATE ................#f_deal_temp ...........SET ................srok.=.srok.-.( ................................SELECT ........................................COUNT(.*.) ..................................FROM ........................................f_holiday.fh .................................WHERE ........................................fh.date_time.BETWEEN.#f_deal_temp.fx_dt_make.and.#f_deal_temp.fx_max_date.AND ........................................c_currency.=.@c_currency_national ..............................) ..........WHERE ................c_market.=.@c_market_forex.AND ................NOT.srok.=.0 ........IF.@check_srok.=.1...../*.=.*/ ........BEGIN ............DELETE ....................#f_deal_temp .............WHERE ....................NOT.srok.=.@srok ........END ........ELSE.IF.@check_srok.=.2...../*.>.*/ ........BEGIN ............DELETE ....................#f_deal_temp .............WHERE ....................NOT.srok.>.@srok ........END ........ELSE.IF.@check_srok.=.3...../*.<.*/ ........BEGIN ............DELETE ....................#f_deal_temp .............WHERE ....................NOT.srok.<.@srok ........END ....END ..../*.Запрос.по.инициатору.*/ ....IF.@f_client12.>.0 ....BEGIN ......../*.Запрос.по.клиенту.портфеля.*/ ........IF.@f_client_4.>.0 ........BEGIN ............UPDATE ....................#f_deal_temp ...............SET ....................f_client12.=.f_client_2 .............WHERE ....................f_client_2.=.@f_client12....AND ....................( ........................( ............................f_client_2.=.f_client_1.....AND ............................fp_client_2.=.@f_client_4 ........................).OR ........................NOT.f_client_2.=.f_client_1 ....................) ........END ........ELSE ........BEGIN ............UPDATE ....................#f_deal_temp ...............SET ....................f_client12.=.f_client_2 .............WHERE ....................f_client_2.=.@f_client12 ........END ....END ....ELSE ....BEGIN ......../*.Запрос.по.клиенту.портфеля.*/ ........IF.@f_client_4.>.0 ........BEGIN ............UPDATE ....................#f_deal_temp ...............SET ....................f_client12.=.f_client_2 .............WHERE ....................fp_client_2.=.@f_client_4 ........END ....END /*-----.возвращаемые.поля.----------------------------------------------------------*/ err: ....SELECT ............f_deal, ............f_deal_reference, ............c_jurtype, ............c_market, ............c_classificator, ............c_classif_name, ............textcolor, ............c_dealstatus, ............f_path, ............number, ............number_jur, ............descr, ............rollover, ............commission, /*-----f_sum.-.первая.сумма.сделки.-------------------------------------------------*/ ............f_sum_1, ............f_sumtype_1, ............c_currency_1, ............currency_iso_1, ............date_time_1, ............value_1, /*-----f_member.-.первый.участник.сделки.-------------------------------------------*/ ............f_member_1, ............c_membertype_1, ............f_client_1, ............code_1, ............rtscode_1, ............rsbcode_1, ............firstname_1, ............f_portf_1, ............f_portf_code_1, /*-----f_sum.-.вторая.сумма.сделки.-------------------------------------------------*/ ............f_sum_2, ............f_sumtype_2, ............c_currency_2, ............currency_iso_2, ............date_time_2, ............value_2, /*-----f_member.-.второй.участник.сделки.-------------------------------------------*/ ............f_member_2, ............c_membertype_2, ............f_client_2, ............code_2, ............rtscode_2, ............rsbcode_2, ............firstname_2, ............f_portf_2, ............f_portf_code_2, /*-----f_sum.-.третья.сумма.сделки.-------------------------------------------------*/ ............f_sum_3, ............f_sumtype_3, ............c_currency_3, ............currency_iso_3, ............date_time_3, ............value_3, /*-----f_member.-.третий.участник.сделки.-------------------------------------------*/ ............f_member_3, ............c_membertype_3, ............f_client_3, ............code_3, ............rtscode_3, ............rsbcode_3, ............firstname_3, /*-----f_member.-.четвертый.участник.сделки.----------------------------------------*/ ............f_member_4, ............c_membertype_4, ............f_client_4, ............code_4, ............rtscode_4, ............rsbcode_4, ............firstname_4, /*-----создал.----------------------------------------------------------------------*/ ............f_user_1, ............usermake_1, ............userdescr_1, ............dt_make_1, /*-----последние.изменения.сделал.--------------------------------------------------*/ ............f_user_2, ............usermodify_2, ............userdescr_2, ............dt_modify_2, /*-----дополнительные.поля.---------------------------------------------------------*/ ............f_rate, ............rate, ............real_rate, ............revers, ............finans, ............market_name, ............c_opertype, ............c_metod, ............c_sumkind, ............f_account_1, ............f_account_2, ............f_account_3, ............f_pos_11, ............f_pos_12, ............f_pos_13, ............f_pos_21, ............f_pos_22, ............f_pos_23, /*-----не.возвращаемые.поля.--------------------------------------------------------*/ ............netting_value_1, ............netting_value_2, ............rollover_value_1, ............rollover_value_2, ............journal_l_jurnal, ............journal_f_deal, ............journal_f_d_f_deal, ............journal_add, ............f_instr_1, ............f_issue_1, ............f_instr_code_1, ............do_dognum_1, ............do_date_time_1, ............do_dogform_name_1, ............do_descr_1, ............do_dognum_2, ............do_date_time_2, ............do_dogform_name_2, ............do_descr_2, ............st_date_pay_1, ............st_date_begin_1, ............st_date_end_1, ............fs_date_pay_1, ............f_pricetype, ............f_pricetype_name, ............f_client12, ............@c_currency_12......c_currency_12, ............rate_pay, ............selectrow ......FROM ............#f_deal_temp ..ORDER.BY ............f_deal ..../*.Удаление.временной.таблицы.*/ ....DROP.TABLE.#f_deal_account ....DROP.TABLE.#f_deal_temp ..../*.Проставим.время.работы.процедуры.*/ END ... |
|||
|
:
Нравится:
Не нравится:
|
|||
|
|
|
14.05.2004, 18:24
|
|||
|---|---|---|---|
Как бороться с блокировками в ASE 12.5.0.3 |
|||
|
#18+
После запуска: sp_sysmon '00:10:00' go Сервер ASE через несколько минут вошел в глубокий штопор и умер так, что пришлось его перезапускать компьютер на котором он работал. Последнее что он выдал перед смертью на запуск sp_who было: Failed to allocate disk space for a work table in database 'tempdb'. You may be able to free up space by using the DUMP TRANsaction command, or you may want to extend the size of the database by using the ALTER DATABASE command. Дальше, что было я думаю рассказывать не стоит, через это проходили все :) Телефон расплавился, начальство пришло лично, ..... Что еще попробовать? Как бороться с блокировками? ... |
|||
|
:
Нравится:
Не нравится:
|
|||
|
|
|
14.05.2004, 23:05
|
|||
|---|---|---|---|
|
|||
Как бороться с блокировками в ASE 12.5.0.3 |
|||
|
#18+
>Размер tempdb 603 Mb = (3Mb Data and Log + 300 Data + 300 Log) Увеличить лог до 600Мб, вынести его на отдельный девайс. >Check - Truncate log on checkpoint Убрать, поставить порог примерно на 200Мб свободного места, а в нем dump tran tempdb with no_log. (можно просто поправит sp_tresholdaction в sybsystemprocs и поменять расположение последнего порога для tempdb) Желательно сделать отдельный кэш для tempdb. Если стартует несколько ядер, то можно поиграться с партициями кэшей. В подобной ситуации может оказаться виноват не тот процесс, который всех блокирует. Посмотри повнимательней, что делает 31-й процесс в первом примере и 61-й во втором. Часто подобная ситуация возникает когда какой-либо процесс проводит операции с большим объемом ввода-вывода в tempdb (например update больших временных таблиц), при этом все процессы, создающие временные таблицы выстраиваются в очередь и все жутко тормозит. ... |
|||
|
:
Нравится:
Не нравится:
|
|||
|
|
|
15.05.2004, 14:56
|
|||
|---|---|---|---|
Как бороться с блокировками в ASE 12.5.0.3 |
|||
|
#18+
Код: plaintext 1. 31 в первом и 61 во втором это я, - кто запускает sh_who, а в остальном все очень похоже на правду. Действительно начинает тормозить если пользователь запросил операции с большим объемом ввода-вывода, в tempdb (например update больших временных таблиц). - Прямо в яблочко. А если таких, в один момент пара, тройка, то база висит 100% две и более минуты. Можно постараться, как вчера, и вообще завалить базу. :( Код: plaintext Сегодня на работу меня не пустят, но в понедельник обязательно все это проделаю. ... |
|||
|
:
Нравится:
Не нравится:
|
|||
|
|
|
15.05.2004, 17:45
|
|||
|---|---|---|---|
|
|||
Как бороться с блокировками в ASE 12.5.0.3 |
|||
|
#18+
>Это как сделать, а всю tempdb в кэш засунуть можно? Как обычно, создаешь кэш: sp_cacheconfig "имя_кэша", "размер_кэша" перезагружаемся. При создании кэша лучше явно указать еденицу измерения, т.е. если 50МБ, то надо писать sp_cacheconfig "имя_кэша", "50M" Всю tempdb засунуть кэш конечно можно (в предельном варианте делают виртуальный диск в ОЗУ и кладут tempdb туда, но видеть такое не приходилось), но обычно столько свободной ОЗУ нет, да это и не эффективно, в твоем случае хватит 100-150Мб. Примерно 30-40 Мб можно отвести под 16Кб пул. sp_poolconfig "имя_кэша", "30M", "16K", "2K" После этого надо привязать новый кэш к tempdb, это можно сделать в Central, папка Caches, выбираем кэш Properties->Cache Bindings или sp_bindcache "имя_кэша","имя_базы" После окончаний всех операций с кэшами и tempdb сервак лучше перезагрузить еще раз. ... |
|||
|
:
Нравится:
Не нравится:
|
|||
|
|
|
17.05.2004, 11:20
|
|||
|---|---|---|---|
|
|||
Как бороться с блокировками в ASE 12.5.0.3 |
|||
|
#18+
Код: plaintext 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. Можно попробовать не на 10 минут, а хотя бы на 1-2... Также могу посоветовать (у самого так работает) для tempdb не делать разных девайсов для лога и данных... У меня две базы 2 и 3.5 Гб, и tempdb недавно только сделал 1Гб, до этого было 512Мб... То, что блокировки висят 2 минуты на апдейтах/делитах больших объемов данных - это вполне нормально, если сервер не очень мощный, а данных и юзеров, одновременно пытающихся что-то делать, много. Процессор один? Если один процессор на 16 активных пользователей ,то это не очень хорошо... Могу посоветовать сделать индекс(ы) на временной таблице #f_deal_temp по всем ключевым/используемым в связях полям. Если данных в таблицу насасывается много, то при операциях идет сканирование всей таблицы. Как следствие, идет интенсивный ввод/вывод + загрузка процессора. Индексы могут уменьшить время операций, число операций ввода/вывода - как следствие, должно уменьшиться время ожидания остальных процессов в блокировке. RAID стоит какой? Сколько и каких дисков? Как загружена дисовая подсистема? (Хотя бы просто посмотри - сильно винтами шуршит?) Какая при этом загрузка проца? 2_Sania Код: plaintext 1. 2. У меня, например, при создании 16К-пула (от 8 до 300 Мб пробовал) начинался жуткий ввод/вывод (винтами постоянно шуршал) и любой select выполнялся раза в 4 дольше... ASE 12.0, правда... Вот 4К-пул, мегов на 150 - дело хорошее. ... |
|||
|
:
Нравится:
Не нравится:
|
|||
|
|
|
17.05.2004, 12:04
|
|||
|---|---|---|---|
Как бороться с блокировками в ASE 12.5.0.3 |
|||
|
#18+
Код: plaintext 1. 2. 3. Стал перечитывать внимательнее, появился вопрос. А как поставить порог на 200Мб для tempdb ? sp_addthreshold tempdb, logsegment, 200, prc_dumptransaction Это правильно? Или лучше указать свою процедуру например? sp_addthreshold tempdb, logsegment, 200, prc_dumptransaction Но тогда почему ASE выдает ошибку: This threshold is too close to one or more existing thresholds. Thresholds must be no closer than 128 pages to each other. Если на tempdb процедура создана: CREATE PROCEDURE prc_dumptransaction @dbname varchar(30), @segmentname varchar(30), @spase_left int, @status int AS DUMP TRANSACTION tempdb with no_log ... |
|||
|
:
Нравится:
Не нравится:
|
|||
|
|
|
17.05.2004, 12:10
|
|||
|---|---|---|---|
Как бороться с блокировками в ASE 12.5.0.3 |
|||
|
#18+
Код: plaintext 1. ... |
|||
|
:
Нравится:
Не нравится:
|
|||
|
|
|
17.05.2004, 12:38
|
|||
|---|---|---|---|
Как бороться с блокировками в ASE 12.5.0.3 |
|||
|
#18+
Код: plaintext Да я это упустил: RAID - 5 пять винчестетов Процессоров 2 Дискового простанства 135 Gb Процессор в критические моменты занят на 60 - 80%% ... |
|||
|
:
Нравится:
Не нравится:
|
|||
|
|
|
17.05.2004, 12:45
|
|||
|---|---|---|---|
Как бороться с блокировками в ASE 12.5.0.3 |
|||
|
#18+
Получилось sp_sysmon '00:10:00' go DBCC.execution.completed..If.DBCC.printed.error.messages,.contact.a.user.with.System.Administrator.(SA).role. =============================================================================== ......Sybase.Adaptive.Server.Enterprise.System.Performance.Report =============================================================================== Server.Version:........Adaptive.Server.Enterprise/12.5.0.3/EBF.11449.ESD#4/P/NT Server.Name:...........Server.is.Unnamed Run.Date:..............May.17,.2004 Statistics.Cleared.at:.12:26:16 Statistics.Sampled.at:.12:31:16 Sample.Interval:.......00:05:00 =============================================================================== Kernel.Utilization ------------------ ..Your.Runnable.Process.Search.Count.is.set.to.2000 ..and.I/O.Polling.Process.Count.is.set.to.10 ..Engine.Busy.Utilization........CPU.Busy...I/O.Busy.......Idle ..------------------------.......--------...--------...-------- ....Engine.0........................0.0.%......0.0.%....100.0.% ..CPU.Yields.by.Engine............per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ....Engine.0.........................32.0.........274.3........9599.....100.0.% ..Network.Checks ....Non-Blocking....................659.7........5654.3......197901......91.2.% ....Blocking.........................64.0.........548.5.......19198.......8.8.% ..-------------------------..------------..------------..---------- ..Total.Network.I/O.Checks..........723.7........6202.8......217099 ..Avg.Net.I/Os.per.Check..............n/a...........n/a.....0.00000.......n/a ..Disk.I/O.Checks ....Total.Disk.I/O.Checks...........691.7........5928.6......207500.......n/a ....Checks.Returning.I/O..............0.0...........0.0...........0.......0.0.% =============================================================================== Worker.Process.Management ------------------------- ..................................per.sec......per.xact.......count..%.of.total .............................------------..------------..----------..---------- .Worker.Process.Requests ...Total.Requests.....................0.0...........0.0...........0.......n/a .Worker.Process.Usage ...Total.Used.........................0.0...........0.0...........0.......n/a ...Max.Ever.Used.During.Sample........0.0...........0.0...........0.......n/a .Memory.Requests.for.Worker.Processes ...Total.Requests.....................0.0...........0.0...........0.......n/a =============================================================================== Parallel.Query.Management ------------------------- ..Parallel.Query.Usage............per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ..Total.Parallel.Queries..............0.0...........0.0...........0.......n/a ..Merge.Lock.Requests.............per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ..Total.#.of.Requests.................0.0...........0.0...........0.......n/a ..Sort.Buffer.Waits...............per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ..Total.#.of.Waits....................0.0...........0.0...........0.......n/a =============================================================================== Task.Management...................per.sec......per.xact.......count..%.of.total ---------------------------..------------..------------..----------..---------- ..Connections.Opened..................0.0...........0.0...........0.......n/a ..Task.Context.Switches.by.Engine ....Engine.0..........................0.6...........5.0.........175.....100.0.% ..Task.Context.Switches.Due.To: ....Voluntary.Yields..................0.3...........2.1..........75......42.9.% ....Cache.Search.Misses...............0.0...........0.0...........0.......0.0.% ....System.Disk.Writes................0.0...........0.0...........0.......0.0.% ....I/O.Pacing........................0.0...........0.0...........0.......0.0.% ....Logical.Lock.Contention...........0.0...........0.0...........0.......0.0.% ....Address.Lock.Contention...........0.0...........0.0...........0.......0.0.% ....Latch.Contention..................0.0...........0.0...........0.......0.0.% ....Log.Semaphore.Contention..........0.0...........0.0...........0.......0.0.% ....PLC.Lock.Contention...............0.0...........0.0...........0.......0.0.% ....Group.Commit.Sleeps...............0.0...........0.0...........0.......0.0.% ....Last.Log.Page.Writes..............0.0...........0.0...........0.......0.0.% ....Modify.Conflicts..................0.0...........0.0...........0.......0.0.% ....I/O.Device.Contention.............0.0...........0.0...........0.......0.0.% ....Network.Packet.Received...........0.0...........0.0...........0.......0.0.% ....Network.Packet.Sent...............0.0...........0.0...........0.......0.0.% ....Other.Causes......................0.3...........2.9.........100......57.1.% =============================================================================== Application.Management ---------------------- ..Application.Statistics.Summary.(All.Applications) ..------------------------------------------------- ..Priority.Changes................per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ....To.High.Priority..................0.0...........0.0...........0.......0.0.% ....To.Medium.Priority................0.1...........0.4..........15......50.0.% ....To.Low.Priority...................0.1...........0.4..........15......50.0.% ..-------------------------..------------..------------..---------- ..Total.Priority.Changes..............0.1...........0.9..........30 ..Allotted.Slices.Exhausted.......per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ....High.Priority.....................0.0...........0.0...........0.......0.0.% ....Medium.Priority...................0.0...........0.0...........1.....100.0.% ....Low.Priority......................0.0...........0.0...........0.......0.0.% ..-------------------------..------------..------------..---------- ..Total.Slices.Exhausted..............0.0...........0.0...........1 ..Skipped.Tasks.By.Engine.........per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ..Total.Engine.Skips..................0.0...........0.0...........0.......n/a ..Engine.Scope.Changes................0.0...........0.0...........0.......n/a =============================================================================== ESP.Management....................per.sec......per.xact.......count..%.of.total ---------------------------..------------..------------..----------..---------- ..ESP.Requests........................0.0...........0.0...........0.......n/a =============================================================================== Housekeeper.Task.Activity ------------------------- ..................................per.sec......per.xact.......count..%.of.total .............................------------..------------..---------- Buffer.Cache.Washes ..Clean..............................0.5...........3.9.........135......99.3.% ..Dirty..............................0.0...........0.0...........1.......0.7.% .............................------------..------------..---------- Total.Washes.........................0.5...........3.9.........136 Garbage.Collections..................0.2...........1.7..........60.......n/a Pages.Processed.in.GC................0.0...........0.0...........0.......n/a Statistics.Updates...................0.0...........0.2...........6.......n/a =============================================================================== Monitor.Access.to.Executing.SQL ------------------------------- ..................................per.sec......per.xact.......count..%.of.total .............................------------..------------..----------..---------- .Waits.on.Execution.Plans............0.0...........0.0...........0.......n/a .Number.of.SQL.Text.Overflows........0.0...........0.0...........0.......n/a .Maximum.SQL.Text.Requested..........n/a...........n/a...........0.......n/a ..(since.beginning.of.sample) =============================================================================== Transaction.Profile ------------------- ..Transaction.Summary.............per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ....Committed.Xacts...................0.1...........n/a..........35.....n/a ..Transaction.Detail..............per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ....Total.Rows.Affected...............0.0...........0.0...........0.......n/a =============================================================================== Transaction.Management ---------------------- ..ULC.Flushes.to.Xact.Log.........per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ....by.Full.ULC.......................0.0...........0.0...........0.......0.0.% ....by.End.Transaction................0.0...........0.0...........0.......0.0.% ....by.Change.of.Database.............0.0...........0.0...........0.......0.0.% ....by.Single.Log.Record..............0.0...........0.0...........0.......0.0.% ....by.Other..........................0.1...........1.0..........35.....100.0.% ..-------------------------..------------..------------..---------- ..Total.ULC.Flushes...................0.1...........1.0..........35 ..ULC.Log.Records.....................0.0...........0.0...........0.......n/a ..Max.ULC.Size.During.Sample..........n/a...........n/a...........0.......n/a ..ULC.Semaphore.Requests ....Granted...........................0.5...........4.0.........140.....100.0.% ....Waited............................0.0...........0.0...........0.......0.0.% ..-------------------------..------------..------------..---------- ..Total.ULC.Semaphore.Req.............0.5...........4.0.........140 ..Log.Semaphore.Requests ....Granted...........................0.1...........1.0..........35.....100.0.% ....Waited............................0.0...........0.0...........0.......0.0.% ..-------------------------..------------..------------..---------- ..Total.Log.Semaphore.Req.............0.1...........1.0..........35 ..Transaction.Log.Writes..............0.0...........0.0...........0.......n/a ..Transaction.Log.Alloc...............0.0...........0.0...........0.......n/a =============================================================================== Index.Management ---------------- ..Nonclustered.Maintenance........per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ....Ins/Upd.Requiring.Maint...........0.0...........0.0...........0.......n/a ......#.of.NC.Ndx.Maint...............0.0...........0.0...........0.......n/a ....Deletes.Requiring.Maint...........0.0...........0.0...........0.......n/a ......#.of.NC.Ndx.Maint...............0.0...........0.0...........0.......n/a ....RID.Upd.from.Clust.Split..........0.0...........0.0...........0.......n/a ......#.of.NC.Ndx.Maint...............0.0...........0.0...........0.......n/a ....Upd/Del.DOL.Req.Maint.............0.0...........0.0...........0.......n/a ......#.of.DOL.Ndx.Maint..............0.0...........0.0...........0.......n/a ..Page.Splits.........................0.0...........0.0...........0.......n/a ..Page.Shrinks........................0.0...........0.0...........0.......n/a ..Index.Scans.....................per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ....Total.Scans.......................0.0...........0.0...........0.......n/a =============================================================================== Metadata.Cache.Management ------------------------- ..Metadata.Cache.Summary.........per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ..Open.Object.Usage ....Active............................n/a...........n/a..........35.......n/a ....Max.Ever.Used.Since.Boot..........n/a...........n/a..........35.......n/a ....Free..............................n/a...........n/a.........465.......n/a ....Reuse.Requests ......Succeeded.......................n/a...........n/a...........0.......n/a ......Failed..........................n/a...........n/a...........0.......n/a ..Open.Index.Usage ....Active............................n/a...........n/a..........12.......n/a ....Max.Ever.Used.Since.Boot..........n/a...........n/a..........12.......n/a ....Free..............................n/a...........n/a.........488.......n/a ....Reuse.Requests ......Succeeded.......................n/a...........n/a...........0.......n/a ......Failed..........................n/a...........n/a...........0.......n/a ..Open.Database.Usage ....Active............................n/a...........n/a...........8.......n/a ....Max.Ever.Used.Since.Boot..........n/a...........n/a...........8.......n/a ....Free..............................n/a...........n/a...........4.......n/a ....Reuse.Requests ......Succeeded.......................n/a...........n/a...........0.......n/a ......Failed..........................n/a...........n/a...........0.......n/a ..Object.Manager.Spinlock.Contention..n/a...........n/a..........n/a......0.0.% ..Object.Spinlock.Contention..........n/a...........n/a..........n/a......0.0.% ..Index.Spinlock.Contention...........n/a...........n/a..........n/a......0.0.% ..Hash.Spinlock.Contention............n/a...........n/a..........n/a......0.0.% =============================================================================== Lock.Management --------------- ..Lock.Summary....................per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ..Total.Lock.Requests.................0.1...........1.0..........35.......n/a ..Avg.Lock.Contention.................0.0...........0.0...........0.......0.0.% ..Deadlock.Percentage.................0.0...........0.0...........0.......0.0.% ..Lock.Detail.....................per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ..Table.Lock.Hashtable ....Lookups...........................0.0...........0.0...........0.......n/a ....Spinlock.Contention...............n/a...........n/a.........n/a.......0.0.% ..Exclusive.Table ....Total.EX-Table.Requests...........0.0...........0.0...........0.......n/a ..Shared.Table ....Total.SH-Table.Requests...........0.0...........0.0...........0.......n/a ..Exclusive.Intent ....Total.EX-Intent.Requests..........0.0...........0.0...........0.......n/a ..Shared.Intent ....Total.SH-Intent.Requests..........0.0...........0.0...........0.......n/a ..Page.&.Row.Lock.HashTable ....Lookups...........................0.0...........0.0...........0.......n/a ....Spinlock.Contention...............n/a...........n/a.........n/a.......0.0.% ..Exclusive.Page ....Total.EX-Page.Requests............0.0...........0.0...........0.......n/a ..Update.Page ....Total.UP-Page.Requests............0.0...........0.0...........0.......n/a ..Shared.Page ....Total.SH-Page.Requests............0.0...........0.0...........0.......n/a ..Exclusive.Row ....Total.EX-Row.Requests.............0.0...........0.0...........0.......n/a ..Update.Row ....Total.UP-Row.Requests.............0.0...........0.0...........0.......n/a ..Shared.Row ....Total.SH-Row.Requests.............0.0...........0.0...........0.......n/a ..Next-Key ....Total.Next-Key.Requests...........0.0...........0.0...........0.......n/a ..Address.Lock.Hashtable ....Lookups...........................0.1...........1.0..........35.......n/a ....Avg.Chain.Length..................n/a...........n/a.....0.00000.......n/a ....Spinlock.Contention...............n/a...........n/a.........n/a.......0.0.% ..Exclusive.Address ....Granted...........................0.1...........1.0..........35.....100.0.% ....Waited............................0.0...........0.0...........0.......0.0.% ..-------------------------..------------..------------..----------..---------- ..Total.EX-Address.Requests...........0.1...........1.0..........35.....100.0.% ..Shared.Address ....Total.SH-Address.Requests.........0.0...........0.0...........0.......n/a ..Last.Page.Locks.on.Heaps ....Total.Last.Pg.Locks...............0.0...........0.0...........0.......n/a ..Deadlocks.by.Lock.Type..........per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ..Total.Deadlocks.....................0.0...........0.0...........0.......n/a ..Deadlock.Detection ....Deadlock.Searches.................0.0...........0.0...........0.......n/a ..Lock.Promotions ....Total.Lock.Promotions.............0.0...........0.0...........0.......n/a ..Lock.Timeouts.by.Lock.Type......per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ..Total.Timeouts......................0.0...........0.0...........0.......n/a =============================================================================== Data.Cache.Management --------------------- ..Cache.Statistics.Summary.(All.Caches) ..------------------------------------- ..................................per.sec......per.xact.......count..%.of.total .............................------------..------------..----------..---------- ....Cache.Search.Summary ......................................0.0...........0.0...........0.......n/a ....Cache.Turnover ......Buffers.Grabbed.................0.0...........0.0...........0.......n/a ....Cache.Strategy.Summary ......................................0.0...........0.0...........0.......n/a ....Large.I/O.Usage ......................................0.0...........0.0...........0.......n/a ....Large.I/O.Effectiveness ......Pages.by.Lrg.I/O.Cached.........0.0...........0.0...........0.......n/a ....Asynchronous.Prefetch.Activity ......................................0.0...........0.0...........0.......n/a ....Other.Asynchronous.Prefetch.Statistics ......APFs.Used.......................0.0...........0.0...........0.......n/a ......APF.Waits.for.I/O...............0.0...........0.0...........0.......n/a ......APF.Discards....................0.0...........0.0...........0.......n/a ....Dirty.Read.Behavior ......Page.Requests...................0.0...........0.0...........0.......n/a ------------------------------------------------------------------------------- ..Cache:.default.data.cache ..................................per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ....Spinlock.Contention...............n/a...........n/a.........n/a.......0.0.% ....Cache.Searches ......Total.Cache.Searches............0.0...........0.0...........0.......n/a ..-------------------------..------------..------------..---------- ....Total.Cache.Searches..............0.0...........0.0...........0 ....Pool.Turnover.....................0.0...........0.0...........0.......n/a ....Buffer.Wash.Behavior ......Statistics.Not.Available.-.No.Buffers.Entered.Wash.Section.Yet ....Cache.Strategy ......Statistics.Not.Available.-.No.Buffers.Displaced.Yet ....Large.I/O.Usage ......Total.Large.I/O.Requests........0.0...........0.0...........0.......n/a ....Large.I/O.Detail ......No.Large.Pool(s).In.This.Cache ....Dirty.Read.Behavior .......Page.Requests...............0.0...........0.0...........0.......n/a =============================================================================== Procedure.Cache.Management........per.sec......per.xact.......count..%.of.total ---------------------------..------------..------------..----------..---------- ..Procedure.Requests..................0.0...........0.0...........0.......n/a =============================================================================== Memory.Management.................per.sec......per.xact.......count..%.of.total ---------------------------..------------..------------..----------..---------- ..Pages.Allocated.....................0.1...........1.1..........37.......n/a ..Pages.Released......................0.1...........1.1..........37.......n/a =============================================================================== Recovery.Management ------------------- ..Checkpoints.....................per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ..Total.Checkpoints...................0.0...........0.0...........0.......n/a =============================================================================== Disk.I/O.Management ------------------- ..Max.Outstanding.I/Os............per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ....Server............................n/a...........n/a...........0.......n/a ....Engine.0..........................n/a...........n/a...........0.......n/a ..I/Os.Delayed.by ....Disk.I/O.Structures...............n/a...........n/a...........0.......n/a ....Server.Config.Limit...............n/a...........n/a...........0.......n/a ....Engine.Config.Limit...............n/a...........n/a...........0.......n/a ....Operating.System.Limit............n/a...........n/a...........0.......n/a ..Total.Requested.Disk.I/Os...........0.0...........0.0...........0 ..Completed.Disk.I/O's ....Total.Completed.I/Os..............0.0...........0.0...........0.......n/a ..-------------------------..------------..------------..---------- ..Total.Completed.I/Os................0.0...........0.0...........0 ..Device.Activity.Detail ..---------------------- ....No.Disk.I/O.in.Given.Sample.Period =============================================================================== Network.I/O.Management ---------------------- ..Total.Network.I/O.Requests..........0.0...........0.0...........0.......n/a ..Total.TDS.Packets.Received......per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ..Total.TDS.Packets.Rec'd.............0.0...........0.0...........0.......n/a ..Total.Bytes.Received............per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ..Total.Bytes.Rec'd...................0.0...........0.0...........0.......n/a ..----------------------------------------------------------------------------- ..Total.TDS.Packets.Sent..........per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ..Total.TDS.Packets.Sent..............0.0...........0.0...........0.......n/a ..Total.Bytes.Sent................per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ..Total.Bytes.Sent....................0.0...........0.0...........0.......n/a ===============================.End.of.Report.================================= ... |
|||
|
:
Нравится:
Не нравится:
|
|||
|
|
|
17.05.2004, 14:33
|
|||
|---|---|---|---|
|
|||
Как бороться с блокировками в ASE 12.5.0.3 |
|||
|
#18+
А ты во время загрузки sp_sysmon дергал? а то что-то не так как-то... А почему у тебя ASE'у только один процессор отведен? Если процов 2, то и работать оба должны. 8) sp_configure 'max online engines',2 sp_configure 'min online engines',2 HyperTreading включен? Если да, то sp_configure 'max online engines',4 sp_configure 'min online engines',3 После вышеописанного надо сервер перегружать. рейд аппаратный? IMHO, лучше из пяти дисков сделать следующее - четыре диска в 10 raid, а 5-й - в hot spare (в горячую замену). Индексы не пробовал на #f_deal_temp создавать? ... |
|||
|
:
Нравится:
Не нравится:
|
|||
|
|
|
17.05.2004, 14:33
|
|||
|---|---|---|---|
Как бороться с блокировками в ASE 12.5.0.3 |
|||
|
#18+
А это сразу после перезапуска сервера: sp_sysmon '00:10:00' go Но на первый взгляд работать лучше не стало :( DBCC.execution.completed..If.DBCC.printed.error.messages,.contact.a.user.with.System.Administrator.(SA).role. =============================================================================== ......Sybase.Adaptive.Server.Enterprise.System.Performance.Report =============================================================================== Server.Version:........Adaptive.Server.Enterprise/12.5.0.3/EBF.11449.ESD#4/P/NT Server.Name:...........Server.is.Unnamed Run.Date:..............May.17,.2004 Statistics.Cleared.at:.14:12:58 Statistics.Sampled.at:.14:22:58 Sample.Interval:.......00:10:00 =============================================================================== Kernel.Utilization ------------------ ..Your.Runnable.Process.Search.Count.is.set.to.2000 ..and.I/O.Polling.Process.Count.is.set.to.10 ..Engine.Busy.Utilization........CPU.Busy...I/O.Busy.......Idle ..------------------------.......--------...--------...-------- ....Engine.0........................1.5.%.....80.2.%.....18.3.% ....Engine.1........................3.2.%......4.8.%.....92.0.% ..------------------------.......--------...--------...-------- ..Summary...........Total...........4.7.%.....85.0.%....110.3.% ..................Average...........2.4.%.....42.5.%.....55.1.% ..CPU.Yields.by.Engine............per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ....Engine.0..........................6.2...........4.4........3716......17.2.% ....Engine.1.........................29.8..........21.1.......17874......82.8.% ..-------------------------..------------..------------..---------- ..Total.CPU.Yields...................36.0..........25.5.......21590 ..Network.Checks ....Non-Blocking.................618336.1......438536.3...371001683.....100.0.% ....Blocking.........................71.6..........50.8.......42982.......0.0.% ..-------------------------..------------..------------..---------- ..Total.Network.I/O.Checks.......618407.8......438587.1...371044665 ..Avg.Net.I/Os.per.Check..............n/a...........n/a.....0.00003.......n/a ..Disk.I/O.Checks ....Total.Disk.I/O.Checks........310912.9......220505.6...186547717.......n/a ....Checks.Returning.I/O.........307527.9......218104.9...184516719......98.9.% ....Avg.Disk.I/Os.Returned............n/a...........n/a.....0.00047.......n/a ..Tuning.Recommendations.for.Kernel.Utilization ..--------------------------------------------- ..-.Consider.decreasing.the.'runnable.process.search.count' ....configuration.parameter.if.you.require.the.CPU's.on ....the.machine.to.be.used.for.other.applications. =============================================================================== Worker.Process.Management ------------------------- ..................................per.sec......per.xact.......count..%.of.total .............................------------..------------..----------..---------- .Worker.Process.Requests ...Total.Requests.....................0.0...........0.0...........0.......n/a .Worker.Process.Usage ...Total.Used.........................0.0...........0.0...........0.......n/a ...Max.Ever.Used.During.Sample........0.0...........0.0...........0.......n/a .Memory.Requests.for.Worker.Processes ...Total.Requests.....................0.0...........0.0...........0.......n/a =============================================================================== Parallel.Query.Management ------------------------- ..Parallel.Query.Usage............per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ..Total.Parallel.Queries..............0.0...........0.0...........0.......n/a ..Merge.Lock.Requests.............per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ..Total.#.of.Requests.................0.0...........0.0...........0.......n/a ..Sort.Buffer.Waits...............per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ..Total.#.of.Waits....................0.0...........0.0...........0.......n/a =============================================================================== Task.Management...................per.sec......per.xact.......count..%.of.total ---------------------------..------------..------------..----------..---------- ..Connections.Opened..................0.0...........0.0...........8.......n/a ..Task.Context.Switches.by.Engine ....Engine.0.........................21.4..........15.2.......12867......42.9.% ....Engine.1.........................28.6..........20.3.......17146......57.1.% ..-------------------------..------------..------------..---------- ....Total.Task.Switches:.............50.0..........35.5.......30013 ..Task.Context.Switches.Due.To: ....Voluntary.Yields..................1.5...........1.0.........878.......2.9.% ....Cache.Search.Misses...............9.1...........6.4........5434......18.1.% ....System.Disk.Writes................0.1...........0.0..........35.......0.1.% ....I/O.Pacing.......................13.0...........9.2........7783......25.9.% ....Logical.Lock.Contention...........0.0...........0.0...........8.......0.0.% ....Address.Lock.Contention...........0.0...........0.0...........0.......0.0.% ....Latch.Contention..................0.0...........0.0...........1.......0.0.% ....Log.Semaphore.Contention..........0.3...........0.2.........151.......0.5.% ....PLC.Lock.Contention...............0.0...........0.0...........0.......0.0.% ....Group.Commit.Sleeps...............3.6...........2.6........2183.......7.3.% ....Last.Log.Page.Writes..............0.0...........0.0..........23.......0.1.% ....Modify.Conflicts..................0.2...........0.1.........118.......0.4.% ....I/O.Device.Contention.............0.0...........0.0...........0.......0.0.% ....Network.Packet.Received...........1.9...........1.4........1150.......3.8.% ....Network.Packet.Sent..............14.2..........10.1........8522......28.4.% ....Other.Causes......................6.2...........4.4........3727......12.4.% =============================================================================== Application.Management ---------------------- ..Application.Statistics.Summary.(All.Applications) ..------------------------------------------------- ..Priority.Changes................per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ....To.High.Priority..................0.3...........0.2.........166......13.9.% ....To.Medium.Priority................1.0...........0.7.........600......50.3.% ....To.Low.Priority...................0.7...........0.5.........427......35.8.% ..-------------------------..------------..------------..---------- ..Total.Priority.Changes..............2.0...........1.4........1193 ..Allotted.Slices.Exhausted.......per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ....High.Priority.....................0.0...........0.0...........0.......0.0.% ....Medium.Priority...................0.4...........0.3.........241.....100.0.% ....Low.Priority......................0.0...........0.0...........0.......0.0.% ..-------------------------..------------..------------..---------- ..Total.Slices.Exhausted..............0.4...........0.3.........241 ..Skipped.Tasks.By.Engine.........per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ..Total.Engine.Skips..................0.0...........0.0...........0.......n/a ..Engine.Scope.Changes................0.0...........0.0...........0.......n/a =============================================================================== ESP.Management....................per.sec......per.xact.......count..%.of.total ---------------------------..------------..------------..----------..---------- ..ESP.Requests........................0.0...........0.0...........0.......n/a =============================================================================== Housekeeper.Task.Activity ------------------------- ..................................per.sec......per.xact.......count..%.of.total .............................------------..------------..---------- Buffer.Cache.Washes ..Clean.............................26.3..........18.6.......15756......96.2.% ..Dirty..............................1.0...........0.7.........615.......3.8.% .............................------------..------------..---------- Total.Washes........................27.3..........19.4.......16371 Garbage.Collections..................0.4...........0.3.........238.......n/a Pages.Processed.in.GC................0.0...........0.0...........0.......n/a Statistics.Updates...................0.2...........0.1.........106.......n/a =============================================================================== Monitor.Access.to.Executing.SQL ------------------------------- ..................................per.sec......per.xact.......count..%.of.total .............................------------..------------..----------..---------- .Waits.on.Execution.Plans............0.0...........0.0...........0.......n/a .Number.of.SQL.Text.Overflows........0.0...........0.0...........0.......n/a .Maximum.SQL.Text.Requested..........n/a...........n/a...........0.......n/a ..(since.beginning.of.sample) =============================================================================== Transaction.Profile ------------------- ..Transaction.Summary.............per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ....Committed.Xacts...................1.4...........n/a.........846.....n/a ..Transaction.Detail..............per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ....Inserts ......APL.Heap.Table................188.0.........133.3......112794......98.2.% ......APL.Clustered.Table.............3.3...........2.4........2000.......1.7.% ......Data.Only.Lock.Table............0.1...........0.1..........85.......0.1.% ..-------------------------..------------..------------..----------..---------- ....Total.Rows.Inserted.............191.5.........135.8......114879......26.2.% ....Updates ......APL.Deferred...................35.6..........25.3.......21371.......7.1.% ......APL.Direct.In-place............23.0..........16.3.......13819.......4.6.% ......APL.Direct.Cheap..............441.9.........313.4......265141......88.3.% ......APL.Direct.Expensive............0.0...........0.0...........0.......0.0.% ......DOL.Deferred....................0.0...........0.0...........0.......0.0.% ......DOL.Direct......................0.0...........0.0...........0.......0.0.% ..-------------------------..------------..------------..----------..---------- ....Total.Rows.Updated..............500.6.........355.0......300331......68.4.% ....Data.Only.Locked.Updates ......Total.Rows.Updated..............0.0...........0.0...........0.......n/a ..-------------------------..------------..------------..----------..---------- ....Total.DOL.Rows.Updated............0.0...........0.0...........0.......0.0.% ....Deletes ......APL.Deferred....................8.2...........5.8........4942......20.9.% ......APL.Direct.....................31.0..........22.0.......18579......78.7.% ......DOL.............................0.1...........0.1..........83.......0.4.% ..-------------------------..------------..------------..----------..---------- ....Total.Rows.Deleted...............39.3..........27.9.......23604.......5.4.% ..=========================..============..============..========== ....Total.Rows.Affected.............731.4.........518.7......438814 =============================================================================== Transaction.Management ---------------------- ..ULC.Flushes.to.Xact.Log.........per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ....by.Full.ULC.....................166.4.........118.0.......99813......87.9.% ....by.End.Transaction................1.1...........0.8.........659.......0.6.% ....by.Change.of.Database.............0.0...........0.0...........5.......0.0.% ....by.Single.Log.Record.............21.4..........15.2.......12826......11.3.% ....by.Other..........................0.4...........0.3.........260.......0.2.% ..-------------------------..------------..------------..---------- ..Total.ULC.Flushes.................189.3.........134.2......113563 ..ULC.Log.Records..................1111.2.........788.1......666745.......n/a ..Max.ULC.Size.During.Sample..........n/a...........n/a........2048.......n/a ..ULC.Semaphore.Requests ....Granted........................2175.4........1542.9.....1305262.....100.0.% ....Waited............................0.0...........0.0...........0.......0.0.% ..-------------------------..------------..------------..---------- ..Total.ULC.Semaphore.Req..........2175.4........1542.9.....1305262 ..Log.Semaphore.Requests ....Granted.........................225.0.........159.6......135023......99.9.% ....Waited............................0.3...........0.2.........151.......0.1.% ..-------------------------..------------..------------..---------- ..Total.Log.Semaphore.Req...........225.3.........159.8......135174 ..Transaction.Log.Writes............120.1..........85.2.......72049.......n/a ..Transaction.Log.Alloc.............187.6.........133.0......112554.......n/a ..Avg.#.Writes.per.Log.Page...........n/a...........n/a.....0.64013.......n/a ..Tuning.Recommendations.for.Transaction.Management ..------------------------------------------------- ..-.Consider.increasing.the.'user.log.cache.size' ....configuration.parameter. =============================================================================== Index.Management ---------------- ..Nonclustered.Maintenance........per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ....Ins/Upd.Requiring.Maint...........0.2...........0.1.........102.......n/a ......#.of.NC.Ndx.Maint...............0.2...........0.1.........102.......n/a ......Avg.NC.Ndx.Maint./.Op...........n/a...........n/a.....1.00000.......n/a ....Deletes.Requiring.Maint...........0.2...........0.1..........98.......n/a ......#.of.NC.Ndx.Maint...............0.2...........0.1..........98.......n/a ......Avg.NC.Ndx.Maint./.Op...........n/a...........n/a.....1.00000.......n/a ....RID.Upd.from.Clust.Split..........0.0...........0.0...........0.......n/a ......#.of.NC.Ndx.Maint...............0.0...........0.0...........0.......n/a ....Upd/Del.DOL.Req.Maint.............0.1...........0.1..........83.......n/a ......#.of.DOL.Ndx.Maint..............0.1...........0.1..........83.......n/a ......Avg.DOL.Ndx.Maint./.Op..........n/a...........n/a.....1.00000.......n/a ..Page.Splits.........................0.0...........0.0...........0.......n/a ..Page.Shrinks........................0.0...........0.0...........0.......n/a ..Index.Scans.....................per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ....Ascending.Scans.................866.1.........614.3......519680......99.4.% ....DOL.Ascending.Scans...............4.8...........3.4........2895.......0.6.% ....Descending.Scans..................0.3...........0.2.........195.......0.0.% ....DOL.Descending.Scans..............0.0...........0.0...........0.......0.0.% .............................------------..------------..---------- ....Total.Scans.....................871.3.........617.9......522770 =============================================================================== Metadata.Cache.Management ------------------------- ..Metadata.Cache.Summary.........per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ..Open.Object.Usage ....Active............................n/a...........n/a.........197.......n/a ....Max.Ever.Used.Since.Boot..........n/a...........n/a.........197.......n/a ....Free..............................n/a...........n/a.........803.......n/a ....Reuse.Requests ......Succeeded.......................n/a...........n/a...........0.......n/a ......Failed..........................n/a...........n/a...........0.......n/a ..Open.Index.Usage ....Active............................n/a...........n/a.........313.......n/a ....Max.Ever.Used.Since.Boot..........n/a...........n/a.........313.......n/a ....Free..............................n/a...........n/a.........187.......n/a ....Reuse.Requests ......Succeeded.......................n/a...........n/a...........0.......n/a ......Failed..........................n/a...........n/a...........0.......n/a ..Open.Database.Usage ....Active............................n/a...........n/a...........7.......n/a ....Max.Ever.Used.Since.Boot..........n/a...........n/a...........7.......n/a ....Free..............................n/a...........n/a...........5.......n/a ....Reuse.Requests ......Succeeded.......................n/a...........n/a...........0.......n/a ......Failed..........................n/a...........n/a...........0.......n/a ..Object.Manager.Spinlock.Contention..n/a...........n/a.........n/a.......0.0.% ..Object.Spinlock.Contention..........n/a...........n/a.........n/a.......0.0.% ..Index.Spinlock.Contention...........n/a...........n/a.........n/a.......0.0.% ..Hash.Spinlock.Contention............n/a...........n/a.........n/a.......0.0.% =============================================================================== Lock.Management --------------- ..Lock.Summary....................per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ..Total.Lock.Requests..............4979.2........3531.4.....2987547.......n/a ..Avg.Lock.Contention.................0.0...........0.0...........8.......0.0.% ..Deadlock.Percentage.................0.0...........0.0...........0.......0.0.% ..Lock.Detail.....................per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ..Table.Lock.Hashtable ....Lookups..........................15.4..........10.9........9232.......n/a ....Avg.Chain.Length..................n/a...........n/a.....0.04528.......n/a ....Spinlock.Contention...............n/a...........n/a.........n/a.......0.0.% ..Exclusive.Table ....Granted...........................3.0...........2.1........1810......99.9.% ....Waited............................0.0...........0.0...........2.......0.1.% ..-------------------------..------------..------------..----------..---------- ..Total.EX-Table.Requests.............3.0...........2.1........1812.......0.1.% ..Shared.Table ....Granted...........................0.4...........0.3.........263.....100.0.% ....Waited............................0.0...........0.0...........0.......0.0.% ..-------------------------..------------..------------..----------..---------- ..Total.SH-Table.Requests.............0.4...........0.3.........263.......0.0.% ..Exclusive.Intent ....Granted...........................0.9...........0.6.........538......99.8.% ....Waited............................0.0...........0.0...........1.......0.2.% ..-------------------------..------------..------------..----------..---------- ..Total.EX-Intent.Requests............0.9...........0.6.........539.......0.0.% ..Shared.Intent ....Granted..........................10.7...........7.6........6395.....100.0.% ....Waited............................0.0...........0.0...........0.......0.0.% ..-------------------------..------------..------------..----------..---------- ..Total.SH-Intent.Requests...........10.7...........7.6........6395.......0.2.% ..Page.&.Row.Lock.HashTable ....Lookups........................3273.8........2321.8.....1964271.......n/a ....Avg.Chain.Length..................n/a...........n/a.....0.00021.......n/a ....Spinlock.Contention...............n/a...........n/a.........n/a.......0.0.% ..Exclusive.Page ....Granted...........................1.5...........1.1.........898......99.9.% ....Waited............................0.0...........0.0...........1.......0.1.% ..-------------------------..------------..------------..----------..---------- ..Total.EX-Page.Requests..............1.5...........1.1.........899.......0.0.% ..Update.Page ....Granted...........................1.1...........0.8.........677.....100.0.% ....Waited............................0.0...........0.0...........0.......0.0.% ..-------------------------..------------..------------..----------..---------- ..Total.UP-Page.Requests..............1.1...........0.8.........677.......0.0.% ..Shared.Page ....Granted........................3197.8........2268.0.....1918691.....100.0.% ....Waited............................0.0...........0.0...........4.......0.0.% ..-------------------------..------------..------------..----------..---------- ..Total.SH-Page.Requests...........3197.8........2268.0.....1918695......64.2.% ..Exclusive.Row ....Granted...........................0.3...........0.2.........194.....100.0.% ....Waited............................0.0...........0.0...........0.......0.0.% ..-------------------------..------------..------------..----------..---------- ..Total.EX-Row.Requests...............0.3...........0.2.........194.......0.0.% ..Update.Row ....Granted...........................0.1...........0.1..........83.....100.0.% ....Waited............................0.0...........0.0...........0.......0.0.% ..-------------------------..------------..------------..----------..---------- ..Total.UP-Row.Requests...............0.1...........0.1..........83.......0.0.% ..Shared.Row ....Granted..........................65.3..........46.3.......39154.....100.0.% ....Waited............................0.0...........0.0...........0.......0.0.% ..-------------------------..------------..------------..----------..---------- ..Total.SH-Row.Requests..............65.3..........46.3.......39154.......1.3.% ..Next-Key ....Total.Next-Key.Requests...........0.0...........0.0...........0.......n/a ..Address.Lock.Hashtable ....Lookups........................1698.1........1204.3.....1018837.......n/a ....Avg.Chain.Length..................n/a...........n/a.....0.00004.......n/a ....Spinlock.Contention...............n/a...........n/a.........n/a.......0.0.% ..Exclusive.Address ....Granted...........................0.6...........0.4.........335.....100.0.% ....Waited............................0.0...........0.0...........0.......0.0.% ..-------------------------..------------..------------..----------..---------- ..Total.EX-Address.Requests...........0.6...........0.4.........335.......0.0.% ..Shared.Address ....Granted........................1697.5........1203.9.....1018501.....100.0.% ....Waited............................0.0...........0.0...........0.......0.0.% ..-------------------------..------------..------------..----------..---------- ..Total.SH-Address.Requests........1697.5........1203.9.....1018501......34.1.% ..Last.Page.Locks.on.Heaps ....Granted.........................188.0.........133.3......112794.....100.0.% ....Waited............................0.0...........0.0...........0.......0.0.% ..-------------------------..------------..------------..----------..---------- ..Total.Last.Pg.Locks...............188.0.........133.3......112794.....100.0.% ..Deadlocks.by.Lock.Type..........per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ..Total.Deadlocks.....................0.0...........0.0...........0.......n/a ..Deadlock.Detection ....Deadlock.Searches.................0.0...........0.0...........4.......n/a ....Searches.Skipped..................0.0...........0.0...........0.......0.0.% ....Avg.Deadlocks.per.Search..........n/a...........n/a.....0.00000.......n/a ..Lock.Promotions ....Total.Lock.Promotions.............0.0...........0.0...........0.......n/a ..Lock.Timeouts.by.Lock.Type......per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ..Total.Timeouts......................0.0...........0.0...........0.......n/a ..Tuning.Recommendations.for.Lock.Management ..------------------------------------------ ..-.Consider.increasing.the.'deadlock.checking.period'.parameter ....by.50.ms. =============================================================================== Data.Cache.Management --------------------- ..Cache.Statistics.Summary.(All.Caches) ..------------------------------------- ..................................per.sec......per.xact.......count..%.of.total .............................------------..------------..----------..---------- ....Cache.Search.Summary ......Total.Cache.Hits.............5392.9........3824.7.....3235735......95.9.% ......Total.Cache.Misses............228.7.........162.2......137243.......4.1.% ..-------------------------..------------..------------..---------- ....Total.Cache.Searches...........5621.6........3987.0.....3372978 ....Cache.Turnover ......Buffers.Grabbed................17.6..........12.5.......10547.......n/a ......Buffers.Grabbed.Dirty...........0.0...........0.0...........0.......0.0.% ....Cache.Strategy.Summary ......Cached.(LRU).Buffers.........5475.8........3883.5.....3285466.....100.0.% ......Discarded.(MRU).Buffers.........0.0...........0.0...........0.......0.0.% ....Large.I/O.Usage ......Large.I/Os.Performed............0.4...........0.3.........256.....100.0.% ......Large.I/Os.Denied.due.to ......Pool.<.Prefetch.Size............0.0...........0.0...........0.......0.0.% ..-------------------------..------------..------------..---------- ....Total.Large.I/O.Requests..........0.4...........0.3.........256 ....Large.I/O.Effectiveness ......Pages.by.Lrg.I/O.Cached.........3.4...........2.4........2048.......n/a ......Pages.by.Lrg.I/O.Used...........0.0...........0.0...........0.......0.0.% ....Asynchronous.Prefetch.Activity ......APFs.Issued.....................8.1...........5.7........4857......11.0.% ......APFs.Denied.Due.To ........APF.I/O.Overloads.............0.0...........0.0...........0.......0.0.% ........APF.Limit.Overloads...........0.0...........0.0...........0.......0.0.% ........APF.Reused.Overloads..........0.0...........0.0...........0.......0.0.% ......APF.Buffers.Found.in.Cache ........With.Spinlock.Held............0.0...........0.0...........0.......0.0.% ........W/o.Spinlock.Held............65.5..........46.5.......39325......89.0.% ..-------------------------..------------..------------..---------- ....Total.APFs.Requested.............73.6..........52.2.......44182 ....Other.Asynchronous.Prefetch.Statistics ......APFs.Used.......................7.7...........5.5........4642.......n/a ......APF.Waits.for.I/O...............5.8...........4.1........3453.......n/a ......APF.Discards....................0.0...........0.0...........0.......n/a ....Dirty.Read.Behavior ......Page.Requests...................0.0...........0.0...........0.......n/a ------------------------------------------------------------------------------- ..Cache:.default.data.cache ..................................per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ....Spinlock.Contention...............n/a...........n/a.........n/a.......0.0.% ....Utilization.......................n/a...........n/a.........n/a......76.4.% ....Cache.Searches ......Cache.Hits...................4280.4........3035.8.....2568251......99.6.% .........Found.in.Wash................5.3...........3.8........3199.......0.1.% ......Cache.Misses...................16.4..........11.6........9811.......0.4.% ..-------------------------..------------..------------..---------- ....Total.Cache.Searches...........4296.8........3047.4.....2578062 ....Pool.Turnover ......2..Kb.Pool ..........LRU.Buffer.Grab............16.3..........11.6........9784.....100.0.% ............Grabbed.Dirty.............0.0...........0.0...........0.......0.0.% ..-------------------------..------------..------------..---------- ....Total.Cache.Turnover.............16.3..........11.6........9784 ....Buffer.Wash.Behavior ......Statistics.Not.Available.-.No.Buffers.Entered.Wash.Section.Yet ....Cache.Strategy ......Cached.(LRU).Buffers.........4294.4........3045.7.....2576642.....100.0.% ......Discarded.(MRU).Buffers.........0.0...........0.0...........0.......0.0.% ....Large.I/O.Usage ......Total.Large.I/O.Requests........0.0...........0.0...........0.......n/a ....Large.I/O.Detail ......No.Large.Pool(s).In.This.Cache ....Dirty.Read.Behavior .......Page.Requests...............0.0...........0.0...........0.......n/a ....Tuning.Recommendations.for.Data.cache.:.default.data.cache ....------------------------------------- ....-.Consider.using.'relaxed.LRU.replacement.policy' ......for.this.cache. ....-.Consider.adding.a.large.I/O.pool.for.this.cache. ------------------------------------------------------------------------------- ..Cache:.tempdb_cache ..................................per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ....Spinlock.Contention...............n/a...........n/a.........n/a.......0.0.% ....Utilization.......................n/a...........n/a.........n/a......23.6.% ....Cache.Searches ......Cache.Hits...................1112.5.........789.0......667484......84.0.% .........Found.in.Wash...............12.1...........8.6........7258.......1.1.% ......Cache.Misses..................212.4.........150.6......127432......16.0.% ..-------------------------..------------..------------..---------- ....Total.Cache.Searches...........1324.9.........939.6......794916 ....Pool.Turnover ......2..Kb.Pool ..........LRU.Buffer.Grab.............0.8...........0.6.........507......66.4.% ............Grabbed.Dirty.............0.0...........0.0...........0.......0.0.% ......16.Kb.Pool ..........LRU.Buffer.Grab.............0.4...........0.3.........256......33.6.% ............Grabbed.Dirty.............0.0...........0.0...........0.......0.0.% ..-------------------------..------------..------------..---------- ....Total.Cache.Turnover..............1.3...........0.9.........763 ....Buffer.Wash.Behavior ......Statistics.Not.Available.-.No.Buffers.Entered.Wash.Section.Yet ....Cache.Strategy ......Cached.(LRU).Buffers.........1181.4.........837.9......708824.....100.0.% ......Discarded.(MRU).Buffers.........0.0...........0.0...........0.......0.0.% ....Large.I/O.Usage ......Large.I/Os.Performed............0.4...........0.3.........256.....100.0.% ......Large.I/Os.Denied.due.to ......Pool.<.Prefetch.Size............0.0...........0.0...........0.......0.0.% ..-------------------------..------------..------------..---------- ....Total.Large.I/O.Requests..........0.4...........0.3.........256 ....Large.I/O.Detail .....16..Kb.Pool ........Pages.Cached..................3.4...........2.4........2048.......n/a ........Pages.Used....................0.0...........0.0...........0.......0.0.% ....Dirty.Read.Behavior .......Page.Requests...............0.0...........0.0...........0.......n/a =============================================================================== Procedure.Cache.Management........per.sec......per.xact.......count..%.of.total ---------------------------..------------..------------..----------..---------- ..Procedure.Requests..................0.6...........0.4.........336.......n/a ..Procedure.Reads.from.Disk...........0.1...........0.1..........80......23.8.% ..Procedure.Writes.to.Disk............0.0...........0.0..........17.......5.1.% ..Procedure.Removals..................0.1...........0.1..........87.......n/a ..Procedure.Recompilations............0.0...........0.0...........4.......n/a ..Recompilations.Requests: ....Execution.Phase...................0.0...........0.0...........4.....100.0.% ....Compilation.Phase.................0.0...........0.0...........0.......0.0.% ....Execute.Cursor.Execution..........0.0...........0.0...........0.......0.0.% ....Redefinition.Phase................0.0...........0.0...........0.......0.0.% ..Recompilation.Reasons: ....Table.Missing.....................0.0...........0.0...........0.......n/a ....Temporary.Table.Missing...........0.0...........0.0...........0.......n/a ....Schema.Change.....................0.0...........0.0...........4.......n/a ....Index.Change......................0.0...........0.0...........0.......n/a ....Isolation.Level.Change............0.0...........0.0...........0.......n/a ....Permissions.Change................0.0...........0.0...........0.......n/a ....Cursor.Permissions.Change.........0.0...........0.0...........0.......n/a ..Tuning.Recommendations.for.Procedure.cache.management ..----------------------------------------------------- ..-.Consider.increasing.the.'procedure.cache.size' ....configuration.parameter. =============================================================================== Memory.Management.................per.sec......per.xact.......count..%.of.total ---------------------------..------------..------------..----------..---------- ..Pages.Allocated.....................0.5...........0.4.........298.......n/a ..Pages.Released......................0.5...........0.4.........297.......n/a =============================================================================== Recovery.Management ------------------- ..Checkpoints.....................per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ....#.of.Normal.Checkpoints...........0.0...........0.0...........3.....100.0.% ....#.of.Free.Checkpoints.............0.0...........0.0...........0.......0.0.% ..-------------------------..------------..------------..---------- ..Total.Checkpoints...................0.0...........0.0...........3 ..Avg.Time.per.Normal.Chkpt.......0.00000.seconds =============================================================================== Disk.I/O.Management ------------------- ..Max.Outstanding.I/Os............per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ....Server............................n/a...........n/a.........111.......n/a ....Engine.0..........................n/a...........n/a..........49.......n/a ....Engine.1..........................n/a...........n/a.........111.......n/a ..I/Os.Delayed.by ....Disk.I/O.Structures...............n/a...........n/a...........0.......n/a ....Server.Config.Limit...............n/a...........n/a...........0.......n/a ....Engine.Config.Limit...............n/a...........n/a...........0.......n/a ....Operating.System.Limit............n/a...........n/a...........0.......n/a ..Total.Requested.Disk.I/Os.........143.9.........102.0.......86327 ..Completed.Disk.I/O's ....Engine.0........................125.5..........89.0.......75272......87.2.% ....Engine.1.........................18.4..........13.1.......11054......12.8.% ..-------------------------..------------..------------..---------- ..Total.Completed.I/Os..............143.9.........102.0.......86326 ..Device.Activity.Detail ..---------------------- ..Device: ....C:\sybase\data\akv_2003.dat ....akv_2003......................per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ..Total.I/Os..........................0.0...........0.0...........0.......n/a ..-------------------------..------------..------------..----------..---------- ..Total.I/Os..........................0.0...........0.0...........0.......0.0.% ..----------------------------------------------------------------------------- ..Device: ....C:\sybase\data\akv_2003.log ....akv_2003_log..................per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ....Reads ......APF.............................0.0...........0.0...........0.......0.0.% ......Non-APF.........................0.0...........0.0...........1.....100.0.% ....Writes............................0.0...........0.0...........0.......0.0.% ..-------------------------..------------..------------..----------..---------- ..Total.I/Os..........................0.0...........0.0...........1.......0.0.% ..----------------------------------------------------------------------------- ..Device: ....C:\sybase\data\akv_2004.dat ....akv_2004......................per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ....Reads ......APF.............................8.1...........5.7........4857......50.0.% ......Non-APF.........................8.1...........5.7........4832......49.8.% ....Writes............................0.0...........0.0..........17.......0.2.% ..-------------------------..------------..------------..----------..---------- ..Total.I/Os.........................16.2..........11.5........9706......11.2.% ..----------------------------------------------------------------------------- ..Device: ....C:\sybase\data\akv_2004.log ....akv_2004_log..................per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ....Reads ......APF.............................0.0...........0.0...........0.......0.0.% ......Non-APF.........................0.0...........0.0...........5......23.8.% ....Writes............................0.0...........0.0..........16......76.2.% ..-------------------------..------------..------------..----------..---------- ..Total.I/Os..........................0.0...........0.0..........21.......0.0.% ..----------------------------------------------------------------------------- ..Device: ....C:\sybase\data\temp_db.dat ....temp_db.......................per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ....Reads ......APF.............................0.0...........0.0...........0.......0.0.% ......Non-APF.........................0.1...........0.0..........33.......1.0.% ....Writes............................5.5...........3.9........3272......99.0.% ..-------------------------..------------..------------..----------..---------- ..Total.I/Os..........................5.5...........3.9........3305.......3.8.% ..----------------------------------------------------------------------------- ..Device: ....C:\sybase\data\temp_db.log ....temp_db_log...................per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ....Reads ......APF.............................0.0...........0.0...........0.......0.0.% ......Non-APF.........................0.7...........0.5.........439.......0.6.% ....Writes..........................119.6..........84.8.......71732......99.4.% ..-------------------------..------------..------------..----------..---------- ..Total.I/Os........................120.3..........85.3.......72171......83.6.% ..----------------------------------------------------------------------------- ..Device: ....C:\sybase\data\temp_db_log.log ....tempdb_lg.....................per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ..Total.I/Os..........................0.0...........0.0...........0.......n/a ..-------------------------..------------..------------..----------..---------- ..Total.I/Os..........................0.0...........0.0...........0.......0.0.% ..----------------------------------------------------------------------------- ..Device: ....C:\sybase\data\test_db.dat ....test_db.......................per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ..Total.I/Os..........................0.0...........0.0...........0.......n/a ..-------------------------..------------..------------..----------..---------- ..Total.I/Os..........................0.0...........0.0...........0.......0.0.% ..----------------------------------------------------------------------------- ..Device: ....C:\sybase\data\test_db.log ....test_log......................per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ..Total.I/Os..........................0.0...........0.0...........0.......n/a ..-------------------------..------------..------------..----------..---------- ..Total.I/Os..........................0.0...........0.0...........0.......0.0.% ..----------------------------------------------------------------------------- ..Device: ....c:\sybase\data\master.dat ....master........................per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ....Reads ......APF.............................0.0...........0.0...........0.......0.0.% ......Non-APF.........................0.1...........0.1..........51.......4.9.% ....Writes............................1.7...........1.2.........991......95.1.% ..-------------------------..------------..------------..----------..---------- ..Total.I/Os..........................1.7...........1.2........1042.......1.2.% ..----------------------------------------------------------------------------- ..Device: ....c:\sybase\data\sybprocs.dat ....sysprocsdev...................per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ....Reads ......APF.............................0.0...........0.0...........0.......0.0.% ......Non-APF.........................0.1...........0.1..........73......90.1.% ....Writes............................0.0...........0.0...........8.......9.9.% ..-------------------------..------------..------------..----------..---------- ..Total.I/Os..........................0.1...........0.1..........81.......0.1.% ..----------------------------------------------------------------------------- =============================================================================== Network.I/O.Management ---------------------- ..Total.Network.I/O.Requests.........16.1..........11.4........9672.......n/a ....Network.I/Os.Delayed..............0.0...........0.0...........0.......0.0.% ..Total.TDS.Packets.Received......per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ....Engine.0..........................0.2...........0.1.........110.......9.6.% ....Engine.1..........................1.7...........1.2........1040......90.4.% ..-------------------------..------------..------------..---------- ..Total.TDS.Packets.Rec'd.............1.9...........1.4........1150 ..Total.Bytes.Received............per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ....Engine.0.........................39.2..........27.8.......23527......31.3.% ....Engine.1.........................86.0..........61.0.......51620......68.7.% ..-------------------------..------------..------------..---------- ..Total.Bytes.Rec'd.................125.2..........88.8.......75147 ...Avg.Bytes.Rec'd.per.Packet..........n/a...........n/a..........65.......n/a ..----------------------------------------------------------------------------- ..Total.TDS.Packets.Sent..........per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ....Engine.0..........................1.0...........0.7.........600.......7.0.% ....Engine.1.........................13.2...........9.4........7922......93.0.% ..-------------------------..------------..------------..---------- ..Total.TDS.Packets.Sent.............14.2..........10.1........8522 ..Total.Bytes.Sent................per.sec......per.xact.......count..%.of.total ..-------------------------..------------..------------..----------..---------- ....Engine.0.......................1094.6.........776.3......656769......16.7.% ....Engine.1.......................5443.4........3860.6.....3266060......83.3.% ..-------------------------..------------..------------..---------- ..Total.Bytes.Sent.................6538.0........4636.9.....3922829 ..Avg.Bytes.Sent.per.Packet...........n/a...........n/a.........460.......n/a ===============================.End.of.Report.================================= ... |
|||
|
:
Нравится:
Не нравится:
|
|||
|
|
|
17.05.2004, 14:50
|
|||
|---|---|---|---|
Как бороться с блокировками в ASE 12.5.0.3 |
|||
|
#18+
sp_configure.'max.online.engines' Parameter.Name.....Default.....Memory.Used.....Config.Value.....Run.Value.....Unit.....Type max.online.engines.......1........147...............2...............2.....number.......static На.такую.установку.выдает.ошибку sp_configure.'max.online.engines',4 go Parameter.Name.....Default.....Memory.Used.....Config.Value.....Run.Value.....Unit.....Type max.online.engines...........................1.............147...............4...............2.....number...................static Configuration.option.changed..Since.the.option.is.static,.Adaptive.Server.must.be.rebooted.in.order.for.the.change.to.take.effect............................... Changing.the.value.of.'max.online.engines'.to.'4'.increases.the.amount.of.memory.ASE.uses.by.166.K. sp_configure.'min.online.engines',3 go 2.....763..........No.matching.configuration.options...Here.is.a.listing.of.groups: ... |
|||
|
:
Нравится:
Не нравится:
|
|||
|
|
|
17.05.2004, 15:22
|
|||
|---|---|---|---|
Как бороться с блокировками в ASE 12.5.0.3 |
|||
|
#18+
Код: plaintext Нужно отлаживаться на процедуре, на это нужно время, а на все подряд ставить индексы нет смысла. Но вечером этим займусь. И как включить и где посмотреть HyperTreading? После всего проделанного, сервер работать стал медленнее. Я вернул sp_configure 'max online engines',2 перезапустил, но ситуация не улучшилась Вчера сложный запрос работал 35 сикунд, сейчас более 2х минут :( Лучшее враг хорошего.... ... |
|||
|
:
Нравится:
Не нравится:
|
|||
|
|
Форумы
[новые:0]
/ Sybase ASA, ASE, IQ
[новые:0]
[игнор отключен]
[закрыт для гостей]
/
Как бороться с блокировками в ASE 12.5.0.3
[новые:0]
/
25 сообщений из 42, страница 1 из 2
