Archives of the TeradataForum

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

	<Prev	Next>		<<First	<Prev	Next>	Last>>

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...

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

or

FROM
   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

AND NOT EXISTS (
   SELECT *
   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

SELECT ...
   ,SUBSTRING(MAX(patt) FROM 3)
FROM
  (
   SELECT
...
     ,TRIM(CHAR_LENGTH(p.prefix) (FORMAT '99'))
         || p.Destination_Tag AS patt

2.3 using OLAP funtions

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

3. Do one join for each possible prefix length:

SEL
   ,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
FROM
   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.

Dieter

	<Prev	Next>		<<First	<Prev	Next>	Last>>