Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Помогите с запросом  [new]
Vsevolod V
Member

Откуда: Россия. Санкт-Петербург
Сообщений: 3199
Всем добрый день.

Выполняю обработку списка телефонных номеров на стороне клиентского c# приложения. Задача в том, чтобы определить для каждого номера телефона, к какому региону\городу он принадлежит. В базе данных хранится информация по сопоставлению всех кодов телефонов по всем операторам связи с регионами\городами в формате, представленном ниже. Записей в этой таблице сопоставления 23 000. За 1 раз осуществляется обработка порядка 3 000 телефонных номеров в 100 параллельных потоков, задача регулярная, и поэтому важна производительность решения.
Помогите, пожалуйста, сделать оптимальный запрос на быстрое получение данных из таблицы сопоставления по 1 номеру телефона. Например, как можно получить из таблицы сопоставления код региона для номеров телефонов: 89994911546 и 89825594623?

Пример данных таблицы сопоставления "код телефона-регион"
create table t_REG_TelCodes
(
	tl_id int identity(1,1) primary key,
	i_id_region int,
	tl_code nvarchar(11)
)
insert into t_REG_TelCodes(i_id_region, tl_code) select 1, '9994911xxx'
insert into t_REG_TelCodes(i_id_region, tl_code) select 1, '9994912xxx'
insert into t_REG_TelCodes(i_id_region, tl_code) select 1, '9994913xxx'
insert into t_REG_TelCodes(i_id_region, tl_code) select 2, '9994919xxx'
insert into t_REG_TelCodes(i_id_region, tl_code) select 2, '9994920xxx'
insert into t_REG_TelCodes(i_id_region, tl_code) select 3, '98302xxxxx'
insert into t_REG_TelCodes(i_id_region, tl_code) select 3, '983030xxxx'
insert into t_REG_TelCodes(i_id_region, tl_code) select 3, '983031xxxx'
insert into t_REG_TelCodes(i_id_region, tl_code) select 3, '983032xxxx'
insert into t_REG_TelCodes(i_id_region, tl_code) select 4, '983050xxxx'
insert into t_REG_TelCodes(i_id_region, tl_code) select 5, '983051xxxx'
insert into t_REG_TelCodes(i_id_region, tl_code) select 6, '983052xxxx'
insert into t_REG_TelCodes(i_id_region, tl_code) select 7, '9825xxxxxx'
insert into t_REG_TelCodes(i_id_region, tl_code) select 8, '9826xxxxxx'
10 фев 19, 12:28    [21805598]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
aleks222
Member

Откуда:
Сообщений: 708
1. Убрать из '9994911xxx' xxx. Чай не порносайт.
2.
Select t.[телефонный номер], r.i_id_region
  from [порядка 3 000 телефонных номеров] as t inner join t_REG_TelCodes as r on t.[телефонный номер]  like tl_code + '%'

3. Нахера тут "100 параллельных потоков"?
10 фев 19, 12:40    [21805607]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
aleks222
Member

Откуда:
Сообщений: 708
Ах да,

4. 89994911546 и 89825594623 приводите к 9994911546 и 9825594623.
5. Либо, что правильнее, в таблицу регионов добавьте 8. Вдруг Куала-Лумпур надо будет обрабатывать?
6. Но есть ишо +7. Значит в таблицу регионов надо добавить записи с +7.
10 фев 19, 12:44    [21805611]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Vsevolod V
Member

Откуда: Россия. Санкт-Петербург
Сообщений: 3199
aleks222,

Данные по [порядка 3 000 телефонных номеров] хранятся не в бд, а в оперативной памяти клиентского приложения. Телефонные номера собираются из различных источников данных в 100 параллельных потоках, задача в том, чтобы сразу после получения номера определить его принадлежность к региону. Можно, кончено, сначала собрать все телефоны, потом вставить их в какую-либо таблицу в БД и выполнить предложенный вами запрос, но данные по [порядка 3 000 телефонных номеров] не требуется хранить в БД, они временные.
10 фев 19, 12:47    [21805613]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Vsevolod V
Member

Откуда: Россия. Санкт-Петербург
Сообщений: 3199
aleks222,

Да, не учел это в примере данных. Все номера телефонов начинаются с "8", и в таблице сопоставления и в [порядка 3 000 телефонных номеров]
10 фев 19, 12:49    [21805615]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
aleks222
Member

Откуда:
Сообщений: 708
Vsevolod V
aleks222,

Данные по [порядка 3 000 телефонных номеров] хранятся не в бд, а в оперативной памяти клиентского приложения. Телефонные номера собираются из различных источников данных в 100 параллельных потоках, задача в том, чтобы сразу после получения номера определить его принадлежность к региону. Можно, кончено, сначала собрать все телефоны, потом вставить их в какую-либо таблицу в БД и выполнить предложенный вами запрос, но данные по [порядка 3 000 телефонных номеров] не требуется хранить в БД, они временные.


Боюсь показаться банальным, но с вашим уровнем программирования быстрее ВСТАВИТЬ номера в таблицу #temporal и выполнить запрос. Можно, даже, по одному.
10 фев 19, 12:51    [21805616]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Vsevolod V
Member

Откуда: Россия. Санкт-Петербург
Сообщений: 3199
Спасибо)
10 фев 19, 12:57    [21805619]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
invm
Member

Откуда: Москва
Сообщений: 8713
Vsevolod V,

Найти один раз регионы для 3000 номеров гораздо оптимальнее, чем 3000 раз искать по одному.
Для этого
1. Уберите из значений префиксов "ххх..."
2. Создайте индекс create index ... on t_REG_TelCodes (tl_code) include (i_id_region)
3. Процедура поиска
create procedure p_GetPhonesWithRegion
 @tl_list xml

as
begin
 set nocount on;
 
 with s(phone_number) as
 (
  select
   t.n.value('@number', 'nvarchar(100)')
  from
   @tl_list.nodes('/phone') t(n)
 )
 select
  s.phone_number, r.i_id_region
 from
  s outer apply
  (select top (1) i_id_region from dbo.t_REG_TelCodes where s.phone_number >= tl_code and s.phone_number like tl_code + N'%' order by tl_code desc) r;

end;
4. На клиенте формируйте список номеров в виде '<phone number = "89830514567" /><phone number = "89830524567" /><phone number = "8982511111" />' и передавайте при вызове процедуры.
10 фев 19, 16:04    [21805706]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
aleks222
Member

Откуда:
Сообщений: 708
invm
Vsevolod V,

Найти один раз регионы для 3000 номеров гораздо оптимальнее, чем 3000 раз искать по одному.
Для этого
1. Уберите из значений префиксов "ххх..."
2. Создайте индекс create index ... on t_REG_TelCodes (tl_code) include (i_id_region)
3. Процедура поиска
create procedure p_GetPhonesWithRegion
 @tl_list xml

as
begin
 set nocount on;
 
 with s(phone_number) as
 (
  select
   t.n.value('@number', 'nvarchar(100)')
  from
   @tl_list.nodes('/phone') t(n)
 )
 select
  s.phone_number, r.i_id_region
 from
  s outer apply
  (select top (1) i_id_region from dbo.t_REG_TelCodes where s.phone_number >= tl_code and s.phone_number like tl_code + N'%' order by tl_code desc) r;

end;
4. На клиенте формируйте список номеров в виде '<phone number = "89830514567" /><phone number = "89830524567" /><phone number = "8982511111" />' и передавайте при вызове процедуры.


Зачем этот бред?
Bulk Insert нонича вполне доступен в C#.
10 фев 19, 16:13    [21805709]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
invm
Member

Откуда: Москва
Сообщений: 8713
aleks222
Зачем этот бред?
Bulk Insert нонича вполне доступен в C#.
Не пробовал задать себе вопрос: "Нахрена провоцировать запись в ЖТ, когда можно этого не делать?"
10 фев 19, 18:27    [21805772]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
aleks222
Member

Откуда:
Сообщений: 708
invm
aleks222
Зачем этот бред?
Bulk Insert нонича вполне доступен в C#.
Не пробовал задать себе вопрос: "Нахрена провоцировать запись в ЖТ, когда можно этого не делать?"

Так он для этого и предназначен?
11 фев 19, 05:50    [21806020]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
invm
Member

Откуда: Москва
Сообщений: 8713
aleks222
Так он для этого и предназначен?
Ну, дарагуля, банки тоже предназначены для выдачи кредитов. Однако, ты же не берешь кредит, когда можно его не брать?
Или таки берешь?
11 фев 19, 10:35    [21806133]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34465
Vsevolod V,

Парень, так тебе не помочь, от тебя надо структуру таблиц и запросы что ты выполняешь.

ДА и тут надо , я думаю, постановку задачи или архитектуру менять.
Поэтому надо техзадание.
Ты же написал только какую=то лабуду в вопросе.
В общем... без шансов почти тебе помочь.

23 тыщи записей сопоставлений вообще можно в клиента БД всосать на старте и далее уже там искать что надо.
100 параллельных потоков там 100% не нужно, нигде нет такого высокого уровня параллелизма, это не повысит производительность.
11 фев 19, 11:41    [21806198]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 6626
Vsevolod V,

уберите кресты и в цикле лайком определяйте от самых длинных к самым коротким. Цикл по длине номера шаблона. Максимум за 9 проходов разберете все номера. Способ достаточно эффективный. На кой вам 100 потоков, реалтайм, что ли?
11 фев 19, 13:29    [21806341]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
=Сергей=
Member

Откуда:
Сообщений: 326
Вам нужно сделать справочник префиксов (DEF-кодов):
create table [T_DEF]([id] int identity(1,1),[Prefix] varchar(20) not null,[Region] nvarchar(100) not null)

заполнить его правильными префиксами, например взять с rossvyaz.ru
Сделать функцию определения региона по префиксу, типа:
create function [dbo].[fn_GetRegion]( @Number varchar(20)) rerurns nvarchar(100)
as
begin
//простая логика
end

После чего будет вам счастье:
declare @Numbers table ([Number] varchar(20))
insert into @Numbers values ('798553223413564','74561233489153','45621681324324351')
select
*
from @Numbers A
outer apply [dbo].[fn_GetRegion](A.[Number]) B

Это будет значительно быстрее, чем обрабатывать данные в софте на C#.
13 фев 19, 12:07    [21808258]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить