I though I had this one licked

[pmacdiddie]

Hi, I posted this a while ago, and I though I got a solution. I added
referencial intergrity between the tables "tblJobs" and
tblJobsLineItems" as was suggested. The time went to .5 seconds.
Great. Unfortunately, the speed only lasted while I was the only user.
As soon as someone else logs in, it goes in the tank.

The old post is listed below.

I have an append query that needs to run every time a line item is
added to a subform. The append writes to a table that is the source
for a pull down box. Problem is that it takes 5 seconds to run. This
makes adding lines to an order too slow for the users. The result of
the query provides real time availabilty, so I really do need this to
work.

The tblJobs has only 10,000 records, tblJobsLineItems has 150,000
records.
INSERT INTO tmpRaItemsInUseTotals ( [Item Number], SumOfQuantity )
SELECT DISTINCTROW tblJobsLineItems.[Item Number],
Sum(tblJobsLineItems.Quantity) AS SumOfQuantity
FROM tblJobs INNER JOIN tblJobsLineItems ON tblJobs.Job_Num =
tblJobsLineItems.Job_Num
WHERE (((tblJobs.Confirmed)<>"no") AND ((tblJobs.SetCall) Between
[Forms]![frmJobs]![SetCall] And [Forms]![frmJobs]![fldRTSCall])) OR
(((tblJobs.Confirmed)<>"no") AND ((tblJobs.fldRTSCall) Between
[Forms]![frmJobs]![SetCall] And [Forms]![frmJobs]![fldRTSCall])) OR
(((tblJobs.Confirmed)<>"no") AND
((tblJobs.SetCall)<[Forms]![frmJobs]![SetCall]) AND
((tblJobs.fldRTSCall)>[Forms]![frmJobs]![fldRTSCall]))
GROUP BY tblJobsLineItems.[Item Number];
Any ideas?

[Lyle Fairfield]

pm********@gmail.com wrote:

INSERT INTO tmpRaItemsInUseTotals ( [Item Number], SumOfQuantity )
SELECT DISTINCTROW tblJobsLineItems.[Item Number],
Sum(tblJobsLineItems.Quantity) AS SumOfQuantity
FROM tblJobs INNER JOIN tblJobsLineItems ON tblJobs.Job_Num =
tblJobsLineItems.Job_Num
WHERE (((tblJobs.Confirmed)<>"no") AND ((tblJobs.SetCall) Between
[Forms]![frmJobs]![SetCall] And [Forms]![frmJobs]![fldRTSCall])) OR
(((tblJobs.Confirmed)<>"no") AND ((tblJobs.fldRTSCall) Between
[Forms]![frmJobs]![SetCall] And [Forms]![frmJobs]![fldRTSCall])) OR
(((tblJobs.Confirmed)<>"no") AND
((tblJobs.SetCall)<[Forms]![frmJobs]![SetCall]) AND
((tblJobs.fldRTSCall)>[Forms]![frmJobs]![fldRTSCall]))
GROUP BY tblJobsLineItems.[Item Number];

Any ideas?

a few ...

Does everyone have his/her own copy of the front end db?
--
Rewrite the SQL.
Can you manage to check tblJobs.Confirmed<>"no" only once?
Is tblJobs.Confirmed indexed?
Is there a good reason that tblJobs.Confirmed is Text and not Boolean?
Can you get rid of the [Forms]![frmJobs]![SetCall] syntax? If frmJobs
has a module then you can use Form_frmJobs.SetCall.Value which might
allow you to see exactly what you are doing. Better yet, use a query
string and enter the form values directly into the query string as
something like
AND tblJobs.fldRTSCall Between
Between " & Form_frmJobsRTSCall.Value & " And ..."
Get rid of the Access induced parentheses and put in your own so that
can see and understand the logic of the SQL.
Can you modify the SQL so that the ORs are minimized or at least
reduced to something like
A And B And (C Or D Or E Or F).
Can you try the SQL with the most likely OR first in the SQL? Does it
make a difference? What about last?
If all this temp table does is act as a candidate table and it is
created from a Select query why make the table at all when you can just
use the Select query as the Candidate data?
--
This stuff looks like pure Microsoft exemplary code. Microsoft's code
is written with the aim of convincing computer newbies that they can do
something with Access and should buy it. It is not written with the
idea of being efficient.

Referential integrity is unlikely to make things slower, not faster.
Probably, the RI resulted in an index or two being created and that
made your query quicker.

Before you try anything I have suggested do a search on me in Google.
Try to determine if I have any clue about what I am saying. If no then
pass on to some other suggestions. When one posts here often some
replies seem to make sense and others not. But those that make sense
may do so because the poster has the same understanding of a problem as
you do; which may mean that while they contribute a nice comfortable
answer for you, they may not have contributed one which is likely to
lead you to an effective and efficient solution.

[Allen Browne]

Suggestions:

a) See Tony Toews's "Performance FAQ" at:
http://www.granite.ab.ca/access/performancefaq.htm
Work through each item in his list.

b) Do you have indexes for the fields used in the WHERE and GROUP BY clauses
of your query?

c) Optimize the WHERE clause. Be sure to use correct data types.
For example, if tblJobs.Confirmed is a yes/no field (not a Text field), use:
tblJobs.Confirmed <False
You can also avoid repeating many of those phrases if you rearrange the
WHERE clause.
It may also help to declare the 2 parameters if they are numbers or dates
(Parameters on Query menu, in query design view.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<pm********@gmail.comwrote in message
news:11**********************@t46g2000cwa.googlegr oups.com...

Hi, I posted this a while ago, and I though I got a solution. I added
referencial intergrity between the tables "tblJobs" and
tblJobsLineItems" as was suggested. The time went to .5 seconds.
Great. Unfortunately, the speed only lasted while I was the only user.
As soon as someone else logs in, it goes in the tank.

The old post is listed below.

I have an append query that needs to run every time a line item is
added to a subform. The append writes to a table that is the source
for a pull down box. Problem is that it takes 5 seconds to run. This
makes adding lines to an order too slow for the users. The result of
the query provides real time availabilty, so I really do need this to
work.

The tblJobs has only 10,000 records, tblJobsLineItems has 150,000
records.

INSERT INTO tmpRaItemsInUseTotals ( [Item Number], SumOfQuantity )
SELECT DISTINCTROW tblJobsLineItems.[Item Number],
Sum(tblJobsLineItems.Quantity) AS SumOfQuantity
FROM tblJobs INNER JOIN tblJobsLineItems ON tblJobs.Job_Num =
tblJobsLineItems.Job_Num
WHERE (((tblJobs.Confirmed)<>"no") AND ((tblJobs.SetCall) Between
[Forms]![frmJobs]![SetCall] And [Forms]![frmJobs]![fldRTSCall])) OR
(((tblJobs.Confirmed)<>"no") AND ((tblJobs.fldRTSCall) Between
[Forms]![frmJobs]![SetCall] And [Forms]![frmJobs]![fldRTSCall])) OR
(((tblJobs.Confirmed)<>"no") AND
((tblJobs.SetCall)<[Forms]![frmJobs]![SetCall]) AND
((tblJobs.fldRTSCall)>[Forms]![frmJobs]![fldRTSCall]))
GROUP BY tblJobsLineItems.[Item Number];

Any ideas?

[pmacdiddie]

Thanks for the comments.

There are indexes on everything with a join, where, or group by. The
logic is for determining whether an item is in use on a particular day.
Jobs can run days, weeks, or even months. So the logic has to check
all of these permutations.

The main point of the question was the degredation of performance when
a second users logs on. When it is single user, the performance is
great. When there is more than one, it is terrible.

Thanks,

Preston
Allen Browne wrote:

Suggestions:

a) See Tony Toews's "Performance FAQ" at:
http://www.granite.ab.ca/access/performancefaq.htm
Work through each item in his list.

b) Do you have indexes for the fields used in the WHERE and GROUP BY clauses
of your query?

c) Optimize the WHERE clause. Be sure to use correct data types.
For example, if tblJobs.Confirmed is a yes/no field (not a Text field), use:
tblJobs.Confirmed <False
You can also avoid repeating many of those phrases if you rearrange the
WHERE clause.
It may also help to declare the 2 parameters if they are numbers or dates
(Parameters on Query menu, in query design view.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<pm********@gmail.comwrote in message
news:11**********************@t46g2000cwa.googlegr oups.com...

Hi, I posted this a while ago, and I though I got a solution. I added
referencial intergrity between the tables "tblJobs" and
tblJobsLineItems" as was suggested. The time went to .5 seconds.
Great. Unfortunately, the speed only lasted while I was the only user.
As soon as someone else logs in, it goes in the tank.

The old post is listed below.

I have an append query that needs to run every time a line item is
added to a subform. The append writes to a table that is the source
for a pull down box. Problem is that it takes 5 seconds to run. This
makes adding lines to an order too slow for the users. The result of
the query provides real time availabilty, so I really do need this to
work.

The tblJobs has only 10,000 records, tblJobsLineItems has 150,000
records.

INSERT INTO tmpRaItemsInUseTotals ( [Item Number], SumOfQuantity )
SELECT DISTINCTROW tblJobsLineItems.[Item Number],
Sum(tblJobsLineItems.Quantity) AS SumOfQuantity
FROM tblJobs INNER JOIN tblJobsLineItems ON tblJobs.Job_Num =
tblJobsLineItems.Job_Num
WHERE (((tblJobs.Confirmed)<>"no") AND ((tblJobs.SetCall) Between
[Forms]![frmJobs]![SetCall] And [Forms]![frmJobs]![fldRTSCall])) OR
(((tblJobs.Confirmed)<>"no") AND ((tblJobs.fldRTSCall) Between
[Forms]![frmJobs]![SetCall] And [Forms]![frmJobs]![fldRTSCall])) OR
(((tblJobs.Confirmed)<>"no") AND
((tblJobs.SetCall)<[Forms]![frmJobs]![SetCall]) AND
((tblJobs.fldRTSCall)>[Forms]![frmJobs]![fldRTSCall]))
GROUP BY tblJobsLineItems.[Item Number];

Any ideas?

[Allen Browne]

Well, there is much more that Access has to do in a multi-user setup, so it
normally does involve some hit.

But if the hit is huge, then you probably do have one of Tony's items to
deal with, such as longer path names on XP.

(It is possible to optimize that WHERE clause, and it could make a
difference, but you probably know that.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<pm********@gmail.comwrote in message
news:11**********************@73g2000cwn.googlegro ups.com...

Thanks for the comments.

There are indexes on everything with a join, where, or group by. The
logic is for determining whether an item is in use on a particular day.
Jobs can run days, weeks, or even months. So the logic has to check
all of these permutations.

The main point of the question was the degredation of performance when
a second users logs on. When it is single user, the performance is
great. When there is more than one, it is terrible.

Thanks,

Preston
Allen Browne wrote:

>Suggestions:

a) See Tony Toews's "Performance FAQ" at:
http://www.granite.ab.ca/access/performancefaq.htm
Work through each item in his list.

b) Do you have indexes for the fields used in the WHERE and GROUP BY
clauses
of your query?

c) Optimize the WHERE clause. Be sure to use correct data types.
For example, if tblJobs.Confirmed is a yes/no field (not a Text field),
use:
tblJobs.Confirmed <False
You can also avoid repeating many of those phrases if you rearrange the
WHERE clause.
It may also help to declare the 2 parameters if they are numbers or dates
(Parameters on Query menu, in query design view.)

<pm********@gmail.comwrote in message
news:11**********************@t46g2000cwa.googleg roups.com...

Hi, I posted this a while ago, and I though I got a solution. I added
referencial intergrity between the tables "tblJobs" and
tblJobsLineItems" as was suggested. The time went to .5 seconds.
Great. Unfortunately, the speed only lasted while I was the only user.
As soon as someone else logs in, it goes in the tank.

The old post is listed below.

I have an append query that needs to run every time a line item is
added to a subform. The append writes to a table that is the source
for a pull down box. Problem is that it takes 5 seconds to run. This
makes adding lines to an order too slow for the users. The result of
the query provides real time availabilty, so I really do need this to
work.

The tblJobs has only 10,000 records, tblJobsLineItems has 150,000
records.

INSERT INTO tmpRaItemsInUseTotals ( [Item Number], SumOfQuantity )
SELECT DISTINCTROW tblJobsLineItems.[Item Number],
Sum(tblJobsLineItems.Quantity) AS SumOfQuantity
FROM tblJobs INNER JOIN tblJobsLineItems ON tblJobs.Job_Num =
tblJobsLineItems.Job_Num
WHERE (((tblJobs.Confirmed)<>"no") AND ((tblJobs.SetCall) Between
[Forms]![frmJobs]![SetCall] And [Forms]![frmJobs]![fldRTSCall])) OR
(((tblJobs.Confirmed)<>"no") AND ((tblJobs.fldRTSCall) Between
[Forms]![frmJobs]![SetCall] And [Forms]![frmJobs]![fldRTSCall])) OR
(((tblJobs.Confirmed)<>"no") AND
((tblJobs.SetCall)<[Forms]![frmJobs]![SetCall]) AND
((tblJobs.fldRTSCall)>[Forms]![frmJobs]![fldRTSCall]))
GROUP BY tblJobsLineItems.[Item Number];

Any ideas?