Re: Which one is faster?

From: Tony <andrewst_at_onetel.net.uk>
Date: 7 Jul 2004 04:30:28 -0700
Message-ID: <c0e3f26e.0407070330.592c8012@posting.google.com>

"Oracle Newbie" <f93c0532_at_yahoo.com> wrote in message news:<2l1dfuF7cgu4U1_at_uni-berlin.de>...
> "Sybrand Bakker" <sybrandb_at_hccnet.nl> wrote in message
> news:phvme0p53ds1h41i8efbgmlqjqj034d6pq_at_4ax.com...
> > On Wed, 7 Jul 2004 09:32:24 +0500, "Oracle Newbie"
> > <f93c0532_at_yahoo.com> wrote:
> >
> > >I have a string of thousands of characters and I want to check if it ends
> > >with '>>' There are 2 ways of doing it ( or more maybe)
> > >
> > >If big_str like '%>>' Then
> > >
> > >OR
> > >
> > >If substr(big_str , length(big_str) - 1 , 2 ) = '>>' Then
> > >
> > >Which one is faster? obviously the 1st one is simpler.
> > >
> > >Or is there a better way?
> > >
> > >thx
> > >
> >
> > The second looks faster to me.
>
> Why? ( esp. when it contains two functions )
>
> > BTW: any reason you didn't try it yourself?
>
> How can I test for speed? I'm using Toad 7.6

Actually, the LIKE version is faster (on 8.1.7.3.0 anyway). To get accurate timings, use SQL_TRACE and TKPROF and look at the CPU timings. But in this case the difference is so dramatic you can see it using SET TIMING ON in SQL Plus (presumably Toad has a similar function?):

SQL> -- Large table I happen to have to hand: SQL> select count(*) from sdd_elements;

  COUNT(*)

---

   1703608

SQL> set timing on
SQL> -- Using LIKE:
SQL> select count(*)

  2 from sdd_elements
  3 where el_name like '%NS';

  COUNT(*)

---

      8031

 real: 5562
SQL> -- Again
SQL> /   COUNT(*)

---

      8031

 real: 5344
SQL> -- Using SUBSTR
SQL> select count(*)
  2 from sdd_elements
  3 where substr(el_name,length(el_name)-1,2) = 'NS';

  COUNT(*)

---

      8031

 real: 40501
SQL> -- Again (zzz...)
SQL> /   COUNT(*)

---

      8031

 real: 30578
SQL> -- Using SUBSTR more efficiently (i.e. without LENGTH) SQL> select count(*)
  2 from sdd_elements
  3 where substr(el_name,-2) = 'NS';

  COUNT(*)

---

      8031

 real: 25828
SQL> -- Again
SQL> /   COUNT(*)

---

      8031

 real: 26297

Those timings are in milliseconds, so the approx. times are:

Using LIKE: 6 seconds
Using SUBSTR and LENGTH: 30 seconds
Using SUBSTR: 26 seconds Received on Wed Jul 07 2004 - 06:30:28 CDT