slow query in survey

[Masoud]

My query is like below. When I open it, quickly will be opened but I want to
go through records in datasheet view, for example go to last record, it takes
times (10 seconds) , please help me about the problem.

My query:

SELECT tbldocserverhyperlink.FileName, tbldocserverhyperlink.Expr1,
tbldocserverhyperlink.[Doc No in server], tbldocserverhyperlink.Expr2,
tbldocserverhyperlink.[Doc No], tbldocserverhyperlink.hyper, qryKeyWord.[Line
Size], qryKeyWord.[Fluid Code], qryKeyWord.[Unit No Iso], qryKeyWord.[Pipe
Sequence No], qryKeyWord.[Piping Class], qryKeyWord.[Insulation Code]
FROM qryKeyWord, tbldocserverhyperlink
WHERE (((tbldocserverhyperlink.[Doc No in server]) Like [qryKeyWord]![Line
Size] & "*" & [qryKeyWord]![Fluid Code] & "*" & [qryKeyWord]![Unit No Iso] &
"*" & [qryKeyWord]![Pipe Sequence No] & "*"));


Thanks in advanced.

 

[John Spencer]

The query will open and display the first screen of records quickly. To
continue to display more records it will have to load the additional records
and that takes time.

You MIGHT get better performance by using a join, but it might be better if
you could further limit the records returned.

Here are some ideas to test, but I think your problem is likely the number of
records you are trying to process rather than the query itself.

SELECT tbldocserverhyperlink.FileName
, tbldocserverhyperlink.Expr1
, tbldocserverhyperlink.[Doc No in server]
, tbldocserverhyperlink.Expr2
, tbldocserverhyperlink.[Doc No]
, tbldocserverhyperlink.hyper
, qryKeyWord.[Line Size]
, qryKeyWord.[Fluid Code]
, qryKeyWord.[Unit No Iso]
, qryKeyWord.[Pipe Sequence No]
, qryKeyWord.[Piping Class]
, qryKeyWord.[Insulation Code]
FROM qryKeyWord INNER JOIN tbldocserverhyperlink
ON tbldocserverhyperlink.[Doc No in server] Like
[qryKeyWord]![Line Size] & "*" & [qryKeyWord]![Fluid Code] & "*" &
[qryKeyWord]![Unit No Iso] & "*" & [qryKeyWord]![Pipe Sequence No] & "*"

Another possible improvement would be to construct the expression in
qryKeyWord before doing the join.

[Line Size] & "*" & [Fluid Code] & "*" & [Unit No Iso] & "*" & [Pipe Sequence
No] & "*"

Or Change the join to the following which may allow the use of any index on
the Line Size field.
ON tbldocserverhyperlink.[Doc No in server] Like
[qryKeyWord]![Line Size] & "*"
AND tbldocserverhyperlink.[Doc No in server] LIKE "*" &
[qryKeyWord]![Fluid Code] & "*" & [qryKeyWord]![Unit No Iso] & "*" &
[qryKeyWord]![Pipe Sequence No] & "*"

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Jerry Whittle]

You're probably just have to accept it. You have a query based on another
query so that will slow things down. Next you are using a rather complicated
Like statement in the Where clause. About the only thing that might help is
to make sure that the following field is indexed. I'm not even sure that will
help.
tbldocserverhyperlink.[Doc No in server]