StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PODB2 Inner Query slow but fast with hardcoded values
text
Body
copied!<p>I'm getting a weird behavior in DB2 sql. (DB2 9.7) Follow is a sample query to fetch employee salary, status and band..</p> <pre><code>SELECT EMP.STATUS, COUNT(*) AS EMP_COUNT, GRP.GROUP_NAME FROM EMPLOYEE EMP, EMPLOYEE_SALARY ES, GROUP_TABLE GRP WHERE EMP.SALARY > (select max(EMP1.SALARY) from EMPLOYEE EMP1, FINANCIAL_YEAR FY where date(EMP1.JOIN_DT) = '2013-01-01' and date(EMP1.DATE_TS) = date(FY.CURRENT_DT) - 2 days) AND EMP.SALARY = E.EMPID AND E.SALARY_GRP = GRP.BAND_GROUP AND GRP.RANGE_SALARY = 'BAND-10' GROUP BY EMP.STATUS, GRP.GROUP_NAME </code></pre> <p>The EMP (employee) table contains around 1million rows. Rest of the tables are very small. The query takes around 10secs to execute</p> <p>But when I hard-code the inner query </p> <p>From</p> <pre><code>select max(EMP1.SALARY) from EMPLOYEE EMP1, FINANCIAL_YEAR FY where date(EMP1.JOIN_DT) = '2013-01-01' and date(EMP1.DATE_TS) = date(FY.CURRENT_DT) - 2 days </code></pre> <p>to</p> <pre><code>select max(EMP1.SALARY) from EMPLOYEE EMP1, FINANCIAL_YEAR FY where date(EMP1.JOIN_DT) = '2013-01-01' and date(EMP1.DATE_TS) = '2013-06-01' </code></pre> <p>The result comes within a second !!</p> <p>"FINANCIAL_YEAR FY" table is a very small table with around 50 rows and hence I don't know why the inner query takes time of itself when its dynamic but very fast when I hard-code</p> <p>Some additional Info</p> <ul> <li>EMPID is integer</li> <li>DATE_TS is timestamp</li> <li>CURRENT_DT, JOIN_DT is date</li> <li>Rest all are VARCHAR</li> <li>EMPID is indexed</li> </ul>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload