Date parameter causes query to run slowly

Date parameter causes query to run slowly

Postby vzczc » Wed 28 Oct 2009 11:31

Version: 5.25.44.0

Using Direct Mode I am having trouble with certain queries involving date parameters.

SELECT col
FROM tbl
WHERE dateCol>:dateParameter
and dateCol2<=:dateParameter2

This runs very slowly, whereas the following query (either in plain text or with string parameters runs fast.

SELECT col
FROM tbl
WHERE dateCol>to_date(:dateParameter, 'yyyymmddhh24miss')
and dateCol2<=to_date(:dateParameter2, 'yyyymmddhh24miss')

What can cause this? Is it a bug or the Oracle instance to blame?
vzczc
 
Posts: 3
Joined: Fri 05 Sep 2008 10:09

Postby Shalex » Wed 28 Oct 2009 15:22

Could you please try the 5.25.49 version? If the problem persists, please send us ( http://www.devart.com/company/contact.html ) a small test project with the DDL/DML script. We will try to reproduce the issue.
Shalex
Devart Team
 
Posts: 7341
Joined: Thu 14 Aug 2008 12:44

Re: Date parameter causes query to run slowly

Postby rwrife » Wed 18 Nov 2009 14:20

vzczc wrote:SELECT col
FROM tbl
WHERE dateCol>:dateParameter
and dateCol2<=:dateParameter2


Does the query run slowly when you run it through a query utility (ie Toad)? If so then it could be that the dateCol is needs and index or the query execution plan needs to be updated on the server. If date columns are not indexed in Oracle it'll have to do a full table scan since it doesn't know the min/max date....same thing happens if the execution plan gets out of whack and a record has a value outside of the plan's expected min/max.
rwrife
 
Posts: 2
Joined: Wed 18 Nov 2009 14:14

Postby Alladin » Sun 22 Nov 2009 16:09

Please, make sure that parameters declared are of type OracleDbType.Date
Alladin
 
Posts: 149
Joined: Mon 27 Nov 2006 16:18


Return to dotConnect for Oracle