Доброго времени суток.
Есть запрос:
#m_forceplan
declare @dated smalldatetime, @bank char(120), @ordernumber char(10), @sorttype1 int, @sorttype varchar(20),
@batchid numeric(15,0), @batchbrief varchar(25), @ourid dsidentifier, @sentaku int, @ip int
select @dated = %date!, @ordernumber = %ordernumber!, @sorttype1 = convert(int,%sortotype!), @sorttype = %sortotype!,
@batchid = %batch!, @sentaku = %choise!, @ip = %ipornot!
select @bank = i.name, @ourid = i.institutionid
from tinstitution i #m_nolock_index(xpktinstitution)
where 1 = 1
and i.institutionid = %institutionid!
select @batchbrief = brief
from tpropertyusr #m_nolock_index(xpktpropertyusr)
where propertyusrid = @batchid
create table #totalsum(acc varchar(20), totalsum numeric(38,2), note varchar(255), spid varchar(25))
create table #oppai(bank varchar(120), ordernumber char(10), number char(10), opcode char(5), operdate smalldatetime,
accd varchar(20), accc varchar(20), namaed varchar(255), namaec varchar(255), inaemd varchar(255), inaemc varchar(255),
inaemdip varchar(255), inaemcip varchar(255), qty numeric(38,2), qtycre numeric(38,2), note varchar(255), sr1accorder varchar(20),
sr2accorder varchar(20), sqtybs numeric(20,2), id varchar(30), qtybs numeric(38,2), fundpr char(3), sortmaass varchar(20),
prior varchar(5), totalsum numeric(38,2), spid varchar(25))
create table #tesutemp(id varchar(25), spid varchar(25))
create index indextesu on #tesutemp (id)
insert #tesutemp(id, spid)
select r.resourceid, @@spid
from tobjclassifier c1 #m_nolock_index(xpktdepclassifier)
left join tobjclassifier c2 #m_nolock_index(xak0tobjclassifier) on c2.parentid = c1.objclassifierid
left join tobjclsrelation oc #m_nolock_index(xie0tobjclsRelation) on oc.objclassifierid = c2.objclassifierid
left join tresource r #m_nolock_index(xpktresource) on r.resourceid = oc.objectid
where c1.brief = 'jetesu'
#m_isolat
#m_forceorder
insert #oppai(bank,ordernumber,number,opcode,operdate,accd,accc,namaed,namaec,inaemd,inaemc,inaemdip,inaemcip,
qty,qtycre,note,sr1accorder,sr2accorder,sqtybs,id,qtybs,fundpr,sortmaass,prior,totalsum,spid)
select
@bank bank
, @ordernumber ordernumber
, ltrim(dt.docnumber) number
, dt.opcode opcode
, dt.date operdate
, rd.brief accd
, rc.brief accc
, rd.name namaed
, rc.name namaec
, id.name inaemd
, ic.name inaemc
, case convert(int,rd.settings) & 1
when 0 then case when id.propdealpart = 1 then id.name
else ltrim(rtrim(reid.reuters)) + ' ' + id.name + ' ' + ltrim(rtrim(id.name1)) + ' ' + ltrim(rtrim(id.name2))
end
else case when bc.propdealpart = 1 then bc.name
else ltrim(rtrim(rebc.reuters)) + ' ' + bd.name + ' ' + ltrim(rtrim(bd.name1)) + ' ' + ltrim(rtrim(bd.name2))
end
end inaemdip
, case convert(int,rc.settings) & 1
when 0 then case when ic.propdealpart = 1 then ic.name
else ltrim(rtrim(reic.reuters)) + ' ' + ic.name + ' ' + ltrim(rtrim(ic.name1)) + ' ' + ltrim(rtrim(ic.name2))
end
else case when bc.propdealpart = 1 then bc.name
else ltrim(rtrim(rebc.reuters)) + ' ' + bc.name + ' ' + ltrim(rtrim(bc.name1)) + ' ' + ltrim(rtrim(bc.name2))
end
end inaemcip
, dt.fixqty qty
, dt.qty qtycre
, ltrim(rtrim(dt.comment)) note
, isnull(nullif(@sorttype,'0'),rd.accorder) sr1accorder --, rd.accorder
, isnull(nullif(@sorttype,'1'),rc.accorder) sr2accorder --, rc.accorder
, isnull(nullif(convert(money,@sorttype1),2),dt.qty) sqtybs
, isnull(nullif(@sorttype,'2'),dt.dealtransactid) id
, dt.qty qtybs
, 810 fundpr
, @sorttype sortmaass
, dt.priority prior
, case when @sorttype = 2 then dt.qty else 0 end totalsum
, @@spid spid
from #tesutemp dd #m_nolock_index(indextesu)
inner join tresource rc #m_nolock_index(xpktresource)
on rc.resourceid = dd.id
and dd.spid=@@spid
and rc.institutionid = @ourid
inner join tdealtransact dt #m_nolock_index(xpktdealtransact)
on rc.resourceid = dt.resourcepsvid
and dt.date = @dated
and dt.instrumentid = 1558 -- select instrumentid from tinstrument where brief='*ВнутрДок'
and dt.confirmed = 1
and dt.batchid = @batchid
and dt.opcode = 17
inner join tresource rd #m_nolock_index(xpktresource)
on rd.resourceid = dt.resourceid
and rd.institutionid = @ourid
inner join tinstitution id #m_nolock_index(xpktinstitution)
on rd.instownerid = id.institutionid
left join treuters reid #m_nolock_index(xak1treuters)
on reid.institutionid = id.institutionid
and reid.isdefault = 1
and reid.reuters in ('ИП','ПБОЮЛ')
inner join tinstitution ic #m_nolock_index(xpktinstitution)
on rc.instownerid = ic.institutionid
left join treuters reic #m_nolock_index(xak1treuters)
on reic.institutionid = ic.institutionid
and reic.isdefault = 1
and reic.reuters in ('ИП','ПБОЮЛ')
inner join tinstitution bd #m_nolock_index(xpktinstitution)
on bd.institutionid = rd.institutionid
left join treuters rebd #m_nolock_index(xak1treuters)
on rebd.institutionid = bd.institutionid
and rebd.isdefault = 1
and rebd.reuters in ('ИП','ПБОЮЛ')
inner join tinstitution bc #m_nolock_index(xpktinstitution)
on bc.institutionid = rc.institutionid
left join treuters rebc #m_nolock_index(xak1treuters)
on rebc.institutionid = bc.institutionid
and rebc.isdefault = 1
and rebc.reuters in ('ИП','ПБОЮЛ')
where 1 = 1
#m_forceplan_off
if @sorttype = 1
insert #totalsum
select opi.accc, sum(opi.qtybs), opi.note, @@spid
from #oppai opi
where opi.spid = @@spid
group by opi.accc, opi.note
if @sorttype = 0
insert #totalsum
select opi.accd, sum(opi.qtybs), opi.note, @@spid
from #oppai opi
where opi.spid = @@spid
group by opi.accd, opi.note
if @sorttype < 2
update #oppai
set totalsum = ts.totalsum
from #totalsum ts
where ts.spid = @@spid
and #oppai.spid = @@spid
and ts.acc = case @sorttype when 0 then #oppai.accd when 1 then #oppai.accc end
and ts.note = #oppai.note
select @sentaku sentaku,@ip ip,bank,ordernumber,number,opcode,operdate,accd,accc,namaed,namaec,
case @ip when 0 then inaemd else inaemdip end inaemd, case @ip when 0 then inaemc else inaemcip end inaemc,
qty,qtycre,note,sr1accorder,sr2accorder,sqtybs,id,qtybs,fundpr,sortmaass,prior,totalsum,spid from #oppai where spid = @@spid
order by qtybs
drop table #tesutemp
drop table #oppai
drop table #totalsum
Результат получаем через 25 минут. Подскажите, пожалуйста, как можно оптимизировать. Хотелось бы сократить время исполнения на столько, на сколько возможно. Любые предложения.
Версия SQL сервера: Microsoft SQL Server 2000 - 8.00.2055 (Intel X86) Dec 16 2008 19:46:53 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
... оно не скучает, оно умирает со скуки...
... слишном странный, чтобы существовать, но слишком редкий, чтобы умереть ...