具有远程Oracle服务器的Django性能非常低 [英] Very slow performance in Django with remote Oracle server
本文介绍了具有远程Oracle服务器的Django性能非常低的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
为什么性能有太大差异？我如何加快Django中的查询？
BTW，我使用的是Django的开发服务器和配置文件工具栏（Django 1.5）。
更新： Django中的查询
holidays_filter = ''
如果过滤器['holidays'] =='exclude'：
holidays_filter ='AND FECHAS.FESTIVO = 0'
hp_inner_join =''
if过滤器['hour-mode'] =='hp-sector-ps'：
hp_inner_join =
INNER JOIN
EGW.RHP_CELLSEC_PS HPCELLPS
ON UCELL2.DIA = HPCELLPS.DIA
和UCELL2.HORA = HPCELLPS.HORA
和UCELL2.RNC = HPCELLPS.RNC
和UCELL2.UTRANCELL = HPCELLPS.CELLID
elif filters ['hour-mode'] =='hp-rnc-ps'：
hp_inner_join =
INNER JOIN
EGW.RHP_RNC_PS HPRNCPS
ON UCELL2.DIA = HPRNCPS.DIA
AND UCELL2.HORA = HPRNCPS.HORA
AND UCELL2.RNC = HPRNCPS.RNC
sql =
SELECT CUSTOM.DIA，CUSTOM.HORA，
ROUND（CUSTOM.TRAF_CS57 + CUSTOM.TRAF_CS64 + CUSTOM.TRAF_CS12 + CUSTOM.TRAF_CSAMR12200 + CUSTOM.TRAF_CSAMR7950 + CUSTOM.TRAF_CSAMR5900 + CUSTOM.TRAF_CSAMR4750，1）AS TRAFICO_CS_ERL，
ROUND（CUSTOM.A + CUSTOM.B + CUSTOM.C + CUSTOM。 D，1）AS TRAFICO_PS_ERL
FROM（SELECT TOTAL.DIA，TOTAL.HORA，TOTAL.RNC，TOTAL.UTRANCELL，
CASE
WHEN TOTAL.PMSAMPLESCS12RABESTABLISH = 0
THEN 0
ELSE TOTAL.PMSUMCS12RABESTABLISH / TOTAL.PMSAMPLESCS12RABESTABLISH
END AS TRAF_CS12，
CASE
WHEN TOTAL.PMSAMPLESBESTCS57RABESTABLISH = 0
THEN 0
ELSE TOTAL.PMSUMBESTCS57RABESTABLISH / TOTAL.PMSAMPLESBESTCS57RABESTABLISH
END AS TRAF _CS57，
CASE
WHEN TOTAL.PMSAMPLESBESTCS64RABESTABLISH = 0
THEN 0
ELSE TOTAL.PMSUMBESTCS64RABESTABLISH / TOTAL.PMSAMPLESBESTCS64RABESTABLISH
结束作为TRAF_CS64，
CASE
WHEN TOTAL.PMSAMPLBESTAMR12200RABESTABLIS = 0
THEN 0
ELSE TOTAL.PMSUMBESTAMR12200RABESTABLISH / TOTAL.PMSAMPLBESTAMR12200RABESTABLIS
END作为TRAF_CSAMR12200，
CASE
WHEN TOTAL.PMSAMPLBESTAMR7950RABESTABLISH = 0
THEN 0
ELSE TOTAL.PMSUMBESTAMR7950RABESTABLISH / TOTAL.PMSAMPLBESTAMR7950RABESTABLISH
END作为TRAF_CSAMR7950，
CASE
WHEN TOTAL.PMSAMPLBESTAMR5900RABESTABLISH = 0
THEN 0
ELSE TOTAL.PMSUMBESTAMR5900RABESTABLISH / TOTAL.PMSAMPLBESTAMR5900RABESTABLISH
结束作为TRAF_CSAMR5900，
CASE
WHEN TOTAL.PMSAMPLBESTAMR4750RABESTABLISH = 0
THEN 0
ELSE TOTAL.PMSUMBESTAMR4750RABESTABLISH / TOTAL.PMSAMPLBESTAMR4750RABESTABLISH
END AS TRAF_CSAMR4750，
CASE
WHEN TOTAL.PMSAMPLEBESTDCHPSINTRABESTABLI = 0
THEN 0
ELSE TOTAL.PMSUMBESTDCHPSINTRABESTABLISH / TOTAL.PMSAMPLEBESTDCHPSINTRABESTABLI
END AS A，
CASE
WHEN TOTAL.PMSAMPLEFACHPSINTRABESTABLISH = 0
THEN 0
ELSE TOTAL.PMSUMFACHPSINTRABESTABLISH / TOTAL.PMSAMPLEFACHPSINTRABESTABLISH
END作为B，
CASE
WHEN TOTAL.PMSAMPBESTPSHSADCHRABESTABLISH = 0
THEN 0
ELSE TOTAL.PMSUMBESTPSHSADCHRABESTABLISH / TOTAL.PMSAMPBESTPSHSADCHRABESTABLISH
END AS C，
CASE
WHEN TOTAL.PMSAMPLBESTPSEULRABESTABLI = 0
THEN 0
ELSE TOTAL.PMSUMBESTPSEULRABESTABLISH / TOTAL.PMSAMPLBESTPSEULRABESTABLI
END AS D
FROM（SELECT UCELL2.DIA，UCELL2.HORA，UCELL2.RNC，UCELL2.UTRANCELL，
SUM（UCELL2.PMSAMPLESCS12RABESTABLISH）AS PMSAMPLESCS12RABESTABLISH，
SUM（UCELL2.PMSUMCS12RABESTABLISH）AS PMSUMCS12RABESTABLISH，
SUM（UCELL3.PMSAMPLESBESTCS57RABESTABLISH）AS PMSAMPLESBESTCS57RABESTABLISH，
SUM（UCELL3.PMSUMBESTCS57RABESTABLISH）AS PMSUMBESTCS57RABESTABLISH ，
SUM（UCELL 3.PMSAMPLESBESTCS64RABESTABLISH）AS PMSAMPLESBESTCS64RABESTABLISH，
SUM（UCELL3.PMSUMBESTCS64RABESTABLISH）AS PMSUMBESTCS64RABESTABLISH，
SUM（UCELL3.PMSUMBESTDCHPSINTRABESTABLISH）AS PMSUMBESTDCHPSINTRABESTABLISH，
SUM（UCELL3.PMSAMPLEBESTDCHPSINTRABESTABLI）AS PMSAMPLEBESTDCHPSINTRABESTABLI，
SUM（UCELL3.PMSUMFACHPSINTRABESTABLISH）AS PMSUMFACHPSINTRABESTABLISH，
SUM（UCELL3.PMSAMPLEFACHPSINTRABESTABLISH）AS PMSAMPLEFACHPSINTRABESTABLISH，
SUM（UCELL3.PMSUMBESTPSHSADCHRABESTABLISH）AS PMSUMBESTPSHSADCHRABESTABLISH，
SUM（UCELL3.PMSAMPBESTPSHSADCHRABESTABLISH）AS PMSAMPBESTPSHSADCHRABESTABLISH，
SUM（UCELL3.PMSUMBESTPSEULRABESTABLISH）作为PMSUMBESTPSEULRABESTABLISH，
SUM（UCELL3.PMSAMPLBESTPSEULRABESTABLI）作为PMSAMPLBESTPSEULRABESTABLI，
SUM（UCELL4.PMSAMPLBESTAMR12200RABESTABLIS）AS PMSAMPLBESTAMR12200RABESTABLIS，
SUM（UCELL4.PMSUMBESTAMR12200RABESTABLISH）AS PMSUMBESTAMR12200RABESTABLISH，
SUM（UCELL4.PMSAMPLBESTAMR7950RABESTABLISH）AS PMSAMPLBESTAMR7950RABESTABLISH，
SUM（UCELL4.PMSUMBESTAMR7950RABESTABLISH）AS PMSUMBESTAMR7950RABESTABLISH ，
SUM（UCELL4.PMSAMPLBESTAMR5900RABESTABLISH）AS PMSAMPLBESTAMR5900RABESTABLISH，
SUM（UCELL4.PMSUMBESTAMR5900RABESTABLISH）AS PMSUMBESTAMR5900RABESTABLISH，
SUM（UCELL4.PMSAMPLBESTAMR4750RABESTABLISH）AS PMSAMPLBESTAMR4750RABESTABLISH，
SUM（UCELL4.PMSUMBESTAMR4750RABESTABLISH） AS PMSUMBESTAMR4750RABESTABLISH
从EGW.TF_RNC_RAN_UCELL2 UCELL2
INNER JOIN
EGW.TF_RNC _RAN_UCELL3 UCELL3
在UCELL2.DIA = UCELL3.DIA
和UCELL2.HORA = UCELL3.HORA
和UCELL2.RNC = UCELL3.RNC
和UCELL2.MO = UCELL3.MO
和UCELL2.MINUTO = UCELL3.MINUTO
和UCELL2.UTRANCELL = UCELL3.UTRANCELL
INNER JOIN
EGW.TF_RNC_RAN_UCELL4 UCELL4
ON UCELL2.DIA = UCELL4.DIA
和UCELL2.HORA = UCELL4.HORA
和UCELL2.RNC = UCELL4.RNC
和UCELL2.MO = UCELL4.MO
和UCELL2.MINUTO = UCELL4 .MINUTO
和UCELL2.UTRANCELL = UCELL4.UTRANCELL
INNER JOIN
JANO.FECHAS FECHAS
ON UCELL2.DIA = FECHAS.FECHA
+ hp_inner_join +
WHERE UCELL2.DIA BETWEEN TO_DATE（％s，'YYYY-MM-DD' ）AND TO_DATE（％s，'YYYY-MM-DD'）
AND UCELL2.HORA BETWEEN％s AND％s
AND UCELL2.RNC =％s
AND UCELL2.UTRANCELL =％ s
和FECHAS.DIASEM IN（％s，％s，％s，％s，％s，％s，％s）
+ holidays_filter +
GROUP BY UCELL2.DIA，UCELL2.HORA，UCELL2.RNC，UCELL2.UTRANCELL）TOTAL）CUSTOM
订单由CUSTOM.DIA，CUSTOM.HORA
解决方案
确定我找到了解决方案。在Django中，所有绑定的参数都被定义为字符串（％s），所以当参数是一个数字时进行大量的隐式转换。解决方案是使用 WHERE 子句中的函数 TO_NUMBER（％s）明确地将其转换为数字。 p>
I'm running a raw SQL query in Django to a remote Oracle Server. The query is very long and it took a minute and a half to complete, but if I do the same query using the Oracle SQL Server program, the query runs in less than a second.
Why is there so much difference in performance? How can I speed up my query in Django?
BTW, I'm using the Django's dev server and the profile toolbar (Django 1.5).
UPDATE: Here is the query in Django
holidays_filter = ''
if filters['holidays'] == 'exclude':
holidays_filter = 'AND FECHAS.FESTIVO = 0'
hp_inner_join = ''
if filters['hour-mode'] == 'hp-sector-ps':
hp_inner_join = """
INNER JOIN
EGW.RHP_CELLSEC_PS HPCELLPS
ON UCELL2.DIA = HPCELLPS.DIA
AND UCELL2.HORA = HPCELLPS.HORA
AND UCELL2.RNC = HPCELLPS.RNC
AND UCELL2.UTRANCELL = HPCELLPS.CELLID
"""
elif filters['hour-mode'] == 'hp-rnc-ps':
hp_inner_join = """
INNER JOIN
EGW.RHP_RNC_PS HPRNCPS
ON UCELL2.DIA = HPRNCPS.DIA
AND UCELL2.HORA = HPRNCPS.HORA
AND UCELL2.RNC = HPRNCPS.RNC
"""
sql = """
SELECT CUSTOM.DIA, CUSTOM.HORA,
ROUND(CUSTOM.TRAF_CS57 + CUSTOM.TRAF_CS64 + CUSTOM.TRAF_CS12 + CUSTOM.TRAF_CSAMR12200 + CUSTOM.TRAF_CSAMR7950 + CUSTOM.TRAF_CSAMR5900 + CUSTOM.TRAF_CSAMR4750, 1) AS TRAFICO_CS_ERL,
ROUND(CUSTOM.A + CUSTOM.B + CUSTOM.C + CUSTOM.D, 1) AS TRAFICO_PS_ERL
FROM (SELECT TOTAL.DIA, TOTAL.HORA, TOTAL.RNC, TOTAL.UTRANCELL,
CASE
WHEN TOTAL.PMSAMPLESCS12RABESTABLISH = 0
THEN 0
ELSE TOTAL.PMSUMCS12RABESTABLISH / TOTAL.PMSAMPLESCS12RABESTABLISH
END AS TRAF_CS12,
CASE
WHEN TOTAL.PMSAMPLESBESTCS57RABESTABLISH = 0
THEN 0
ELSE TOTAL.PMSUMBESTCS57RABESTABLISH / TOTAL.PMSAMPLESBESTCS57RABESTABLISH
END AS TRAF_CS57,
CASE
WHEN TOTAL.PMSAMPLESBESTCS64RABESTABLISH = 0
THEN 0
ELSE TOTAL.PMSUMBESTCS64RABESTABLISH / TOTAL.PMSAMPLESBESTCS64RABESTABLISH
END AS TRAF_CS64,
CASE
WHEN TOTAL.PMSAMPLBESTAMR12200RABESTABLIS = 0
THEN 0
ELSE TOTAL.PMSUMBESTAMR12200RABESTABLISH / TOTAL.PMSAMPLBESTAMR12200RABESTABLIS
END AS TRAF_CSAMR12200,
CASE
WHEN TOTAL.PMSAMPLBESTAMR7950RABESTABLISH = 0
THEN 0
ELSE TOTAL.PMSUMBESTAMR7950RABESTABLISH / TOTAL.PMSAMPLBESTAMR7950RABESTABLISH
END AS TRAF_CSAMR7950,
CASE
WHEN TOTAL.PMSAMPLBESTAMR5900RABESTABLISH = 0
THEN 0
ELSE TOTAL.PMSUMBESTAMR5900RABESTABLISH / TOTAL.PMSAMPLBESTAMR5900RABESTABLISH
END AS TRAF_CSAMR5900,
CASE
WHEN TOTAL.PMSAMPLBESTAMR4750RABESTABLISH = 0
THEN 0
ELSE TOTAL.PMSUMBESTAMR4750RABESTABLISH / TOTAL.PMSAMPLBESTAMR4750RABESTABLISH
END AS TRAF_CSAMR4750,
CASE
WHEN TOTAL.PMSAMPLEBESTDCHPSINTRABESTABLI = 0
THEN 0
ELSE TOTAL.PMSUMBESTDCHPSINTRABESTABLISH / TOTAL.PMSAMPLEBESTDCHPSINTRABESTABLI
END AS A,
CASE
WHEN TOTAL.PMSAMPLEFACHPSINTRABESTABLISH = 0
THEN 0
ELSE TOTAL.PMSUMFACHPSINTRABESTABLISH / TOTAL.PMSAMPLEFACHPSINTRABESTABLISH
END AS B,
CASE
WHEN TOTAL.PMSAMPBESTPSHSADCHRABESTABLISH = 0
THEN 0
ELSE TOTAL.PMSUMBESTPSHSADCHRABESTABLISH / TOTAL.PMSAMPBESTPSHSADCHRABESTABLISH
END AS C,
CASE
WHEN TOTAL.PMSAMPLBESTPSEULRABESTABLI = 0
THEN 0
ELSE TOTAL.PMSUMBESTPSEULRABESTABLISH / TOTAL.PMSAMPLBESTPSEULRABESTABLI
END AS D
FROM (SELECT UCELL2.DIA, UCELL2.HORA, UCELL2.RNC, UCELL2.UTRANCELL,
SUM(UCELL2.PMSAMPLESCS12RABESTABLISH) AS PMSAMPLESCS12RABESTABLISH,
SUM(UCELL2.PMSUMCS12RABESTABLISH) AS PMSUMCS12RABESTABLISH,
SUM(UCELL3.PMSAMPLESBESTCS57RABESTABLISH) AS PMSAMPLESBESTCS57RABESTABLISH,
SUM(UCELL3.PMSUMBESTCS57RABESTABLISH) AS PMSUMBESTCS57RABESTABLISH,
SUM(UCELL3.PMSAMPLESBESTCS64RABESTABLISH) AS PMSAMPLESBESTCS64RABESTABLISH,
SUM(UCELL3.PMSUMBESTCS64RABESTABLISH) AS PMSUMBESTCS64RABESTABLISH,
SUM(UCELL3.PMSUMBESTDCHPSINTRABESTABLISH) AS PMSUMBESTDCHPSINTRABESTABLISH,
SUM(UCELL3.PMSAMPLEBESTDCHPSINTRABESTABLI) AS PMSAMPLEBESTDCHPSINTRABESTABLI,
SUM(UCELL3.PMSUMFACHPSINTRABESTABLISH) AS PMSUMFACHPSINTRABESTABLISH,
SUM(UCELL3.PMSAMPLEFACHPSINTRABESTABLISH) AS PMSAMPLEFACHPSINTRABESTABLISH,
SUM(UCELL3.PMSUMBESTPSHSADCHRABESTABLISH) AS PMSUMBESTPSHSADCHRABESTABLISH,
SUM(UCELL3.PMSAMPBESTPSHSADCHRABESTABLISH) AS PMSAMPBESTPSHSADCHRABESTABLISH,
SUM(UCELL3.PMSUMBESTPSEULRABESTABLISH) AS PMSUMBESTPSEULRABESTABLISH,
SUM(UCELL3.PMSAMPLBESTPSEULRABESTABLI) AS PMSAMPLBESTPSEULRABESTABLI,
SUM(UCELL4.PMSAMPLBESTAMR12200RABESTABLIS) AS PMSAMPLBESTAMR12200RABESTABLIS,
SUM(UCELL4.PMSUMBESTAMR12200RABESTABLISH) AS PMSUMBESTAMR12200RABESTABLISH,
SUM(UCELL4.PMSAMPLBESTAMR7950RABESTABLISH) AS PMSAMPLBESTAMR7950RABESTABLISH,
SUM(UCELL4.PMSUMBESTAMR7950RABESTABLISH) AS PMSUMBESTAMR7950RABESTABLISH,
SUM(UCELL4.PMSAMPLBESTAMR5900RABESTABLISH) AS PMSAMPLBESTAMR5900RABESTABLISH,
SUM(UCELL4.PMSUMBESTAMR5900RABESTABLISH) AS PMSUMBESTAMR5900RABESTABLISH,
SUM(UCELL4.PMSAMPLBESTAMR4750RABESTABLISH) AS PMSAMPLBESTAMR4750RABESTABLISH,
SUM(UCELL4.PMSUMBESTAMR4750RABESTABLISH) AS PMSUMBESTAMR4750RABESTABLISH
FROM EGW.TF_RNC_RAN_UCELL2 UCELL2
INNER JOIN
EGW.TF_RNC_RAN_UCELL3 UCELL3
ON UCELL2.DIA = UCELL3.DIA
AND UCELL2.HORA = UCELL3.HORA
AND UCELL2.RNC = UCELL3.RNC
AND UCELL2.MO = UCELL3.MO
AND UCELL2.MINUTO = UCELL3.MINUTO
AND UCELL2.UTRANCELL = UCELL3.UTRANCELL
INNER JOIN
EGW.TF_RNC_RAN_UCELL4 UCELL4
ON UCELL2.DIA = UCELL4.DIA
AND UCELL2.HORA = UCELL4.HORA
AND UCELL2.RNC = UCELL4.RNC
AND UCELL2.MO = UCELL4.MO
AND UCELL2.MINUTO = UCELL4.MINUTO
AND UCELL2.UTRANCELL = UCELL4.UTRANCELL
INNER JOIN
JANO.FECHAS FECHAS
ON UCELL2.DIA = FECHAS.FECHA
""" + hp_inner_join + """
WHERE UCELL2.DIA BETWEEN TO_DATE(%s, 'YYYY-MM-DD') AND TO_DATE(%s, 'YYYY-MM-DD')
AND UCELL2.HORA BETWEEN %s AND %s
AND UCELL2.RNC = %s
AND UCELL2.UTRANCELL = %s
AND FECHAS.DIASEM IN (%s,%s,%s,%s,%s,%s,%s)
""" + holidays_filter + """
GROUP BY UCELL2.DIA, UCELL2.HORA, UCELL2.RNC, UCELL2.UTRANCELL) TOTAL) CUSTOM
ORDER BY CUSTOM.DIA, CUSTOM.HORA
"""
解决方案
OK I found the solution. In Django all binded parameters are defined as strings (%s) so a lot of implicit conversion is made when the parameter is, for instance, a number. The solution is to explicitly typecast them as number using the function TO_NUMBER(%s) in the WHERE clause.
这篇关于具有远程Oracle服务器的Django性能非常低的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
