Date parameter causes query to run slowly

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
vzczc
Posts: 3
Joined: Fri 05 Sep 2008 10:09

Date parameter causes query to run slowly

Post by 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 dateCol2to_date(:dateParameter, 'yyyymmddhh24miss')
and dateCol2<=to_date(:dateParameter2, 'yyyymmddhh24miss')

What can cause this? Is it a bug or the Oracle instance to blame?

Shalex
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

Post by 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.

rwrife
Posts: 2
Joined: Wed 18 Nov 2009 14:14

Re: Date parameter causes query to run slowly

Post by 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.

Alladin
Posts: 149
Joined: Mon 27 Nov 2006 16:18
Contact:

Post by Alladin » Sun 22 Nov 2009 16:09

Please, make sure that parameters declared are of type OracleDbType.Date

Post Reply