Finding prefixes of 500.000 phones !

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Finding prefixes of 500.000 phones !

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-01-09 : 07:15:29

Adrian writes "I have two tables My_Phones ( PhoneNo varchar(32), Date varchar(32), Duration varchar(32) ) imported from a text file ( that's why all the columns are varchars ) and a table My_Prefix ( Prefix varchar(16) PRIMARY KEY, Country varchar(32) )

SELECT * FROM My_Phones
0040724215950 Jan-01-2003 67
0040216845157 Jan-02-2003 34
0040245614904 Jan-03-2003 56

SELECT * FROM My_Prefix
004 Romania
004021 Romania_Bucharest
0040723 Romania_Connex

I want to find for every phone in My_Phones it's coresponding Prefix and Country having in mind that I have to choose prefix with the longest length .

I tried to create a cursor FOR SELECT Prefix, Country FROM My_Prefix ORDER BY LEN(Prefix) into @prefix, @country ( because I may have prefixes that start with the same numbers ( ex: 0042021 must be selected first than 0042 ) ) and then DELETE FROM My_Phones WHERE PhoneNo LIKE @prefix+'%' after I previous created a trigger when delete from My_Phones that inserted into another table the PhoneNo, Prefix ...

But this is very slow ! - because I don't have any key in My_Phones.

Anyone have a better idea ?
Something like a cursor for every row in My_Phones and then select Prefix, Country from MyPrefix where LEFT(@phone,LEN(PREFIX)) = PREFIX AND LEN(PREFIX) = MAX(LEN(PREFIX))

But this will be also slow - to may calculations - and not using the primary key because I need LEN(PREFIX) not PREFIX ..."

nr
SQLTeam MVY

12543 Posts

Posted - 2003-01-09 : 07:40:30

This wouldn't be a telecoms rating system would it?

You have the dialling codes in a table?
Include the code length on this and loop through and set the dialling code ID on the table.

declare @i int
select @i = max(codelen) from DiallingCodes
while @i > 2
begin
update myphones set codeid = diallingcodes.id
where left(code,@i) = DiallingCodes.code
and DiallingCodes.codelen = @i
and codeid is null

set @i = @i - 1
end

make sure you include 00 as a default code

To be scalable you should do this in batches of records.
I implement this using a table which holds the IDs of the calls to work on. The workflow processes these records to completion then gets the next. The number of recs that are selected is in a variable so can be changed from system to system depending on memory.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources