It works great on my test environment, but please test yourself. i need lab rats!
In my testing, I found that it was able to load the series tab in 1 second, where it used to take 60. most accounts used to load in 3 seconds, and now load in .5 seconds.
To Implement, replace the If @Action = 'Season' ... section in the GetClientLists stored Procedure with the following:
IF @Action = 'Season'
begin
set nocount on
if @clientcode > 0
set @clientgroupcode = (select top 1 cltgroupcode from clients with(nolock) where cltcode = @clientcode)
create table #output (ProgramCode int, LastTransact int, TransactDate datetime, TransactNum int, Client_Code int, Client varchar(255),
FullName varchar(255), SeasonDescr varchar(100), SeriesDescr varchar(255), HallDescr varchar(100), AreaDescr varchar (100),
RowDescr varchar(10), ColDescr varchar(10), Season_Code int, Season varchar(100), Series_Code int, Series varchar(255),
SeriesType int, SubsType varchar(255), Hall varchar(255), Area varchar(255), Sector varchar(255), Row varchar(10), Col varchar(10),
Price money, Subsidy money, ChairCode int, AreaCode int, Status_Code int, Status varchar(50), Renewstatus varchar(50),
NumOfEvents int, NumOfBonus int, NumOfOptional int, TicketsLeft int, BonusLeft int, OptionalLeft int, PrintCount int, SelectionCode int,
SerialNum varchar(50), OriginalProgram int, OpeningDate datetime, FreezedStatus_code int, EndDate datetime, LastPrintDate datetime )
insert into #output (Programcode, LastTransact,
TransactDate,TransactNum ,Client_Code ,Client ,Fullname ,SeasonDescr ,Seriesdescr ,Halldescr ,areadescr ,rowdescr ,coldescr ,
season_code,season ,Series_code ,Series ,SeriesType ,SubsType ,Hall ,Area ,Sector ,Row ,Col ,Price ,Subsidy ,
chaircode ,areacode ,status_code ,status ,renewstatus ,NumOfEvents ,NumOfBonus ,NumOfOptional ,Ticketsleft ,BonusLeft, OptionalLeft ,PrintCount ,
SelectionCode, SerialNum ,OriginalProgram ,Openingdate ,FreezedStatus_code ,EndDate, lastprintdate )
select
sp.prorecnum,
case when sp.prolasttransact = 0 then sp.prorecnum else sp.prolasttransact end,
sp.protransactdate,
sp.protransactnum,
prosubsnum,
'', '',
sn.ssndescr,
sr.srdescr,
ha.haname,
'', '', '',
sn.ssncode,
sn.ssndescr,
sr.srcode,
sr.srdescr,
sp.proseriestype,
sp.prosubstype,
ha.haname,
'', '', '', '',
sp.proprice,
sp.prosubsidy,
sp.proseatnum,
sp.proarea,
sp.procurrentstatus,
'' as status ,
convert(varchar(10),prorenewstatus) as renewstatus,
sp.pronumofevents,
sp.probonus,
sp.prooptionalevents,
0,0,0,
sp.proprintnum,
sp.proselectioncode,
sp.proserialnumber,
case when sp.prolasttransact = 0 then sp.prorecnum else sp.prolasttransact end,
protransactdate,
Case proLastTransact
When 0 Then IsNull((Select Top 1 eesStatus From ExtendedEntityStatuses with(nolock) Where eesEntityCode = proRecNum And eesEntityType = 1 And eesStatus < 8), 0)
Else IsNull((Select Top 1 eesStatus From ExtendedEntityStatuses with(nolock) Where eesEntityCode = proLastTransact And eesEntityType = 1 And eesStatus < 8), 0)
End,
case when sp.proenddate = '1/1/1900' then
case when sr.srenddate = '1/1/1900' then sn.ssntodate else sr.srenddate end
else sp.proenddate end ,
'1/1/1900'
from subsprogram sp with(nolock) inner join
serieses sr with(nolock) on sp.proseriescode = sr.srcode inner join
seasons sn with(nolock) on ssncode = srseasons left outer join
halls ha on ha.hacode = sr.srhall
where
@clientgroupcode = sp.procustnum and
sp.proCurrentStatus IN (10, 21, 22, 23, 27, 28, 29)
and (sp.proseasoncode = @seasoncode or @seasoncode = 0)
and (sp.prosubsnum = @clientcode or @viewallingroup = 1 )
and dbo.IsRestriction(@IsAccessControl,@UserGroup,10, sr.srCode,@LockAction, sr.srOrgUnit) = 0
ORDER BY proRecNum DESC
update #output set status_code = case renewstatus when '32' then 36 when '30' then 30 else 34 end where enddate < getdate() and status_code = 10
update #output set status = stsdescription from statustype with(nolock) inner join #output on stsstatus = status_code
update #output set renewstatus = stsdescription from statustype with(nolock) inner join #output on stsstatus = convert(int,renewstatus)
where isnumeric(renewstatus) = 1
declare @trseason table (trcolumn varchar(100))
insert into @trseason
SELECT trColumn FROM TablesRules WHERE trTable = 'SeasonTabFields' AND trDisplay = 1
if exists (SELECT * FROM @trseason WHERE trColumn = 'Client')
update #output set client = cltclientname, fullname = cltclientname
from #output inner join clients on clients.cltcode = #output.client_code
if exists (SELECT * FROM @trseason WHERE trColumn = 'area')
update #output set Areadescr = arname , area = arname
from areas inner join #output on areacode = arcode
if exists (SELECT * FROM @trseason WHERE trColumn in ('col','row','sector' ) )
update #output set col = ahchair, row = ahline, sector = ahshortarname, rowdescr = ahline, coldescr = ahchair
from #output inner join areachair with(nolock) on ahchaircode = chaircode
if exists (SELECT * FROM @trseason WHERE trColumn = 'ticketsleft')
update #output set ticketsleft = numofevents - tix.tixcount
from #output inner join
(select tissubsprogram, count(*) as tixcount from ticketssubscription with(nolock)
where (tisStatus = 1 OR ( tisStatus = 8 AND tisIsTradeIn = 1 ))
AND tisTickType IN (810, 811, 812, 813, 814)
group by tissubsprogram)
as tix on tix.tissubsprogram = originalprogram
if exists (SELECT * FROM @trseason WHERE trColumn = 'bonusleft')
update #output set bonusleft = numofbonus - tix.tixcount
from #output inner join
(select tissubsprogram, count(*) as tixcount from ticketssubscription with(nolock)
where (tisStatus = 1 OR ( tisStatus = 8 AND tisIsTradeIn = 1 ))
AND tisTickType IN (820,821)
group by tissubsprogram)
as tix on tix.tissubsprogram = originalprogram
if exists (SELECT * FROM @trseason WHERE trColumn = 'optionalleft')
update #output set optionalleft = numofoptional - tix.tixcount
from #output inner join
(select tissubsprogram, count(*) as tixcount from ticketssubscription with(nolock)
where (tisStatus = 1 OR ( tisStatus = 8 AND tisIsTradeIn = 1 ))
AND tisTickType IN (830,831)
group by tissubsprogram)
as tix on tix.tissubsprogram = originalprogram
if exists (SELECT * FROM @trseason WHERE trColumn = 'lastprintdate')
update #output set lastprintdate = pr.lastprintdate
from #output inner join
(
Select max(praUpdated) as lastprintdate, pradoccode From printingaudit(NOLOCK)
Where praSourceTable=4 and praStatus<>3
group by pradoccode
) as pr on pr.pradoccode = programcode
select ProgramCode ,LastTransact , TransactDate , TransactNum , Client_Code , Client ,
FullName , SeasonDescr , SeriesDescr , HallDescr , AreaDescr ,
RowDescr , ColDescr , Season_Code , Season , Series_Code , Series ,
SeriesType , SubsType , Hall , Area , Sector , Row , Col ,
Price , Subsidy , ChairCode , AreaCode , Status_Code , Status , Renewstatus ,
NumOfEvents , NumOfBonus , NumOfOptional , TicketsLeft , BonusLeft , OptionalLeft , PrintCount , SelectionCode ,
SerialNum , OriginalProgram , OpeningDate , FreezedStatus_code , EndDate , LastPrintDate
from #output
drop table #output
end
No comments:
Post a Comment