Jdbc Query With Dates

[lhowell]

When I run the JDBC query with the date equal it runs extremely fast but when we change the where clause to greater than/less than it is extremely slow. I've included the two queries. How do we get the greater than/less than to run just as fast as the equal?

SELECT OEEL.orderno, OEEL.invoicedt
FROM PUB.OEEL AS OEEL
INNER JOIN PUB.OEEH AS OEEH ON OEEL.cono = OEEH.cono AND OEEL.orderno = OEEH.orderno AND OEEL.ordersuf = OEEH.ordersuf
WHERE oeel.cono = 1 AND oeel.statustype IN ('I','i') AND oeel.whse = 'IA' AND OEEL.invoicedt = '2015-10-01'
AND oeel.specnstype IN ('','n','N','s','S') AND OEEH.stagecd IN ('4', '5')

VS.

SELECT OEEL.orderno, OEEL.invoicedt
FROM PUB.OEEL AS OEEL
INNER JOIN PUB.OEEH AS OEEH ON OEEL.cono = OEEH.cono AND OEEL.orderno = OEEH.orderno AND OEEL.ordersuf = OEEH.ordersuf
WHERE oeel.cono = 1 AND oeel.statustype IN ('I','i') AND oeel.whse = 'IA' AND OEEL.invoicedt >= '2015-10-01' AND OEEL.invoicedt <= '2015-10-01' AND oeel.specnstype IN ('','n','N','s','S') AND OEEH.stagecd IN ('4', '5')

 

[TomBascom]

If nothing else an equality match on a specific date is going to return much less data. Your ">=" and "<=" variation is, essentially, a table scan. Assuming that there is data with a variety of invoicedt values I would be shocked if it *didn't* take a lot longer.

 

[TheMadDBA]

I recognize those tables

You would need an index that has the following columns with invoicedt at the very end for the database to be able to use the index properly with date ranges... for this exact query

oeel.cono
oeel.statustype
oeel.whse
oeel.specnstype
oeel.invoicedt

Once you start using ranges the OpenEdge database stops using the index... so ranges only work well when the ranged column is the last component of the index.