4 Replies Latest reply on Sep 13, 2006 10:35 AM by EmmanuelRuiz

    query times out

    EmmanuelRuiz
      The following statement in my query (see enclosed) is timing out. We are using Oracle DB and the field oth_tchname_ssn contains data such as 123456789:123456789:123456789:123456789:123456789:123456789

      or b.oth_tchname_ssn like <cfqueryparam value="%#ReReplaceNoCase(socNum, '[^0-9]', '', 'All')#%" cfsqltype="CF_SQL_VARCHAR">

      Any ideas?
        • 1. Re: query times out
          ksmith Level 1
          What version of Oracle, ColdFusion and database drivers are you using. If you hard code the cfqueryparam values does it run? What is your timeout set to? Does the same hardcoded query run in sqlplus or another query tool? How long does it take to run in the query tool? A little more information is needed to help you.
          • 2. Re: query times out
            EmmanuelRuiz Level 1
            We are using Oracle Database 10g Release 10.2.0.2.0 ,CFMX 6.1 updater, and JDBC 3.5 dirvers. I hard-coded the queryparam like this:

            or oth_tchname_ssn like '%#ReReplaceNoCase(socNum, '[^0-9]', '', 'All')#%'

            and it runs but takes like 185 seconds and returns 118,000 records when the table remediation only has 69,000 records and table MATHPREP_0506 has 19,000 records.

            • 3. Re: query times out
              ksmith Level 1
              So how long does this run in sqlplus or another query tool? I would expect that it takes a long time there also. You are using like and IN on 7 columns. This must cause a lot of tablescanning. You can see if indexing those columns improves performance - but 7 indices may not make sense either.

              You can try it as a stored procedure also.

              • 4. Re: query times out
                EmmanuelRuiz Level 1
                The problem was that the statement was missing parenthesis tags as follows:

                and ('#ReReplaceNoCase(socNum, '[^0-9]', '', 'All')#'
                IN (tch_socsecnum,math_tchname_ssn,eng_tchname_ssn,socstud_tchname_ssn,sci_tchname_ssn,read_ tchname_ssn)
                or oth_tchname_ssn like '%#ReReplaceNoCase(socNum, '[^0-9]', '', 'All')#%' )

                We also indexed some columns. I will eventually swtich this to a stored procedure. It runs quite fast now.

                Thanks for your help.