Showing posts with label dataset. Show all posts
Showing posts with label dataset. Show all posts

Monday, March 26, 2012

Optimized way of Importing more than 300000 lines of xml data into SQL

Hi,

We have a 120 MB huge relational xml file, that we parse it through xsd file and import it into SQL Server relational tables using .net Dataset class. It takes around 2 hours to validate and import this huge file into sql server.

When the import is happening most of the Server CPU and memory resources are also more than 50%.

We would like to increase the performance of this import and we are ready to redesign the application if we find a better solution.

Any ideas/suggestions sincerely appreciated.

javed.

Hi Javed,

You can use XML Bulk Load component to import your xml data directly into SQL Server.

Refer Books on line SQL Server 2005 :

SQL Server 2005 Books online ->

SQL Server Programming Reference ->

SQL XML 4.0 Programming ->

Performing Bulk Load of XML Data (SQLXML 4.0)

Refer the following links to know more about XmlBulkLoad component:

Performing Bulk Load of XML Data (SQLXML 4.0)

Using SQL Server's XML Support > XML Bulk Load

How to import XML into SQL Server with the XML Bulk Load component

Thanks

Naras.

Wednesday, March 21, 2012

Optimization Within ForEachLoop For Iteration Through A Large DataSet.

I would truly appreciate some Information/clarification for the below scenario:
I am a novice and am still learning with this new tool:

I am Iterating through a DataSet that is stored in a variable as an object.
The Data Set contains approximately 10,000 lines of data ( here in lies the problem)

I am using a ForEachLoop container to iterate through this large set of data:

In the loop are several ExecuteSQLTasks, which may or may not return a results.
There are roughly 12 tasks here.
At the end of the 12 tasks I do an Update.

I find the processing to be slow. Even after:

- placing the iteration into a separate package and calling it out of process.


- Running the iteration with only one process within it.(for testing purposes)

From what I've read on the forum, execution should be quite fast

Could someone enlighten me on how execution is called within the ForEachLoop, are there solutions I can pursue ? Or should I rethink the design?

your help is greatly appreciatedIf you could build your process using data flows instead of Exec SQL, I think your performance will go up. It sounds like you are inserting/updating each row individually, rather than treating it as a batch. SSIS performs much better when you make use of the data flow components, and handle your processing within the data flow.|||Thank You Very Much for you Prompt Reply John.

Yes I have been following your advice. Hence my Tardy Reply.

With some design changes, appropriate lookups and parallel processing
I've managed to successfully execute the package under 10 minutes.

Though I'm sure there is room for Improvement.

Once again thank you for your advice.|||

Glad to help. Do you mind marking my response as an answer? It helps others searching the forums to find useful information.

Be sure to post if you need anything else.

Optimization Within ForEachLoop For Iteration Through A Large DataSet.

I would truly appreciate some Information/clarification for the below scenario:
I am a novice and am still learning with this new tool:

I am Iterating through a DataSet that is stored in a variable as an object.
The Data Set contains approximately 10,000 lines of data ( here in lies the problem)

I am using a ForEachLoop container to iterate through this large set of data:

In the loop are several ExecuteSQLTasks, which may or may not return a results.
There are roughly 12 tasks here.
At the end of the 12 tasks I do an Update.

I find the processing to be slow. Even after:

- placing the iteration into a separate package and calling it out of process.


- Running the iteration with only one process within it.(for testing purposes)

From what I've read on the forum, execution should be quite fast

Could someone enlighten me on how execution is called within the ForEachLoop, are there solutions I can pursue ? Or should I rethink the design?

your help is greatly appreciatedIf you could build your process using data flows instead of Exec SQL, I think your performance will go up. It sounds like you are inserting/updating each row individually, rather than treating it as a batch. SSIS performs much better when you make use of the data flow components, and handle your processing within the data flow.|||Thank You Very Much for you Prompt Reply John.

Yes I have been following your advice. Hence my Tardy Reply.

With some design changes, appropriate lookups and parallel processing
I've managed to successfully execute the package under 10 minutes.

Though I'm sure there is room for Improvement.

Once again thank you for your advice.|||

Glad to help. Do you mind marking my response as an answer? It helps others searching the forums to find useful information.

Be sure to post if you need anything else.