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

Откуда:
Сообщений: 10
Здравствуйте!

Нужно сгенерировать большой объём тестовых данных - больше 600 000 000 записей. Если делать обычным INSERT INTO ... VALUES, то это займет больше 50 часов. Каким образом можно существенно сократить время выполнения данной процедуры?
10 июн 11, 18:07    [10798561]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая вставка большого количества записей  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Сделать файло соответвующего формата и залить bcp.

Еще можно генерировать и заливать разом по 100к - 1кк записей, но все зависит от того, что и чем вы генерируете.
10 июн 11, 18:10    [10798576]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая вставка большого количества записей  [new]
DrVavilon
Member

Откуда:
Сообщений: 10
Гавриленко Сергей Алексеевич
Сделать файло соответвующего формата и залить bcp.


Не совсем понял, как это реализовать. Мне нужно забить тестовыми данными, в которых должна сохраняться определенная логика. Файл же нужно заполнить, чтобы копировать из него.
10 июн 11, 18:26    [10798663]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая вставка большого количества записей  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
ну так возьмите свой запрос когторый генерит ети миллиона записей - и вылете его в файл тем де bcp ,потом подымите файл в таблицу как вам поповетовали
10 июн 11, 18:33    [10798717]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая вставка большого количества записей  [new]
DrVavilon
Member

Откуда:
Сообщений: 10
Честно говоря, никогда такого не делал( А можно какой-то пример привести? Или пошаговый алгоритм. Был бы очень благодарен
10 июн 11, 19:00    [10798920]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая вставка большого количества записей  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
DrVavilon, вот только совсем недавно эксперементировал...

insert into...
select ...

работает медленнее чем

select ...
into ...

Правда данных генерерировал что-то около 5 записей по 6 байт (30000000 штук). Заняло это где-то 15-25 секунд, балк вычитка с KILOBYTES_PER_BATCH = 64 заняла около 63569 ms, тобишь минуту с копейками.
10 июн 11, 19:23    [10799029]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая вставка большого количества записей  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
Собственно, вот че делалось:
--CREATE DATABASE tst
--go
--USE tst
--go
--DROP  TABLE tempTbl

SET STATISTICS TIME ON
SELECT  t.a , t.b , t.с , t.d , t.e
INTO tempTbl
FROM (
select a='123456',b='789012',с='345678',d='901234',e='567890'
from
(
select 1 n union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10
) a1
CROSS JOIN
(
select 1 n union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10
) a2
CROSS JOIN
(
select 1 n union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10
) a3
) t CROSS JOIN (
select a=1
from
(
select 1 n union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10
) a1
CROSS JOIN
(
select 1 n union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10
) a2
CROSS JOIN
(
select 1 n union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10
) a3
) t1 CROSS JOIN 
(
SELECT a1.n FROM (
select 1 n union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10
) a1
CROSS JOIN (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3) a3
) t2
SET STATISTICS TIME OFF

-- bcp "SELECT  a + ',' + b + ',' + с + ',' + d + ',' + e FROM tst.dbo.tempTbl" queryout e:\bulk.txt -c -T -S .\SQLEXPRESS >nul
--TRUNCATE TABLE dbo.tempTbl
SET STATISTICS TIME ON
BULK INSERT dbo.tempTbl FROM 'e:\bulk.txt' WITH (TABLOCK, KILOBYTES_PER_BATCH = 64, ROWTERMINATOR='\n', FIELDTERMINATOR=',')
SET STATISTICS TIME OFF
10 июн 11, 19:25    [10799040]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая вставка большого количества записей  [new]
DrVavilon
Member

Откуда:
Сообщений: 10
kDnZP, Огромное спасибо за пример. Теперь понятно каким путем нужно идти)
Только не понял, как Вы заполняете таблицу tempTbl. Я как раз не могу придумать, как в моем случае её заполнять; Схематически, мне нужно сделать приблизительно следующее:

DECLARE @Index int
SET @Index = 1
WHILE @Index < 100000000
BEGIN
	INSERT INTO Form VALUES (@Index, 'val' + CAST(@Index as varchar(10)), null, null)
	SET @Index = @Index + 1
END

Но, так как SELECT INTO <new table> здесь в цикле использовать нельзя, то нужно эти данные (которые, по сути, получаються в результате циклического обхода) получить одним запросом, как это сделали Вы.

Увы, пока не представляю как...(
10 июн 11, 20:44    [10799388]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая вставка большого количества записей  [new]
NoLock
Member

Откуда: левый верний угол
Сообщений: 90
В одном из моих проектов BULK работал в 20 раз быстрее, чем C# программа, которая разбирала файл и вставляла по одной записи:
insert into T
	(a,b,c)
select 
	a,b,c
from openrowset
(
	bulk 'e:\test.csv',
	FIRSTROW = 2,
	FORMATFILE='e:\testFormat.xml'
) as [src]
GO

Формат файл:
<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
	<RECORD>
		<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="12"/>
		<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="2"/>
		<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="16"/>
	</RECORD>
	<ROW>
		<COLUMN SOURCE="1" NAME="a" xsi:type="SQLINT"/>
		<COLUMN SOURCE="2" NAME="b" xsi:type="SQLNVARCHAR"/>
		<COLUMN SOURCE="3" NAME="c" xsi:type="SQLNVARCHAR"/>
	</ROW>
</BCPFORMAT>
10 июн 11, 21:35    [10799646]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая вставка большого количества записей  [new]
DrVavilon
Member

Откуда:
Сообщений: 10
Интересно) Правда, у меня сейчас проблема другая. С тем, чтобы прочитать и вставить записи из файла посредством BULK проблем нет. Вопрос в том, как эти данные в таком количестве и формируемые по вышеописанной схеме в этот файл впихнуть?
10 июн 11, 21:58    [10799740]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая вставка большого количества записей  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
DrVavilon
как эти данные в таком количестве и формируемые по вышеописанной схеме в этот файл впихнуть?
Не скулем единым ... ;)
10 июн 11, 22:17    [10799797]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая вставка большого количества записей  [new]
NoLock
Member

Откуда: левый верний угол
Сообщений: 90
Написать программу, которая это делает? :o)
Если это повторяюшиеся данные, то создаете текстовый фаил 1.txt с повторяющимся фрагментом (не забудте Enter после последней строки).
copy 1.txt+1.txt 2.txt
del 1.txt
copy 2.txt+2.txt 1.txt
del 2.txt
Если понравилось - повторить несколько раз.
Результат использовать для BULKLOAD.
10 июн 11, 22:22    [10799815]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая вставка большого количества записей  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
DrVavilon,

SELECT t.rn, 'val' + CAST(t.rn as varchar(10)) v, NULL f3, NULL f4
--INTO Form
FROM (
SELECT  ROW_NUMBER() OVER (ORDER BY v1.type) rn
FROM    master..spt_values v1 ,
        master..spt_values v2 ,
        master..spt_values v3 ,
        master..spt_values v4
) t
WHERE t.rn<1000 -- 100000000
10 июн 11, 22:41    [10799884]     Ответить | Цитировать Сообщить модератору
 Re: Быстрая вставка большого количества записей  [new]
DrVavilon
Member

Откуда:
Сообщений: 10
Mnior
Не скулем единым ... ;)

К сожалению, я ограничен именно им)


NoLock
Написать программу, которая это делает? :o)
Если это повторяюшиеся данные, то создаете текстовый фаил 1.txt с повторяющимся фрагментом (не забудте Enter после последней строки).
copy 1.txt+1.txt 2.txt
del 1.txt
copy 2.txt+2.txt 1.txt
del 2.txt
Если понравилось - повторить несколько раз.
Результат использовать для BULKLOAD.

Думаю, это не подойдет - мне все таки нужно будет не просто копировать данные одного файла во второй, так как данные у меня не совсем повторяющиеся

kDnZP
DrVavilon,

SELECT t.rn, 'val' + CAST(t.rn as varchar(10)) v, NULL f3, NULL f4
--INTO Form
FROM (
SELECT  ROW_NUMBER() OVER (ORDER BY v1.type) rn
FROM    master..spt_values v1 ,
        master..spt_values v2 ,
        master..spt_values v3 ,
        master..spt_values v4
) t
WHERE t.rn<1000 -- 100000000

Спасибо большое, этот код похож на свет в конце тоннеля))) И, что главное, работает быстро. Попробую использовать именно такой способ. Напишу, что из этого получилось)
11 июн 11, 23:38    [10802675]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить