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

Откуда:
Сообщений: 1965
Дано: файл .csv размером в 800+ МБ, 3570 тысяч строк. При помощи .dtsx-пакета он втягивается в MS SQL за 15 минут, включая создание пакета с нуля. Недостаток: использование .dtsx-пакета.

Есть вариант с копированием данных из файла в промежуточную базу Access, к которой создан linked server на MS SQL Server-e. После закачки данных в БД Access на сервере запускается хранимая процедура, качающая данные с linked server-a. Требует 40 минут. Недостаток - необходимость создавать linked server на сервере.

Если же работать с файлом из VBA с помощью insert into [ODBC; Driver={SQL Server}...].ИмяТаблицы FROM [...]
, то тогда процесс грозится растянуться часов на 6. Недостаток: время.

Вставка с чтением из файла по 1000 строк и их вставкой в таблицу сервера сожрёт ещё больше времени. Недостаток: время.

Вопрос: какие варианты VBA-кода позволят сократить время загрузки данных из .xlsx/.csv файлов на MS SQL Server до времени, всего в 2-3 раза больше, чем время работы с .dtsx пакетом, без создания дополнительных объектов (процедур или linked server-ов) на MS SQL Server-e?
30 янв 14, 17:05    [15495949]     Ответить | Цитировать Сообщить модератору
 Re: Методы копирования данных из Excel и csv-файлов в MS SQL, сравнимые по скорости с dtsx  [new]
aleks2
Guest
bcp.exe

bulk insert
30 янв 14, 17:10    [15495979]     Ответить | Цитировать Сообщить модератору
 Re: Методы копирования данных из Excel и csv-файлов в MS SQL, сравнимые по скорости с dtsx  [new]
DaniilSeryi
Member

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

bcp.exe - сейчас делаю,
а bulk insert из VBA - как? И ведь он требует, чтобы пользователь входил в соответствующую роль?
30 янв 14, 17:26    [15496072]     Ответить | Цитировать Сообщить модератору
 Re: Методы копирования данных из Excel и csv-файлов в MS SQL, сравнимые по скорости с dtsx  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 35370
Блог
DaniilSeryi,

а по вашему SSIS-пакет не требует разрешений?
30 янв 14, 17:31    [15496117]     Ответить | Цитировать Сообщить модератору
 Re: Методы копирования данных из Excel и csv-файлов в MS SQL, сравнимые по скорости с dtsx  [new]
DaniilSeryi
Member

Откуда:
Сообщений: 1965
Критик,

если пакет и Source-файлы лежат на жёстком диске у пользователя, то нужны только права на доступ к Destination-таблицам.
И входить в серверную роль bulkadmin для использования .dtsx мне не пришлось. Только public хватило.
30 янв 14, 17:38    [15496175]     Ответить | Цитировать Сообщить модератору
 Re: Методы копирования данных из Excel и csv-файлов в MS SQL, сравнимые по скорости с dtsx  [new]
Glory
Member

Откуда:
Сообщений: 104751
DaniilSeryi
И входить в серверную роль bulkadmin для использования .dtsx мне не пришлось. Только public хватило.

ну так у вас наверное и bulk insert-а в пакете не было?
30 янв 14, 17:46    [15496220]     Ответить | Цитировать Сообщить модератору
 Re: Методы копирования данных из Excel и csv-файлов в MS SQL, сравнимые по скорости с dtsx  [new]
DaniilSeryi
Member

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

нет, естественно.
30 янв 14, 17:48    [15496241]     Ответить | Цитировать Сообщить модератору
 Re: Методы копирования данных из Excel и csv-файлов в MS SQL, сравнимые по скорости с dtsx  [new]
Glory
Member

Откуда:
Сообщений: 104751
DaniilSeryi
Glory,

нет, естественно.

и вы думаете, что dtsx обладает какой то секретной командой, кроме insert или bulk insert ?
30 янв 14, 17:57    [15496296]     Ответить | Цитировать Сообщить модератору
 Re: Методы копирования данных из Excel и csv-файлов в MS SQL, сравнимые по скорости с dtsx  [new]
DaniilSeryi
Member

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

Мне в общем-то всё равно, какими секретными командами он обладает - insert, bulk insert или вызов bcp.
Главное, какие секретные команды в VBA позволят добиться скорости загрузки, как у dtsx или сравнимой. У коллег есть мнение, что мне поможет вызов bcp.
30 янв 14, 18:03    [15496333]     Ответить | Цитировать Сообщить модератору
 Re: Методы копирования данных из Excel и csv-файлов в MS SQL, сравнимые по скорости с dtsx  [new]
Glory
Member

Откуда:
Сообщений: 104751
DaniilSeryi
Главное, какие секретные команды в VBA позволят добиться скорости загрузки, как у dtsx или сравнимой. У коллег есть мнение, что мне поможет вызов bcp.

Для трассировки команд, которые выполняются на сервере есть SQL Profiler
bcp - это клиентская утилита, запускающая на сервере bulk insert
30 янв 14, 18:06    [15496359]     Ответить | Цитировать Сообщить модератору
 Re: Методы копирования данных из Excel и csv-файлов в MS SQL, сравнимые по скорости с dtsx  [new]
DaniilSeryi
Member

Откуда:
Сообщений: 1965
Итоги: использование bcp - 2-3 минуты на 800 с лишним МБ.
Но самое интересное - а без использования bcp как можно быстро загрузить данные на сервер посредством VBA?
31 янв 14, 12:39    [15499756]     Ответить | Цитировать Сообщить модератору
 Re: Методы копирования данных из Excel и csv-файлов в MS SQL, сравнимые по скорости с dtsx  [new]
Glory
Member

Откуда:
Сообщений: 104751
http://msdn.microsoft.com/en-us/library/7ek5da1a(v=vs.110).aspx
31 янв 14, 13:36    [15500228]     Ответить | Цитировать Сообщить модератору
 Re: Методы копирования данных из Excel и csv-файлов в MS SQL, сравнимые по скорости с dtsx  [new]
user89
Member

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

если у базы модель воссановления = simple, попробуйте такой код (у меня он немногим проиграл bcp.exe)
if object_id('tempdb..#tmp') is not null drop table #tmp
select *
into #tmp
from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=c:\Temp\','select * from BigFile.csv')
31 янв 14, 16:22    [15501403]     Ответить | Цитировать Сообщить модератору
 Re: Методы копирования данных из Excel и csv-файлов в MS SQL, сравнимые по скорости с dtsx  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31949
DaniilSeryi
Но самое интересное - а без использования bcp как можно быстро загрузить данные на сервер посредством VBA?
Просто вызвав сиквельную команду, поищите по insert bulk
31 янв 14, 16:25    [15501426]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить