java sql 查询50条数据 1.700秒 算快吗
sql 语句如下：
select *
from (select t.user_id,
ent_name,
decl_reg_no,
ori_reg_no,
linkman,
linkman_photo,
mobile_telephone,
electrograph,
ent_address,
email,
f_gethuifangdate(t.user_id, t.corp_code) as reg,
account_total,
oper_user,
to_char(oper_date, 'yyyy-mm-dd') as oper,
linkman2,
linkman_photo2,
ent_namesimple,
user_code,
t.corp_code,
lihua_account,
balance_date,
arrearage_date,
client_type,
deposit,
nvl(fee_total, 0) as qiankuan_date,
f_getbalamode(charge_type) as charge_type,
liushi,
officeno,
a.cnum,
f_getqiankuandate(t.user_id, t.corp_code) as day,
officeno1,
liushicause,
f_getHandler(t.handler, corp_code) as handler,
to_char(cause_date, 'yyyy-mm-dd') as ca,
ent_bankaccount,
ts_memo,
Finance_ren,
Finance_tel,
legal,
Legal_tel,
Trade_address,
Trade_tel,
Trade_fax,
Trade_linker,
Trade_linker_tel,
Trade_legal,
Trade_legal_tel,
min_account,
sub_account,
member_type,
soru,
dijiao
from (select user_id, count(*) as cnum
from (select user_id
from hdyw_ywdjmx
where fee_status = '2'
and flow_status <> '7'
and (cd_flag is null or cd_flag = '0')
and corp_code = 'hd'
union all
select user_id
from hdyw_ywdjmx_archives
where fee_status = '2'
and (flow_status <> '7')
and (cd_flag is null or cd_flag = '0')
and corp_code = 'hd')
group by user_id) a,
(select user_id,
sum(fee_total) as fee_total,
sum(soru) as soru,
sum(dijiao) as dijiao
from (select pay_entid as user_id,
sum((sub_charge + t.ori_charge +
f_getFareNum(yw_id, '3') +
f_getfare(yw_id, '1') + express_fare +
other_fare)) as fee_total,
sum(sub_charge + t.ori_charge +
f_getFareNum(yw_id, '2')) as soru,
sum(f_getfare(yw_id, '1') +
f_getFareNum(yw_id, '1')) as dijiao
from HDYW_YWDJMX t
where t.fee_status = '1'
and (t.flow_status <> '7' and t.cd_flag is null)
and t.cancellation is null
and corp_code = 'hd'
group by pay_entid
union all
select pay_entid as user_id,
sum((sub_charge + t.ori_charge +
f_getFareNum(yw_id, '3') +
f_getfare(yw_id, '1') + express_fare +
other_fare)) as fee_total,
sum(sub_charge + t.ori_charge +
f_getFareNum(yw_id, '2')) as soru,
sum(f_getfaresub(yw_id, charge_no, '2') +
f_getFareNum(yw_id, '1')) as dijiao
from hdyw_sfmxb t
where t.fee_status = '1'
and t.cancellation is null
and corp_code = 'hd'
group by pay_entid
union all
select user_id,
-account_total as fee_total,
0 as soru,
-account_total as dijiao
from hdyw_khjbzlgl t
where account_total < 0
and corp_code = 'hd'
union all
select user_id,
-sub_account as fee_total,
0 as soru,
-sub_account as dijiao
from hdyw_khjbzlgl tx
where tx.sub_account < 0
and corp_code = 'hd')
group by user_id) b,
hdyw_khjbzlgl t
where t.user_id = a.user_id(+)
and t.user_id = b.user_id(+)
and t.corp_code = 'hd'
and (t.liushi is null or t.liushi = '102'))
where 1 = 1
order by qiankuan_date desc
[解决办法]
50条就要1.7s？太慢了吧。。
不做看你sql逻辑也挺复杂。。。
[解决办法]
语句好长，是字段太多了吗
[解决办法]
50条1.7s肯定是算慢的了，而且是很慢。
你用太多union all了，这个是很不高效的用法。
[解决办法]
不必要的字段列就不要查了吧、
这语句太长了
请问是几个表联查吗？
看样子是涉及到了财务数据、
sql太长了、速度确实慢点
50条应该在几十ms以内、不应该秒级别的、
[解决办法]
这个讨论点太片面了。
要说清楚数据规模，是否有索引等问题。
最终，有的时候不是SQL本身的问题，而是数据结构存在问题，导致SQL没多少可优化空间。
[解决办法]
50条数据就要1.7秒，这可要了命了。。。0.17秒都很慢了。。。
[解决办法]
访问数据库的效率主要取决于Connection，其次还有sql优化，索引，表空间之类的都需要注意，
估计你的数据库连接是普通的Connection才会这么慢，你用连接池试试吧！
一般来讲jdbc的效率高于hibernate，sql的效率高于hql
[解决办法]
神一样的SQL语句！！！
设计数据库的人应该拖出去弹到死！
