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?

[ksmith]

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.

[EmmanuelRuiz]

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.

[ksmith]

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.

[EmmanuelRuiz]

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.