Skip to main content
A
Anonymous
August 4, 2011
Question

slowness

  • August 4, 2011
  • 1 reply
  • 1323 views

After many changes to many ColdFusion programs, the system is having intermittant slowness. The changes include new cfms, many new queries, and a new table.

I'm putting tickcount()s for some of the code into a table and found very slow insert and update queries into a table with over 10 million records, many indexes, and 35 fields. It has thousands of inserts and updates each day. Those insert and update queries have not been changed in years. In logs, it looks like the slowness sometimes correponds to the running of a report that's run against that table, when the report requests many rows.

1. Could the size of the table and the activity be the problem ?

    If so, wouldn't the slowness start gradually instead of suddenly ?

2. In select FIELDS from DEMOGRAPHIC left outer join LARGETABLE on JOINCONDITION where WHERECONDITION, is the right place for a SQL nolock directive after the "on" ?

Any suggestions would be really appreciated.

    This topic has been closed for replies.

    1 reply

    Owainnorth
    Owainnorth
    Inspiring
    August 4, 2011

    Could the size of the table and the activity be the problem ?

        If so, wouldn't the slowness start gradually instead of suddenly ?

    Well that depends - is it growing in size gradually? The only real reason something would "suddenly" slow down with no changes is because something has run out of RAM, and is either paging to disk or just running out of heap space.

    If queries are taking an age to run, test them outside of CF. Then tune them accordingly.

    A
    Anonymous
    August 4, 2011

    The size grows gradually.

    Those queries run fine thousands of times a day and have for years but recently started going super slowly a few times a day. From a log we keep, it looks like this happens especially when several records are trying to be inserted at the same second. There are about 500 people using the system.

    Still have not put tickcount()s around all the code so there might be others.

    Could sql nolock to help alleviate this while the real problem is being looked for ?

    A
    Adam Cameron.
    Inspiring
    August 5, 2011

    Have you identified whether this is a CF problem or a DB server problem?

    It sounds to me like it's a DB server problem, so approaching it from a CF perspective is perhaps not the best approach.

    --

    Adam

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