> Here's what I have, it was written by another developer but I've been tasked > with trying to make it run faster > > <cfquery name="qEntityInformation" datasource="#AppDataSource#"> > SELECT top 100 > INVN AS EntityId, ... > FROM ENTITY_ALL > WHERE 1 = 1 > <cfif attributes.Keywords neq ""> > AND > <cfswitch expression="#attributes.DataField#"> > <cfcase value="EntityId"> > ENTITY_ALL.INVN LIKE '%#attributes.Keywords#%' > </cfcase> ..... > <cfdefaultcase> > ((ENTITY_ALL.INVN LIKE '%#attributes.Keywords#%') OR > (FNAME LIKE '%#attributes.Keywords#%') OR > (SNAME LIKE '%#attributes.Keywords#%') OR > (SITE LIKE '%#attributes.Keywords#%') OR > (SADDR LIKE '%#attributes.Keywords#%') OR > (CITY LIKE '%#attributes.Keywords#%') OR > (STATE LIKE '%#attributes.Keywords#%') OR > (COUNTRY LIKE '%#attributes.Keywords#%') OR > (ZIP LIKE '%#attributes.Keywords#%') OR > (TYPE LIKE '%#attributes.Keywords#%')) > </cfdefaultcase> > </cfswitch> > </cfif> > ORDER BY #attributes.SortField# #SortDirection# > </cfquery>
> What I want to do is pass attributes.DataField to a stored procedure and > build the where clause based on it. Why do you think converting this to a stored procedure is going to make it significantly faster? What does the explain output tell you about the relative timing of planning and execution? Jochem ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:5:202485 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/5 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:5 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.5 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
