Query is too slow, using LIKE operator

[PushCode]

I'm trying to search an access database with a fairly large table (llK+ records).

Need to search based on input from three text fields on the search form.

The query I have is running terribly slow...like unacceptably slow. I'm using the LIKE operater, and wondering if maybe there is a better way.

Here's the query:

<cfquery name="getObsv" datasource="#app.ds#">
  	SELECT a.observationnumber, a.observation, b.eventbegan, c.agencyname, e.sitename
	FROM observations a, inspections b, agency c, inspsites d, plantsites e
	WHERE a.eventid = b.inspectiontid
	AND b.agencyid = c.agencyid
	AND b.inspectiontid = d.inspectionid
	AND d.plantsiteid = e.plantsiteid
	AND a.observation LIKE '%#page.obsv1#%'
	OR a.observation LIKE '%#page.obsv2#%'
	OR a.observation LIKE '%#page.obsv3#%'
  </cfquery>

Any thoughts?

Thanks for any suggestions!

 

[r937]

not gonna get better

searching at the left of a column value (LIKE 'foo%') can utilize an index, but searching within a column value (LIKE '%foo%') requires a table scan

your alternative would be fulltext searching

r937.com | rudy.ca

 

[PushCode]

Can I use CONTAINS or any other operator? This is an Access DB. How would I go about doing fulltext searching on it?

Thanks!

 

[r937]

ah, sorry, i forgot it was access, which doesn't have a fulltext indexing feature

does your installation use Verity?

r937.com | rudy.ca

 

[PushCode]

No, apparently I don't.

Here's an option... if I forced the user to enter all search phrases into one field, would that help performance?

 

[PushCode]

And by "No, apparently I don't", I mean no, we don't have Verity at our disposal, sadly.

Just wanted to clarify that after re-reading my previous post.

 

[r937]

> if I forced the user to enter all search phrases into one field, would that help performance?

not sure i understand what you're suggesting, but generally speaking, it is not the developer's job to "force" the user to do something

it's actually the other way around ;-)

r937.com | rudy.ca

 

[PushCode]

I know, not my first choice to force anything, but I've been handed an ugly set of limitations that need a solution.

Well thanks for the help, I'll figure something out.

 

[jstreich]

Can I ask the question that's on everone's mind... "Why access?" MySQL (free depending on use), Oracle and MS SQL server are all better options to Access. Then again I think that pencil and paper are a better solution than access.

[plug=shameless]

http://game-master.us/phpx-3.4.0/

[/plug]

 

[PushCode]

Q - Why Access?

A - Application is already built and used by hundreds of folks within our company (it's intranet based). I'm developing some reporting functionality and don't have the time, resources or permission to rewrite the thing in MS SQL or Oracle, which are both available in our company. What are ya gonna do? Sometimes you just use what is given to you.

 

[cfStarlight]

As an aside, shouldn't your query be ..?

<cfquery name="getObsv" datasource="#app.ds#">
      SELECT a.observationnumber, a.observation, b.eventbegan, c.agencyname, e.sitename
    FROM observations a, inspections b, agency c, inspsites d, plantsites e
    WHERE a.eventid = b.inspectiontid
    AND b.agencyid = c.agencyid
    AND b.inspectiontid = d.inspectionid
    AND d.plantsiteid = e.plantsiteid
    AND 
    (
    a.observation LIKE '%#page.obsv1#%'
    OR a.observation LIKE '%#page.obsv2#%'
    OR a.observation LIKE '%#page.obsv3#%'
    )
  </cfquery>

 

[r937]

O
M
G

i never even looked at the query

well spotted, cfStarlight!



r937.com | rudy.ca

 

[cfStarlight]

Thanks I thought it might be a typo, but just in case it wasn't I wanted to mention it.

 

[PushCode]

Bingo cfStarlight...that's just what I needed. Knew I was doing something wrong. Now the performance is at least reasonable/acceptable.

Thank you Baby Jesus!