[cx-oracle-users] Performance differences between bind parameters and string substitution
Brought to you by:
atuining
From: Hancock, D. \(DHANCOCK\) <DHA...@ar...> - 2005-11-01 20:38:36
|
We're using Oracle 10g, Python 2.3, cx_Oracle "HEAD" (sorry no better version number than that, but we picked up the latest version--4.1--earlier this year and compiled against 10g headers). We're seeing counterintuitive behavior with one of our queries (we can't reproduce it with other "biggish" queries). If we construct the query by substituting strings instead of using bind parameters, the query runs 100 times faster! For the same dataset, that's 30 seconds with bind parameters and 0.3 seconds with string substitution. We're holding as much constant as possible (arraysize, for example, is staying at 20). This is repeatable. The furthest we've gotten towards isolating the problem is to learn that we can reproduce the "fix" by only string-substituting the date fields. A few more data points: When we "explain plan" on both versions of the query, we get identical results. When we run both queries in SQL*Plus, we get consistent fast results (< 1 second). But when we run both queries from Python via cx_Oracle, we get the huge discrepancy. We only started noticing this a couple weeks ago, but that doesn't mean it wasn't happening before, we just didn't notice. We've worked around this by changing just this one query to do string substitution, but I'd REALLY like to know what the cause is and fix it. Does this ring a bell for anyone? I'd expect bind-parameter queries to run faster, not slower, and this one is a LOT slower. Cheers! -- David Hancock | dha...@ar... | 410-266-4384=20 |