Существует такая проблема

Есть две таблицы

Calls (DialedNumber,Duration,RateId,Cost) - телефонные звонки (50000-1млн записей)

Например
70957777777 60 5 NULL
78122222222 60 6 NULL
78123222222 60 6 NULL

Rates (RateId,DestinationCode,Rate) - тарифы (>200000 записей)
Например
5 7095 0,5
6 7095 0,5
6 7812 0,5 - действителен для всех звонков вида 7812xxx кроме 78122
6 78122 0,1

таким образом звонки должны тарифицироваться согласно тарифу для кода который
наиболее совпадает с набранным номером т.е. 7812222222 будет по тарифу 0,1 ,а
7812322222 по тарифц 0,5.

Собственоо вопрос - как это лучше всего реализовать на SQL Server?

Пробовал три способа
1. Цикл по длинне кода в таблице rates - оказался самый быстрый
DECLARE @codelen tinyint
SELECT @codelen=10
WHILE @codelen>0
BEGIN
UPDATE calls
SET cost=duration*Rate
WHERE substring(DialedNumber,1,datalength(DestinationCode))=DestinationCode
and datalength(destinationcode)=@codelen
and calls.rateid=rates.rateid
and cost is null
SELECT @codelen=@codelen-1
END
2. Один запрос - очень медленно
UPDATE calls
SET cost=duration*rates.rate
from rates
WHERE rates.Destinationcode=(select max(destinationcode) from rates r1 where dialednumber like code+'%' and rates.rateid=r1.rateid)
and rates.rateid=calls.rateid

3 Перебор звонков в курсоре - еще медленнее.

Ести ли у кого идеи как это еще можно реализовать поменяв структуру базы и/или запросы?

Сергей,
вы правы - номера приходят с АТС в неразделенном виде.

Генадий,
Идея разделять номер на код+остаток была, но она не годится в силу того что для разных тарифных планов могут использоватся разные набор кодов, например,

Набраны
781223456 -для клиента с планом N1
781223456 -для клиента с планом N2

В Плане 1 задан только
код 7 -$0,2
В Плане 2 заданы коды
7812 - 0,10
78122 -0,05
То есть получаем что один и тот же номер должен разбится в одном случае как (7)81223456 а в другом как (78122)3456.
таким образом мы не можем заранее сказать что номер состоит из кода и остатка не посмотрев на тарифный план.

Дмитрий,
не совсем понял ваш пример.

>>Это не повод запихивать два атрибута в один, отношение многие ко многим никто не отменял.
В том-то все и дело, что это изначально один атрибут -номер "физически" набранный на телефоне.
Задача в том и состоит чтобы наиболее эффективным способом разделить этот номер на два атрибута- "значащий код" на основе которого звонку ставится в соответствие тариф и "балласт" -остаток номера.

RateID это номер тарифной таблицы которую необходимо использовать для тарификации того или иного звонка. PK для таблицы Rates это (rate_id,destinationcode):
RateId,DestinationCode,Rate
--начало первой таблицы
1 7095 0,2
1 7096 0,1
1 7812 0,3
1 78122 0,2
1 781222 0,3
--начало второй таблицы
2 7095 0,01
2 7096 0,02
2 7812 0,03
-- начало третей таблицы
3 7 0,20


Увы,ваш запрос будет работать в случае таблиц 1 и 3, но в случае 2 несработает:
набранному номеру 78122222 будут соответствовать три позиции таблицы -где гарантия что UPDATE будет использовать именоо 781222 ?
Если бы можно было сделать
UPDATE....
ORDER BY DATALENGTH(781222) DESC - тогда да.

Читал я все это.Rates специально денормализована для ускорения работы. Когда идет UPDATE на 1000000 записей то тут не до нормальных форм.

Кстати, запросик то сработает, но не гарантированно - зависит от того в каком порядке "Физически" были вставлены записи в таблицу Rates.

Пример:
CREATE TABLE Calls (DialedNumber varchar(14),Duration int,RateId int,Cost money)
CREATE TABLE Rates (RateId int,DestinationCode varchar(14),Rate money)

INSERT INTO Calls (DialedNumber,Duration,RateId)VALUES ('7095x',60,5)
INSERT INTO Calls (DialedNumber,Duration,RateId)VALUES ('78122x',60,6)
INSERT INTO Calls (DialedNumber,Duration,RateId)VALUES ('78123x',60,6)
INSERT INTO RATES (RateId,DestinationCode,Rate) VALUES (5,'7095',0.5)
--Вставляем сначала короткий код а затем длинный
INSERT INTO RATES (RateId,DestinationCode,Rate) VALUES (6,'7812',1)
INSERT INTO RATES (RateId,DestinationCode,Rate) VALUES (6,'78122',2)

update Calls set Calls.Cost = Calls.Duration * Rates.Rate from Calls, Rates
where Calls.RateID = Rates.RateID and Rates.DestinationCode = substring(Calls.DialedNumber, 1, len(Rates.DestinationCode))

select * from calls
--Получаем результат:
DialedNumber Duration RateId Cost
-------------- ----------- ----------- ---------------------
7095x 60 5 30.0000
78122x 60 6 120.0000
78123x 60 6 60.0000

truncate table rates

INSERT INTO RATES (RateId,DestinationCode,Rate) VALUES (5,'7095',0.5)
-- а теперь вставляем сначала длинный код а затем короткий
INSERT INTO RATES (RateId,DestinationCode,Rate) VALUES (6,'78122',2)
INSERT INTO RATES (RateId,DestinationCode,Rate) VALUES (6,'7812',1)

update Calls set Calls.Cost = Calls.Duration * Rates.Rate from Calls, Rates
where Calls.RateID = Rates.RateID and Rates.DestinationCode = substring(Calls.DialedNumber, 1, len(Rates.DestinationCode))

select * from calls
DialedNumber Duration RateId Cost
-------------- ----------- ----------- ---------------------
7095x 60 5 30.0000
78122x 60 6 60.0000
78123x 60 6 60.0000

Так я же SELECT и ускоряю - SELECT идет из денормализованной RATES для update таблицы Calls.
>>Вопрос: если у Вас в Rates составной PK, то почему FK у вас состоит из части PK????!!!

Непонял, почему части?

Да в этом то задача и состоит чтобы определить какому PK в Rates соответствует запись в Calls.

Действительно Уффф. Такое впечатление что мы с вами говорим на разных языках.

Может я чего-то не улавливаю, но я не вижу каким образом ваши комментарии относительно определения ключей имеют отношение к моей задаче. Если можно, приведите конкретный пример.

>>>>Когда идет UPDATE на 1000000 записей то тут не до нормальных форм.
>> Очепятка что ли?
Имел в виду что

UPDATE calls
SET rate=.....
FROM rates --вот он SELECT который я имел в виду
WHERE ....
будет быстрее чем

UPDATE calls
SET rate=
FROM rates_normalized inner join codes on..
inner join ...
inner join ...
inner join ...
WHERE