Skip to main content
P
porkslap
Inspiring
June 1, 2018
Question

oracle insert delay

  • June 1, 2018
  • 2 replies
  • 540 views

We are in the process of investigating an Oracle insert delay.  The page in question does two inserts into two separate tables.  The first insert completes immediately but the second query can take up to 3 minutes to get written to the db.

Does CF receive any acknowledgement from oracle that an insert command was received or completed from Oracle?  It seems obvious that CF will continue to process code after the second insert which means that CF doesn't wait for the insert to be completed.

Is it possible that CF is holding  the second insert command in a buffer due to server load or some other circumstances?

    This topic has been closed for replies.

    2 replies

    D
    Community Expert
    Dave WattsCommunity Expert
    Community Expert
    June 4, 2018

    CF doesn't continue after a CFQUERY until it gets a response from the database. However, it's possible that the database is not actually completing the insert before it responds to CF - or at least, that other clients aren't able to view the inserted data yet. This kind of thing is pretty unlikely, but it is possible. Remember that your database is designed to be accessed concurrently by multiple users, and has mechanisms to make this possible without showing concurrency issues to users. I suspect that's what's happening here, simple as that. You can always try running the same query from SQL*Plus and see what happens there.

    Dave Watts, Fig Leaf Software

    Dave Watts, Eidolon LLC
    WolfShade
    WolfShade
    Legend
    June 1, 2018

    There are so many variables that can come into play that might lead to the issue you are experiencing.  Unless I am missing something, CF shouldn't continue processing anything until after both queries have completed (unless you are placing them in CFTHREAD, in which case they would process independently and then CF would continue regardless of their status.)

    How much data is being inserted in the second query?  Are you including any binary files as part of that insert?  Have you tested for network bottlenecks?  How many webservers are working with that database?  How many users?  Is this in production, or staging/development?  What is the architecture?

    I'm sure others here can come up with many more questions.

    V/r,

    ^ _ ^

    P
    porkslapAuthor
    Inspiring
    June 1, 2018

    Hi WolfShade,

    no cfthread involved.  I assumed that CF handled any query the same way regadless of whether it was a select, insert, etc.  There is not alot of data in either query (purchase info, name address, items ordered etc, no files, binary data).

    We are using separate CF (windows)  and Oracle servers (unix).  We have many CF apps going against the same oracle server without issue.  It is only this one app and seems to be only with inserts and updates to the order_detail table - which by the way has over 2 million records.  The db folks have "indexed" the table.

    The issue is in production.  The database folks cloned the prod db to dev and we cannot replicate it in dev.  The issue began right after the clone.

    WolfShade
    WolfShade
    Legend
    June 1, 2018

    My first reaction is to think that the index in production isn't exactly the same as the index in dev.  Have you tried re-indexing the production db?

    V/r,

    ^ _ ^

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices