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

Откуда:
Сообщений: 10
Доброе время суток.
Подскажите как можно оптимизировать данный запрос?

Declare TablesAndFields cursor for
SELECT objects.name as Tablename, columns.name as columnname
FROM
    dbo.sysobjects as objects
left join dbo.syscolumns as columns
on objects.id = columns.id
where objects.xtype = 'U'
and columns.xtype = 61

open TablesAndFields

Declare @TableName as varchar(100)
Declare @ColumnName as varchar(100)

FETCH NEXT FROM TablesAndFields
    into @TableName, @ColumnName

WHILE @@FETCH_STATUS = 0
BEGIN
    Exec ('update ' + @TableName + '
    set ' + @ColumnName + ' = dateAdd(year,+2000,'+@ColumnName+')
    where ' + @ColumnName + ' < ''3999-12-31 23:59:59''')

     FETCH NEXT FROM TablesAndFields
    into @TableName, @ColumnName
END

close TablesAndFields
deallocate TablesAndFields
update dbo._YearOffset set Offset=0


Сообщение было отредактировано: 12 июл 11, 10:23
11 июл 11, 21:23    [10958325]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
sp_msforeachtable
11 июл 11, 21:53    [10958412]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
BenD
Member

Откуда:
Сообщений: 10
Признаюсь честно, я вообще не селен в SQL запросах. Поэтому буду признателен если тыкните носом куда вставить и какой текст.
В изначальном варианте запрос не отрабатывает до конца, выдает ошибку (скорей всего входных данных данных много), да и работает медленно (судя по монитору ресурсов, сначала идет чтение всей таблицы + вычисление, а затем процесс записи, но на 100% ни чего не юзается), т.к. размер базы большой, очень большой :)
11 июл 11, 22:06    [10958456]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Makar4ik
Member

Откуда: Когда-то были Лужки, а теперь Бордюр-Сити.
Сообщений: 2680
Knyazev Alexey
sp_msforeachtable
sp_msforeachtable - тот же курсор, только сложнее и дороже.
Оптимизировать по скорости выполнения - имхо индексами по полям Datetime.
11 июл 11, 22:09    [10958463]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Нектотам
Guest
BenD,

Это запрос по переведению информационной базы 1С 8 с режима "без смещения дат" на "смещение дат 2000". Индексы по датам есть на многих колонках с датами, поэтому для каждой таблицы лучше было бы просто отключить индексы с этими колонками и пересоздать после расколбаса ( http://msdn.microsoft.com/ru-ru/library/ms177406.aspx ). Makar4ik в данном случае не прав, т.к. изменяются именно те столбцы, на которые он предлагает навесить индексы. Кстати, sp_msforeachtable здесь не вариант, т.к. обходятся колонки, а не таблицы.

Сообщение было отредактировано: 12 июл 11, 06:17
12 июл 11, 06:08    [10959347]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
BenD
Member

Откуда:
Сообщений: 10
Да, это перевод базы в смещение 2000 1с, стандартными средствами не вариант, т.к. выгрузка ДТшника будет нереально долгой.
В том виде запрос выдал ошибку, после 36 часов работы:
The maximum system-generated unique value for a duplicate group was exceeded for index with partition ID 422701233012736. Dropping and re-creating the index may resolve this; otherwise, use another clustering key.
Оборудование мощное, база более 2 ТБ.
Т.е. мне нужно отключить индексы и запустить скрипт, затем запустить ребилд индексов?
12 июл 11, 09:46    [10959798]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Ray D
Member

Откуда: from the middle of nowhere
Сообщений: 3598
Блог
Забавно... Вот эта ошибка:
автор
The maximum system-generated unique value for a duplicate group was exceeded for index with partition ID 422701233012736. Dropping and re-creating the index may resolve this; otherwise, use another clustering key.

возникает, если вставить в кластерный индекс больше 4млрд+ неуникальных значений (т.к. uniquifier -- 4 байта). Код ошибки не 666?
12 июл 11, 10:03    [10959892]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
AHDP
Member

Откуда:
Сообщений: 1226
А почему смещение базы после выполнения преобразования дат +2000 устанавливаете в 0? Должно быть +2000.
12 июл 11, 10:18    [10960008]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
BenD
Member

Откуда:
Сообщений: 10
Это я ошибся, когда сюда выкладывал запрос.
В конце 2000 естественно стояло.
12 июл 11, 10:20    [10960034]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
BenD
Member

Откуда:
Сообщений: 10
Полный текст лога:

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(808251 row(s) affected)

(2049 row(s) affected)

(2049 row(s) affected)

(384 row(s) affected)

(0 row(s) affected)

(8905 row(s) affected)

(8905 row(s) affected)

(0 row(s) affected)

(18904 row(s) affected)

(18904 row(s) affected)

(18904 row(s) affected)

(18904 row(s) affected)

(18904 row(s) affected)

(18904 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(5643183 row(s) affected)
Msg 666, Level 16, State 2, Line 1
The maximum system-generated unique value for a duplicate group was exceeded for index with partition ID 422701233012736. Dropping and re-creating the index may resolve this; otherwise, use another clustering key.
12 июл 11, 10:22    [10960044]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Покажите вашу таблицу, на которой фейлит. Вообще - странно.
12 июл 11, 10:24    [10960059]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
BenD
Member

Откуда:
Сообщений: 10
К сожалению не могу показать таблицу... Коммерческая тайна.
12 июл 11, 10:28    [10960088]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
BenD
К сожалению не могу показать таблицу... Коммерческая тайна.
Я про структуру и объем. Названия ваши коммерчески тайных полей можете заменить на f1, f2, ... , fn.
12 июл 11, 10:33    [10960126]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
AHDP
Member

Откуда:
Сообщений: 1226
И посмотрите, что за данные в поле, которое апдейтиится. В каких индексах оно учавствует.
Кстати, зачем ограничение на 4000 год? Смещение в 2000 применяется на уровне обмена данными с SQL и на внутренне органичение 1С в 4000 год не влияет.
12 июл 11, 10:49    [10960232]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
BenD
Member

Откуда:
Сообщений: 10
Гавриленко Сергей Алексеевич
BenD
К сожалению не могу показать таблицу... Коммерческая тайна.
Я про структуру и объем. Названия ваши коммерчески тайных полей можете заменить на f1, f2, ... , fn.

Как мне определить в какой таблице стопорнулось?
Т.к. есть таблица в 5,8 млн записей, а есть и таблица в 27 млн. записей.
П.С. напоминаю, что я слабо разбираюсь в Скуле.
12 июл 11, 11:01    [10960305]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
BenD
Member

Откуда:
Сообщений: 10
AHDP,
В силу своей не грамотности в написании запросов, нашел скрипт, который изменял смещение с 2000 на 0 и переделал наоборот.
Поэтому и оставалось такое ограничение.
12 июл 11, 11:03    [10960315]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Glory
Member

Откуда:
Сообщений: 104751
http://robboek.com/2009/02/13/sql-server-uniqueifier-values-and-the-number-of-the-beast/ ???
12 июл 11, 11:04    [10960319]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
BenD
Гавриленко Сергей Алексеевич
пропущено...
Я про структуру и объем. Названия ваши коммерчески тайных полей можете заменить на f1, f2, ... , fn.

Как мне определить в какой таблице стопорнулось?
Т.к. есть таблица в 5,8 млн записей, а есть и таблица в 27 млн. записей.
П.С. напоминаю, что я слабо разбираюсь в Скуле.
print @TableName в ваш скрипт вставьте.
12 июл 11, 11:30    [10960472]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
BenD
Member

Откуда:
Сообщений: 10
Запустить повторно смогу на выходных.
Как можно его пока что оптимизировать? есть мысли?
12 июл 11, 11:48    [10960581]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
1. Написать скрипт, который будет апдейтить все поля одной таблицы за один запрос.
2. Снести перед апдейтом все индексы, кроме кластерных.
3. Возможно, переписать скрипт так, чтобы он апдейтил не все сразу, а порциями, скажем, по 100к записей.
12 июл 11, 12:03    [10960676]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
BenD
Member

Откуда:
Сообщений: 10
М-да... на словах понятно, но как это реализовать когда не шаришь в языке )))
Спасибо, что путь указали, куда двигаться. Попробую что-нибудь навоять, если разберусь.
12 июл 11, 12:13    [10960737]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить