The Small Difference Between XML and RDBMS

OracleNerd posted a rather heated discussion between himself and an application developer, and few people jumped in to comment.  I can’t recommend reading this discussion, it is full of unsupported assertions and rude language. Something that was mentioned in it, did catch my attention.

MCohen wrote “So for example, it shouldn’t really matter to me that we’re using an Oracle database over say, an object database or xml files, or Amazon S3. We’re not quite there yet, but we’re making progress.”

I worked on few projects that stored data in XML files. Usually it starts rather innocently. The program needs to store and retrieve few configuration parameters, and using a database for that seems like an overkill. So they put it in an XML file.

As the project grows, more configuration parameters are added. Some of them may be depended on each other, so if you update one and fail to update the other, you need to undo your first update. Congratulations, you just reinvented transactions.

Few weeks later the project have several components and maybe even threads. Sometimes, several of those need to access the data at once. Maybe you serialize all the access to the data, completely locking the file while it is updated. Maybe you want to allow reads while an update is going on, but how will you prevent reading partially updated data? Different RDBMS has different solutions for concurrency, but Java’s XML library will let you write your own. Enjoy.

I’m not saying XML is always a bad idea. Sometimes, you need your data store to be human-readable. Sometimes, your data is just not very well structured, and would not fit in an RDBMS. But using XML has a cost, and you should make sure it is worth it. To make the right decision, you should know what is the difference between an XML file and an RDBMS.

If you don’t know whether your data is in XML or in RDBMS, how will you know whether or not you need to implement your own transactions?

Advertisements

10 Comments on “The Small Difference Between XML and RDBMS”

  1. I love :xml

    I just had a session about XML and SQL and showed an amazing demo on how powerful it is 🙂


    C:\Users\Laurent> sqlplus -L scott/tiger

    SQL*Plus: Release 11.1.0.7.0 - Production on Sun Dec 14 15:47:43 2008

    Copyright (c) 1982, 2008, Oracle. All rights reserved.

    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    SCOTT@lsc01> set timi on
    SCOTT@lsc01> select xmlelement(x,1) from dual;
    XMLELEMENT(X,1)
    ----------------------------------
    <X>1</X>

    Elapsed: 00:00:33.61

  2. prodlife says:

    I wish I could have been there. Maybe we can convince you to give a session at our user group one of this days.

    I was not talking about XML inside Oracle. There the trade offs are different. You have transactions and concurrency built in, and you wait 30 seconds for an element.

    How did you do that, btw? Somehow, mine is slightly faster…

    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    SQL> set timi on
    SQL> select xmlelement(x,1) from dual;

    XMLELEMENT(X,1)
    —————————————————————————–

    1

    Elapsed: 00:00:00.23

  3. chet says:

    Gary Myers had a similar take as you did: http://igor-db.blogspot.com/2008/12/certainty-bottleneck-and-orms.html

    That’s one angle I hadn’t even considered…but you are absolutely right. It’s actually how we got started the first time (back in January), based on this article: http://thedailywtf.com/Comments/Those-Unstable-Databases–An-Interesting-Database-Field.aspx

  4. prodlife says:

    Hi Chet,
    Thanks for the links! I commented on Gary’s blog.
    Sorry for not joining the discussion on your blog, but I promised myself to never discuss databases with someone who says “Database X is way faster than Oracle”. Which happened to be in the very first paragraph you quoted from M.

  5. To have it below 1sec, how did you do that? You tried it 50 times and showed me your best time :* ? Which would still be incredible slow… 0.2 sec to select it

    Come on, try to run this script, if possible not in PROD

    set echo on tim on timi on
    shutdown immediate
    startup
    select xmlelement(x,1) from dual;

    PS: Fortunately I know how buggy wordpress it so I took care to have <x>1</x> as output, this time !

    PS: I wish you could have been there too. I will consider your offer to speak in your area one day.

    • prodlife says:

      You are just too slow, DDL:

      SQL*Plus: Release 11.1.0.6.0 – Production on Mon Dec 15 08:58:16 2008

      Copyright (c) 1982, 2007, Oracle. All rights reserved.

      Connected to:
      Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
      With the Partitioning, OLAP, Data Mining and Real Application Testing options

      SQL> set echo on tim on timi on
      08:58:21 SQL> shutdown immediate
      Database closed.
      Database dismounted.
      ORACLE instance shut down.
      08:58:38 SQL> startup
      ORACLE instance started.

      Total System Global Area 535662592 bytes
      Fixed Size 1334380 bytes
      Variable Size 318768020 bytes
      Database Buffers 209715200 bytes
      Redo Buffers 5844992 bytes
      Database mounted.
      Database opened.
      08:59:50 SQL> select xmlelement(x,1) from dual;

      XMLELEMENT(X,1)
      —————————————————————————–

      1

      Elapsed: 00:00:00.08
      08:59:58 SQL>

  6. Sokrates says:

    Laurent, here’s what I got
    I suspect, it is probably a 11g-issue (?)

    10.2.0.2.0 > set echo on tim on timi on
    12:11:10 10.2.0.2.0 >
    12:11:18 10.2.0.2.0 > shutdown abort
    ORACLE instance shut down.
    12:11:27 10.2.0.2.0 > startup open
    ORACLE instance started.

    Total System Global Area 2147483648 bytes
    Fixed Size 2072576 bytes
    Variable Size 486539264 bytes
    Database Buffers 1644167168 bytes
    Redo Buffers 14704640 bytes
    Database mounted.
    Database opened.
    12:11:47 10.2.0.2.0 > select xmlelement(x,1) from dual;

    XMLELEMENT(X,1)
    ——————————————————————————–
    1

    Elapsed: 00:00:00.95

  7. Chris Neumueller says:

    Human-readable XML? Come on 😉

    “XML is Lisp’s bastard nephew, with uglier syntax and no semantics.” (Phil Wadler)

  8. Strange strange, well, it must be my notebook then :))

    SYS@LSC01> select * from v$version;
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
    PL/SQL Release 11.1.0.7.0 - Production
    CORE 11.1.0.7.0 Production
    TNS for 32-bit Windows: Version 11.1.0.7.0 - Production
    NLSRTL Version 11.1.0.7.0 - Production

    SYS@LSC01> set timi on
    SYS@LSC01> shutdown immediate
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SYS@LSC01> set time on
    19:20:24 SYS@LSC01> startup
    ORACLE instance started.
    Total System Global Area 368263168 bytes
    Fixed Size 1347384 bytes
    Variable Size 318767304 bytes
    Database Buffers 41943040 bytes
    Redo Buffers 6205440 bytes
    Database mounted.
    Database opened.
    19:20:46 SYS@LSC01> select xmlelement(x,1) from dual;
    XMLELEMENT(X,1)
    ----------------------------------------------------
    <X>1</X>

    Elapsed: 00:00:21.80

    compare with model :

    select * from dual model dimension by (1 x) measures (1 y) rules iterate (1e9) ();
    X Y
    ---------- ----------
    1 1

    Elapsed: 00:00:26.21

    about the same time


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s