-- 以下兩個查詢效率相當，查詢時間均為0.0xx秒，~ 但是Hibernate HQL不支持from后面接子查詢，可以直接使用SQL方式解決，ibatis當然可以用了。
SELECT count(*) from (
SELECT sum(t2.STATE) as vnSum, t1.ID FROM
vnetinfo_table t1, vm_vn_table t2
where t1.ID = t2.VN_NID and t1.ZONE_ID = '4028a49c3facdf26013fae12531b286e' GROUP BY t1.ID) t where t.vnSum = 0;
SELECT count(*) FROM vnetinfo_table v ,(
SELECT sum(t2.STATE) as vnSum, t1.ID as idd FROM
vnetinfo_table t1, vm_vn_table t2
where t1.ID = t2.VN_NID and t1.ZONE_ID = '4028a49c3facdf26013fae12531b286e' GROUP BY t1.ID) t
where v.ID = t.idd and t.vnSum = 0;
-- 查詢效率太低了，查詢時間均為0.5xx秒左右。要比以上兩個SQL耗時很多倍。~ Hibernate支持在where后用子查詢作為查詢條件。
SELECT count(*) FROM vnetinfo_table v WHERE v.id in
(SELECT CASE WHEN SUM(t2.STATE)=0 THEN t1.ID ELSE '' END
FROM vnetinfo_table t1, vm_vn_table t2
where t1.ID = t2.VN_NID and t1.ZONE_ID = '4028a49c3facdf26013fae12531b286e' GROUP BY t1.ID);
-- 使用join方式替換in 查詢時間均為0.09x秒左右。明顯提高了效率，但是Hibernate HQL任然不支持在這種聯合子查詢方式。
SELECT count(*) FROM vnetinfo_table v INNER JOIN (SELECT CASE WHEN SUM(t2.STATE)=0 THEN t1.ID ELSE '' END as idd
FROM vnetinfo_table t1, vm_vn_table t2
WHERE t1.ID = t2.VN_NID and t1.ZONE_ID = '4028a49c3facdf26013fae12531b286e' GROUP BY t1.ID) t ON v.ID = t.idd and t.idd IS not NULL;
附：Mysql中關于Exists用法的介紹請參見：http://www.nowamagic.net/librarys/veda/detail/639
posted on 2013-08-21 13:47
David1228 閱讀(3759)
評論(2) 編輯 收藏