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.

 

[lespaul]

you still haven't answered the question 'is there an index on the WO field?'

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[lynchg]

have you indexed that field in your table yet? That should help with speed.

The Trim function is going to slow down the process because it has to evaluate all of the values in that field.

A real solution would be to get rid of the trailing spaces in that field, or add a field and insert a trimmed form of the work order number value every time you add a record. Then you can search against that trimmed field when running your query.

 

[HeathRamos]

Can you index a linked table in Access?

Can't really make any changes to the Oracle side, besides creating views, etc.

 

[lynchg]

If this is really important I would talk to the Oracle dba's about modifying the table. I don't know what purpose is being served by padding the field with spaces.

I link to some old mainframe DB2 tables that have spaces in all of their text fields (I think they are all char datatype instead of varchar), they tell me it is too much trouble to change it so I am forced to do a lot of RTrim().

You could request that this field be indexed, it should make for a huge improvement in performance.

 

[HeathRamos]

on the server side, it looks like the workorder field is part of a multiple field index (workorderno, operationno and entity).

 

[lespaul]

they should still be able to add an index on just the workorderno field.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[lynchg]

You can have a dba analyze the performance and it is possible to adjust the index based on need. I don't know if that would be possible in your case. Sometimes when a field is the 2nd or 3rd field in a multi field index, the query analyzer will choose not to use the index when executing a query. You would need to have a discussion about what can be done. Generally decisions affecting performance are made on a least cost/greatest benefit basis. I know that sometimes I get what I want and other times I have to suffer with poor performance because my task was deemed a low priority.

 

[HeathRamos]

has anyone created a pseudo index before?

basically says to create a new query, go into sql, and run the create index statement one the odbc linked table. just curious where it stored that information in access since it says it doesn't create it on the server. maybe if you look at design view of the linked table, it will be there under indexes and when you run on query that uses that field, it will automatically use that index?

Found this on a website:

One powerful feature of the create index statement is to create an index on an ODBC linked table. This is usually referred to as a pseudo index and can significantly improve the performance of slow ODBC queries by adding an index on field references in the right side of where clauses in queries.

 

[lynchg]

I have never heard of it.

Keep researching, sounds promising.

 

[HeathRamos]

A question I have will still be even if the index works, does that mean when you type in a value in the query, it doesn't need an index but when you prompt for one, it does?

I tried creating a pseudo-index on a linked table and it game me an error that a primary key already existed on that table, even though when I tried to create the index I didn't say it was unique.

 

[HeathRamos]

well...I was able to create a pseudo-index on a view but it makes it a primary key (not good since not unique).

Not sure how to create one that isn't unique