Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Экспорт данных  [new]
ExportCTAS
Guest
Есть таблица 100млн записей с уникальным PK-полем ID
Есть вторая таблица 1.5млн записей, которая содержит ID, которые надо выгрузить из первой таблицы во внешний файл.

Пока пробовал решения влоб, результата нет:
1. Создать новую таблицу запросом create as select ... с нужными записями. Чтобы потом ее выгрузить.
План запроса показывает обход индекса по вложенному циклу, но все долго висит, вешая всю машину и в итоге падает по ошибке ввода/вывода.
2. Пробовал выгрузить данные мастером экспорта прямо запросом. Тоже долго висел, висел, подвешивая всю машину при этом и так не вышел на прогресс (чтобы было видно, что пошла выгрузка записей).

Вопрос: как правильно выполнить экспорт этих нужных данных из первой таблицы?
7 окт 16, 11:14    [19754211]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт данных  [new]
Megabyte
Member

Откуда: ближайшее заМКАДье
Сообщений: 4975
Выгружать по частям.
7 окт 16, 12:56    [19754939]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт данных  [new]
ExportCTAS
Guest
Подсмотрел про T-SQL, нарисовал хранимку, которая бежит по курсору таблицы с ID, достает записи из другой таблицы по ID и складывает по 5000 записей в третью таблицу для экспорта.
Комп хотя бы не виснет, правда шуршит небыстро. Ждемс )
7 окт 16, 12:57    [19754957]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт данных  [new]
komrad
Member

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

как вариант:
1) создать вьюшку, которая будет выдавать записи из таблицы №1 с ID, содержащимися в таблице №2
2) выгрузить эту вьюшку, используя BCP, во внешний файл в текстовом формате с произвольным разделителем.
7 окт 16, 13:01    [19754975]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт данных  [new]
ExportCTAS
Guest
komrad
как вариант

Пробовал (правда через мастер экспорта/импорта, bcp не пробовал).
Машина адски подвисает и на ней становится невозможно работать.
А сейчас вроде шуршит хранимка и не мешает совсем, данные заливаются в табличку, которую потом надеюсь легко выгрузить, главное, чтобы не сломалось на вводе/выводе как при ctas-е )
7 окт 16, 13:07    [19755028]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт данных  [new]
komrad
Member

Откуда:
Сообщений: 5516
ExportCTAS
komrad
как вариант

Пробовал (правда через мастер экспорта/импорта, bcp не пробовал).

не доверяю я всяким "мастерам", когда можно руками сделать с нужным уровнем контроля и гранулярности операций
7 окт 16, 13:13    [19755080]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт данных  [new]
ExportCTAS
Guest
komrad
не доверяю я всяким "мастерам", когда можно руками сделать с нужным уровнем контроля и гранулярности операций

Я просто не в курсе как можно контролировать выгрузку вьюшки. Похоже, что это подразумевает выполнение запроса вьюшки, но если этот запрос сам по себе не в состоянии выполнится, пыхтит изо всех сил, вешает машину и в итоге отваливается, как это можно контролировать, поставив на экспорт это вьюшку? :) Надо видимо, отладить сам запрос сначала. Но как, не знаю, в MSSQL не спец. Да и план, которые показывается вроде вполне нормальный - такой же который реализовал хранимкой - нестед луп по первой табличке с доставанием по id данных из второй таблички.
7 окт 16, 13:57    [19755446]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт данных  [new]
komrad
Member

Откуда:
Сообщений: 5516
ExportCTAS
komrad
не доверяю я всяким "мастерам", когда можно руками сделать с нужным уровнем контроля и гранулярности операций

Я просто не в курсе как можно контролировать выгрузку вьюшки. Похоже, что это подразумевает выполнение запроса вьюшки, но если этот запрос сам по себе не в состоянии выполнится, пыхтит изо всех сил, вешает машину и в итоге отваливается, как это можно контролировать, поставив на экспорт это вьюшку? :) Надо видимо, отладить сам запрос сначала. Но как, не знаю, в MSSQL не спец. Да и план, которые показывается вроде вполне нормальный - такой же который реализовал хранимкой - нестед луп по первой табличке с доставанием по id данных из второй таблички.

если в Вашем случае, процедура удовлетворяет по общей нагрузке на систему и есть свободное место на дисках для копии таблицы, то всё хорошо

