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

Задача такая. Нужно загружать данные (например, таблица Excel ~40000 записей) через web-интерфейс в MSSQL.

Это реализовано двумя способами:
1. Файл переписывается на сервер, загоняется в Dataset и в цикле вызывается серверная процедура с параметрами, которая инсертит построчно данные в таблицу. Время, приблизительно 15 минут.
Основной недостаток - веб-приложение во время добовления данных недоступно.
Поэтому возникла идея: взять *.xls файл и на его основе программно создать *.xml файл, переписать его на сервер и джобом по расписанию мониторить папку. Появился файл, запускается процедура, парсит XML и добавляет данные в таблицу.

2. Что и было реализовано через
SELECT @xml = CONVERT(xml, BulkColumn, 2) FROM OPENROWSET(
   BULK 'E:\Test.xml',
           SINGLE_BLOB
) AS x
В цикле перебираем XML и инсертим.
-- считаем строки в XML
SELECT @RowCount=@xml.value('count(/NewDataSet/TABLE)','int')

-- перебираем  все строки в XML переменной и добавляем в таблицу Tab1
SET    @Counter=1
    WHILE  @Counter<=@RowCount
		BEGIN
			SELECT  
				@Contract=T.Contract,
				@Product=T.Product,
				.......
				@CreateUser=T.CreateUser
			FROM (SELECT
				  x.value('Номер_x0020_контракта[1]', 'nvarchar(100)') AS [Contract],
				  x.value('Продукт[1]', 'nvarchar(100)') AS Product,
				  .....
				  x.value('CreateUser[1]', 'nvarchar(100)') AS CreateUser
				FROM @xml.nodes('//NewDataSet[1]/TABLE[sql:variable("@Counter")]') Tab1(x)) as T

			INSERT INTO [dbo].[Tab1]([Contract], [Product], ... [CreateUser])
			VALUES (@Contract,@Product, .... CreateUser)

Попробовал добавить 20000 записей, время выпонения ~7-8 сек на 1 запись!!!

Подскажите, как можно оптимизировать 2 вариант? Есть способ работать с большим XML быстрее? Спасибо.
29 окт 09, 10:47    [7854071]     Ответить | Цитировать Сообщить модератору
 Re: Большой XML в SQL server 2005  [new]
Aleksey-K
Member

Откуда: Москва
Сообщений: 3116
Может лучше через OPENXML ?
С уважением, Алексей
29 окт 09, 10:50    [7854102]     Ответить | Цитировать Сообщить модератору
 Re: Большой XML в SQL server 2005  [new]
Glory
Member

Откуда:
Сообщений: 104760
Ваша проблема в том, что у вас цикл.
Почему вы сразу не делаете INSERT всех записей из xml ?
Почему не делаете сразу INSERT из OPENDATASOURCE() к Excel файлу ?
29 окт 09, 10:53    [7854141]     Ответить | Цитировать Сообщить модератору
 Re: Большой XML в SQL server 2005  [new]
Дмитрий_N1
Guest
Glory
Ваша проблема в том, что у вас цикл.
Почему вы сразу не делаете INSERT всех записей из xml ?

Спасибо. Перед Insert выполняется проверка и некоторые операции с данными. Буду благодарен, если приведете пример вставки всех записей.

Почему не делаете сразу INSERT из OPENDATASOURCE() к Excel файлу ?

Сейчас пробую. Напишу, подходит ли для этой задачи..
29 окт 09, 12:50    [7855302]     Ответить | Цитировать Сообщить модератору
 Re: Большой XML в SQL server 2005  [new]
Дмитрий_N1
Guest
Aleksey-K
Может лучше через OPENXML ?
С уважением, Алексей


Спасибо, почитал.. Вроде для больших XML не рекомендуется, медленно..
29 окт 09, 12:52    [7855320]     Ответить | Цитировать Сообщить модератору
 Re: Большой XML в SQL server 2005  [new]
Glory
Member

Откуда:
Сообщений: 104760
Дмитрий_N1
Glory
Ваша проблема в том, что у вас цикл.
Почему вы сразу не делаете INSERT всех записей из xml ?

Спасибо. Перед Insert выполняется проверка и некоторые операции с данными. Буду благодарен, если приведете пример вставки всех записей.

INSERT ... SELECT ...
29 окт 09, 12:52    [7855327]     Ответить | Цитировать Сообщить модератору
 Re: Большой XML в SQL server 2005  [new]
D2NX
Member

Откуда: Минск
Сообщений: 4
Так а зачем в XML-то перегонять? В CSV - гораздо стратежней, я считаю. Потом можно очень быстро балк инсертом вставить.
29 окт 09, 12:56    [7855366]     Ответить | Цитировать Сообщить модератору
 Re: Большой XML в SQL server 2005  [new]
DeColo®es
Member

Откуда: Москва
Сообщений: 5499
Блог
Дмитрий_N1,

Если использовать одну-две конструкции, да еще в одном SELECT, без цикла как у Вас, то XQuery быстрее.
Но если нужно получить сразу много значений, то OPENXML обгоняет XQuery на первом же повороте. :)
29 окт 09, 13:11    [7855508]     Ответить | Цитировать Сообщить модератору
 Re: Большой XML в SQL server 2005  [new]
Дмитрий_N1
Guest
Спасибо за внимание к теме!
D2NX

Так а зачем в XML-то перегонять? В CSV - гораздо стратежней, я считаю. Потом можно очень быстро балк инсертом вставить.

Просто файлы бывают не только xls, но и txt с разделителями, а XML вроде как единый формат получается..
DeColo®es

Но если нужно получить сразу много значений, то OPENXML обгоняет XQuery на первом же повороте. :)

С OPENXML еще не сталкивался, но спасибо, на будущее учту!
Glory
Дмитрий_N1
Glory
Ваша проблема в том, что у вас цикл.
Почему вы сразу не делаете INSERT всех записей из xml ?

Спасибо. Перед Insert выполняется проверка и некоторые операции с данными. Буду благодарен, если приведете пример вставки всех записей.

INSERT ... SELECT ...

Вам отдельное спасибо! Как только убрал цикл, 21791 записей за 59 сек:) Потрясающий результат!!!
Такое время выполнения меня вполне устраивает, но все же еще остался вопрос.. спортивный:) На форуме прочитал, что добавляли 50000 записей за 1 сек.. Как такое можно реализовать?
29 окт 09, 15:59    [7857368]     Ответить | Цитировать Сообщить модератору
 Re: Большой XML в SQL server 2005  [new]
Alexandr Kravchuk
Member

Откуда:
Сообщений: 70
Очень быстро добавляются только через bulk insert, от которого Вы отказались (не приняли CSV).
6 ноя 09, 13:01    [7891258]     Ответить | Цитировать Сообщить модератору
 Re: Большой XML в SQL server 2005  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31227
Дмитрий_N1
Просто файлы бывают не только xls, но и txt с разделителями, а XML вроде как единый формат получается..
В качесnве единого формата можно принять и csv, почему же только XML?
6 ноя 09, 14:36    [7892220]     Ответить | Цитировать Сообщить модератору
 Re: Большой XML в SQL server 2005  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
XML, cvs - какая разница. Для Xml-я есть SQLXMLBulkLoad
6 ноя 09, 16:28    [7893346]     Ответить | Цитировать Сообщить модератору
 Re: Большой XML в SQL server 2005  [new]
Дмитрий_N
Member

Откуда: Москва
Сообщений: 155
Добрый день!

