running query every day is slower

[Masoud]

Hello

Actually my query at first was slow so I put the tread, and after some
suggestions my query is now like below. So it solved my problem a bit. For
example running query at first was 15 second with this suggested query
running was 7 seconds. But now after importing more data in the table, it
takes 60 seconds to run. Please help, what is the problem.
Thanks in advanced.

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 INNER JOIN tbldocserverhyperlink ON
tbldocserverhyperlink.[Doc No in server] like qryKeyWord.[Line No];

 

[Marshall Barton]

Actually my query at first was slow so I put the tread, and after some
suggestions my query is now like below. So it solved my problem a bit. For
example running query at first was 15 second with this suggested query
running was 7 seconds. But now after importing more data in the table, it
takes 60 seconds to run. Please help, what is the problem.
Thanks in advanced.

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 INNER JOIN tbldocserverhyperlink ON
tbldocserverhyperlink.[Doc No in server] like qryKeyWord.[Line No];

Almost certainly, the problem is the ON clause. That is a
particularly convoluted way to compare 6 values. You will
probably have to live with it unless you can get into
tbldocserverhyperlink and redesign it so it has a separate
field for each value.

 

[John W. Vinson]

Hello

Actually my query at first was slow so I put the tread, and after some
suggestions my query is now like below. So it solved my problem a bit. For
example running query at first was 15 second with this suggested query
running was 7 seconds. But now after importing more data in the table, it
takes 60 seconds to run. Please help, what is the problem.
Thanks in advanced.

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 INNER JOIN tbldocserverhyperlink ON
tbldocserverhyperlink.[Doc No in server] like qryKeyWord.[Line No];

If you can't do as Marshall suggests and change the structure of
tblDocServerHyperlink, is there any way you can parse the line size, fluid
code, etc. out of the [Doc No in server] field into separate (indexed!!!)
fields of their own? If the doc no field has fixed length portions, or
delimiters, or something that would let you extract these values rather than
having to do an inefficient, non-indexed blind search for them, it would make
your query much more practical.

Could you post an example of a Doc No and indicate which part of the Doc No
corresponds to these different fields?

 

[Masoud]

Hello

Actually [Doc No in server] are file names on the server, (I have used bad
name for this field) any way in most cases these file names do not have
regular name or fixed length also they are 20000 files so it takes a lot of
time to rename them.
I made a table query and transfer all the data in slow query to the new
table, now my running time is good. Actually my slow query in previous should
be running after pushing each button in my form in while was not necessary,
because it reads the file name and file path in the network, so I decided to
do this one time each day by using another button and transfer the data to
the new table.
So another buttons now are free, and they work with the table.

This is what I have done and I do not now this is correct way or no?

Thanks in Advanced

My [Doc No in server] are like this:

Doc No in server

10-G10010016-G03P2-N_Sht_1.pdf
10-G10010017-G03P2-N_Sht_1.pdf
20-HC10010004-G05P4-N_Sht_1.pdf
20-HC10020004-G05P4-N_Sht_1.pdf

Line Size fluid code Unit No Iso Pipe Sequence No Piping Class
Insulation Cod

10 G 100 10016 G03P2
N
10 G 100 10017 G03P2
N
20 HC 100 10004 G05P4
N
20 HC 100 20004 G05P4
N

Hello

Actually my query at first was slow so I put the tread, and after some
suggestions my query is now like below. So it solved my problem a bit. For
example running query at first was 15 second with this suggested query
running was 7 seconds. But now after importing more data in the table, it
takes 60 seconds to run. Please help, what is the problem.
Thanks in advanced.

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 INNER JOIN tbldocserverhyperlink ON
tbldocserverhyperlink.[Doc No in server] like qryKeyWord.[Line No];

If you can't do as Marshall suggests and change the structure of
tblDocServerHyperlink, is there any way you can parse the line size, fluid
code, etc. out of the [Doc No in server] field into separate (indexed!!!)
fields of their own? If the doc no field has fixed length portions, or
delimiters, or something that would let you extract these values rather than
having to do an inefficient, non-indexed blind search for them, it would make
your query much more practical.

Could you post an example of a Doc No and indicate which part of the Doc No
corresponds to these different fields?

 

[John W. Vinson]

Actually [Doc No in server] are file names on the server, (I have used bad
name for this field) any way in most cases these file names do not have
regular name or fixed length also they are 20000 files so it takes a lot of
time to rename them.
I made a table query and transfer all the data in slow query to the new
table, now my running time is good. Actually my slow query in previous should
be running after pushing each button in my form in while was not necessary,
because it reads the file name and file path in the network, so I decided to
do this one time each day by using another button and transfer the data to
the new table.
So another buttons now are free, and they work with the table.

This is what I have done and I do not now this is correct way or no?

Thanks in Advanced

My [Doc No in server] are like this:

Doc No in server

10-G10010016-G03P2-N_Sht_1.pdf
10-G10010017-G03P2-N_Sht_1.pdf
20-HC10010004-G05P4-N_Sht_1.pdf
20-HC10020004-G05P4-N_Sht_1.pdf

Sounds like you've made the best of a rather bad situation: if there is in
fact no consistancy with the filenames it's tough.

If they are at least consistant in that they have a number, a hyphen, a text
string like G10010016, another hyphen, etc. then you could (in the process of
loading your local table) use the Split() function to parse it out into
individual fields, but I'd worry about typographical errors, missing or extra
hyphens, etc.; if you're getting adequate performance with the local table
that may be your best bet.