У BCP есть ключи -F, -L, -b - они дают возможность выгружать по частям, заливать батчами
7 окт 16, 14:17    [19755559]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт данных  [new]
ExportCTAS
Guest
komrad, процедура нагрузке удовлетворяет, но все равно упала на вводе-выводе.
Поэтому инсерт обернул в трай-кэч, дабы пропустить данные, на которых она падает (в моем случае не критично если из миллионов потеряется пара-тройка записей).
В итоге получилась такая хпшка:
- открываем курсор с Id-шниками по одной таблице и идем по нему
- для каждой строки курсора ищем по id в нужной таблице запись и переливаем ее в отдельную табличку для дальнейшего экспорта
- при этом якобы делаем это транзакциями по 5000 строк (не уверен, что это на самом деле так, но пусть будет)
+
CREATE PROCEDURE [dbo].[export_proc]
AS
   DECLARE @i int = 0 
   DECLARE @v_id varchar(max)

   DECLARE my_cur CURSOR FOR 
      SELECT id FROM [....].[dbo].[source_table]
   
   OPEN my_cur

   FETCH NEXT FROM my_cur INTO @v_id
   
   WHILE @@FETCH_STATUS = 0
   BEGIN
      SET @i= @i+1
      IF (@i = 1) 
        BEGIN tran

      IF (@i = 5001) 
      BEGIN
         COMMIT tran
        SET @i = 0        
      END 

      BEGIN try 
         INSERT INTO [....].[dbo].[rec_to_export] 
              SELECT .....
                FROM [....].[dbo].[other_source_table] d
               WHERE d.id = @v_id
      END try
      BEGIN catch
         SELECT @v_id
      END catch
     
      FETCH NEXT FROM my_cur INTO @v_id
   END
   
   COMMIT tran
   CLOSE my_cur
   DEALLOCATE my_cur
GO

Работает хорошо, никому не мешает, скорость нормальная, оставил на выхи, но она упала по нехватке памяти. outofmemory.
Есть подозрение, что 99% беда в курсоре. Т.е. при фетче курсора все данные тупо тянутся в память, ну она и забивается.

В этой связи вопрос. Как грамотно переписать эту хп-шку. Понимаю, что было бы разумно выбирать данные по частям, например, разбив на диапазоны по какому-то идентификатору, но если принять такого идентификатора нет?
Еще классический вариант:
1 - добавляем поле-флаг, в котором будем отмечать, что запись уже выгружена (делаем индекс по этому полю)
2 - дальше в цикле выбираем записи в курсор например по 10000 штук, у которых не выставлен флаг
3 - инсертим их в целевую табличку и выставляем флаг, что запись обработана
4 - повторяем п2 пока в курсор не перестанут подбираться записи, т.е. пока все не будут отмечены флагом
Но может в MSSQL можно как-то проще? Например как-то ограничить "окно" курсора, чтобы он в памяти не держал весь набор, а только те данные, которые только что профетчил?
10 окт 16, 11:42    [19763163]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт данных  [new]
ExportCTAS
Guest
ExportCTAS
1 - добавляем поле-флаг, в котором будем отмечать, что запись уже выгружена (делаем индекс по этому полю)
2 - дальше в цикле выбираем записи в курсор например по 10000 штук, у которых не выставлен флаг
3 - инсертим их в целевую табличку и выставляем флаг, что запись обработана
4 - повторяем п2 пока в курсор не перестанут подбираться записи, т.е. пока все не будут отмечены флагом

Точнее, наоборот:
1 - + поле с дефолтным значением например "1", кот означает, что запись еще не обрабатывалась + индекс по нему
2 - берем по этому индексу топ 5000 записей, переливаем в целевую таблицу, поле-флаг чистим, и так пока все не обработаем
10 окт 16, 11:45    [19763187]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт данных  [new]
_human
Member

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

exec xp_cmdshell
'bcp "select * from [dbo].[source_table] s
where exists (select * from [dbo].[other_source_table] d
				WHERE s.id = d.id)" queryout "C:\Export.txt" -T -c -d db_name'


скорось будет агонь. можно еще поиграться с "-a packet_size"
автор
-a packet_size

Specifies the number of bytes, per network packet, sent to and from the server. A server configuration option can be set by using SQL Server Management Studio (or the sp_configure system stored procedure). However, the server configuration option can be overridden on an individual basis by using this option. packet_size can be from 4096 to 65535 bytes; the default is 4096.

Increased packet size can enhance performance of bulk-copy operations. If a larger packet is requested but cannot be granted, the default is used. The performance statistics generated by the bcp utility show the packet size used.
10 окт 16, 12:24    [19763407]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт данных  [new]
komrad
Member

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

а у вас сколько записей в таблице 1 с айдишниками, которые соответствуют таблице 2? в процентном отношении ко всей таблице?

Можно зайти еще с такой стороны:
1) сделать таблицу 3, копию таблицы 1
2) батчами удалить из нее записи с ID, не входящими в таблицу 2
3) выгрузить таблицу 3

