MySQL vs XML performance

[Alex]

Working on a new PHP project that will (hopefully) have heavy storage requirements (now im not talking clusters and hadoop and all of that but just like heavy for 1 server...)

I was confident that MySQL was the perfect choice until I heard that regardless of the amount of data in question, XML is always faster than MySQL...

I had the impression that initially it was true but as the amount of information grew, MySQL qould gradually take the crown. The way I see it, as far as XML goes you have to load the entire file into memory and parse the entire thing it, which takes time. Maybe I'm not too sure about how extracting data from either of these two works (ive always blindly used MySQL with no problems) but in this case I want it to be as efficient as possible.

THanks,

Alex

 

[Crusty]

XML is not designed to be use a relational datastore. XML was first introduced in order to provide a standard way of transmitting data from system to system w/o giving access to the originating systems.

That being said, if your dataset is going to be small and there aren't many relations I don't see why XML couldn't be used. We use it for a datastore in one of our real time applications. Application starts up, reads the XML, transfers the data to memory, and then whenever the dataset changes it's rewritten to the file in a background thread to not block the program. The dataset is small and after the initial user input hardly changes, it also only has 2 parent-child relations. I can say that it is very fast, and for the size and complexity of the dataset it's probably faster then a database... but I have no tests to prove it.

The advantages to it is that it's far simpler then using an sqlite or local SQL server install on each client machine.

Since you are talking about a service instead of a client application I would strongly urge you to use RDBMS, as your dataset grows and grows the XML will be increasingly slower and slower unless you always keep a fresh copy in memory and only read the XML files upon service reboot.

 

[Apathetic]

> XML is always faster than MySQL...

That's certainly not true - especially if you need to search through multiple records for matching data. A properly written SQL query with a few choice indecies will crush any app which has to load, parse, and then search the XML for which ever data you are looking for.

If you're talking about processing a single small XML file vs a single database record I guess I'd have to say I don't know.

Dave

 

[Nothinman]

I was confident that MySQL was the perfect choice until I heard that regardless of the amount of data in question, XML is always faster than MySQL...

I would add whatever source gave you that information to the "Don't listen to these guys again" list.

 

[jjones]

I don't know about speed, but I use XML in an application to cut down on overall database load. The part of the application that uses it is perfectly suited for XML use, otherwise I wouldn't have considered it, the rest of the application works with the DB.

I wouldn't hesitate to use XML over DB given appropriate conditions, which is primarily when reading infrequently updated datasets, at least from my POV.

 

[Alex]

Originally posted by: Nothinman

I was confident that MySQL was the perfect choice until I heard that regardless of the amount of data in question, XML is always faster than MySQL...

I would add whatever source gave you that information to the "Don't listen to these guys again" list.

my thoughts exactly

just wanted to confirm this beyond any doubt!

 

[Alex]

Originally posted by: jjones
I don't know about speed, but I use XML in an application to cut down on overall database load. The part of the application that uses it is perfectly suited for XML use, otherwise I wouldn't have considered it, the rest of the application works with the DB.

I wouldn't hesitate to use XML over DB given appropriate conditions, which is primarily when reading infrequently updated datasets, at least from my POV.

im thinking something similar... the bulk of the application will be somewhat database intensive, making XML inviable for the reasons stated above.
however, we're considering integrating a Flex module for interactive charts, reports and such and in that case i could generate an XML file with the relevant information and use it to feed Flex!

 

[Crusty]

Originally posted by: alex

Originally posted by: jjones
I don't know about speed, but I use XML in an application to cut down on overall database load. The part of the application that uses it is perfectly suited for XML use, otherwise I wouldn't have considered it, the rest of the application works with the DB.

I wouldn't hesitate to use XML over DB given appropriate conditions, which is primarily when reading infrequently updated datasets, at least from my POV.

im thinking something similar... the bulk of the application will be somewhat database intensive, making XML inviable for the reasons stated above.
however, we're considering integrating a Flex module for interactive charts, reports and such and in that case i could generate an XML file with the relevant information and use it to feed Flex!

Exactly

 

[PhatoseAlpha]

Are performance bonuses from XML are limited to thin client applications where a server side XML cache would be useful and a client side one would be impossible?

 

[bobross419]

I never thought about using XML for storage, and since I've been having a metric butt ton of problems getting Netbeans to work with an embedded database I figured I'd ask in this thread.

I'm working on a program to track my car's MPG. Eventually I will be tracking the following items:

Date
Gas Station
Price
Miles
Gallons
Tire Pressure (4 fields for this)
Recent Maintenance (Oil Change, Tire Change, Tune Up, etc... probably a text box as opposed to a text field)

I'm doing the programming in Java and after I complete the desktop app I will move on to making a version of this for my Blackberry so I don't have to carry around a huge stack of gas receipts with miles written on them (I have close to 60 sitting in my console right now and will be entered into the 'database' once I have it up and running).

I'm not expecting to need more than 2 or 3 tables at the most, so would XML be a valid file storage medium for this application or should I continue to bang my head against my keyboard getting the database to work?

It has been mentioned a few times that XML will work fine, but for large amounts of data a true DB would be more efficient/appropriate. I might be a bit naive about data sizes, so is what I'm looking at too much for XML to handle?

Thanks, and I hope I'm not derailing the topic - I figured it might help others to have an example of the potential use of XML as a storage medium.

 

[Snapster]

Originally posted by: Nothinman

I was confident that MySQL was the perfect choice until I heard that regardless of the amount of data in question, XML is always faster than MySQL...

I would add whatever source gave you that information to the "Don't listen to these guys again" list.

^^

 

[spamsk8r]

Also, if you use SAX to parse the XML you don't have to load the entire thing into memory, if you know what data you want. With DOM parsers you do, however, but they allow you to access any part of the file you want much more quickly as a result.

 

[NiKeFiDO]

Correct me if I'm mistaken, but isn't XML slower as it involves reading files off a hard drive, vs reading data in a sql server? (Although I suppose that ends in seek time on a hard drive as well... I think...?)

In my own opinion, I would use XML only for items that you want users to edit "easily" -

for instance, things like what mysql to connect to, or types of config (of course, these can be set with .ini style files or just an included PHP file!)

Cool use of mySQL with xml in an extensive system is Magento's e-commerce solution.

For most persistent data, mySQL is my recommendation.