Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Sun, 17 Aug 2003 @ 19:56:48 GMT

  <Prev Next>  
Next> Last>>  

Subj:   Assigning destination tags to CDRs
From:   Dieter Noeth

Hi all,

i'm doing some research on common problems in SQL and how to solve it efficiently.

One of those is matching called_no to prefixes. There's usually a small number of prefixes (~1000-2000) and a large number of called_no (Millions).

I'd like to know how the Telco's out there solve that problem, maybe you could provide me with some details.

I'm aware of several techniques:

1. Using an INMOD in FastLoad/MLoad instead of SQL.

2. Join using LIKE or SUBSTRING...

   CDRs c
     LEFT JOIN Prefixes p
       ON SUBSTR(c.called_no,1,CHAR_LENGTH(p.prefix)) = p.prefix


   CDRs c
     LEFT JOIN Prefixes p
       ON c.called_no like p.prefix || '%'

...and the find the longest matching prefix...

2.1 using NOT EXISTS

   FROM Prefixes p2
   WHERE CHAR_LENGTH(p2.prefix) > CHAR_LENGTH(p1.prefix)
   AND SUBSTRING(p2.prefix FROM 1 FOR p1.prefix) = p1.prefix
   AND SUBSTRING(c.called_no FROM 1 FOR CHAR_LENGTH(p2.prefix)) = p2.prefix);

2.2 using concatenation of length and tag, extracting the longest pattern using Group By

     ,TRIM(CHAR_LENGTH(p.prefix) (FORMAT '99'))
         || p.Destination_Tag AS patt

2.3 using OLAP funtions

   RANK() OVER (PARTITION BY c.called_no ORDER BY p.pattern DESC) = 1

3. Do one join for each possible prefix length:

   ,COALESCE(p12.prefix, p11.prefix, p10.prefix, p9.prefix, p8.prefix,
             p7.prefix, p6.prefix, p5.prefix, p4.prefix, p3.prefix,
             p2.prefix) AS pat
   CDRs c
     LEFT JOIN Prefixes p12
       ON SUBSTR(c.called_no,1,12) = p12.prefix
     LEFT JOIN Prefixes p11
       ON SUBSTR(c.called_no,1,11) = p11.prefix
     LEFT JOIN Prefixes p10
       ON SUBSTR(c.called_no,1,10) = p10.prefix
     LEFT JOIN Prefixes p9

4. In V2R5 there's a now 1MB SQL request size, so i tested building a huge sophisticated CASE statement to replace the join. The CASE results in a warning "3705 Request executed in interpretive EVL mode" ;-)

Some test showed huge differences in CPU time and Disk IOs:

2.1: worst
2.2: CPU ~ 1/4 of 2.1, IO ~ 1/3
2.3: less CPU than 2.2, but ~ 2x IO
3: CPU ~ 1/15 of 2.3, but ~ 2x IOs
4: CPU a bit more than 3 and fewest IOs

So 3 and 4 seems to be the fastest ways, but i didn't test 1 yet, because i'd like to do it with plain SQL.


  <Prev Next>  
Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 28 Jun 2020