Был в отпуске, а перед отпуском сделал через SQLXMLBulkLoad. Таким образом:
Dim objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.4.0")
objBL.ConnectionString = "provider=SQLOLEDB;Data Source=;Initial Catalog=;Persist Security Info=True;User ID=;Password=;"
objBL.ErrorLogFile = Server.MapPath("\website3\uploadtemp\") & "error.log"
Dim schema_xsd As String = Server.MapPath("\website3\uploadtemp\") & "way.xsd"
Dim fail_xml As String = Server.MapPath("\website3\uploadtemp\") & "way.xml"
Try
    objBL.Execute(schema_xsd, fail_xml)
Catch
 .....
End Try
В данных есть дата в фомате XML, поэтому пришлось делать временную таблицу. А затем из нее делать Insert в основную с конвертацией из текста в дату. То есть после вышенаписанного кода вызывается процедура:
INSERT INTO Way (.....)
			SELECT  
....
convert(datetime,convert(varchar,convert(xml,type_Date).value('xs:dateTime(.)','datetime'),1),1) as type_Date,
....							
			FROM way_Temp
-- очищаем временную таблицу way_Temp
			TRUNCATE TABLE [way_Temp]
Загрузка выполняется каждый день с утра. Все работает нормально (время ~20-25 сек), но всегда со второго и далее раза.. Первый раз всегда ошибка. Во временной таблице данные появляются, а в основную не вставляются. Подскажите, в чем может быть дело? Спасибо!

P.S. Это ASP.NET приложение.
1 дек 09, 11:49    [8001115]     Ответить | Цитировать Сообщить модератору
 Re: Большой XML в SQL server 2005  [new]
Glory
Member

Откуда:
Сообщений: 104760
Дмитрий_N

Загрузка выполняется каждый день с утра. Все работает нормально (время ~20-25 сек), но всегда со второго и далее раза.. Первый раз всегда ошибка. Во временной таблице данные появляются, а в основную не вставляются. Подскажите, в чем может быть дело? Спасибо!

А не пробовали прочитать сообщение об ошибке ?
1 дек 09, 11:51    [8001128]     Ответить | Цитировать Сообщить модератору
 Re: Большой XML в SQL server 2005  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Дмитрий_N
convert(datetime,convert(varchar,convert(xml,type_Date).value('xs:dateTime(.)','datetime'),1),1) as type_Date
Каков тайный смысл этих множественных манипуляций? Нагрузка сервера?
1 дек 09, 14:24    [8002657]     Ответить | Цитировать Сообщить модератору
 Re: Большой XML в SQL server 2005  [new]
Дмитрий_N
Member

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

А не пробовали прочитать сообщение об ошибке ?

Честно, еще нет.. Был в отпуске, завтра утром при первой загрузке прочитаю и напишу..
Каков тайный смысл этих множественных манипуляций? Нагрузка сервера?

Не знал как по другому преобразовать дату к формату, понятному SQL. С этим преобразованием вообще проблема. Если делать так, то
'1983-06-21T00:00:00+04:00'
становится 20.06.1983, то есть на 1 день меньше. Пока вышел из положения так:
(case left(type_Date,10) when '0001-01-01' then NULL else left(type_Date,10) end) as type_Date,
Понимаю, что решение временное, но как правильно сделать пока не знаю..
Если поможете разобраться с правильным преобразованием даты, буду очень Вам благодарен!
1 дек 09, 16:15    [8003578]     Ответить | Цитировать Сообщить модератору
 Re: Большой XML в SQL server 2005  [new]
Дмитрий_N
Member

Откуда: Москва
Сообщений: 155
Сообщение об ошибке малоинформативное: 404 "The page cannot be found"
Хотя страница по этому адресу точно есть, весь код выполняется в ней и во время выполнения перенаправлений нет. Алгоритм такой:
1. Выбираем файл для загрузки.
2. Подготавливаем данные.
3. Из подготовленных данных формируем XML.
4. Загружаем XML во временную таблицу скриптом при помощи SQLXMLBulkLoad.
5. Запускаем процедуру переноса данных из временной таблицы в основную с конвертацией типов данных.
Using Connection As New System.Data.SqlClient.SqlConnection
            Connection.ConnectionString = ConfigurationManager.ConnectionStrings(2).ConnectionString
            Dim queryStr As String = ("Way_Update")
            Dim command As New System.Data.SqlClient.SqlCommand(queryStr, Connection)

            Try
                Connection.Open()
                command.ExecuteNonQuery() -- Тут возникает ошибка
                .......
Вторая загрузка всегда проходит четко, ошибок нет.
2 дек 09, 09:12    [8005955]     Ответить | Цитировать Сообщить модератору
 Re: Большой XML в SQL server 2005  [new]
Дмитрий_N
Member

Откуда: Москва
Сообщений: 155
Может при вызове процедуры переноса данных временная таблица еще заблокирована? Если так, то как с этим бороться?
2 дек 09, 09:16    [8005969]     Ответить | Цитировать Сообщить модератору
 Re: Большой XML в SQL server 2005  [new]
Glory
Member

Откуда:
Сообщений: 104760
Дмитрий_N
Сообщение об ошибке малоинформативное: 404 "The page cannot be found"

Разумеется малоинформативное. Потому что это сообщенеи браузера, а не MSSQL


Дмитрий_N

1. Выбираем файл для загрузки.
2. Подготавливаем данные.
3. Из подготовленных данных формируем XML.

Что то несовесм понятно. Если файл уже есть, то зачем его еще преобразовывать в XML, который потом еще раз парсить ?


Дмитрий_N

Может при вызове процедуры переноса данных временная таблица еще заблокирована?

Ну так узнайте об этом и многом другом, что происходит на сервере при выполнении процедуры
Есть же средства мониторинга

Сообщение было отредактировано: 2 дек 09, 10:19
2 дек 09, 10:16    [8006363]     Ответить | Цитировать Сообщить модератору
 Re: Большой XML в SQL server 2005  [new]
Дмитрий_N
Member

Откуда: Москва
Сообщений: 155
Да, есть исходный файл Excel. INSERT из OPENDATASOURCE() - пока не могу получить права на такую операцию. Поэтому XML.

Ну так узнайте об этом и многом другом, что происходит на сервере при выполнении процедуры
Есть же средства мониторинга

Вы имеете ввиду SQL Profiler? Пока нет доступа.. Пробую получить..
2 дек 09, 11:03    [8006771]     Ответить | Цитировать Сообщить модератору
 Re: Большой XML в SQL server 2005  [new]
Glory
Member

Откуда:
Сообщений: 104760
Дмитрий_N
Да, есть исходный файл Excel. INSERT из OPENDATASOURCE() - пока не могу получить права на такую операцию. Поэтому XML.

А XML то зачем ??? Записать Excel как текстовый файл и загрузить его bulk insert-ом не камильфо ?
2 дек 09, 11:05    [8006783]     Ответить | Цитировать Сообщить модератору
 Re: Большой XML в SQL server 2005  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Дмитрий_N
Если делать так, то
'1983-06-21T00:00:00+04:00'
становится 20.06.1983, то есть на 1 день меньше.
Какой нафиг +04 да ещё и в excel??? Тут чистый '1983-06-21'
Откуда '0001-01-01' ???
Курим Date and Time Data Types

XML чуть надёжнее TXT - не зависит от данных. Ни тебе табуляторов, запятых ...
Но это не аргумент для частного случая. У меня просто автоматизация ...
2 дек 09, 13:33    [8008009]     Ответить | Цитировать Сообщить модератору
 Re: Большой XML в SQL server 2005  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Чуть не забыл:
Дмитрий_N
objBL.ErrorLogFile = Server.MapPath("\website3\uploadtemp\") & "error.log"
Смотрели?
2 дек 09, 13:34    [8008020]     Ответить | Цитировать Сообщить модератору
 Re: Большой XML в SQL server 2005  [new]
Дмитрий_N
Member

Откуда: Москва
Сообщений: 155
Glory
А XML то зачем ??? Записать Excel как текстовый файл и загрузить его bulk insert-ом не камильфо ?

Да, спасибо, согласен! Сделал как Вы сказали:
BULK INSERT OAK_REPORT
   FROM 'E:\website3\UploadTemp\load_way.txt
   WITH 
      (
         FIELDTERMINATOR ='^',
         ROWTERMINATOR ='\n',
		 FIRSTROW = 2,
		 FORMATFILE = 'E:\website3\UploadTemp\formatfile.Fmt'
      )
Время загрузки стало еще меньше. (61500 записей, 33 поля за 6 сек!!!) Это не может не радовать:)
И все же нужно понять, почему загрузка XML работает со второго раза..
Mnior
Какой нафиг +04 да ещё и в excel??? Тут чистый '1983-06-21'

Данные из excel были преобразованы в XML при помощи WriteXml, дата автоматически стала именно такой.
Откуда '0001-01-01' ???

Если дата не указана в исходном файле, то при SQLXMLBulkLoad она устанавливается как
'0001-01-01T00:00:00+04:00'

Error.Log смотрел, и еще утром стер.. Там тоже было что-то неинформативное.. Если завтра с утра ошибка повторится, то напишу..
Сейчас сделал так...
SET @OK=0
	WHILE @OK <>1
		BEGIN
			BEGIN TRY
					
				INSERT INTO Way
                                ....
                                SELECT
                                ....
                                FROM way_Temp
	                SET @OK=1
			END TRY
                        BEGIN CATCH
                                 WAITFOR DELAY '00:00:03'
		                 SET @OK=0
			END 
               END
Предположив, что таблица занята, подождать 3 секунды и попробовать перенести данные опять. Завтра утром смогу сообщить результаты..
2 дек 09, 15:54    [8009272]     Ответить | Цитировать Сообщить модератору
 Re: Большой XML в SQL server 2005  [new]
Glory
Member

Откуда:
Сообщений: 104760
Дмитрий_N

И все же нужно понять, почему загрузка XML работает со второго раза..

Для этого нужно сообщение об ошибке.
2 дек 09, 16:00    [8009339]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить