Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Ускорить запрос с REPLACE  [new]
2viper2viper
Member

Откуда:
Сообщений: 271
Подскажите, можно как то ускорить запрос:
UPDATE [dbo].[76]
SET RegistrationNumber = REPLACE(RegistrationNumber, ' ', '')  --
UPDATE [dbo].[76]
SET RegistrationNumber = REPLACE(RegistrationNumber, 'А', 'A') 
UPDATE [dbo].[76]
SET RegistrationNumber = REPLACE(RegistrationNumber, 'В', 'B') 
UPDATE [dbo].[76]
SET RegistrationNumber = REPLACE(RegistrationNumber, 'Е', 'E') 
UPDATE [dbo].[76]
SET RegistrationNumber = REPLACE(RegistrationNumber, 'І', 'I') 
UPDATE [dbo].[76]
SET RegistrationNumber = REPLACE(RegistrationNumber, 'К', 'K') 
UPDATE [dbo].[76]
SET RegistrationNumber = REPLACE(RegistrationNumber, 'М', 'M') 
UPDATE [dbo].[76]
SET RegistrationNumber = REPLACE(RegistrationNumber, 'Н', 'H') 
UPDATE [dbo].[76]
SET RegistrationNumber = REPLACE(RegistrationNumber, 'О', 'O') 
UPDATE [dbo].[76]
SET RegistrationNumber = REPLACE(RegistrationNumber, 'Р', 'P') 
UPDATE [dbo].[76]
SET RegistrationNumber = REPLACE(RegistrationNumber, 'С', 'C') 
UPDATE [dbo].[76]
SET RegistrationNumber = REPLACE(RegistrationNumber, 'Т', 'T') 
UPDATE [dbo].[76]
SET RegistrationNumber = REPLACE(RegistrationNumber, 'У', 'Y') 
UPDATE [dbo].[76]
SET RegistrationNumber = REPLACE(RegistrationNumber, 'Х', 'X')  


Нужно в колонке с номерными знаками убрать пробелы и свести кириллицу в единую символику на латинице
Всего более 6 млн строк
Выполняется уже около суток. Процессор нагружен на 1-3 %
20 ноя 14, 16:39    [16880013]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить запрос с REPLACE  [new]
Glory
Member

Откуда:
Сообщений: 104751
А один UPDATE не пробовали написать ?
20 ноя 14, 16:40    [16880020]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить запрос с REPLACE  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
2viper2viper,

Один UPDATE с вложенными REPLACE.
Возможно, стоит апдейтить в цикле порциями
с целью уменьшить файл с логом транзакции.
Размер порции записей можно подобрать.
20 ноя 14, 16:43    [16880037]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить запрос с REPLACE  [new]
2viper2viper
Member

Откуда:
Сообщений: 271
iap,

как правильно вложить реплайсы в один апдейт? не нашел примера
20 ноя 14, 17:04    [16880240]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить запрос с REPLACE  [new]
LSV
Member [заблокирован]

Откуда: Киев
Сообщений: 30817
Объявить переменную.
Сделать в ней все замены.
Проапдейтить.
(профит)
20 ноя 14, 17:06    [16880254]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить запрос с REPLACE  [new]
Glory
Member

Откуда:
Сообщений: 104751
2viper2viper
как правильно вложить реплайсы в один апдейт? не нашел примера

Вы не в курсе, что функции могут быть вложенными ?
replace(replace(replace(replace(...),,),,),,)
20 ноя 14, 17:06    [16880258]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить запрос с REPLACE  [new]
2viper2viper
Member

Откуда:
Сообщений: 271
запрос и с одним UPDATE+REPLACE выполняется не менее двух часов
Может есть возможность увеличить нагрузку на процессор? или как-то ускорить выполнение этой строки запроса
20 ноя 14, 17:09    [16880282]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить запрос с REPLACE  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
2viper2viper
iap,

как правильно вложить реплайсы в один апдейт? не нашел примера
Есть ли у кого готовая функция перевода (транслит) с руского на английский
20 ноя 14, 17:11    [16880295]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить запрос с REPLACE  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
2viper2viper
запрос и с одним UPDATE+REPLACE выполняется не менее двух часов
Может есть возможность увеличить нагрузку на процессор? или как-то ускорить выполнение этой строки запроса
Читать ответы умеете?
20 ноя 14, 17:11    [16880298]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить запрос с REPLACE  [new]
Glory
Member

Откуда:
Сообщений: 104751
2viper2viper
Может есть возможность увеличить нагрузку на процессор?

Вы уверены, что для изменения данных в таблице нужно именно процессорное врамя, а не, скажем, быстрый диск ?

2viper2viper
ли как-то ускорить выполнение этой строки запроса

Начать с плана.
Хотя там скорее всего и так полное сканирование и транзакция на 6млн записей
20 ноя 14, 17:15    [16880325]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить запрос с REPLACE  [new]
zenk
Member

Откуда: Москва
Сообщений: 115
Если вы вычищаете случайные ошибки ввода, которых относительно немного,
попробуйте добавить проверку на наличие запрещенного символа, например:

UPDATE [dbo].[76]
SET RegistrationNumber = REPLACE(RegistrationNumber, ' ', '')
WHERE  RegistrationNumber LIKE '%  %';
GO
UPDATE [dbo].[76]
SET RegistrationNumber = REPLACE(RegistrationNumber, 'А', 'A') 
WHERE  RegistrationNumber LIKE '%А%';
20 ноя 14, 17:30    [16880455]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить запрос с REPLACE  [new]
2viper2viper
Member

Откуда:
Сообщений: 271
zenk
Если вы вычищаете случайные ошибки ввода, которых относительно немного,
попробуйте добавить проверку на наличие запрещенного символа, например:

UPDATE [dbo].[76]
SET RegistrationNumber = REPLACE(RegistrationNumber, ' ', '')
WHERE  RegistrationNumber LIKE '%  %';
GO
UPDATE [dbo].[76]
SET RegistrationNumber = REPLACE(RegistrationNumber, 'А', 'A') 
WHERE  RegistrationNumber LIKE '%А%';

Да ошибок там не мало.
20 ноя 14, 17:33    [16880484]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить запрос с REPLACE  [new]
2viper2viper
Member

Откуда:
Сообщений: 271
iap
2viper2viper
запрос и с одним UPDATE+REPLACE выполняется не менее двух часов
Может есть возможность увеличить нагрузку на процессор? или как-то ускорить выполнение этой строки запроса
Читать ответы умеете?


не уточнил, что кроме перестоения запроса. Спасибо за совет с вложенными реплейсами

UPDATE [SAO1CDATA].[dbo].[76]
SET RegistrationNumber = 
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
RegistrationNumber, 
' ', '') 
,'А', 'A')
,'В', 'B') 
,'Е', 'E') 
,'І', 'I') 
,'К', 'K') 
,'М', 'M') 
,'Н', 'H') 
,'О', 'O')
,'Р', 'P')
,'С', 'C')
,'Т', 'T')  
,'У', 'Y') 
, 'Х', 'X')  
20 ноя 14, 17:35    [16880495]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить запрос с REPLACE  [new]
Glory
Member

Откуда:
Сообщений: 104751
zenk
Если вы вычищаете случайные ошибки ввода, которых относительно немного,
попробуйте добавить проверку на наличие запрещенного символа, например:

UPDATE [dbo].[76]
SET RegistrationNumber = REPLACE(RegistrationNumber, ' ', '')
WHERE  RegistrationNumber LIKE '%  %';
GO
UPDATE [dbo].[76]
SET RegistrationNumber = REPLACE(RegistrationNumber, 'А', 'A') 
WHERE  RegistrationNumber LIKE '%А%';

Даже если ошибок ввода мало, то для такого LIKE все равно придется просканировать всю таблицу
А в вашем варианте еще и несколько раз
Если и будет выигрыш, то только за счет размера транзакции.

Сообщение было отредактировано: 20 ноя 14, 17:36
20 ноя 14, 17:35    [16880497]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить запрос с REPLACE  [new]
zenk
Member

Откуда: Москва
Сообщений: 115
Glory,

Может резко сократиться лог + "процесс будет под контролем".

А если на RegistrationNumber какой-то триггер настроен, то получим доп. выигрыш.
20 ноя 14, 17:42    [16880563]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить запрос с REPLACE  [new]
Glory
Member

Откуда:
Сообщений: 104751
zenk
Может резко сократиться лог + "процесс будет под контролем".

Так его и так можно сократить, если делать апдейт частями

zenk
А если на RegistrationNumber какой-то триггер настроен, то получим доп. выигрыш.

14 полных сканирования таблицы с неизвестным числов реальных обновлений vs апдейт всех записей ?
Тут уж как повезет
20 ноя 14, 17:45    [16880596]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить запрос с REPLACE  [new]
Glory
Member

Откуда:
Сообщений: 104751
Имхо,
курсор с LIKE по маске всех нужных символов и update where current будет лучше всего
20 ноя 14, 17:58    [16880708]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить запрос с REPLACE  [new]
zenk
Member

Откуда: Москва
Сообщений: 115
Glory,

Да что-то там у автора явно за кулисами спрятано.

На старом тестовом сервере фиктивный UPDATE (ничего на самом деле не поменялось)

для таблицы в 2+ млн. записей выполнился за минуту:

UPDATE Document
SET Number = REPLACE(Number, 'А', 'А')
WHERE Number LIKE '%А%';

(358673 row(s) affected)

Есть ещё вариант - у автора вопроса RegistrationNumber входит в какое-то очень большое число индексов.
Тогда стандартное решение - их отключение с последующим перестроением.
20 ноя 14, 18:12    [16880826]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить запрос с REPLACE  [new]
2viper2viper
Member

Откуда:
Сообщений: 271
Запрос с вложенным replace выполнился за полтора часа.
Спасибо Glory и iap :)
Так понимаю, что вместо полтора десятков сканирования по логике вложенной функции replace таблица сканируется раз, и перебираются все символы?
zenk, спасибо за идею. как альтернатива, на выходных потестирую
20 ноя 14, 21:36    [16881870]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить запрос с REPLACE  [new]
Glory
Member

Откуда:
Сообщений: 104751
2viper2viper
Так понимаю, что вместо полтора десятков сканирования по логике вложенной функции replace таблица сканируется раз, и перебираются все символы?

полное чтение таблицы делал update. а не replace
И в вашем случае делал это 14 раз

14 * 1.5ч = 21 час
20 ноя 14, 21:58    [16881957]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить запрос с REPLACE  [new]
2viper2viper
Member

Откуда:
Сообщений: 271
Glory, учитывая, что этот запрос нужно повесить на агента, ночи не хватило бы..))
Спасибо за разьяснение и помощь
20 ноя 14, 23:21    [16882225]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить запрос с REPLACE  [new]
zenk
Member

Откуда: Москва
Сообщений: 115
2viper2viper,

>>этот запрос нужно повесить на агента
Очень странное архитектурное решение, если не сказать ещё хуже...
Нельзя создать триггер или контролировать ввод на уровне приложения?
21 ноя 14, 16:27    [16886440]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить запрос с REPLACE  [new]
2viper2viper
Member

Откуда:
Сообщений: 271
zenk
2viper2viper,

>>этот запрос нужно повесить на агента
Очень странное архитектурное решение, если не сказать ещё хуже...
Нельзя создать триггер или контролировать ввод на уровне приложения?

Эта база не используется для операционных целей.
Ее назначение - формирование отчетов, не модифицируя данные ЦБД. А в том виде, в котором хранятся данные в ЦБД, работать с ними не возможно
25 ноя 14, 12:24    [16901097]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить запрос с REPLACE  [new]
zenk
Member

Откуда: Москва
Сообщений: 115
2viper2viper,
А ЦБД разные варианты написания допустимы или нет?
2viper2viper
Да ошибок там не мало.

Ведь даже вы называете разные варианты написания ошибками…
25 ноя 14, 19:18    [16904283]     Ответить | Цитировать Сообщить модератору
 Re: Ускорить запрос с REPLACE  [new]
2viper2viper
Member

Откуда:
Сообщений: 271
zenk
2viper2viper,
А ЦБД разные варианты написания допустимы или нет?
2viper2viper
Да ошибок там не мало.

Ведь даже вы называете разные варианты написания ошибками…


к сожалению, допустимы. не настроена уникальность поля vin-кодов.
разные варианты написания и есть ошибками, поскольку операционисты должны использовать только латиницу при введении номерных знаков
26 ноя 14, 20:30    [16910398]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить