Query very Slow

[Guest]

Hi:
I have QUERY for my purchasing data information, on Tables I just Have one
table and the other are link_tables.
IM, RQ, MOS,OP,ID AND TX.
On the form you need to type an Item Number and this one should looking for
the number related with him and show up on the screen.
At this time the key is looking for them but I think is going one by one
until get all numbers corresponding to the key. (140000)
Therefore is taking around 3 minutes to give results on the screen.
And also is repeating the same number 8 times and some of them 4 times.
For example the result for the key is 5 different type of numbers, each one
is repeating, some of them 8 times and the other 4 times.

I have three different forms, one for the ID, one for the Transaction and
one for the Requirements, the ID and TX I don't have any problem, after type
the Number immediately I got results on the screen, but with the RQ the time
is totally different.
On the design view I have trust relationship among:
IM, RQ, OP, MOS, MO_STAT, IM1.
Im_key is pointing to RQ_Imkey, Im1 (Im_key) is pointing to the MOS_Imkey,
MO_Status is pointing to MOS_Status.
In RQ_satus and OP_Status I have <>C on the Criteria; in Im_Key I have a
Forms formula pointing to the FORM and the name of the label under FORM.

I would like to know what is the reason to receive results so SLOW and
Reapeted 4 and 8 times.

Your help will be greatly appreciate

Thank you very much in advance and I hope to be hearing from you soon.

 

[John Vinson]

Hi:
I have QUERY for my purchasing data information, on Tables I just Have one
table and the other are link_tables.

There's no such thing as a "link_table". There's just tables. You can
use tables in the role of instantiating a many to many relationship
but... it's still a table.

IM, RQ, MOS,OP,ID AND TX.

Giving us two letter table names does not convey any useful
information...

On the form you need to type an Item Number and this one should looking for
the number related with him and show up on the screen.

Typing it into... what? A Combo Box? a Textbox? Do you have code that
looks up the value?

At this time the key is looking for them but I think is going one by one
until get all numbers corresponding to the key. (140000)
Therefore is taking around 3 minutes to give results on the screen.

Is this field Indexed in your table?

And also is repeating the same number 8 times and some of them 4 times.
For example the result for the key is 5 different type of numbers, each one
is repeating, some of them 8 times and the other 4 times.

I'm sorry, I have NO idea what you're saying here. What is the
Recordsource for the Form? Could you post the SQL?

I have three different forms, one for the ID, one for the Transaction and
one for the Requirements, the ID and TX I don't have any problem, after type
the Number immediately I got results on the screen, but with the RQ the time
is totally different.
On the design view I have trust relationship among:
IM, RQ, OP, MOS, MO_STAT, IM1.
Im_key is pointing to RQ_Imkey, Im1 (Im_key) is pointing to the MOS_Imkey,
MO_Status is pointing to MOS_Status.
In RQ_satus and OP_Status I have <>C on the Criteria; in Im_Key I have a
Forms formula pointing to the FORM and the name of the label under FORM.

Please post the SQL.

I would like to know what is the reason to receive results so SLOW and
Reapeted 4 and 8 times.

I have no idea; probably because the query is incorrectly structured.
If you'll post the SQL we can see if it's correct or incorrect, and
suggest ways to fix.

Your help will be greatly appreciate

Thank you very much in advance and I hope to be hearing from you soon.

John W. Vinson[MVP]

 

[Guest]

Hi John:
Thanks for your response...

Look this is the situation...
I have two words because I'm working with COBOL program database and Cobol
is using those particulars letters. IM (Item Number), ID (Identification), RQ
(Requirements), TX (Transaction Number), etc.

This is the list that I have on my TABLES: ID - IM- IM1- ML-MO_STAT - MOS -
MOS1 - OP -RQ - TX... All of them are LINK Tables Except the MO_Stat because
is a Table.
I have three different forms 1)the ID,2) Transaction and 3) Requirements.
The first two are working correctly. I’m having problems with the
Requirements. In design view, I have a trust relationship built among: IM,
RQ, OP, MOS, MO_STAT, IM1.
The Im_key is pointing to RQ_Imkey, Im1 (Im_key) is pointing to the
MOS_Imkey, MO_Status is pointing to MOS_Status.
In RQ_satus and OP_Status, I have criteria of “<>Câ€.
In Im_Key I have a Forms formula pointing to the FORM and the name of the
label under FORM.

The Item number is typing into a TEXTBox. and this one look for the correct
number with his information. for example if I type R12345 I need to know the
complete information about that number,
C78170C R 0078999 78 14.07 2 0 20 3/20/2006 R648944
2 3/21/2006 3/20/2006
Where this information is corresponding to the Item Number R12345. But in
this case this information is repeating 4 and 8 times in a duplication issue.

Ok This is the SQL View of this program.

SELECT IM1.IM_KEY, MO_STAT.MO_DESC_STATUS, RQ.RQ_STATUS, MOS.MOS_JOB,
MOS.MOS_LOT, RQ.RQ_EST_COST, RQ.RQ_QTY_REQ, RQ.RQ_QTY_ISS, RQ.RQ_OP_NUM,
RQ.RQ_DATE_REQ, IM.IM_KEY, [RQ_QTY_REQ]-[RQ_QTY_ISS] AS BAL_REQ,
MOS.MOS_WANTDATE, MOS.MOS_DATE_REL
FROM PD, ((IM INNER JOIN ((IM1 INNER JOIN (RQ INNER JOIN MOS ON
(RQ.RQ_MOSSEQNUM = MOS.MOS_SEQNUM1) AND (RQ.RQ_MOSTYPE = MOS.MOS_TYPE) AND
(RQ.RQ_MOSJOB = MOS.MOS_JOB) AND (RQ.RQ_MOSLOT = MOS.MOS_LOT)) ON IM1.IM_KEY
= MOS.MOS_IMKEY) INNER JOIN OP ON (RQ.RQ_MOSTYPE = OP.OP_MOSTYPE) AND
(RQ.RQ_MOSJOB = OP.OP_MOSJOB) AND (RQ.RQ_MOSLOT = OP.OP_MOSLOT) AND
(RQ.RQ_MOSSEQNUM = OP.OP_MOSSEQNUM)) ON IM.IM_KEY = RQ.RQ_IMKEY) INNER JOIN
MO_STAT ON MOS.MOS_STATUS = MO_STAT.MO_STATUS) INNER JOIN OL ON
MOS.MOS_OLPHKEY = OL.OL_PHKEY
WHERE (((RQ.RQ_STATUS)<>"C") AND
((IM.IM_KEY)=[Forms]![PurchinfoFRM]![ItemNumber]) AND
(([RQ_QTY_REQ]-[RQ_QTY_ISS])>0) AND ((OP.OP_STATUS)<>"C"));

Your help will be greatly appreciated.

I hope to be hearing from you soon.

Robert

 

[Guest]

John:
The big problem is the time, because I got the information that I need on
the screen, from the database. Correct Item Number and correct data for this
one.

But answered your questions:
1.- Yes is indexed... the IM_KEY is indexed key
2.- The recordsource for the form is: the QryItemNumber.

For the RQ query information the time to get information is around 3
minutes, with the others two queries is just one second.

Thank you very much and I'll be waiting for your response on the matter.

Robert

Hi John:
Thanks for your response...

Look this is the situation...
I have two words because I'm working with COBOL program database and Cobol
is using those particulars letters. IM (Item Number), ID (Identification), RQ
(Requirements), TX (Transaction Number), etc.

This is the list that I have on my TABLES: ID - IM- IM1- ML-MO_STAT - MOS -
MOS1 - OP -RQ - TX... All of them are LINK Tables Except the MO_Stat because
is a Table.
I have three different forms 1)the ID,2) Transaction and 3) Requirements.
The first two are working correctly. I’m having problems with the
Requirements. In design view, I have a trust relationship built among: IM,
RQ, OP, MOS, MO_STAT, IM1.
The Im_key is pointing to RQ_Imkey, Im1 (Im_key) is pointing to the
MOS_Imkey, MO_Status is pointing to MOS_Status.
In RQ_satus and OP_Status, I have criteria of “<>Câ€.
In Im_Key I have a Forms formula pointing to the FORM and the name of the
label under FORM.

The Item number is typing into a TEXTBox. and this one look for the correct
number with his information. for example if I type R12345 I need to know the
complete information about that number,
C78170C R 0078999 78 14.07 2 0 20 3/20/2006 R648944
2 3/21/2006 3/20/2006
Where this information is corresponding to the Item Number R12345. But in
this case this information is repeating 4 and 8 times in a duplication issue.

Ok This is the SQL View of this program.

SELECT IM1.IM_KEY, MO_STAT.MO_DESC_STATUS, RQ.RQ_STATUS, MOS.MOS_JOB,
MOS.MOS_LOT, RQ.RQ_EST_COST, RQ.RQ_QTY_REQ, RQ.RQ_QTY_ISS, RQ.RQ_OP_NUM,
RQ.RQ_DATE_REQ, IM.IM_KEY, [RQ_QTY_REQ]-[RQ_QTY_ISS] AS BAL_REQ,
MOS.MOS_WANTDATE, MOS.MOS_DATE_REL
FROM PD, ((IM INNER JOIN ((IM1 INNER JOIN (RQ INNER JOIN MOS ON
(RQ.RQ_MOSSEQNUM = MOS.MOS_SEQNUM1) AND (RQ.RQ_MOSTYPE = MOS.MOS_TYPE) AND
(RQ.RQ_MOSJOB = MOS.MOS_JOB) AND (RQ.RQ_MOSLOT = MOS.MOS_LOT)) ON IM1.IM_KEY
= MOS.MOS_IMKEY) INNER JOIN OP ON (RQ.RQ_MOSTYPE = OP.OP_MOSTYPE) AND
(RQ.RQ_MOSJOB = OP.OP_MOSJOB) AND (RQ.RQ_MOSLOT = OP.OP_MOSLOT) AND
(RQ.RQ_MOSSEQNUM = OP.OP_MOSSEQNUM)) ON IM.IM_KEY = RQ.RQ_IMKEY) INNER JOIN
MO_STAT ON MOS.MOS_STATUS = MO_STAT.MO_STATUS) INNER JOIN OL ON
MOS.MOS_OLPHKEY = OL.OL_PHKEY
WHERE (((RQ.RQ_STATUS)<>"C") AND
((IM.IM_KEY)=[Forms]![PurchinfoFRM]![ItemNumber]) AND
(([RQ_QTY_REQ]-[RQ_QTY_ISS])>0) AND ((OP.OP_STATUS)<>"C"));

Your help will be greatly appreciated.

I hope to be hearing from you soon.

Robert

There's no such thing as a "link_table". There's just tables. You can
use tables in the role of instantiating a many to many relationship
but... it's still a table.


Giving us two letter table names does not convey any useful
information...


Typing it into... what? A Combo Box? a Textbox? Do you have code that
looks up the value?


Is this field Indexed in your table?


I'm sorry, I have NO idea what you're saying here. What is the
Recordsource for the Form? Could you post the SQL?


Please post the SQL.


I have no idea; probably because the query is incorrectly structured.
If you'll post the SQL we can see if it's correct or incorrect, and
suggest ways to fix.


John W. Vinson[MVP]