Есть апдейт (это расчет рыночной стоимости)
update tr_bill_in_portfolio
set rate = convert(decimal(30,10),(select sum(isnull(#rate_by_day.rate,0)*tmp_bill_in_qty_by_day.qty)
from tmp_bill_in_qty_by_day,
#rate_by_day
where tmp_bill_in_qty_by_day.sub_id=tr_bill_in_portfolio.subacc_id
and tmp_bill_in_qty_by_day.stock_id=tr_bill_in_portfolio.stock_id
and #rate_by_day.stock_id=tmp_bill_in_qty_by_day.stock_id
and #rate_by_day.day=tmp_bill_in_qty_by_day.day

)/ @days)
where id=@id
and tr_bill_in_portfolio.place_id=@tmp_com_id

Наблюдаются страшнейшие тормоза...конечно тут играет роль большой объем таблиц ,например tmp_bill_in_qty_by_day около 900000 записей,#rate_by_day около 200000... В частости дико тормозит не только выборка данных, но и конвертация в децимал.
Может можно что-то сделать с этим?

есть вот такие индексы
id subacc_id stock_id place_id id,place_id,act_acc_id id, place_id, subacc_id, stock_id

create table #accounts (
id numeric,
bill_sum decimal(30,10),
bill_price decimal(30,10) null
)

create table #days (day datetime)

create table #qty_by_day (
stock_id numeric,
sub_id numeric,
day datetime,
qty decimal(30,10)
)

create table #rate_by_day
(
day datetime,
stock_id numeric,
rate decimal(30,10) null,
max_day datetime null,
min_day datetime null
)

create table #tmp_days(day datetime)
--------------------------------------------------------
print 'tr_bill_in_portfolio'
SETUSER 'dbo'
go

create table dbo.tr_bill_in_portfolio (
id numeric(18, 0) not null,
subacc_id numeric(18, 0) not null,
stock_id numeric(18, 0) not null,
qty decimal(30, 10) not null,
rate decimal(30, 10) null,
bill_sum decimal(30, 10) null,
place_id numeric(18, 0) null,
act_acc_id numeric(18, 0) null
)
lock datapages
on 'default'
go

print 'tr_bill_in_portfolio_01'
create nonclustered index tr_bill_in_portfolio_01
on dbo.tr_bill_in_portfolio (id)
on 'default'
go

print 'tr_bill_in_portfolio_02'
create nonclustered index tr_bill_in_portfolio_02
on dbo.tr_bill_in_portfolio (subacc_id)
on 'default'
go

print 'tr_bill_in_portfolio_03'
create nonclustered index tr_bill_in_portfolio_03
on dbo.tr_bill_in_portfolio (stock_id)
on 'default'
go

print 'tr_bill_in_portfolio_04'
create nonclustered index tr_bill_in_portfolio_04
on dbo.tr_bill_in_portfolio (place_id)
on 'default'
go

print 'tr_bill_in_portfolio_05'
create nonclustered index tr_bill_in_portfolio_05
on dbo.tr_bill_in_portfolio (id, place_id, act_acc_id)
on 'default'
go

print 'tr_bill_in_portfolio_06'
create nonclustered index tr_bill_in_portfolio_06
on dbo.tr_bill_in_portfolio (id, place_id, subacc_id, stock_id)
on 'default'
go

SETUSER
go
-----------------------------------------------

update tr_bill_in_portfolio
set rate = convert(decimal(30,10),(select sum(isnull(#rate_by_day.rate,0)*#qty_by_day.qty)
from #qty_by_day,
#rate_by_day
where #qty_by_day.sub_id=tr_bill_in_portfolio.subacc_id
and #qty_by_day.stock_id=tr_bill_in_portfolio.stock_id
and #rate_by_day.day=#qty_by_day.day
and #rate_by_day.stock_id=#qty_by_day.stock_id

)/ @days)
where id=@id
and tr_bill_in_portfolio.place_id=@tmp_com_id

План
---------------------
QUERY PLAN FOR STATEMENT 245 (at line 1035).




STEP 1
The type of query is UPDATE.
The update mode is direct.


FROM TABLE
tr_bill_in_portfolio
Nested iteration.
Index : tr_bill_in_portfolio_05
Forward scan.
Positioning by key.
Keys are:
id ASC
place_id ASC


Run subquery 1 (at nesting level 1).
Using I/O Size 4 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 4 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
TO TABLE
tr_bill_in_portfolio


NESTING LEVEL 1 SUBQUERIES FOR STATEMENT 245.


QUERY PLAN FOR SUBQUERY 1 (at nesting level 1 and at line 1036).


Correlated Subquery.
Subquery under an EXPRESSION predicate.




STEP 1
The type of query is SELECT.
Evaluate Ungrouped SUM OR AVERAGE AGGREGATE.


FROM TABLE
#qty_by_day
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 4 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.


FROM TABLE
#rate_by_day
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 4 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.


END OF QUERY PLAN FOR SUBQUERY 1.

В начале поста запрос немного другой.
Смотреть на этот;)

update tr_bill_in_portfolio
set rate = convert(decimal(30,10),(select sum(isnull(#rate_by_day.rate,0)*#qty_by_day.qty)
from #qty_by_day,
#rate_by_day
where #qty_by_day.sub_id=tr_bill_in_portfolio.subacc_id
and #qty_by_day.stock_id=tr_bill_in_portfolio.stock_id
and #rate_by_day.day=#qty_by_day.day
and #rate_by_day.stock_id=#qty_by_day.stock_id

)/ @days)
where id=@id
and tr_bill_in_portfolio.place_id=@tmp_com_id

Таблицы здоровенные

ASE 12.0

Есть только одна проблема. Вроде бы ASE 12.0 не будет использовать индексы в процедуре на временные таблицы.
т.е в таком случае индекс на временную таблицу просто лишняя трата времени

А делать не временные как-то не очень хочется...

Сделал таблицы не временными... теперь переполняется лог при инсерте...

в боевой
а как батчами?
как ограничить выборку?