Instr vs wildcards for parameter searches

[databasedonr]

Greetings all

I am just working on some parameter queries, and am wondering if anyone has done any benchmarking with instr vs. wildcards? Instr appears to be a more elegant solution, but is there any solid performance advantage or disadvantage?

My apologies if this should rather be in the queries thread....

 

[Mile-O]

InStr() only returns the numerical position of the string you are searching for (or returns 0 [False] if not found) wheras the use of Wildcards (or, really, the Like operator) enables looser/better searching.

InStr() is a function and Like is an operator.

Therefore, there's no comparison.

 

[databasedonr]

I don't follow, sir.

For example, I have a query that goes like this:

PARAMETERS [Enter Service Name] Text ( 255 );
SELECT DISTINCTROW tblService.ServiceID, tblService.SupervisingOfficer, tblService.ServiceName, tblService.ServiceType, tblService.Attn, tblService.LiaisonOfficer, tblService.Copy1, tblService.Copy2, tblService.Team, tblService.Comments, tblPhone.Telephone, tblPhone.Fax, tblPhone.Liaison, tblPhone.LiaisonExtension, tblAddress.AddressLine1, tblAddress.AddressLine2, tblAddress.City, tblAddress.Province, tblAddress.PostalCode
FROM (((tblService INNER JOIN tblAddress ON tblService.ServiceID = tblAddress.ServiceID) INNER JOIN tblPhone ON tblService.ServiceID = tblPhone.ServiceID)
WHERE (((InStr([tblService].[ServiceName],[Enter Service Name]))<>False));

This query returns the EXACT same results as if I run this one:

SELECT DISTINCTROW tblService.ServiceID, tblService.SupervisingOfficer, tblService.ServiceName, tblService.ServiceType, tblService.Attn, tblService.LiaisonOfficer, tblService.Copy1, tblService.Copy2, tblService.Team, tblService.Comments, tblPhone.Telephone, tblPhone.Fax, tblPhone.Liaison, tblPhone.LiaisonExtension, tblAddress.AddressLine1, tblAddress.AddressLine2, tblAddress.City, tblAddress.Province, tblAddress.PostalCode
FROM (((tblService INNER JOIN tblAddress ON tblService.ServiceID = tblAddress.ServiceID) INNER JOIN tblPhone ON tblService.ServiceID = tblPhone.ServiceID)
WHERE ((([tblService].[ServiceName]) Like "*" & [Enter Service Name] & "*"));

So, what I am wondering is, which, if either, is the better route to go? I am searching thus for a record based on the parameter value being in the string of the field. It *seems* more elegant to use the instr function, but I am wondering about performance, missed data, etc.

Please pardon my ignorance; it is limitless

 

[databasedonr]

I'm interested to know more ....

Partial string searchers are therefore more expensive than wildcard searches?

Where can I read/learn/see about using indexes in a similar search?

In the present environment (a relatively new database) I have less than 1000 records in any of the relevant tables, so my response time is okay. The advantage is that the database is relatively new, so I have lots of time to make changes before I have a lot of records.

Thanks for responses -- this is quite interesting!

 

[databasedonr]

I was asking, not paraphrasing, but at any rate, I am really curious how I can make these faster.

The service names are such that I can't use first letter. Ex. I have service names "Upper Ottawa" and "Ottawa Valley", thus I could use an "O" as index character, but not first letter, because I would want both these services to return in my query.

But I am wondering -- if I have an index on the Servicename field, will that help? Or a composite index on my ServiceID and ServiceName? The ServiceID is guaranteed unique in the table... Will either of those help?

If I understand correctly, both of these searches are potentially slow because I am looking at a partial string. I wonder if it therefore make more sense if I create a subquery to extract the ServiceID, and then once I have the ServiceID return the rest of my fields?