https:

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

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

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

 < Last>>

Attachments

Library

Quick Reference

Archives

Sample Index

 2016 2007 2015 2006 2014 2005 2013 2004 2012 2003 2011 2002 2010 2001 2009 2000 2008 1999

2003 Indexes

 Jan Jul Feb Aug Mar Sep Apr Oct May Nov Jun Dec

 Top Home Privacy Feedback