Menu
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
"""
842
asked Nov 21 '25 04:11
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.
132
answered Nov 22 '25 19:11
You should run both queries with an explain plan to see the differences. My guess is that in the literal parameter case, an index is being used, and in the bound parameter case it may not be used. IF you can see the difference between the two, you may need to add a "hint" clause to your select statement to force the correct index to be picked. You can read about hints here
If you are using sqlplus, you can turn on autotrace to show you the explain plan and execution statistics, example:
SQL> set autotrace on
SQL> set linesize 200
SQL> set serveroutput on
SQL> spool foo.log
SQL> select count(*) from ucbcust; -- this is just a test table in my database. replace with your SQL.
SQL> ... output shows...
SQL> spool off
Output would look something like this (obviously, different for your query):
Execution Plan
----------------------------------------------------------
Plan hash value: 1527793343 ----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01
| 1 | SORT AGGREGATE | | 1 | |
| 2 | INDEX FAST FULL SCAN| UCBCUST_CUST_KEY_INDEX | 2088 | 3 (0)| 00:00:01
| ----------------------------------------------------------------------------------------
Statistics
0 recursive calls
0 db block gets
17 consistent gets
0 physical reads
0 redo size
343 bytes sent via SQL*Net to client
364 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
To execute the parameterized query in a similar fashion in sqlplus, you will need to define a variable as a placeholder, example:
SQL> variable foo number
SQL> exec :foo := 1000
PL/SQL procedure successfully completed.
SQL> select :foo from dual
2 /
:FOO
----------
1000
So, in your query, replace all the "%*" parameters with a variable and run in Sqlplus to see the execution details. Hopefully, with those tools, you will see the difference in the execution plans.
37
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
