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

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

Задача: Перегнать XML файлы в БД MS SQL Server 2012. Делаю я это используя SQLXMLBULKLOADLib (SQLXML 4.0) в .Net.
Имеется XML файл (11Мб) и XSD-схема к нему с правилами маппинга, который кладется в три таблицы (Table1 - 1 запись, Table2 - 3'000 записи, Table3 - 4'500 записи). Связаны они между собой внешними ключами, которые прописаны в XSD-схеме.

Проблема: Время перегона XML в SQL составляет больше одного часа. (Хотя пробовал перегонять 10'000 записей из одной XML (45Мб) в одну таблицу, то занимает 20 секунд).

Предполагаемая проблема: Идет проверка на внешние ключи, поэтому пока все данные не проверяться, инсерт не произойдет.

Пробовал:
  • Отключить проверку внешних ключей: CheckConstraints = false
  • Отключить проверку при вставке в SQL : ALTER TABLE TableName NOCHECK CONSTRAINT FK_Name
    Вышеперечисленные методы не помогли.

    Прошу помощи.
  • 22 ноя 12, 16:41    [13514453]     Ответить | Цитировать Сообщить модератору
     Re: SQL XML Bulk Insert выполняется очень долго. Пути оптимизации.  [new]
    Maxx
    Member [скрыт]

    Откуда:
    Сообщений: 24290
    а все потому,что реальные таблицы -должны отличаться от "импорт" схемы..не надо путать ETL и OLTP
    22 ноя 12, 16:51    [13514546]     Ответить | Цитировать Сообщить модератору
     Re: SQL XML Bulk Insert выполняется очень долго. Пути оптимизации.  [new]
    Kairat V. Beysenov
    Member

    Откуда: Astana City
    Сообщений: 21
    Maxx, а можно подробнее что именно необходимо сделать в моем случае?
    Я так понял что нужно XML положить в SQL, а потом уже используя ETL сформировать нужные нам таблицы?

    Но как быстро положить XML в SQL?
    22 ноя 12, 17:26    [13514854]     Ответить | Цитировать Сообщить модератору
     Re: SQL XML Bulk Insert выполняется очень долго. Пути оптимизации.  [new]
    Maxx
    Member [скрыт]

    Откуда:
    Сообщений: 24290
    ну моя видит следуещее
    1. Создатет таблицы без фк куда будет заходить булк лоад (импорт схема) ,убераете все привязки таблиц к дрцг другу в xsd
    2. Рисуете процесс обработки и валидации пришедших данных
    3. Переливаете в реальные таблицы то,что прошло пукнт1 и пункт 2 ,что непрошло пункт 2 - разбераетесь или на лету или по аудиту
    22 ноя 12, 17:30    [13514890]     Ответить | Цитировать Сообщить модератору
     Re: SQL XML Bulk Insert выполняется очень долго. Пути оптимизации.  [new]
    Kairat V. Beysenov
    Member

    Откуда: Astana City
    Сообщений: 21
    Пробовал без связей заливать в sql: убирал FK между таблицами, убирал аннотации relationship из XSD. Но SQLXMLBULK выдает ошибку о том что ожидается связь relationship.

    +
    <?xml version="1.0" encoding="utf-16"?>
    <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema" elementFormDefault="qualified" attributeFormDefault="unqualified" version="1.0">
    	<xsd:annotation>
    		<xsd:appinfo>
    			<sql:relationship name="FK_GbdrnObjectInfoItem5_Response5"  parent="dbo.Response5" parent-key="Id" child="dbo.GbdrnObjectInfoItem5" child-key="Response5"/>
    			<sql:relationship name="FK_ImmovablesRight5_GbdrnObjectInfoItem5" parent="dbo.GbdrnObjectInfoItem5" parent-key="Id" child="dbo.ImmovablesRight5" child-key="GbdrnObjectInfoItem5"/>
    		</xsd:appinfo>
    	</xsd:annotation>
    	<xsd:element name="P305ResponseData" sql:relation="dbo.Response5">
    		<xsd:complexType>
    			<xsd:sequence>
    				<xsd:element name="ResponseData" sql:is-constant="1">
    					<xsd:complexType>
    						<xsd:sequence>
    							<xsd:element name="RegistredRightsPersonRequest" sql:is-constant="1">
    								<xsd:complexType>
    									<xsd:sequence>
    										<xsd:element name="DeclarantName" sql:is-constant="1">
    											<xsd:complexType>
    												<xsd:sequence>
    													<xsd:element name="LastName" type="xsd:string" sql:field="DeclarantNameLastName"/>
    													<xsd:element name="FirstName" type="xsd:string" sql:field="DeclarantNameFirstName"/>
    													<xsd:element name="MiddleName" type="xsd:string" sql:field="DeclarantNameMiddleName"/>
    													<xsd:element name="BornDt" type="xsd:dateTime" sql:field="DeclarantNameBornDt"/>
    												</xsd:sequence>
    											</xsd:complexType>
    										</xsd:element>
    										<xsd:element name="DeclarantIIN" type="xsd:decimal" sql:field="DeclarantIIN"/>
    										<xsd:element name="DeclarantIdentityDoc" sql:is-constant="1">
    											<xsd:complexType>
    												<xsd:sequence>
    													<xsd:element name="TypeId" type="xsd:string" sql:field="DeclarantIdentityDocTypeId"/>
    													<xsd:element name="Name" type="xsd:string" sql:field="DeclarantIdentityDocName"/>
    													<xsd:element name="Series" type="xsd:int" sql:field="DeclarantIdentityDocSeries"/>
    													<xsd:element name="Number" type="xsd:string" sql:field="DeclarantIdentityDocNumber"/>
    													<xsd:element name="IssueDate" type="xsd:dateTime" sql:field="DeclarantIdentityDocIssueDate"/>
    													<xsd:element name="IssueSource" type="xsd:string" sql:field="DeclarantIdentityDocIssueSource"/>
    													<xsd:element name="SeriesExtra" sql:mapped="false">
    														<xsd:complexType>
    															<xsd:attribute name="nil" type="xsd:boolean"/>
    														</xsd:complexType>
    													</xsd:element>
    													<xsd:element name="NumberExtra" sql:mapped="false">
    														<xsd:complexType>
    															<xsd:attribute name="nil" type="xsd:boolean"/>
    														</xsd:complexType>
    													</xsd:element>
    													<xsd:element name="NumberExtraSeries" sql:mapped="false">
    														<xsd:complexType>
    															<xsd:attribute name="nil" type="xsd:boolean"/>
    														</xsd:complexType>
    													</xsd:element>
    												</xsd:sequence>
    											</xsd:complexType>
    										</xsd:element>
    										<xsd:element name="ObjectType" type="xsd:int" sql:field="ObjectType"/>
    										<xsd:element name="ObjectLocation" sql:is-constant="1">
    											<xsd:complexType>
    												<xsd:sequence>
    													<xsd:element name="corpus" type="xsd:string" sql:field="ObjectLocationCorpus"/>
    												</xsd:sequence>
    											</xsd:complexType>
    										</xsd:element>
    										<xsd:element name="OwnerShort" sql:mapped="false">
    											<xsd:complexType>
    												<xsd:attribute name="nil" type="xsd:boolean"/>
    											</xsd:complexType>
    										</xsd:element>
    										<xsd:element name="CadastralNumber" type="xsd:string" sql:field="CadastralNumber"/>
    										<xsd:element name="Language" type="xsd:string" sql:field="Language"/>
    									</xsd:sequence>
    								</xsd:complexType>
    							</xsd:element>
    							<xsd:element name="RegisteredRightsEvidencePerson" sql:is-constant="1">
    								<xsd:complexType>
    									<xsd:sequence>
    										<xsd:element name="StatementFeedPlace" type="xsd:string" sql:field="StatementFeedPlace"/>
    										<xsd:element name="StatementNumber" type="xsd:decimal" sql:field="StatementNumber"/>
    										<xsd:element name="StatementDate" type="xsd:dateTime" sql:field="StatementDate"/>
    										<xsd:element name="Declarant" sql:is-constant="1">
    											<xsd:complexType>
    												<xsd:sequence>
    													<xsd:element name="LastName" type="xsd:string" sql:field="DeclarantLastName"/>
    													<xsd:element name="FirstName" type="xsd:string" sql:field="DeclarantFirstName"/>
    													<xsd:element name="MiddleName" type="xsd:string" sql:field="DeclarantMiddleName"/>
    													<xsd:element name="BornDt" type="xsd:dateTime" sql:field="DeclarantBornDt"/>
    												</xsd:sequence>
    											</xsd:complexType>
    										</xsd:element>
    										<xsd:element name="DeclarantIIN" type="xsd:decimal" sql:mapped="false"/>
    										<xsd:element name="DeclarantBirthDate" type="xsd:dateTime" sql:field="DeclarantBirthDate"/>
    										<xsd:element name="IdentityDoc" sql:is-constant="1">
    											<xsd:complexType>
    												<xsd:sequence>
    													<xsd:element name="TypeId" type="xsd:string" sql:field="IdentityDocTypeId"/>
    													<xsd:element name="Name" type="xsd:string" sql:field="IdentityDocName"/>
    													<xsd:element name="Series" type="xsd:int" sql:field="IdentityDocSeries"/>
    													<xsd:element name="Number" type="xsd:string" sql:field="IdentityDocNumber"/>
    													<xsd:element name="IssueDate" type="xsd:dateTime" sql:field="IdentityDocIssueDate"/>
    													<xsd:element name="IssueSource" type="xsd:string" sql:field="IdentityDocIssueSource"/>
    													<xsd:element name="SeriesExtra" sql:mapped="false">
    														<xsd:complexType>
    															<xsd:attribute name="nil" type="xsd:boolean"/>
    														</xsd:complexType>
    													</xsd:element>
    													<xsd:element name="NumberExtra" sql:mapped="false">
    														<xsd:complexType>
    															<xsd:attribute name="nil" type="xsd:boolean"/>
    														</xsd:complexType>
    													</xsd:element>
    													<xsd:element name="NumberExtraSeries" sql:mapped="false">
    														<xsd:complexType>
    															<xsd:attribute name="nil" type="xsd:boolean"/>
    														</xsd:complexType>
    													</xsd:element>
    												</xsd:sequence>
    											</xsd:complexType>
    										</xsd:element>
    										<xsd:element name="EmptyLineForBurdens" type="xsd:string" sql:field="EmptyLineForBurdens"/>
    										<xsd:element name="EmptyLineForUPSes" type="xsd:string" sql:field="EmptyLineForUPSes"/>
    										<xsd:element name="ObjectInfos" sql:is-constant="1">
    											<xsd:complexType>
    												<xsd:sequence>
    													<xsd:element name="GbdrnObjectInfoItem" maxOccurs="unbounded" sql:relation="dbo.GbdrnObjectInfoItem5" sql:relationship="FK_GbdrnObjectInfoItem5_Response5">
    														<xsd:complexType>
    															<xsd:sequence>
    																<xsd:element name="ObjectTypeId" type="xsd:string" sql:field="GbdrnObjectInfoItemObjectTypeId"/>
    																<xsd:element name="KadastrNumber" type="xsd:string" sql:field="GbdrnObjectInfoItemKadastrNumber"/>
    																<xsd:element name="ObjectLocation" type="xsd:string" sql:field="GbdrnObjectInfoItemObjectLocation"/>
    																<xsd:element name="ObjectEndUse" type="xsd:string" sql:field="GbdrnObjectInfoItemObjectEndUse"/>
    																<xsd:element name="Storeys" type="xsd:string" sql:field="GbdrnObjectInfoItemStoreys"/>
    																<xsd:element name="SquareTotal" type="xsd:string" sql:field="GbdrnObjectInfoItemSquareTotal"/>
    																<xsd:element name="Divisibility" type="xsd:string" sql:field="GbdrnObjectInfoItemDivisibility"/>
    																<xsd:element name="SpecialNotes" type="xsd:string" sql:field="GbdrnObjectInfoItemSpecialNotes"/>
    																<xsd:element name="Rights" sql:is-constant="1">
    																	<xsd:complexType>
    																		<xsd:sequence>
    																			<xsd:element name="ImmovablesRight" maxOccurs="unbounded" sql:relation="dbo.ImmovablesRight5" sql:relationship="FK_ImmovablesRight5_GbdrnObjectInfoItem5">
    																				<xsd:complexType>
    																					<xsd:sequence>
    																						<xsd:element name="RightType" type="xsd:string" sql:field="ImmovablesRightRightType"/>
    																						<xsd:element name="RightSubstance" type="xsd:string" sql:field="ImmovablesRightRightSubstance"/>
    																						<xsd:element name="RightOwner" type="xsd:string" sql:field="ImmovablesRightRightOwner"/>
    																						<xsd:element name="OwnershipType" type="xsd:string" sql:field="ImmovablesRightOwnershipType"/>
    																						<xsd:element name="RightFoundation" sql:is-constant="1">
    																							<xsd:complexType>
    																								<xsd:sequence>
    																									<xsd:element name="DocList" sql:is-constant="1">
    																										<xsd:complexType>
    																											<xsd:sequence>
    																												<xsd:element name="DocTip" type="xsd:string" sql:field="DocListDocTip"/>
    																												<xsd:element name="DocNo" type="xsd:string" sql:field="DocListDocNo"/>
    																												<xsd:element name="DocDt" type="xsd:string" sql:field="DocListDocDt"/>
    																											</xsd:sequence>
    																										</xsd:complexType>
    																									</xsd:element>
    																								</xsd:sequence>
    																							</xsd:complexType>
    																						</xsd:element>
    																						<xsd:element name="RegistrationDate" type="xsd:string" sql:field="RegistrationDate"/>
    																					</xsd:sequence>
    																				</xsd:complexType>
    																			</xsd:element>
    																		</xsd:sequence>
    																	</xsd:complexType>
    																</xsd:element>
    																<xsd:element name="ChargeList" type="xsd:string" sql:field="ChargeList"/>
    																<xsd:element name="ClaimsAndDeals" type="xsd:string" sql:field="ClaimsAndDeals"/>
    															</xsd:sequence>
    														</xsd:complexType>
    													</xsd:element>
    												</xsd:sequence>
    											</xsd:complexType>
    										</xsd:element>
    										<xsd:element name="MessageProcessingResult" type="xsd:int" sql:field="MessageProcessingResult"/>
    									</xsd:sequence>
    								</xsd:complexType>
    							</xsd:element>
    						</xsd:sequence>
    					</xsd:complexType>
    				</xsd:element>
    				<xsd:element name="ResponseInfo" sql:is-constant="1">
    					<xsd:complexType>
    						<xsd:sequence>
    							<xsd:element name="requestNumber" type="xsd:decimal" sql:field="requestNumber"/>
    							<xsd:element name="messageId" type="xsd:decimal" sql:field="messageId"/>
    							<xsd:element name="chainId" type="xsd:decimal" sql:field="chainId"/>
    							<xsd:element name="messageDate" type="xsd:string" sql:field="messageDate"/>
    							<xsd:element name="digiSign" type="xsd:string" sql:field="digiSign"/>
    							<xsd:element name="status" sql:is-constant="1">
    								<xsd:complexType>
    									<xsd:sequence>
    										<xsd:element name="Code" type="xsd:int" sql:field="statusCode"/>
    										<xsd:element name="Message" type="xsd:string" sql:field="statusMessage"/>
    									</xsd:sequence>
    								</xsd:complexType>
    							</xsd:element>
    						</xsd:sequence>
    					</xsd:complexType>
    				</xsd:element>
    			</xsd:sequence>
    			<xsd:attribute name="type" type="xsd:string" sql:mapped="false"/>
    		</xsd:complexType>
    	</xsd:element>
    </xsd:schema>
    
    22 ноя 12, 18:06    [13515223]     Ответить | Цитировать Сообщить модератору
     Re: SQL XML Bulk Insert выполняется очень долго. Пути оптимизации.  [new]
    Maxx
    Member [скрыт]

    Откуда:
    Сообщений: 24290
    Maxx
    убераете все привязки таблиц к дрцг другу в xsd


    а ето ж осталось у вас
    22 ноя 12, 18:17    [13515274]     Ответить | Цитировать Сообщить модератору
     Re: SQL XML Bulk Insert выполняется очень долго. Пути оптимизации.  [new]
    Kairat V. Beysenov
    Member

    Откуда: Astana City
    Сообщений: 21
    Пишу просто с айпада, под рукой была только исходная схема.
    Из неё я убрал все вхождения relationship-ов.
    22 ноя 12, 18:32    [13515357]     Ответить | Цитировать Сообщить модератору
     Re: SQL XML Bulk Insert выполняется очень долго. Пути оптимизации.  [new]
    aman-yo
    Member

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

    Такая же проблема, возникнула.
    Пока для примера пробую на простой xml-ке:
    +
    <?xml version="1.0" encoding="utf-16"?>
    <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema" elementFormDefault="qualified" attributeFormDefault="unqualified" version="1.0">
    <!--<xsd:annotation>
    <xsd:appinfo>
    <sql:relationship name="FK_Department_Company" parent="dbo.Company" parent-key="Id" child="dbo.Department" child-key="CompanyId"/>
    </xsd:appinfo>
    </xsd:annotation>-->
    <xsd:element name="Records" sql:is-constant="1">
    <xsd:complexType>
    <xsd:sequence>
    <xsd:element name="xsdType" type="xsd:string" sql:mapped="false"/>
    <xsd:element name="Company" maxOccurs="unbounded" sql:relation="dbo.Company3">
    <xsd:complexType>
    <xsd:sequence>
    <xsd:element name="Name" type="xsd:string" sql:field="Name"/>
    <xsd:element name="Department" sql:relation="dbo.Department3">
    <!--</xsd:element> maxOccurs="unbounded" sql:relation="dbo.Department3" sql:relationship="FK_Department_Company">-->
    <xsd:complexType>
    <xsd:sequence>
    <xsd:element name="Name" type="xsd:string" sql:field="Name"/>
    <xsd:element name="Count" type="xsd:int" sql:field="Count"/>
    </xsd:sequence>
    </xsd:complexType>
    </xsd:element>
    </xsd:sequence>
    </xsd:complexType>
    </xsd:element>
    </xsd:sequence>
    </xsd:complexType>
    </xsd:element>
    </xsd:schema>

    Попробовал убрать relationship-ы, но балк лоад ругается relationship expected on 'Department'.
    23 ноя 12, 08:06    [13516791]     Ответить | Цитировать Сообщить модератору
     Re: SQL XML Bulk Insert выполняется очень долго. Пути оптимизации.  [new]
    alexeyvg
    Member

    Откуда: Moscow
    Сообщений: 31438
    aman-yo
    Попробовал убрать relationship-ы, но балк лоад ругается relationship expected on 'Department'.
    Вы выложите вариант xsd, на котором ругается...
    23 ноя 12, 09:04    [13516935]     Ответить | Цитировать Сообщить модератору
     Re: SQL XML Bulk Insert выполняется очень долго. Пути оптимизации.  [new]
    aman-yo
    Member

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

    Здеся ругается

    К сообщению приложен файл (Schema.xsd - 4Kb) cкачать
    23 ноя 12, 10:01    [13517244]     Ответить | Цитировать Сообщить модератору
     Re: SQL XML Bulk Insert выполняется очень долго. Пути оптимизации.  [new]
    Kairat V. Beysenov
    Member

    Откуда: Astana City
    Сообщений: 21
    Не могу положить xml, не учитывая внешние ключи.
    Вот пример данных Data.xml:
    +
    <Records>
    	<xsdType>Schema.xsd</xsdType>
    	<Company>
    		<ID>1</ID>
    		<Name>Prism Works</Name>
    		<Department>
    			<ID>1</ID>
    			<Name>Sales</Name>
    			<Company>1</Company>
    		</Department>
    		<Department>
    			<ID>2</ID>
    			<Name>Transportation</Name>
    			<Company>1</Company>
    		</Department>
    	</Company>
    </Records>
    

    Вот к ней схема Schema.xsd:
    +
    <?xml version="1.0" encoding="utf-16"?>
    <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema" elementFormDefault="qualified" attributeFormDefault="unqualified" version="1.0">
    	<xsd:element name="Records" sql:is-constant="1">
    		<xsd:complexType>
    			<xsd:sequence>
    				<xsd:element name="xsdType" type="xsd:string" sql:mapped="false"/>
    				<xsd:element name="Company" sql:relation="dbo.Company">
    					<xsd:complexType>
    						<xsd:sequence>
    							<xsd:element name="ID" type="xsd:int" sql:field="ID"/>
    							<xsd:element name="Name" type="xsd:string" sql:field="Name"/>
    							<xsd:element name="Department" maxOccurs="unbounded" sql:relation="dbo.Department">
    								<xsd:complexType>
    									<xsd:sequence>
    										<xsd:element name="ID" type="xsd:int" sql:field="ID"/>
    										<xsd:element name="Name" type="xsd:string" sql:field="Name"/>
    										<xsd:element name="Company" type="xsd:int" sql:field="Company"/>
    									</xsd:sequence>
    								</xsd:complexType>
    							</xsd:element>
    						</xsd:sequence>
    					</xsd:complexType>
    				</xsd:element>
    			</xsd:sequence>
    		</xsd:complexType>
    	</xsd:element>
    </xsd:schema>
    

    Вот схема таблиц в БД:
    +
    CREATE TABLE dbo.Company (
    	 ID		Int NOT NULL,
    	 Name		VarChar(50)	NOT NULL
    )
    CREATE TABLE dbo.Department (
    	 ID		Int NOT NULL,
    	 Name		VarChar(50) NOT NULL,
    	 Company	Int NOT NULL
    )
    

    При запуске такого кода:
    _bulkLoad = new SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class {
    				ConnectionString = connectionString,
    				KeepIdentity = true,
    				CheckConstraints = false,
    				SchemaGen = false,
    				ErrorLogFile = "Logger.log"
    			};
    _bulkLoad.Execute("Schema.xsd", "Data.xml");
    

    Выдается ошибка:
    Schema: relationship expected on 'Department'.
    23 ноя 12, 10:53    [13517588]     Ответить | Цитировать Сообщить модератору
     Re: SQL XML Bulk Insert выполняется очень долго. Пути оптимизации.  [new]
    alexeyvg
    Member

    Откуда: Moscow
    Сообщений: 31438
    Kairat V. Beysenov
    Выдается ошибка:
    Schema: relationship expected on 'Department'.
    А в таблицах то остались FK?
    Maxx
    ну моя видит следуещее
    1. Создаёте таблицы без фк куда будет заходить булк лоад (импорт схема) ,убераете все привязки таблиц к дрцг другу в xsd
    2. Рисуете процесс обработки и валидации пришедших данных
    3. Переливаете в реальные таблицы то,что прошло пукнт1 и пункт 2 ,что непрошло пункт 2 - разбераетесь или на лету или по аудиту
    23 ноя 12, 11:42    [13517985]     Ответить | Цитировать Сообщить модератору
     Re: SQL XML Bulk Insert выполняется очень долго. Пути оптимизации.  [new]
    Kairat V. Beysenov
    Member

    Откуда: Astana City
    Сообщений: 21
    alexeyvg,

    Как я привел выше, схема БД без внешних ключей:
    CREATE TABLE dbo.Company (
    	 ID		Int NOT NULL,
    	 Name		VarChar(50)	NOT NULL
    )
    CREATE TABLE dbo.Department (
    	 ID		Int NOT NULL,
    	 Name		VarChar(50) NOT NULL,
    	 Company	Int NOT NULL
    )
    
    23 ноя 12, 12:59    [13518702]     Ответить | Цитировать Сообщить модератору
     Re: SQL XML Bulk Insert выполняется очень долго. Пути оптимизации.  [new]
    skorpk
    Member

    Откуда: Волгоград
    Сообщений: 273
    Чем не подходит вариант загрузки через SSIS?
    25 ноя 12, 18:53    [13525893]     Ответить | Цитировать Сообщить модератору
    Все форумы / Microsoft SQL Server Ответить