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

最新推荐文章于 2023-12-26 17:05:20 发布

原创最新推荐文章于 2023-12-26 17:05:20 发布 · 232 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #sql

文章讨论了在GBase南大通用8a数据库中，使用复杂的SQL语句进行并发执行时性能下降的问题。作者提出了将原SQL语句拆分为三个独立查询并合并排序的优化方案，以提高执行效率。

描述:

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。