Prompt in Query makes it run really slow

[HeathRamos]

I have a query that joins 6 tables.

In this query, it prompts as a criteria for a workorder number, which is a text field. The prompt looks something like : Like [Input WO #] & "*"

Running this query takes about 5 minutes to run if you prompt for the workorder number. If you manually type in the workorder number in the query and run it (instead of prompting it) it takes only seconds to run.

What could cause this slow down?

The weird thing is that the query used to run quickly with the prompt a couple of weeks ago and nothing has changed in the Access database.

 

[PHV]

Is the workorder number field indexed ?
You really need a Like operator instead of = ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[HeathRamos]

These tables are odbc links to an oracle database.

Not sure what you mean by: You really need a Like operator instead of = ?

If the criteria didn't have the Like condition, it won't return any records at all.

 

[lynchg]

If a person typed in the entire Input WO #, wouldn't they get all of the records that matched on that value?

Let's say one of your Input WO # values is 12345.

Your "Like" search won't just look for exact matches, it will look for any value that begins with 12345. You are forcing it to search every value in the table to see if it can match those first 5 characters.

If you index the field and prompt them to enter the entire value, it will execute very fast. If you need to do a partial string search (Like xxxx*, which is what you are doing now), it will run more slowly, and continue to slow down as more data is added to the table.

You said it used to run faster, have ther been a considerable amount of records added to the table you are searching on?

 

[lynchg]

Try getting rid of the Like, the ampersand, and the asterisk, and fill in your prompt with the Input WO #. It should run in the same time as entering a value directly into the criteria box in your query.

 

[HeathRamos]

Well...I am using the Like [WO#] & "*" because it won't work otherwise. My guess is that is pads spaces at the end of workorder numbers in the table. Workorder numbers are either 6 numbers long or 7 with a letter at the end. The field is a 12 character field.
It would be nice if [WO#] worked on it's own but it doesn't.

 

[lynchg]

What value do you enter when you say:

"If you manually type in the workorder number in the query and run it (instead of prompting it) it takes only seconds to run.

 

[HeathRamos]

In the criteria field:

"880541

 

[lynchg]

If you prompt for the same value without the Like you will get the same result as entering 880541 in the criteria field.

 

[HeathRamos]

FYI.

In the criteria section of the query under workorder number, I have tried it 4 different ways.

#1 "880541"
Returns 6 records almost immediately
#2 Like [WO#] & "*"
Returns 6 records in about 6 minutes
#3 [WO#] & " " (6 blank spaces)
Returns 6 records in 1 min 45 sec
#4 [WO#]
Returns 0 records almost immediately

 

[lynchg]

You enter [WO#] in the criteria field, execute the query, a prompt pops up waiting for you to enter a value, you enter 880541, and it returns zero records.

You enter 880541 in the criteria field and it returns 6 records in an instant.

Is this correct?

 

[HeathRamos]

correct (except it has quotes around it since it is text).

 

[lynchg]

It seems impossible because you are feeding it the exact same value.

I don't have time to continue right now, I'll have to check in again tomorrow. I hope you get an answer before then.

Good Luck

 

[PHV]

Define the parameter as text.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[HeathRamos]

what do you mean exactly by define parameter as text?

 

[PHV]

menu Query -> Parameters ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[HeathRamos]

Well...

I tried putting [WO] as the parameter and setting WO as text in the parameter query section but it comes up blank again.

I tried Like [WO] & "*" and had the same WO as text parameter but it took awhile to run again.

 

[JerryKlmns]

How about you add an extra field not shown, like

Field Name :Left([workorder],6)
Criteria :[WO#]

and let the workorder field visible, since you need all records that match the first six digits?

 

[lynchg]

Try using the trim function on the field you are seeking to match with the parameter.

Field Name :Trim([workorder])
Criteria :[WO#]

Then you are not locked into just matching on 6 characters, you will match just on the "real" data in that field and don't have to worry about the trailing spaces.

 

[HeathRamos]

Using the Trim function makes it so I don't have to use the Like [WO] & "*" anymore but it still takes awhile (2-3 minutes) instead of just seconds.