11 Replies Latest reply: Dec 23, 2012 11:02 PM by Billy~Verreynne RSS

    BLOB column inserts are slow

    Sreekeshava S
      Hi All,

      Have a table with 7 columns where 4 columns are of Varchar2 type, 2 columns are of NUMBER type and 1 column is of type BLOB.

      Myself inserting the values to the table from JAVA program. Insertion to VARCHAR2 and NUMBER type columns are very much fast. But insertion to BLOB column is dead slow(data to BLOB column values are about 10KB).

      Please help me in this regard to insert BLOB values fastly.


      Regards/Sreekeshava S
        • 1. Re: BLOB column inserts are slow
          Bawer
          Sreekeshava S wrote:
          Myself inserting the values to the table from JAVA program. Insertion to VARCHAR2 and NUMBER type columns are very much fast. But insertion to BLOB column is dead slow(data to BLOB column values are about 10KB).
          provide more info pls.
          where do you run 'insert blob'? (in java or pl/sql)
          how do you get BLOB data? (do you create it in java or is it a parameter passed from plsql?
          post a small sample code.
          • 2. Re: BLOB column inserts are slow
            avish16
            Please check if the following might help you -
            1) You can speed it up by breaking the problem by running the process in parallel.
            2) Also you can try altering the table to nologing.
            3) Also check the query execution plans and track out areas of concern for sample rows and share the same in case insertion is too slow.
            • 3. Re: BLOB column inserts are slow
              Sreekeshava S
              Insert blob is from JAVA program. Inserting a Java object in BLOB column.
              • 4. Re: BLOB column inserts are slow
                BluShadow
                Sreekeshava S wrote:
                Insert blob is from JAVA program. Inserting a Java object in BLOB column.
                How many blobs are you inserting? Just one record or many?
                Is the Java running on the server or on a client computer across the network?

                We need more information, otherwise we can only guess.
                • 5. Re: BLOB column inserts are slow
                  Billy~Verreynne
                  avish16 wrote:
                  Please check if the following might help you -
                  1) You can speed it up by breaking the problem by running the process in parallel.
                  2) Also you can try altering the table to nologing.
                  3) Also check the query execution plans and track out areas of concern for sample rows and share the same in case insertion is too slow.
                  Not feasible or sensible given the problem description.
                  • 6. Re: BLOB column inserts are slow
                    Billy~Verreynne
                    Sreekeshava S wrote:

                    Myself inserting the values to the table from JAVA program. Insertion to VARCHAR2 and NUMBER type columns are very much fast. But insertion to BLOB column is dead slow(data to BLOB column values are about 10KB).
                    What exactly is slow? Is the database server slow? Is the network slow? You are sending a lot more data from the client to the server when inserting LOBs.

                    More data means more work. More work for the client to ship data. More work for the network to transport the data. More work for the server to process that data.

                    So which of these moving parts is the major contributor to slow performance?

                    You have identified a symptom - slow performance.

                    You now have to identify the actual problem. Where is it slow?

                    Once you have identified that, you can look at the why it is slow, and the how to fix it.
                    • 7. Re: BLOB column inserts are slow
                      Sreekeshava S
                      Running JAVA program in the same server as that of DB. And inserting 250 records/ sec(during peak load and 50 records/sec during normal load). where each record is having size of 10K(Blob column size).
                      • 8. Re: BLOB column inserts are slow
                        Sreekeshava S
                        Running JAVA program in the same server as that of DB. And inserting 250 records/ sec(during peak load and 50 records/sec during normal load). where each record is having size of 10K(Blob column size).
                        • 9. Re: BLOB column inserts are slow
                          Billy~Verreynne
                          Sreekeshava S wrote:
                          Running JAVA program in the same server as that of DB.
                          Connecting how? IPC? TCP? Dedicated server? Shared server?

                          Calling Oracle how? Doing a SQL statement prepare per insert? Reusing the SQL cursor handle? Binding variables?
                          And inserting 250 records/ sec(during peak load and 50 records/sec during normal load). where each record is having size of 10K(Blob column size).
                          And what is slow? You have NOT yet provided ANY evidence that points to the actual INSERT being slow.

                          As I have already explained, there are a number of layers from client to server - and any, or all of these, could be contributing to the problem.

                          Use your web browser and look up what instrumentation is. Apply it. Instrument your code. On the client. On the server. So you have evidence (call stats and metrics) to use to determine what and where the performance problem is. And not have to guess - and like most developers point your finger at the database in the false belief that your client code, client design, and client usage of the database, are all perfect.
                          • 10. Re: BLOB column inserts are slow
                            Sreekeshava S
                            Removed the Blob column. broken down BLOB contents and stored in to VARCHAR2
                            • 11. Re: BLOB column inserts are slow
                              Billy~Verreynne
                              And how did that identify and address the problem?

                              You've implemented a workaround. And you are still very much ignorant as to why using a LOB was slow.

                              What have you learned? Nothing.

                              Would you be able to address a similar performance problem in future, where you cannot implement a varchar2 hack/work-around. No.

                              Problem. 1. You. 0.

                              Not a good situation to be in as a programmer.