C# databases and xml
Discussion
Hi all,
I was hoping that the PH massive can help me out. I have written a c# program that reads data from a number of xml files and the using stored procedures puts this data in to a number of tables in a data base. The tables are linked by keys that link records. The program is to bulk import data it all works. However importing the data is slow I have a couple of million records to insert.
So I am looking for advice on making this process quicker.
I have looked round the web and come across sqlbulkcopy witch would appear to be fast at importing data in to one table as long as you are just importing a whole table at once. However my problem is that the data I am importing is coming from a number of flat xml files linked by alpernumeric fields, the keys are generated by the database as the data is inserted is there away to build datasets locally with keys in then bulk copy up each dataset to its respective table?
Failing that is there another way I do not know of to speed this process up?
Blake
I was hoping that the PH massive can help me out. I have written a c# program that reads data from a number of xml files and the using stored procedures puts this data in to a number of tables in a data base. The tables are linked by keys that link records. The program is to bulk import data it all works. However importing the data is slow I have a couple of million records to insert.
So I am looking for advice on making this process quicker.
I have looked round the web and come across sqlbulkcopy witch would appear to be fast at importing data in to one table as long as you are just importing a whole table at once. However my problem is that the data I am importing is coming from a number of flat xml files linked by alpernumeric fields, the keys are generated by the database as the data is inserted is there away to build datasets locally with keys in then bulk copy up each dataset to its respective table?
Failing that is there another way I do not know of to speed this process up?
Blake
Haven't had the need to use bulk copy, so can't really comment on that. I assume the slow bit is the reading of the XML files?
Are you handling these XML files as XMLDocuments or string files? Using the System.xml libraries will be quicker than you doing this yourself, also ADO.NET can handle xml data the same as any other data source, so using ADO to both read and write may be preferable.
Also make sure you use "SET NOCOUNT ON" at the start of each stored proc, we were amazed how much extra time it took without that.
Are you handling these XML files as XMLDocuments or string files? Using the System.xml libraries will be quicker than you doing this yourself, also ADO.NET can handle xml data the same as any other data source, so using ADO to both read and write may be preferable.
Also make sure you use "SET NOCOUNT ON" at the start of each stored proc, we were amazed how much extra time it took without that.
Hi,
Thanks for the link.
Unfortunately, my xml files are all heavily interlinked. The data is being restructured in to a new format as it is inserted in to the database. Hence I am usually searching on records in part built tables as I go along to find the key values that need to be added to the item being created.
Hence the reason I was thinking of building up data sets local and then copying the data set as a whole to the table it represents. Can create a schema of data sets that have key values that are populated in to there data sets as you add records to a set?
Blake
Thanks for the link.
Unfortunately, my xml files are all heavily interlinked. The data is being restructured in to a new format as it is inserted in to the database. Hence I am usually searching on records in part built tables as I go along to find the key values that need to be added to the item being created.
Hence the reason I was thinking of building up data sets local and then copying the data set as a whole to the table it represents. Can create a schema of data sets that have key values that are populated in to there data sets as you add records to a set?
Blake
ceebmoj said:
Hence the reason I was thinking of building up data sets local and then copying the data set as a whole to the table it represents. Can create a schema of data sets that have key values that are populated in to there data sets as you add records to a set?
Blake
Using a DataSet you can construct pretty much any data structure you want, simply by adding DataTables and setting the column properties, however I'd be surprised if this is where your bottle neck is. Blake
Well bulk load (following the advice in the link) is the way to go, but assuming you can't do that...
You can send multiple statements at a database in one go. Formulate a set of SQL statements, ";" separated, and send them in one command. You don't need to send them one at a time. This may help.
However, since it is a bulk import, presumably done just once, does it really matter if it takes a day or so to do the processing?
You can send multiple statements at a database in one go. Formulate a set of SQL statements, ";" separated, and send them in one command. You don't need to send them one at a time. This may help.
However, since it is a bulk import, presumably done just once, does it really matter if it takes a day or so to do the processing?
Hi,
Reading the xml files is fast, all xml is handles as xml documents simply changing the stored procedure calls to stub functions so that the code is performing all the data reading \ processing and it all rattles through.
So it looks to me like the delays are in firing the data in to the data base I will invest “SET NOCOUNT ON” but I am sure most if not all stored procedures have this set.
With the exception of using stored procedures witch I am accessing via ADO I am using ADO at all points.
blake
Reading the xml files is fast, all xml is handles as xml documents simply changing the stored procedure calls to stub functions so that the code is performing all the data reading \ processing and it all rattles through.
So it looks to me like the delays are in firing the data in to the data base I will invest “SET NOCOUNT ON” but I am sure most if not all stored procedures have this set.
With the exception of using stored procedures witch I am accessing via ADO I am using ADO at all points.
blake
If you have several tables that are related to each other by key constraints, and you are inserting records into those tables so they all tend to grow together, then you are gong to find the process is slow. If you insert a record into a dependent table that requires a look-up in another table, then unless that other table has already got some indexing, the look-up will need to do a table scan and will be slow. But the flipside is that if you are inserting lots of records into a table with indexing, then the indexing has to be updated after every insert and that is slow too. It is a Catch 22 situation.
You can either accept that it will be slow, or you can pre-process the data in the XML so that you can insert large blocks of data into individual database tables in one hit ... i.e. you pretty much need to normalise the data before you send it to the database. That will involve more programming, so if this is a one-off task, it may not be worth it. Having said that, normalising data is not hard so long as you have some efficient dictionary and list objects available in your development framework. Personally, I'd use Python as it has lists and dictionaries as basic in-built datatypes, plus libs for parsing XML data. I'd export data to flat files and then BULK INSERT them into SQL Server.
You can either accept that it will be slow, or you can pre-process the data in the XML so that you can insert large blocks of data into individual database tables in one hit ... i.e. you pretty much need to normalise the data before you send it to the database. That will involve more programming, so if this is a one-off task, it may not be worth it. Having said that, normalising data is not hard so long as you have some efficient dictionary and list objects available in your development framework. Personally, I'd use Python as it has lists and dictionaries as basic in-built datatypes, plus libs for parsing XML data. I'd export data to flat files and then BULK INSERT them into SQL Server.
sp60 said:
Why are you using stored procs?
Because as of a couple of weeks a go I had done nothing with any of the technologies listed and it look like the way to go at the time.ATG said:
Catch 22 situation.
thanks for the info I think I will look at preprosesing the data in to flat tables and seing how long that proces takes to do its magic. While it is a bulk import and it should only happen once or twice, It is being bulk imported to a development database who schema is changing on a daily basis so it will likely have to happen a reasonable number of times probably 2 or three times a weak at this point it takes that long just to import the data also ther is the task of keeping the import utilities in line with the data base.
Edited by ceebmoj on Tuesday 31st July 13:29
ceebmoj said:
sp60 said:
Why are you using stored procs?
Because as of a couple of weeks a go I had done nothing with any of the technologies listed and it look like the way to go at the time.If you really must to this programatically using .net, ditch the use of stored procs as they are uneccesary and won't add anything to the speed of the process. Use dataadapters to fetch the data into a well structured dataset, with proper relationships etc.. Then use the dataadapter to save back up to the database. The structure of the dataset will enable ado to automatically sort out all the keys, foreign keys etc for you.
1. Use Intermediate denormalised tables (also useful for reloading quickly without doing your XML parsing). Then try and use SQL server rather than code to bulk load your transactional DB.
2 Drop Indexes
Do loads of inserts
Rebuild indexes.
Is generally quicker than inserting into an indexed table for large number of inserts.
3. Employee me for lots of money to do it for you :-)
2 Drop Indexes
Do loads of inserts
Rebuild indexes.
Is generally quicker than inserting into an indexed table for large number of inserts.
3. Employee me for lots of money to do it for you :-)
m12_nathan said:
c# also has lists and dictionaries as built in types, even better using generic collections you can have strongly typed dictionaries. No need for python for stuff like that.
Sure there's no need, it's just better suited for knocking up quick scripts for one-off tasks.Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff



