What is best to use for best performance : XML or db??

Question

Hi

Dont know if this is the right forum so please correct me if so.
Im struggeling with a little dilemma. Is it better to use xml or db when retreiving data? The data contains a number of rules that needs to be used when reading a certain file. The rules tells how the file should be read and where the data in the file (in this case its an edifact file) eshould be stored in the db. Since the rules might change or new types of files would be added, makes it a bit difficult to hardcode all the rules directly in the code. To make it more user friendly i have made a program where its possible to make new rules or edit the existing. These rules might have to me used 100 times in a minute.
So is it better to save the rules in a xml file and the read the xml file every time an edifact file is recieved, or is it better to save the rules in a db and retreive the rules from there?
With the xml file i only have to read one time and with the db i have to read about 50 times.
What gives the best perfomance? Loading the xml file 100 times or reading 5000 records from the db in a minute??

Mahesh Chand · Answer

Sam has good points. First of all I think reading from a database or from an XML file should not create any memory problem. You can place all rules in the database in a say Rules table. You do not need to read these rules one rule at a time but read the entire table in one time in a DataSet which is nothing but XML. Now that said how frequently the rules are changed? Who is changing them? Are multiple users changing the rules simultaneously? I personally would prefer all rules to be stored in an XML file not in the database. Also depends on how many rules are and what the relationship among the rules is you can have multiple XML files to store these rules. Rules are more like configurations.

Christian · Answer

Thanks for the reply! Edifact is a little like xml but just much more complicated. Edifact can contain a number of different dataelements and each dataelement can have a different meaning according the the other dataelements in the edifact file. Edifact is an international standard for exchanging information bewteen different systems. That means the recieving needs to follow the standard to understand and save the data correctly. There are many different standards with many rules of how to read the file. If I recieve an edifact file i might have to read 50 different rules to make sure that the data is stored correctly in the db. So if i save all the rules in the db i have to read each rule every time I recieve an edifact. So if the edifact contains 50 different dataelements i have to read 50 times in the db to make sure i have all the rules. With the xml i can read one xml file containing all the rules into the memory and the start splitting up the edifact file. If the rules are stored in the db I only have to read the excact number of rules i ned. But that means a lot of reading from the db. In the end it could end up being 5000 calls to the db if 100 edifact files is recieved within a minute. So im just a little concerned about the memory usage and the performance waste of reading the same xml into the memory every time an edifact is recieved. And also will it give any kind of deadlock problems if the same xml file is trying to be loaded at the same time if two different edifact files is being recieved within the same millisecond? So: Is it best to fill up the memory with the the same xml file over and over again or to make many many sql calls to the db? I hope im not too difficult to understand so please just ask if you have any questions :)

Sam Hobbs · Answer

Why do you say  db i have to read about 50 times ? It is very likely that if it is true that you need to read the XML only once then it is true that you only need to read the database once. Whatever you intend to use to contain the data after reading XML you can use to contain the data after reading the database. Unless you intend to re-process the XML 50 times in which case you can use the XML more efficiently by processing the data into a DataSet with DataTables or into collections. A DataSet is an in-memory copy of a database but you can create a DataSet from any data then process the data in a manner equivalent to a database.