from: "Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>
subject: AW: Using like on a result string
A temporary table is worse, because you need some time to write the
information.
Like Birgitta said - avoid temp tables. They are overused and tend to have
an unnecessary, negative impact on performance.
In addition to the valid solutions other's have responded with, if
possible, consider testing a query or view that performs your LIKE
comparison against each individual component using OR. Use whichever
solution runs faster. e.g.
where a.ItmNum like '%string%'
or ItmDsc like '%string%'
or Vendor like '%string%'
etc.
Using WHERE filters over calculated expressions, while sometimes
unavoidable, tend to not be stellar performers since doing so will most
likely cripple the ability to effectively use indexes.
If the %string% value is coming from a user prompt screen, consider
providing a separate filter prompt for the user for each component you're
concatenating together. Doing so is more code, but properly implemented it
will likely result in reduced run time and less burden on the system. If
you go that route, I think using SQL descriptors, like in the following
article, provides the best balance of performance and clean code - A First
Look At SQL Descriptors <
http://www.itjungle.com/fhg/fhg091515-story01.html>.
The more frequently this SQL will be used, the more likely you should go
that route.
Placing the SQL inside a view or SQL stored procedure is a good idea, so
that I/O interface can be centrally used by any user interface (green
screen, web, mobile, etc.).
Mike
midrange.com
