Home » SQL & PL/SQL » SQL & PL/SQL » SYSDATE Performance
SYSDATE Performance [message #351716] Thu, 02 October 2008 13:49 Go to next message
dguad
Messages: 4
Registered: October 2008
Junior Member
Hi All...Great Forum,

I have a simple query that pulls rows by expiration date from a table with 2 Mil+ records. When I put a date in the where clause (ex. 02-OCT-08) the query takes about 80secs to run. If I use sysdate + 1 (to get 02-OCT-08) the query time triples to 220secs. Is there a proper way to use sysdate in an SQL query? I am attempt to have the query pull tomorrow's expiration date. I have tried the following.

1) where exp_date = '02-OCT-08'; 80secs
2) where exp_date = TO_DATE(sysdate) + 1; 200secs
3) where TO_DATE(exp_date) = TO_DATE(sysdate) + 1; 350secs
4) where TO_DATE(exp_date,'DD-MON-YYYY') = TO_DATE(sysdate, 'DD-MON-YYYY') + 1; 280secs

EXP_DATE is Type DATE

Thank you in Advance

[Updated on: Thu, 02 October 2008 13:51]

Report message to a moderator

Re: SYSDATE Performance [message #351718 is a reply to message #351716] Thu, 02 October 2008 14:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
TO_DATE(sysdate) is WRONG.
SYSDATE is a DATE and converting a date to a date is... (choose your word).

Same thing for TO_DATE(exp_date) (with or without format).

Regards
Michel
Re: SYSDATE Performance [message #351719 is a reply to message #351718] Thu, 02 October 2008 14:12 Go to previous messageGo to next message
dguad
Messages: 4
Registered: October 2008
Junior Member
Thank you for your response.

where exp_date = sysdate still doubles the queries performance. Is there somethine else i should be using to programatically get the date?
Re: SYSDATE Performance [message #351720 is a reply to message #351718] Thu, 02 October 2008 14:26 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@Michel,

Quote:

TO_DATE(sysdate) is WRONG.



I just wanted to clarify something about this. I also thought this was wrong until I tried the following queries. I was under the impression that its truncating the date to start of a particular day and storing the truncated date in date format.

Please consider the following codes

SELECT SYSDATE Date_Col FROM Dual;

DATE_COL
---------------------
10/2/2008 3:18:33 PM


SELECT TO_DATE(SYSDATE) Date_Col FROM Dual;

DATE_COL
---------------------
10/2/2008


SELECT TO_CHAR(TO_DATE(SYSDATE), 'MM/DD/YYYY HH24:MI:SS') Date_Col FROM Dual;

DATE_COL
---------------------
10/2/2008 00:00:00


I am now confused with this. Can you please advice me on this?

Thanks,
Jo
Re: SYSDATE Performance [message #351722 is a reply to message #351719] Thu, 02 October 2008 14:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is there somethine else i should be using to programatically get the date?

No.

Quote:
where exp_date = sysdate still doubles the queries performance

I doubt this is true unless your query is very badly written as SYSDATE is normally only called once and then is treated as a constant.

Regards
Michel

[Updated on: Thu, 02 October 2008 14:32]

Report message to a moderator

Re: SYSDATE Performance [message #351723 is a reply to message #351720] Thu, 02 October 2008 14:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I just wanted to clarify something about this. I also thought this was wrong until I tried the following queries. I was under the impression that its truncating the date to start of a particular day and storing the truncated date in date format.

If you want to truncate a date, use TRUNC.
I maintain that TO_DATE(SYSDATE) is wrong. It implies 2 implicit conversions and so the result depends on your default format:
SQL> select sysdate, to_date(sysdate) from dual;
SYSDATE             TO_DATE(SYSDATE)
------------------- -------------------
02/10/2008 21:31:17 02/10/2008 21:31:17

1 row selected.

Mine always includes time.

Regards
Michel
Re: SYSDATE Performance [message #351725 is a reply to message #351719] Thu, 02 October 2008 14:39 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Assuming exp_date is a "date" datatype, try:
where trunc(exp_date) = trunc(sysdate) + 1;
or
where exp_date between trunc(sysdate+1) and trunc(sysdate+2);


Consider indexing exp_date if the query/delete benefit outweighs the insert performance degredation (and update performance too if you update exp_date).

standard.to_date is defined like this:

 function TO_DATE(LEFT NUMBER, RIGHT VARCHAR2) return DATE IS
 begin
   return (TO_DATE(TO_char(LEFT), RIGHT));
 end TO_DATE;


So, when you provide sysdate for the 1st parm, it does an implicit conversion to text and then to_date() converts the text back to date. It's a very common and terrible bug to have in your code. Understanding the isssue now will save lots of pain later...
Re: SYSDATE Performance [message #351726 is a reply to message #351723] Thu, 02 October 2008 14:41 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@Michel,

Thanks Michel. The above query result was from Toad 9.0 Client. I tried executing it in SQL *Plus and it gave me the same output as yours.

Sorry my bad Confused

Thanks again.

Regards,
Jo
Re: SYSDATE Performance [message #351728 is a reply to message #351716] Thu, 02 October 2008 14:42 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
I also get times (shown below from an 8i database).

I'd agree with Michel on not doing the to_date on a date with the implicit conversions. I'm wondering if that is what is causing the loss of time on your system? Since you are doing conversions without specifying formats, and therefore relying on default or local settings.

Note that my session has time included in the nls_date_format.

SMARTIN@jdcp.world > select to_char(to_date(sysdate,'DD-MON-YYYY HH24:MI:SS')) from dual;

TO_CHAR(TO_DATE(SYSD
--------------------
02-OCT-2008 15:36:24

1 row selected.

SMARTIN@jdcp.world > select to_date(sysdate) from dual;

TO_DATE(SYSDATE)
--------------------
02-OCT-2008 15:37:06

1 row selected.

SMARTIN@jdcp.world > select to_char(to_date(sysdate), 'DD-MON-YYYY HH24:MI:SS') from dual;

TO_CHAR(TO_DATE(SYSD
--------------------
02-OCT-2008 15:37:41

1 row selected.

Re: SYSDATE Performance [message #351729 is a reply to message #351716] Thu, 02 October 2008 14:45 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Regarding performance, are you sure exp_date is a date in the database, and not merely at some other application layer/level?

Also, watch out when you apply functions to an indexed field...
Re: SYSDATE Performance [message #351733 is a reply to message #351725] Thu, 02 October 2008 14:58 Go to previous messageGo to next message
dguad
Messages: 4
Registered: October 2008
Junior Member
andrew again wrote on Thu, 02 October 2008 15:39
Assuming exp_date is a "date" datatype, try:
where trunc(exp_date) = trunc(sysdate) + 1;



This executes the query in the same time as typing the exact date in.

Thank you again for you help.
Re: SYSDATE Performance [message #351734 is a reply to message #351729] Thu, 02 October 2008 15:04 Go to previous messageGo to next message
dguad
Messages: 4
Registered: October 2008
Junior Member
smartin wrote on Thu, 02 October 2008 15:45
Regarding performance, are you sure exp_date is a date in the database, and not merely at some other application layer/level?

Also, watch out when you apply functions to an indexed field...


I am querying from a db for reporting purposes and have no control how it is created, indexed etc... Describe on the table does tell me that exp_date is in fact Type DATE.

[Updated on: Thu, 02 October 2008 15:05]

Report message to a moderator

Re: SYSDATE Performance [message #351808 is a reply to message #351734] Fri, 03 October 2008 05:02 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
So we know what we care comparing here, can you post the Explain Plan of the two queries.

Ross Leishman
Previous Topic: Help with GROUP BY in subquery
Next Topic: How to check whether the value is there in the database or not
Goto Forum:
  


Current Time: Thu Dec 08 20:36:06 CST 2016

Total time taken to generate the page: 0.07825 seconds