Loading ...
Sorry, an error occurred while loading the content.

DB Performance question.

Expand Messages
  • mitusingh
    Hello, I have a question about general performance of XML-DBMS. I am currently tyring to use it to transfer approximately 10 XML documents (about 70MB each).
    Message 1 of 4 , Apr 24, 2002
    • 0 Attachment
      Hello,

      I have a question about general performance of XML-DBMS. I am
      currently tyring to use it to transfer approximately 10 XML
      documents (about 70MB each). I have already transfered 3 and the
      server is working on the fourth. However, it seems to have slowed
      down a lot. It was somewhere between 30-45 mins for the first one
      and as I insert more into the database, it seems to be getting
      slower (to the tune, that now it takes 15 mins to insert 700 records
      and on a 30,000 record file... that takes a while :) ).

      It seems like a simple no index problem. However, since I am
      just inserting (and assuming that there are no lookups), I shouldn't
      need indices and having indices would probably slow it down. Any
      ideas? Any help would be great.

      Thanks,

      Mitu
    • Ronald Bourret
      A very difficult question. I doubt it is related to indexes. I assume you are using XML-DBMS version 1.01 or 1.02? If so, here are some possibilities: 1) The
      Message 2 of 4 , Apr 26, 2002
      • 0 Attachment
        A very difficult question. I doubt it is related to indexes.

        I assume you are using XML-DBMS version 1.01 or 1.02? If so, here are
        some possibilities:

        1) The number of tables into which data is inserted exceeds the number
        of prepared statements allowed on a connection. This would cause
        XML-DBMS to discard prepared statements and reprepare them later, which
        takes additional time.

        2) The documents all insert data into different tables and the number of
        tables is large. XML-DBMS does not discard prepared statements, so
        continued use with different maps might eventually use up database
        resources.

        3) The number of prepared statements allowed on a connection is
        "infinite", and a bug in XML-DBMS prevents the reuse of prepared
        statements. (I don't know if such a bug exists. I am just guessing
        here.) XML-DBMS therefore continually creates new prepared statements
        and eventually uses up most/all database resources.

        4) There is a memory leak in your DOM implementation. With very large
        documents, this might cause all available memory to be rapidly used up.

        If you are comfortable looking at XML-DBMS code, I suggest you try to
        see what is happening with prepared statements -- whether they are being
        allocated correctly. The relevant code is in the Map object -- see the
        code that builds, prepares, and checks in and out INSERT statements.

        Two workarounds might be:

        1) Close and recompile your Map between documents. This should release
        database resources. Given the size of your documents, recompiling the
        map should be relatively inexpensive.

        2) Call Map.closeStatements() after each call to
        DOMToDBMS.storeDocument. This will close/release database resources.
        This means that SQL statements will be reprepared for each new document,
        but assuming that the documents contain large amounts of data being sent
        to the same set of tables, the cost will be relatively low.

        -- Ron


        mitusingh wrote:
        >
        > Hello,
        >
        > I have a question about general performance of XML-DBMS. I am
        > currently tyring to use it to transfer approximately 10 XML
        > documents (about 70MB each). I have already transfered 3 and the
        > server is working on the fourth. However, it seems to have slowed
        > down a lot. It was somewhere between 30-45 mins for the first one
        > and as I insert more into the database, it seems to be getting
        > slower (to the tune, that now it takes 15 mins to insert 700 records
        > and on a 30,000 record file... that takes a while :) ).
        >
        > It seems like a simple no index problem. However, since I am
        > just inserting (and assuming that there are no lookups), I shouldn't
        > need indices and having indices would probably slow it down. Any
        > ideas? Any help would be great.
      • mitusingh
        Hi, Thank you for your reply. I have some more observations. 1. Running multiple instances on different machines seems to slow the process down. I am not
        Message 3 of 4 , Apr 30, 2002
        • 0 Attachment
          Hi,

          Thank you for your reply. I have some more observations.

          1. Running multiple instances on different machines seems to slow
          the process down. I am not sure why this is the case in Oracle but
          doesn't seem to happen for MSSQL. Also, multiple instances mess up
          the primary key on some of the inserts. In one table there were 3
          sets of 2 records that shared a PK (ie. 2 records with one PK).

          2. Going thru the source code I read this on line 266 of Map.java:
          "The statements are closed only when the application calls this
          method, the Map object is deleted (such as by the garbage
          collector), or the database closes them when committing a
          transaction."

          I am running my DOMToDBMS in COMMIT_AFTERINSERT, does that mean that
          after every insert the prepared statments are being re-prepared? I
          am running more tests to see if this is the case.

          I'll post updates as soon as I get them.

          Thanks,

          Mitu

          --- In xml-dbms@y..., Ronald Bourret <rpbourret@r...> wrote:
          > A very difficult question. I doubt it is related to indexes.
          >
          > I assume you are using XML-DBMS version 1.01 or 1.02? If so, here
          are
          > some possibilities:
          >
          > 1) The number of tables into which data is inserted exceeds the
          number
          > of prepared statements allowed on a connection. This would cause
          > XML-DBMS to discard prepared statements and reprepare them later,
          which
          > takes additional time.
          >
          > 2) The documents all insert data into different tables and the
          number of
          > tables is large. XML-DBMS does not discard prepared statements, so
          > continued use with different maps might eventually use up database
          > resources.
          >
          > 3) The number of prepared statements allowed on a connection is
          > "infinite", and a bug in XML-DBMS prevents the reuse of prepared
          > statements. (I don't know if such a bug exists. I am just guessing
          > here.) XML-DBMS therefore continually creates new prepared
          statements
          > and eventually uses up most/all database resources.
          >
          > 4) There is a memory leak in your DOM implementation. With very
          large
          > documents, this might cause all available memory to be rapidly
          used up.
          >
          > If you are comfortable looking at XML-DBMS code, I suggest you try
          to
          > see what is happening with prepared statements -- whether they are
          being
          > allocated correctly. The relevant code is in the Map object -- see
          the
          > code that builds, prepares, and checks in and out INSERT
          statements.
          >
          > Two workarounds might be:
          >
          > 1) Close and recompile your Map between documents. This should
          release
          > database resources. Given the size of your documents, recompiling
          the
          > map should be relatively inexpensive.
          >
          > 2) Call Map.closeStatements() after each call to
          > DOMToDBMS.storeDocument. This will close/release database
          resources.
          > This means that SQL statements will be reprepared for each new
          document,
          > but assuming that the documents contain large amounts of data
          being sent
          > to the same set of tables, the cost will be relatively low.
          >
          > -- Ron
          >
          >
          > mitusingh wrote:
          > >
          > > Hello,
          > >
          > > I have a question about general performance of XML-DBMS. I
          am
          > > currently tyring to use it to transfer approximately 10 XML
          > > documents (about 70MB each). I have already transfered 3 and the
          > > server is working on the fourth. However, it seems to have
          slowed
          > > down a lot. It was somewhere between 30-45 mins for the first
          one
          > > and as I insert more into the database, it seems to be getting
          > > slower (to the tune, that now it takes 15 mins to insert 700
          records
          > > and on a 30,000 record file... that takes a while :) ).
          > >
          > > It seems like a simple no index problem. However, since I am
          > > just inserting (and assuming that there are no lookups), I
          shouldn't
          > > need indices and having indices would probably slow it down. Any
          > > ideas? Any help would be great.
        • Ronald Bourret
          ... Does this happen if you are running normal JDBC code? That is, not using XML-DBMS? ... I assume you are using the generated keys? If so, you should be able
          Message 4 of 4 , May 2, 2002
          • 0 Attachment
            mitusingh wrote:

            > Thank you for your reply. I have some more observations.
            >
            > 1. Running multiple instances on different machines seems to slow
            > the process down. I am not sure why this is the case in Oracle but
            > doesn't seem to happen for MSSQL.

            Does this happen if you are running normal JDBC code? That is, not using
            XML-DBMS?

            > Also, multiple instances mess up
            > the primary key on some of the inserts. In one table there were 3
            > sets of 2 records that shared a PK (ie. 2 records with one PK).

            I assume you are using the generated keys? If so, you should be able to
            fix this problem by uncommenting the line in
            KeyGeneratorImpl.initialize() that sets auto-commit to false.

            Also, can somebody who knows more about transaction theory answer the
            following question? With auto-commit set to false, KeyGeneratorImpl
            executes the following two SQL statements in a single transaction:

            begin transaction
            UPDATE XMLDBMSKey SET HighKey = HighKey + 1
            SELECT HighKey FROM XMLDBMSKey
            commit

            If two different instances of KeyGeneratorImpl execute these statements
            at the same time, I assume that the database is smart enough to queue
            the second instance until the transaction run by the first instance has
            committed. Is this correct?

            If not, I cannot see how to guarantee that two different instances don't
            get the same value of HighKey.

            > 2. Going thru the source code I read this on line 266 of Map.java:
            > "The statements are closed only when the application calls this
            > method, the Map object is deleted (such as by the garbage
            > collector), or the database closes them when committing a
            > transaction."
            >
            > I am running my DOMToDBMS in COMMIT_AFTERINSERT, does that mean that
            > after every insert the prepared statments are being re-prepared? I
            > am running more tests to see if this is the case.

            You can see if this is the case by looking at the value of
            DatabaseMetaData.supportsOpenStatementsAcrossCommit() in JDBC or
            sqlGetInfo(SQL_CURSOR_COMMIT_BEHAVIOR) in ODBC. This value is usually
            listed in the driver documentation.

            -- Ron
          Your message has been successfully submitted and would be delivered to recipients shortly.