2008-6-4 11:50
lfree
问一个sql的优化
在我们的生产系统，有一条sql语句，问一下大家该如何优化:
SELECT drug_export_detail.retail_price, drug_export_detail.purchase_price
FROM drug_export_detail, drug_export_master
WHERE (drug_export_detail.document_no = drug_export_master.document_no)
AND ( (drug_export_master.STORAGE = :xx1)
AND (drug_export_detail.drug_code = :xx2)
AND (drug_export_detail.package_spec = :xx3)
AND (drug_export_detail.firm_id = :xx4)
)
AND export_date =
(SELECT MAX (export_date)
FROM drug_export_detail, drug_export_master
WHERE (drug_export_detail.document_no =
drug_export_master.document_no
)
AND ( (drug_export_master.STORAGE = :xx1)
AND (drug_export_detail.drug_code = :xx2)
AND (drug_export_detail.package_spec = :xx3)
AND (drug_export_detail.firm_id = :xx4)
)
GROUP BY drug_export_master.STORAGE,
drug_export_detail.drug_code,
drug_export_detail.package_spec,
drug_export_detail.firm_id)
AND ROWNUM = 1;
在我们的生产系统，有一条sql语句，问一下大家该如何优化:
SELECT drug_export_detail.retail_price, drug_export_detail.purchase_price
FROM drug_export_detail, drug_export_master
WHERE (drug_export_detail.document_no = drug_export_master.document_no)
AND ( (drug_export_master.STORAGE = :xx1)
AND (drug_export_detail.drug_code = :xx2)
AND (drug_export_detail.package_spec = :xx3)
AND (drug_export_detail.firm_id = :xx4)
)
AND export_date =
(SELECT MAX (export_date)
FROM drug_export_detail, drug_export_master
WHERE (drug_export_detail.document_no =
drug_export_master.document_no
)
AND ( (drug_export_master.STORAGE = :xx1)
AND (drug_export_detail.drug_code = :xx2)
AND (drug_export_detail.package_spec = :xx3)
AND (drug_export_detail.firm_id = :xx4)
)
GROUP BY drug_export_master.STORAGE,
drug_export_detail.drug_code,
drug_export_detail.package_spec,
drug_export_detail.firm_id)
AND ROWNUM = 1;
2008-6-4 13:51
wollaston
憋不住问一句
是不是发错版面了?
是不是发错版面了?
2008-6-4 16:11
lfree
没有，军惠的东西，熟悉的人许多，如果发到别的组，别人并不知道如何优化。
以及模式。
以及模式。
2008-6-4 19:20
wuhuaT
我觉得还是到开发板的人多些,这和行业没关系吧,
只认SQL不认行业
只认SQL不认行业
2008-6-5 16:18
lfree
丫!
看来许多军惠的用户都不注意sql的优化问题
看来许多军惠的用户都不注意sql的优化问题
2008-6-5 21:50
天光云影
是许多的军惠用户还没看到帖子呢。
4、5年没接触军惠的东西了，都忘的差不多了。
是要取某药最近一次出库时的零售和采购价格？
4、5年没接触军惠的东西了，都忘的差不多了。
是要取某药最近一次出库时的零售和采购价格？
2008-6-6 08:03
lfree
正确/
这个语句是做采购计划时候执行的.
这个语句是做采购计划时候执行的.
2008-6-6 10:06
lfree
SELECT DISTINCT "OPERATION_BILL_ITEMS"."PATIENT_ID",
"OPERATION_BILL_ITEMS"."OPER_ID", "PAT_MASTER_INDEX"."NAME",
"PAT_MASTER_INDEX"."SEX", "PAT_MASTER_INDEX"."CHARGE_TYPE"
FROM "PAT_MASTER_INDEX",
"OPERATION_BILL_ITEMS",
"SURGERY"."OPERATION_MASTER"
WHERE ("SURGERY"."OPERATION_MASTER"."PATIENT_ID" =
"OPERATION_BILL_ITEMS"."PATIENT_ID"
)
AND ("SURGERY"."OPERATION_MASTER"."VISIT_ID" =
"OPERATION_BILL_ITEMS"."VISIT_ID"
)
AND ("SURGERY"."OPERATION_MASTER"."OPER_ID" =
"OPERATION_BILL_ITEMS"."OPER_ID"
)
AND ("OPERATION_BILL_ITEMS"."PATIENT_ID" =
"PAT_MASTER_INDEX"."PATIENT_ID"
)
AND ( (TO_CHAR ("OPERATION_MASTER"."START_DATE_TIME",
'YYYY-MM-DD'
) = ：operating_date
)
AND ("OPERATION_BILL_ITEMS"."PERFORMED_BY" = ：performed_by)
)
再贴一个,现在看军惠的sql写的真烂!!!!
[[i] 本帖最后由 lfree 于 2008-6-6 10:10 编辑 [/i]]
"OPERATION_BILL_ITEMS"."OPER_ID", "PAT_MASTER_INDEX"."NAME",
"PAT_MASTER_INDEX"."SEX", "PAT_MASTER_INDEX"."CHARGE_TYPE"
FROM "PAT_MASTER_INDEX",
"OPERATION_BILL_ITEMS",
"SURGERY"."OPERATION_MASTER"
WHERE ("SURGERY"."OPERATION_MASTER"."PATIENT_ID" =
"OPERATION_BILL_ITEMS"."PATIENT_ID"
)
AND ("SURGERY"."OPERATION_MASTER"."VISIT_ID" =
"OPERATION_BILL_ITEMS"."VISIT_ID"
)
AND ("SURGERY"."OPERATION_MASTER"."OPER_ID" =
"OPERATION_BILL_ITEMS"."OPER_ID"
)
AND ("OPERATION_BILL_ITEMS"."PATIENT_ID" =
"PAT_MASTER_INDEX"."PATIENT_ID"
)
AND ( (TO_CHAR ("OPERATION_MASTER"."START_DATE_TIME",
'YYYY-MM-DD'
) = ：operating_date
)
AND ("OPERATION_BILL_ITEMS"."PERFORMED_BY" = ：performed_by)
)
再贴一个,现在看军惠的sql写的真烂!!!!
[[i] 本帖最后由 lfree 于 2008-6-6 10:10 编辑 [/i]]
2008-6-6 23:59
alinew
第一个SQL直接order by export_date后外面套一个ROWNUM = 1不行么？
2008-6-19 16:53
fals
[quote]原帖由 [i]lfree[/i] 于 2008-6-4 11:50 发表 [url=http://www.itpub.net/redirect.php?goto=findpost&pid=10567782&ptid=999793][img]http://www.itpub.net/images/common/back.gif[/img][/url]
在我们的生产系统，有一条sql语句，问一下大家该如何优化:
SELECT drug_export_detail.retail_price, drug_export_detail.purchase_price
FROM drug_export_detail, drug_export_master
WHERE (drug_export_detail.document_no = drug_export_master.document_no)
AND ( (drug_export_master.STORAGE = :xx1)
AND (drug_export_detail.drug_code = :xx2)
AND (drug_export_detail.package_spec = :xx3)
AND (drug_export_detail.firm_id = :xx4)
)
AND export_date =
(SELECT MAX (export_date)
FROM drug_export_detail, drug_export_master
WHERE (drug_export_detail.document_no =
drug_export_master.document_no
)
AND ( (drug_export_master.STORAGE = :xx1)
AND (drug_export_detail.drug_code = :xx2)
AND (drug_export_detail.package_spec = :xx3)
AND (drug_export_detail.firm_id = :xx4)
)
GROUP BY drug_export_master.STORAGE,
drug_export_detail.drug_code,
drug_export_detail.package_spec,
drug_export_detail.firm_id)
AND ROWNUM = 1; [/quote]
优化成这个行不？
select a.retail_price,a.purchase_price
from drug_export_detail a,
drug_export_master b,
(select max(export_date) max_export_date
from drug_export_detail m,
drug_export_master n
where m.document_no = n.docment_no
and n.storage = :xx1
and n.drug_code = :xx2
and n.package_spec = :xx3
and n.firm_id = :xx4) c
where a.docment_no = b.docment_no
and b.storage = :xx1
and b.drug_code = :xx2
and b.pacakge_spec = :xx3
and b.firm_id = :xx4
and a.export_date = c.max_export_date;
主表和明细表分别取两次是不可避免的，但是可以去掉不必要的group by ，这个对性能影响是比较大的
在我们的生产系统，有一条sql语句，问一下大家该如何优化:
SELECT drug_export_detail.retail_price, drug_export_detail.purchase_price
FROM drug_export_detail, drug_export_master
WHERE (drug_export_detail.document_no = drug_export_master.document_no)
AND ( (drug_export_master.STORAGE = :xx1)
AND (drug_export_detail.drug_code = :xx2)
AND (drug_export_detail.package_spec = :xx3)
AND (drug_export_detail.firm_id = :xx4)
)
AND export_date =
(SELECT MAX (export_date)
FROM drug_export_detail, drug_export_master
WHERE (drug_export_detail.document_no =
drug_export_master.document_no
)
AND ( (drug_export_master.STORAGE = :xx1)
AND (drug_export_detail.drug_code = :xx2)
AND (drug_export_detail.package_spec = :xx3)
AND (drug_export_detail.firm_id = :xx4)
)
GROUP BY drug_export_master.STORAGE,
drug_export_detail.drug_code,
drug_export_detail.package_spec,
drug_export_detail.firm_id)
AND ROWNUM = 1; [/quote]
优化成这个行不？
select a.retail_price,a.purchase_price
from drug_export_detail a,
drug_export_master b,
(select max(export_date) max_export_date
from drug_export_detail m,
drug_export_master n
where m.document_no = n.docment_no
and n.storage = :xx1
and n.drug_code = :xx2
and n.package_spec = :xx3
and n.firm_id = :xx4) c
where a.docment_no = b.docment_no
and b.storage = :xx1
and b.drug_code = :xx2
and b.pacakge_spec = :xx3
and b.firm_id = :xx4
and a.export_date = c.max_export_date;
主表和明细表分别取两次是不可避免的，但是可以去掉不必要的group by ，这个对性能影响是比较大的
2008-6-19 17:14
fals
[quote]原帖由 [i]lfree[/i] 于 2008-6-6 10:06 发表 [url=http://www.itpub.net/redirect.php?goto=findpost&pid=10587566&ptid=999793][img]http://www.itpub.net/images/common/back.gif[/img][/url]
SELECT DISTINCT "OPERATION_BILL_ITEMS"."PATIENT_ID",
"OPERATION_BILL_ITEMS"."OPER_ID", "PAT_MASTER_INDEX"."NAME",
"PAT_MASTER_INDEX"."SEX", "PAT_MASTER_INDEX"."CHARGE_TYPE"
FROM "PAT_MASTER_INDEX",
"OPERATION_BILL_ITEMS",
"SURGERY"."OPERATION_MASTER"
WHERE ("SURGERY"."OPERATION_MASTER"."PATIENT_ID" =
"OPERATION_BILL_ITEMS"."PATIENT_ID"
)
AND ("SURGERY"."OPERATION_MASTER"."VISIT_ID" =
"OPERATION_BILL_ITEMS"."VISIT_ID"
)
AND ("SURGERY"."OPERATION_MASTER"."OPER_ID" =
"OPERATION_BILL_ITEMS"."OPER_ID"
)
AND ("OPERATION_BILL_ITEMS"."PATIENT_ID" =
"PAT_MASTER_INDEX"."PATIENT_ID"
)
AND ( (TO_CHAR ("OPERATION_MASTER"."START_DATE_TIME",
'YYYY-MM-DD'
) = ：operating_date
)
AND ("OPERATION_BILL_ITEMS"."PERFORMED_BY" = ：performed_by)
)
再贴一个,现在看军惠的sql写的真烂!!!! [/quote]
这个SQL看不太明白程序员的意图，是不是想查一下在某天手术室有哪些操作员收了哪些病人的费用？为什么偏要指定执行科室？难道是其他科室要查看手术室替自己记了些什么费用么？如果是这样的话，为什么不显示明细，而只显示病人及其身份？
如果不需要限定执行科室，可以优化为如下语句：
SELECT b.PATIENT_ID,
c.OPER_ID, b.NAME,
b.SEX, b.CHARGE_TYPE
FROM PAT_MASTER_INDEX b,
SURGERY.OPERATION_MASTER c
WHERE c.PATIENT_ID = b.PATIENT_ID
AND ((TO_CHAR (c.START_DATE_TIME,'YYYY-MM-DD') = ：operating_date);
SELECT DISTINCT "OPERATION_BILL_ITEMS"."PATIENT_ID",
"OPERATION_BILL_ITEMS"."OPER_ID", "PAT_MASTER_INDEX"."NAME",
"PAT_MASTER_INDEX"."SEX", "PAT_MASTER_INDEX"."CHARGE_TYPE"
FROM "PAT_MASTER_INDEX",
"OPERATION_BILL_ITEMS",
"SURGERY"."OPERATION_MASTER"
WHERE ("SURGERY"."OPERATION_MASTER"."PATIENT_ID" =
"OPERATION_BILL_ITEMS"."PATIENT_ID"
)
AND ("SURGERY"."OPERATION_MASTER"."VISIT_ID" =
"OPERATION_BILL_ITEMS"."VISIT_ID"
)
AND ("SURGERY"."OPERATION_MASTER"."OPER_ID" =
"OPERATION_BILL_ITEMS"."OPER_ID"
)
AND ("OPERATION_BILL_ITEMS"."PATIENT_ID" =
"PAT_MASTER_INDEX"."PATIENT_ID"
)
AND ( (TO_CHAR ("OPERATION_MASTER"."START_DATE_TIME",
'YYYY-MM-DD'
) = ：operating_date
)
AND ("OPERATION_BILL_ITEMS"."PERFORMED_BY" = ：performed_by)
)
再贴一个,现在看军惠的sql写的真烂!!!! [/quote]
这个SQL看不太明白程序员的意图，是不是想查一下在某天手术室有哪些操作员收了哪些病人的费用？为什么偏要指定执行科室？难道是其他科室要查看手术室替自己记了些什么费用么？如果是这样的话，为什么不显示明细，而只显示病人及其身份？
如果不需要限定执行科室，可以优化为如下语句：
SELECT b.PATIENT_ID,
c.OPER_ID, b.NAME,
b.SEX, b.CHARGE_TYPE
FROM PAT_MASTER_INDEX b,
SURGERY.OPERATION_MASTER c
WHERE c.PATIENT_ID = b.PATIENT_ID
AND ((TO_CHAR (c.START_DATE_TIME,'YYYY-MM-DD') = ：operating_date);
2008-6-19 17:20
lfree
写错许多，改正一下。
SELECT a.retail_price, a.purchase_price
FROM drug_export_detail a,
drug_export_master b,
(SELECT MAX (export_date) max_export_date
FROM drug_export_detail m, drug_export_master x
WHERE m.document_no = x.document_no
AND x.STORAGE = :xx1
AND m.drug_code = :xx2
AND m.package_spec = :xx3
AND m.firm_id = :xx4) c
WHERE a.document_no = b.document_no
AND b.STORAGE = :xx1
AND a.drug_code = :xx2
AND a.package_spec = :xx3
AND a.firm_id = :xx4
AND b.export_date = c.max_export_date
AND ROWNUM = 1
SELECT a.retail_price, a.purchase_price
FROM drug_export_detail a,
drug_export_master b,
(SELECT MAX (export_date) max_export_date
FROM drug_export_detail m, drug_export_master x
WHERE m.document_no = x.document_no
AND x.STORAGE = :xx1
AND m.drug_code = :xx2
AND m.package_spec = :xx3
AND m.firm_id = :xx4) c
WHERE a.document_no = b.document_no
AND b.STORAGE = :xx1
AND a.drug_code = :xx2
AND a.package_spec = :xx3
AND a.firm_id = :xx4
AND b.export_date = c.max_export_date
AND ROWNUM = 1
2008-6-19 17:21
lfree
这样确实快一些，主要是可以利用export_date索引。
2008-6-19 17:24
lfree
[quote]原帖由 [i]fals[/i] 于 2008-6-19 17:14 发表 [url=http://www.itpub.net/redirect.php?goto=findpost&pid=10717415&ptid=999793][img]http://www.itpub.net/images/common/back.gif[/img][/url]
这个SQL看不太明白程序员的意图，是不是想查一下在某天手术室有哪些操作员收了哪些病人的费用？为什么偏要指定执行科室？难道是其他科室要查看手术室替自己记了些什么费用么？如果是这样的话，为什么不显示明细，而只显示病人及其身份？
如果不需要限定执行科室，可以优化为如下语句：
SELECT b.PATIENT_ID,
c.OPER_ID, b.NAME,
b.SEX, b.CHARGE_TYPE
FROM PAT_MASTER_INDEX b,
SURGERY.OPERATION_MASTER c
WHERE c.PATIENT_ID = b.PATIENT_ID
AND ((TO_CHAR (c.START_DATE_TIME,'YYYY-MM-DD') = ：operating_date); [/quote]
哈哈，我也不知道为什么？
这个SQL看不太明白程序员的意图，是不是想查一下在某天手术室有哪些操作员收了哪些病人的费用？为什么偏要指定执行科室？难道是其他科室要查看手术室替自己记了些什么费用么？如果是这样的话，为什么不显示明细，而只显示病人及其身份？
如果不需要限定执行科室，可以优化为如下语句：
SELECT b.PATIENT_ID,
c.OPER_ID, b.NAME,
b.SEX, b.CHARGE_TYPE
FROM PAT_MASTER_INDEX b,
SURGERY.OPERATION_MASTER c
WHERE c.PATIENT_ID = b.PATIENT_ID
AND ((TO_CHAR (c.START_DATE_TIME,'YYYY-MM-DD') = ：operating_date); [/quote]
哈哈，我也不知道为什么？
2008-6-19 17:24
fals
[quote]原帖由 [i]lfree[/i] 于 2008-6-19 17:21 发表 [url=http://www.itpub.net/redirect.php?goto=findpost&pid=10717508&ptid=999793][img]http://www.itpub.net/images/common/back.gif[/img][/url]
这样确实快一些，主要是可以利用export_date索引。 [/quote]
改得挺对，我粗心了，没有仔细去看这些表的结构。
一方面可以使用export_date索引，另一方面去掉了不必要的group by ，这个可能引起硬盘排序
这样确实快一些，主要是可以利用export_date索引。 [/quote]
改得挺对，我粗心了，没有仔细去看这些表的结构。
一方面可以使用export_date索引，另一方面去掉了不必要的group by ，这个可能引起硬盘排序
2008-6-19 17:27
fals
[quote]原帖由 [i]lfree[/i] 于 2008-6-19 17:24 发表 [url=http://www.itpub.net/redirect.php?goto=findpost&pid=10717526&ptid=999793][img]http://www.itpub.net/images/common/back.gif[/img][/url]
哈哈，我也不知道为什么？ [/quote]
这个SQL是从哪个窗口里跟踪出来的？抓个图出来看看吧，或者可以修改业务。
那个distinct挺讨厌的，多数情况下都会引起硬盘排序，如果能够去掉的话，会省很多
哈哈，我也不知道为什么？ [/quote]
这个SQL是从哪个窗口里跟踪出来的？抓个图出来看看吧，或者可以修改业务。
那个distinct挺讨厌的，多数情况下都会引起硬盘排序，如果能够去掉的话，会省很多
2008-6-20 09:04
lfree
fals 还是蛮厉害，一下就发现这个distinct。
就是这个distinct，程序员的素质太差了。
实际上不在于去掉，不知道程序员为什么这样写。
就是这个distinct，程序员的素质太差了。
实际上不在于去掉，不知道程序员为什么这样写。
2008-6-20 17:39
fals
是从性能视图里跟出来的么？如果是这样的话，很有必要把产生这个SQL的前台程序抓出来，多花点时间抓一下吧，估计可以去掉这个业务需求。
一条坏的SQL，可以搞死整个数据库。既然已经发现了，就不要放过去。
或者程序员自己都没明白为什么要写这个SQL呢，下面的人也就稀里糊涂地用。
一条坏的SQL，可以搞死整个数据库。既然已经发现了，就不要放过去。
或者程序员自己都没明白为什么要写这个SQL呢，下面的人也就稀里糊涂地用。
2008-6-26 20:51
husthxd
呵呵，没有任何背景知识，看这么长的sql还是有点吃力的。
补充一下，纯技术来看，可以试试用分析函数row_number代替rownum=1
补充一下，纯技术来看，可以试试用分析函数row_number代替rownum=1
2008-6-27 18:40
wollaston
没有用过军惠,用Sqlserver的飘过
页: [1]
查看完整版本: 问一个sql的优化
Powered by ITPUB论坛