GBase南大通用8aSql 语句并发执行性能差

描述:

GBase南大通用8aSql 语句并发执行性能差:
select *
from (select *
from (select rowid as id,
‘1’ as item1,
OrignialCallingParty as item2,
OrignialCalledParty as item3,
TariffInfo as item4,
BalanceInfo as item5,
to_char(CallDuriation) as item6,
to_char(SessionBeginTime, ‘YYYYMMDDHH24MISS’) as item7, SessionTerminatedTime as item8,
RoamingType as item9,
to_char(CallingPartyLacId) as item10,
PayTime as time,
‘’ || LongDistanceType as item11,
ServiceScenarious as item12,
‘201310’ as tbName,
CallingPartyCellId || ‘,’ || CalledPartyCellId as item13, ‘13026800298’ as phone
from voice_cdr_201310
where ChargedParty = ‘8613026800298’
and SessionBeginTime >= ‘2013-09-01 00:00:00’
and SessionBeginTime <= ‘2013-10-08 11:25:51’
and single_flag = 1
order by item7 desc limit 50) a

union all
select *
from (select rowid as id,
‘1’ as item1,
OrignialCallingParty as item2,
OrignialCalledParty as item3,
TariffInfo as item4,
BalanceInfo as item5,
to_char(CallDuriation) as item6,
to_char(SessionBeginTime, ‘YYYYMMDDHH24MISS’) as item7, SessionTerminatedTime as item8,
RoamingType as item9,
to_char(CallingPartyLacId) as item10,
PayTime as time,
‘’ || LongDistanceType as item11,
ServiceScenarious as item12,
‘201309’ as tbName,
CallingPartyCellId || ‘,’ || CalledPartyCellId as item13, ‘13026800298’ as phone
from voice_cdr_201309
where ChargedParty = ‘8613026800298’
and SessionBeginTime >= ‘2013-09-01 00:00:00’
and SessionBeginTime <= ‘2013-10-08 11:25:51’
and single_flag = 1
order by item7 desc limit 50) b
union all
select *
from (select rowid as id,
‘1’ as item1,
OrignialCallingParty as item2,
OrignialCalledParty as item3,
TariffInfo as item4,
BalanceInfo as item5,
to_char(CallDuriation) as item6,
to_char(SessionBeginTime, ‘YYYYMMDDHH24MISS’) as item7, SessionTerminatedTime as item8,
RoamingType as item9,
to_char(CallingPartyLacId) as item10,
PayTime as time,
‘’ || LongDistanceType as item11,
ServiceScenarious as item12,
‘20131008’ as tbName,
CallingPartyCellId || ‘,’ || CalledPartyCellId as item13, ‘13026800298’ as phone

from voice_cdr_20131008
where ChargedParty = ‘8613026800298’
and SessionBeginTime >= ‘2013-09-01 00:00:00’ and SessionBeginTime <= ‘2013-10-08 11:25:51’ and single_flag = 1
order by item7 desc limit 50) c) a
order by item7 desc limit 0, 50;

解决办法:

GBase南大通用8a语句改写:
(select rowid as id,
‘1’ as item1,
OrignialCallingParty as item2,
OrignialCalledParty as item3,
TariffInfo as item4,
BalanceInfo as item5,
to_char(CallDuriation) as item6,
to_char(SessionBeginTime, ‘YYYYMMDDHH24MISS’) as item7, SessionTerminatedTime as item8,

RoamingType as item9,
to_char(CallingPartyLacId) as item10,
PayTime as time,
‘’ || LongDistanceType as item11,
ServiceScenarious as item12,
‘201310’ as tbName,
CallingPartyCellId || ‘,’ || CalledPartyCellId as item13, ‘13026800298’ as phone
from voice_cdr_201310
where ChargedParty = ‘8613026800298’
and SessionBeginTime >= ‘2013-09-01 00:00:00’
and SessionBeginTime <= ‘2013-10-08 11:25:51’
and single_flag = 1)
union
(select rowid as id,
‘1’ as item1,
OrignialCallingParty as item2,
OrignialCalledParty as item3,
TariffInfo as item4,
BalanceInfo as item5,
to_char(CallDuriation) as item6,
to_char(SessionBeginTime, ‘YYYYMMDDHH24MISS’) as item7, SessionTerminatedTime as item8,
RoamingType as item9,
to_char(CallingPartyLacId) as item10,

PayTime as time,
‘’ || LongDistanceType as item11,
ServiceScenarious as item12,
‘201309’ as tbName,
CallingPartyCellId || ‘,’ || CalledPartyCellId as item13, ‘13026800298’ as phone
from voice_cdr_201309
where ChargedParty = ‘8613026800298’
and SessionBeginTime >= ‘2013-09-01 00:00:00’
and SessionBeginTime <= ‘2013-10-08 11:25:51’
and single_flag = 1)
union
(select rowid as id,
‘1’ as item1,
OrignialCallingParty as item2,
OrignialCalledParty as item3,
TariffInfo as item4,
BalanceInfo as item5,
to_char(CallDuriation) as item6,
to_char(SessionBeginTime, ‘YYYYMMDDHH24MISS’) as item7, SessionTerminatedTime as item8,
RoamingType as item9,
to_char(CallingPartyLacId) as item10,
PayTime as time,
‘’ || LongDistanceType as item11,
ServiceScenarious as item12,
‘20131008’ as tbName,
CallingPartyCellId || ‘,’ || CalledPartyCellId as item13, ‘13026800298’ as phone
from voice_cdr_20131008
where ChargedParty = ‘8613026800298’
and SessionBeginTime >= ‘2013-09-01 00:00:00’
and SessionBeginTime <= ‘2013-10-08 11:25:51’
and single_flag = 1)
order by item7 desc limit 0, 50。