cx-oracle-users

Re: [cx-oracle-users] binding datetime in query

[Rizzuto, R. <Ray...@si...>]

I suspect the issue may be that Oracle is converting the Date column to a datetime, and comparing each row with the datetime I passed in the bound variable.

I tried to use datetime().date(), but cxOracle seems to still convert that to a datetime.

From: Rizzuto, Raymond
Sent: Monday, February 14, 2011 2:13 PM
To: 'cx-...@li...'
Subject: binding datetime in query

I am issuing a query to our database, with a where clause that is comparing a Date column to a parameter.  The parameter is bound to a datetime object.  I am using version 5.02.  Will the datetime object be sent as a native Oracle date type, or will it be sent as a string?  If the latter, what format would it be in?

The reason I ask is that the DBA's think the query is not performing well, and there is concern that the optimizer is not using the index on the date column due to the way I am passing in the date.

The date parameter is being passed via a dictionary, like so:

params["p_order_date"] = orderDate.datetime()  # returns a datetime type from a custom class
.
.
cursor.execute(query, params)

The query string passed to execute is built dynamically, and executed once.

Ray

________________________________
Ray Rizzuto
ray...@si...<mailto:ray...@si...>
Susquehanna International Group
(610)747-2336 (W)
(215)776-3780 (C)


________________________________
IMPORTANT: The information contained in this email and/or its attachments is confidential. If you are not the intended recipient, please notify the sender immediately by reply and immediately delete this message and all its attachments. Any review, use, reproduction, disclosure or dissemination of this message or any attachment by an unintended recipient is strictly prohibited. Neither this message nor any attachment is intended as or should be construed as an offer, solicitation or recommendation to buy or sell any security or other financial instrument. Neither the sender, his or her employer nor any of their respective affiliates makes any warranties as to the completeness or accuracy of any of the information contained herein or that this message or any of its attachments is free of viruses.

Re: [cx-oracle-users] binding datetime in query

[Chris A. <chr...@ub...>]

Issued by UBS AG or affiliates to professional investors only.
Product of a sales/trading desk and not the Research Department. 
For the full published research report, including important 
disclosures, go to the ResearchWeb at www.ubs.com.  Opinions 
expressed may differ from those of other divisions of UBS, including 
Research. 
UBS may trade as principal in instruments identified herein and may 
accumulate/have accumulated a long/short position in instruments or 
derivatives thereof. UBS has policies designed to manage conflicts of 
interest. This e-mail is not an official confirmation of terms and 
unless stated, is not a personal recommendation, offer or 
solicitation to buy or sell. Any prices or quotations contained 
herein are indicative only and not for valuation purposes. 
Communications may be monitored.

Statement of Risk
Options, structured derivative products and futures are not suitable
for all investors, and trading in these instruments is considered 
risky and may be appropriate only for sophisticated investors. Past 
performance is not necessarily indicative of future results. Various 
theoretical explanations of the risks associated with these 
instruments have been published. Prior to buying or selling an 
option, and for the complete risks relating to options, you must 
receive a copy of "The Characteristics and Risks of Standardized 
Options." You may read the document at 
http://www.theocc.com/publications/risks/riskchap1.jsp
or ask your salesperson for a copy.

UBS 2011.  All rights reserved.
Intended for recipient only and not for further distribution without
the consent of UBS.

UBS reserves the right to retain all messages. Messages are protected
and accessed only in legally justified cases.

Re: [cx-oracle-users] binding datetime in query

[Mark H. <mh...@pi...>]

On 2/14/11 12:32 PM, Rizzuto, Raymond wrote:
> I suspect the issue may be that Oracle is converting the Date column to a datetime, and comparing each row with the datetime I passed in the bound variable.
>
> I tried to use datetime().date(), but cxOracle seems to still convert that to a datetime.

what happens if you use to_date() in your query string, and pass in the parameter as
a string?

This will confirm that the execution plan is indeed using a date type,
and you can see if the optimizer is still skipping over the index on
the date column.

if so, then there's some db or query tuning that needs to be done; if not,
the problem is verified to be one of date conversion on the client side.

hth!

Re: [cx-oracle-users] binding datetime in query

[Rizzuto, R. <Ray...@si...>]

I did that test, and the to_date() on a string was no faster than passing the native datetime object.   I'm quite glad that the information I was given was incorrect, and cx_Oracle is working as expected.

-----Original Message-----
From: Mark Harrison [mailto:mh...@pi...]
Sent: Monday, February 14, 2011 3:41 PM
To: cx-...@li...
Subject: Re: [cx-oracle-users] binding datetime in query

On 2/14/11 12:32 PM, Rizzuto, Raymond wrote:
> I suspect the issue may be that Oracle is converting the Date column to a datetime, and comparing each row with the datetime I passed in the bound variable.
>
> I tried to use datetime().date(), but cxOracle seems to still convert that to a datetime.

what happens if you use to_date() in your query string, and pass in the parameter as
a string?

This will confirm that the execution plan is indeed using a date type,
and you can see if the optimizer is still skipping over the index on
the date column.

if so, then there's some db or query tuning that needs to be done; if not,
the problem is verified to be one of date conversion on the client side.

hth!





IMPORTANT: The information contained in this email and/or its attachments is confidential. If you are not the intended recipient, please notify the sender immediately by reply and immediately delete this message and all its attachments. Any review, use, reproduction, disclosure or dissemination of this message or any attachment by an unintended recipient is strictly prohibited. Neither this message nor any attachment is intended as or should be construed as an offer, solicitation or recommendation to buy or sell any security or other financial instrument. Neither the sender, his or her employer nor any of their respective affiliates makes any warranties as to the completeness or accuracy of any of the information contained herein or that this message or any of its attachments is free of viruses.