[thelist] Knuckle-headed SQL Question
rudy
Rudy_Limeback at maritimelife.ca
Mon Nov 6 14:12:07 CST 2000
> Here's my solution for ASP/Access database... I'd be interested to know
if
> there is a better option .. this one works just fine for me tho, but am
not
> sure how well it does performance wise.
>
> SqlString = SqlString & "SELECT whatever FROM wherever "
> SqlString = SqlString & "WHERE (Name LIKE '0%') OR (Name LIKE '1%') OR
(Name
> LIKE '2%') OR (Name LIKE '3%') OR (Name LIKE '4%') OR (Name LIKE '5%')
OR
> (Name LIKE '6%') OR (Name LIKE '7%') OR (Name LIKE '8%') OR (Name LIKE
'9%')
> "
hi michele
a better option? that depends on the database (access? mysql? sql/server?
oracle? db2? informix?)
it also depends on whether there are any indexes declared on the column in
question -- no index, you get a scan of all the rows in the table
note that using LIKE in a query causes (used to cause? i'm a bit out of
touch with the latest database developments) some database optimizers to
throw up their hands, figuratively speaking, and scan all the rows of the
table regardless of whether an index exists
it depends on how sophisticated the optimizer is in terms of calculating
the potential number of input/output operations using the index to access
the table rows, versus the number of i/o's just to scan all the table rows
for a query such as LIKE 'fumblegoozer%' in a 15-character column, using
the index might be efficient
for a query such as LIKE '%foo' (in any size column), the index would be
pretty useless
anyhow, michele, for the example you gave, with ten LIKEs all OR'd
together, just the fact that there are so many ORs might throw some
optimizers off
you might want to try
where Name >= '0'
instead, but be aware that you'll get everything that starts not only with
a digit but also any other characters that collate higher than the 9
(although off the top of my head i can't think of any)
furthermore, some optimizers will evaluate Name >= '0' favourably and will
use the index after all
finally, i really like james' idea of using REGEXP, but of course the
particular database language has to support that
rudy
r937.com
More information about the thelist
mailing list