Home » SQL & PL/SQL » SQL & PL/SQL » Which one is more effective?
Which one is more effective? [message #214291] Mon, 15 January 2007 12:57 Go to next message
manish mendiratta
Messages: 110
Registered: May 2002
Senior Member
Hello Guys,

Could you please suggest which one would be more efficient and faster

select cast(sysdate as timestamp) from dual;
or
SELECT TO_TIMESTAMP(to_char(sysdate,'DD/MM/YYYY HH24:MI:SS'),'DD/MM/YYYY HH24:MI:SS')
FROM dual;

and why? I have to get timestamp and convert them using from_tz function. qry with CAST is taking forever to return.
Please Suggest
Thanks
M
Re: Which one is more effective? [message #214292 is a reply to message #214291] Mon, 15 January 2007 13:14 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Option 3: assign without using sql? (I never worked with timestamps so you'll have to check if it works, but it is definitely the most efficient for dates)
Re: Which one is more effective? [message #214294 is a reply to message #214292] Mon, 15 January 2007 13:18 Go to previous messageGo to next message
manish mendiratta
Messages: 110
Registered: May 2002
Senior Member
Thanks foryour reply Frank. I didnot get you here .."assign w/o using SQL"
Re: Which one is more effective? [message #214299 is a reply to message #214291] Mon, 15 January 2007 13:56 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member
bspmgphm>select cast(sysdate as timestamp) from dual;

CAST(SYSDATEASTIMESTAMP)
---------------------------------------------------------------------------
15-JAN-07 02.55.05.000000 PM

Elapsed: 00:00:00.18

Would like to know why cast is taking "so long" for you ?

===========



Srini






Re: Which one is more effective? [message #214300 is a reply to message #214299] Mon, 15 January 2007 14:08 Go to previous messageGo to next message
manish mendiratta
Messages: 110
Registered: May 2002
Senior Member
Well not when selecting from dual. I have a qry where i am using cast / From_tz functions to get data. When i run the qry w/o these two functions i have faster results but with these two performance in really down

WHERE l.ACTIONDATE >= cast (from_tz(cast((sysdate-10) as timestamp), 'Canada/Eastern') at time zone 'Canada/Eastern' as date)
AND l.ACTIONDATE <= cast (from_tz(cast(sysdate as timestamp), 'Canada/Eastern') at time zone 'Canada/Eastern' as date)

Re: Which one is more effective? [message #214308 is a reply to message #214291] Mon, 15 January 2007 15:43 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member


What is the query execution plan ?

Srini
Re: Which one is more effective? [message #214310 is a reply to message #214308] Mon, 15 January 2007 15:54 Go to previous messageGo to next message
manish mendiratta
Messages: 110
Registered: May 2002
Senior Member
Well execution plan i can;t paste, but i can tell you that both the queries i.e.
(A)
WHERE l.ACTIONDATE >= :1
AND l.ACTIONDATE <= :2
and
(B)
WHERE l.ACTIONDATE >= cast (from_tz(cast(:1 as timestamp), 'Canada/Eastern') at time zone 'Canada/Eastern' as date)
AND l.ACTIONDATE <= cast (from_tz(cast(:2 as timestamp), 'Canada/Eastern') at time zone 'Canada/Eastern' as date)

have exactly similar execution plans. they do FTS on tables(Not a concern right now) but (A) is much faster than (B).
THnaks
M
Re: Which one is more effective? [message #214311 is a reply to message #214291] Mon, 15 January 2007 15:58 Go to previous messageGo to next message
manish mendiratta
Messages: 110
Registered: May 2002
Senior Member
Srini,

Here's the plan
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2171 | 69472 | 1640 |
| 1 | SORT GROUP BY | | 2171 | 69472 | 1640 |
|* 2 | FILTER | | | | |
|* 3 | HASH JOIN | | 2171 | 69472 | 1625 |
| 4 | TABLE ACCESS FULL| SUPP_ACTION | 24 | 408 | 2 |
|* 5 | TABLE ACCESS FULL| SUPP_USER_ACTION | 2174 | 32610 | 1622 |
--------------------------------------------------------------------------
Re: Which one is more effective? [message #214319 is a reply to message #214291] Mon, 15 January 2007 17:53 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member


If both have identical execution plans , then run 10046 trace for both of them and figure out what is happening. Pay attention to cpu and waits.

Srini
Re: Which one is more effective? [message #214384 is a reply to message #214291] Tue, 16 January 2007 03:29 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, running a little timing test:
DECLARE
  l_time    PLS_INTEGER;
  l_iter    PLS_INTEGER := 100000;
  l_val     TIMESTAMP;
BEGIN
  dbms_random.seed(DBMS_UTILITY.GET_TIME);

  l_time := DBMS_UTILITY.GET_TIME;
  FOR i IN 1..l_iter LOOP
    SELECT CAST(SYSDATE AS TIMESTAMP) INTO l_val FROM dual;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Pass 1 Took '||TO_CHAR(DBMS_UTILITY.GET_TIME - l_time));
  l_time := DBMS_UTILITY.GET_TIME; 

  FOR i IN 1..l_iter LOOP
    SELECT TO_TIMESTAMP(TO_CHAR(SYSDATE,'DD/MM/YYYY HH24:MI:SS'),'DD/MM/YYYY HH24:MI:SS')
    INTO l_val
    FROM dual;    
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('Pass 2 Took '||TO_CHAR(DBMS_UTILITY.GET_TIME - l_time));
END;

I find that the SELECT CAST(SYSDATE AS TIMESTAMP is about 22% faster
Re: Which one is more effective? [message #214437 is a reply to message #214311] Tue, 16 January 2007 08:08 Go to previous message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
manish mendiratta wrote on Mon, 15 January 2007 16:58
Srini,

Here's the plan
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2171 | 69472 | 1640 |
| 1 | SORT GROUP BY | | 2171 | 69472 | 1640 |
|* 2 | FILTER | | | | |
|* 3 | HASH JOIN | | 2171 | 69472 | 1625 |
| 4 | TABLE ACCESS FULL| SUPP_ACTION | 24 | 408 | 2 |
|* 5 | TABLE ACCESS FULL| SUPP_USER_ACTION | 2174 | 32610 | 1622 |
--------------------------------------------------------------------------



Would you be able to read that if someone asked you to do it? After 97 messages, you should know how to use CODE tags.
Previous Topic: Telephone problem
Next Topic: Calling two variables in a script?
Goto Forum:
  


Current Time: Thu Dec 08 00:13:35 CST 2016

Total time taken to generate the page: 0.15300 seconds