Кстати, у вас таблица 1 целостна? Пробовали ее выгружать целиком?
10 окт 16, 12:24    [19763410]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт данных  [new]
aleks2
Guest
1. "но она упала по нехватке памяти. outofmemory." - установите последний SP. Ибо, хотя смысл в вашем коде отсутствует немножко менее, чем полностью, падать и жрать память он не должен.

2. Нахера вы там какие-то транзакции мучаете?

3.
CREATE PROCEDURE [dbo].[export_proc]
AS
--   DECLARE @v_id varchar(max) -- ? шо, и правда varchar(max)? Не верю!

   -- это 100% не нужно, если на [dbo].[source_table] есть уникальный столбец.
   select i = identity(int, 1, 1), id into #source_table from [dbo].[source_table];
   alter table #source_table add primary key(i);
   
   DECLARE @i int = 0, @rc int = 1; 

   while @rc > 0 begin
       with x as ( select top(50000) * from #source_table where i > @i order by i asc )
        INSERT INTO [dbo].[rec_to_export] 
                  SELECT d.....
                    FROM [dbo].[other_source_table] d
                         inner join x on  WHERE d.id = x.id;

       set  @rc = @@rowcount;

       with x as ( select top(50000) * from #source_table where i > @i order by i asc )
         select top(1) @i = i from x order by i desc;

   end;
10 окт 16, 12:25    [19763412]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт данных  [new]
komrad
Member

Откуда:
Сообщений: 5516
aleks2
2. Нахера вы там какие-то транзакции мучаете?

+1 и курсоры заодно
10 окт 16, 12:28    [19763435]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт данных  [new]
ExportCTAS
Guest
Ребят, я ж говорю, что на каких-то данных выборка из source-таблицы падает по вводу-выводу.
Поэтому решил читать построчно, дабы в блоке исключения просто пропустить записи, на которых ломаюсь.
Поэтому insert select это хорошо конечно, но так можно пропустить большие куски данных имхо.
А транзакции - так это не работал MSSQL и не знаком с его транзакционной моделью тсказать )
aleks2, но примерно понял, попробую переписать так же, но построчно ))
10 окт 16, 12:40    [19763488]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт данных  [new]
komrad
Member

Откуда:
Сообщений: 5516
ExportCTAS
Ребят, я ж говорю, что на каких-то данных выборка из source-таблицы падает по вводу-выводу.

может у вас таблица битая - проверьте ее командой dbcc checktable
10 окт 16, 12:45    [19763518]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт данных  [new]
ExportCTAS
Guest
komrad
может у вас таблица битая - проверьте ее командой dbcc checktable

Проверил: 0 ошибок размещения 35 ошибок согласования. И куда с этим добром идти? :)
Среди ошибок что-то вроде: страница ... не обнаружена при просмотре хотя на нее ссылается родительская страница.
Если есть возможность как-то отремонтировать не сильно затратно и заморочено, но можно попробовать, если нет, то мне проще просто пропустить "битые" данные.
10 окт 16, 14:17    [19764155]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт данных  [new]
komrad
Member

Откуда:
Сообщений: 5516
ExportCTAS
komrad
может у вас таблица битая - проверьте ее командой dbcc checktable

Проверил: 0 ошибок размещения 35 ошибок согласования. И куда с этим добром идти? :)

Выложите результат проверки - так понятней будет, что с ней делать.
10 окт 16, 14:29    [19764227]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт данных  [new]
ExportCTAS
Guest
komrad
Выложите результат проверки - так понятней будет, что с ней делать.

Долго разбираться не хотелось. Поставил проверку с флагом REPAIR чего-то там. Она ничего не смогла поправить.
В итоге запустил переливку, как и хотел по одной записи, но по частям
+
ALTER PROCEDURE [dbo].[export_proc]
AS
   DECLARE @i int = 1
   DECLARE @v_id varchar(max)

   WHILE @i > 0 
   BEGIN
      SET @i = 0

      DECLARE my_cur CURSOR FOR 
         SELECT TOP 5000 id FROM source_table WHERE is_proc = 1
      OPEN my_cur      
      FETCH NEXT FROM my_cur INTO @v_id

      WHILE @@FETCH_STATUS = 0
      BEGIN
         SET @i = @i + 1
        
         BEGIN TRY 
            INSERT INTO target_table 
            SELECT d.*
              FROM other_source_table d
             WHERE d.id = @v_id
         END TRY
         BEGIN CATCH
            SELECT @v_id
         END CATCH

         UPDATE source_table 
             SET is_proc = null
          WHERE id = @v_id 

         FETCH NEXT FROM my_cur INTO @v_id
      END
      COMMIT
      CLOSE my_cur
      DEALLOCATE my_cur
   END

Может и изврат, но за ночь отработало.
Из 1.5млн записей, не прочитались только 3шт, что вполне приемлемо.
11 окт 16, 10:19    [19767475]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить