This content has been marked as final. Show 3 replies
> it takes a long time to return the values and is just not efficient
LIKE is one of the slower comparisons. Using it in a query often means the database will not be able to utilize existing indexes that might help optimize query speed. Your query is entirely composed of LIKE comparisons. Review the execution plan to see where the bottleneck is and if there is a way to improve the query time.
> I would like to search by month and year. And right now it is not working correctly
DateAdded should be stored in a date/datetime column, not varchar. Then you could properly search by date or month and year
I agree with cf_dev2 that you should find a way to get the date field converted to a date column instead of varchar. Then you could use SQL's "between" operator.
You *might* be able to speed up what you have now by doing some thing like:
<cfif DateAdded neq "">
<cfparam name="datelist" default="">
<cfloop from="1" to="31" index="i">
<cfset datetoadd = monthasstring(month(form.dateadded)) & ' ' & i & ', ' & year(form.dateadded)>
<cfset datelist = listappend(datelist,datetoadd,'|')>
<cfset datelist = listQualify(datelist,"'","|","all")>
<cfset datelist = listChangeDelims(datelist,",","|")>
AND DateAdded IN (#datelist#)
You should really be using the cfqueryparam tag too.
As you said, converting the column to datetime would be the best option.
IN (...) is akin to a series of OR statements. I don't think a query with 28 to 31 OR statements would be much better than a LIKE statement.