Slow Append Query

[pmacdiddie]

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, tblJobsLineItem s has 150,000
records.

INSERT INTO tmpRaItemsInUse Totals ( [Item Number], SumOfQuantity )
SELECT DISTINCTROW tblJobsLineItem s.[Item Number],
Sum(tblJobsLine Items.Quantity) AS SumOfQuantity
FROM tblJobs INNER JOIN tblJobsLineItem s ON tblJobs.Job_Num =
tblJobsLineItem s.Job_Num
WHERE (((tblJobs.Conf irmed)<>"no") AND ((tblJobs.SetCa ll) Between
[Forms]![frmJobs]![SetCall] And [Forms]![frmJobs]![fldRTSCall])) OR
(((tblJobs.Conf irmed)<>"no") AND ((tblJobs.fldRT SCall) Between
[Forms]![frmJobs]![SetCall] And [Forms]![frmJobs]![fldRTSCall])) OR
(((tblJobs.Conf irmed)<>"no") AND
((tblJobs.SetCa ll)<[Forms]![frmJobs]![SetCall]) AND
((tblJobs.fldRT SCall)>[Forms]![frmJobs]![fldRTSCall]))
GROUP BY tblJobsLineItem s.[Item Number];

Any ideas?

[Allen Browne]

Not sure I understand the WHERE clause.

All phrases have
tblJobs.Confirm ed <"no"
It will therefore be more effient to break that out and use:

WHERE (tblJobs.Confir med <"no")
AND ((tblJobs.SetCa ll Between [Forms]![frmJobs]![SetCall]
And [Forms]![frmJobs]![fldRTSCall])
OR (tblJobs.fldRTS Call Between [Forms]![frmJobs]![SetCall]
And [Forms]![frmJobs]![fldRTSCall])
OR ((tblJobs.SetCa ll < [Forms]![frmJobs]![SetCall])
AND (tblJobs.fldRTS Call [Forms]![frmJobs]![fldRTSCall])))

What I don't understand is that the criteria accepts SetCall between the 2
values in the form, or beyond those values. The effect of that would be to
accept all non-null values for this field? Could you use:

WHERE (tblJobs.Confir med <"no")
AND ((tblJobs.SetCa ll Is Not Null)
OR (tblJobs.fldRTS Call Between [Forms]![frmJobs]![SetCall]
And [Forms]![frmJobs]![fldRTSCall]))

Other suggestions:
- Make sure you have indexes on Confirmed, SetCall, fldRTSCall, and [Item
Number].

- Make sure you have a relation with Referential Integrity between the 2
tables (so the foreign key is indexed.)

- Any chance of using a yes/no field instead of a Text field for Confirmed?

- Unless SetCall and fldRTSCall are Text fields, declare the parameters.
Choose Parameters in the Query menu, and enter 2 rows into the dialog for:
[Forms]![frmJobs]![SetCall]
[Forms]![frmJobs]![fldRTSCall]
Alternatively, concatenate the values into the string, and execute it.

If it is still slow, see Tony Toews' Performance FAQ at:
http://www.granite.ab.ca/access/performancefaq.htm

--
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********@gma il.comwrote in message
news:11******** **************@ h48g2000cwc.goo glegroups.com.. .

>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, tblJobsLineItem s has 150,000
records.

INSERT INTO tmpRaItemsInUse Totals ( [Item Number], SumOfQuantity )
SELECT DISTINCTROW tblJobsLineItem s.[Item Number],
Sum(tblJobsLine Items.Quantity) AS SumOfQuantity
FROM tblJobs INNER JOIN tblJobsLineItem s ON tblJobs.Job_Num =
tblJobsLineItem s.Job_Num
WHERE (((tblJobs.Conf irmed)<>"no") AND ((tblJobs.SetCa ll) Between
[Forms]![frmJobs]![SetCall] And [Forms]![frmJobs]![fldRTSCall])) OR
(((tblJobs.Conf irmed)<>"no") AND ((tblJobs.fldRT SCall) Between
[Forms]![frmJobs]![SetCall] And [Forms]![frmJobs]![fldRTSCall])) OR
(((tblJobs.Conf irmed)<>"no") AND
((tblJobs.SetCa ll)<[Forms]![frmJobs]![SetCall]) AND
((tblJobs.fldRT SCall)>[Forms]![frmJobs]![fldRTSCall]))
GROUP BY tblJobsLineItem s.[Item Number];

Any ideas?

[salad]

pm********@gmai l.com wrote:

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, tblJobsLineItem s has 150,000
records.

INSERT INTO tmpRaItemsInUse Totals ( [Item Number], SumOfQuantity )
SELECT DISTINCTROW tblJobsLineItem s.[Item Number],
Sum(tblJobsLine Items.Quantity) AS SumOfQuantity
FROM tblJobs INNER JOIN tblJobsLineItem s ON tblJobs.Job_Num =
tblJobsLineItem s.Job_Num
WHERE (((tblJobs.Conf irmed)<>"no") AND ((tblJobs.SetCa ll) Between
[Forms]![frmJobs]![SetCall] And [Forms]![frmJobs]![fldRTSCall])) OR
(((tblJobs.Conf irmed)<>"no") AND ((tblJobs.fldRT SCall) Between
[Forms]![frmJobs]![SetCall] And [Forms]![frmJobs]![fldRTSCall])) OR
(((tblJobs.Conf irmed)<>"no") AND
((tblJobs.SetCa ll)<[Forms]![frmJobs]![SetCall]) AND
((tblJobs.fldRT SCall)>[Forms]![frmJobs]![fldRTSCall]))
GROUP BY tblJobsLineItem s.[Item Number];

Any ideas?

Are Item Number, Confirmed, SetCall, fldRTSCAll all indexed?

Once the append is performed, do you need to requery the dropdown? Do
you then display 10,000+ records in the dropdown?

Have you executed this query by itself?

What happens if you separate the insert from the Select? Maybe make the
select a query, then do the insert off the query.

How many records do you append each time you run the query? It appears
you append all summary line item records. That sounds ineffecient. Why
not do the append just for the job?

[pmacdiddie]

Thanks,

This only returns 10 - 20 rows. I will break out into select and
benchmark the time. Everything is indexed that is criteria or sorted.
I will also try Tony's adding Relationship as there is none there.

P

salad wrote:

pm********@gmai l.com wrote:

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, tblJobsLineItem s has 150,000
records.

INSERT INTO tmpRaItemsInUse Totals ( [Item Number], SumOfQuantity )
SELECT DISTINCTROW tblJobsLineItem s.[Item Number],
Sum(tblJobsLine Items.Quantity) AS SumOfQuantity
FROM tblJobs INNER JOIN tblJobsLineItem s ON tblJobs.Job_Num =
tblJobsLineItem s.Job_Num
WHERE (((tblJobs.Conf irmed)<>"no") AND ((tblJobs.SetCa ll) Between
[Forms]![frmJobs]![SetCall] And [Forms]![frmJobs]![fldRTSCall])) OR
(((tblJobs.Conf irmed)<>"no") AND ((tblJobs.fldRT SCall) Between
[Forms]![frmJobs]![SetCall] And [Forms]![frmJobs]![fldRTSCall])) OR
(((tblJobs.Conf irmed)<>"no") AND
((tblJobs.SetCa ll)<[Forms]![frmJobs]![SetCall]) AND
((tblJobs.fldRT SCall)>[Forms]![frmJobs]![fldRTSCall]))
GROUP BY tblJobsLineItem s.[Item Number];

Any ideas?

Are Item Number, Confirmed, SetCall, fldRTSCAll all indexed?

Once the append is performed, do you need to requery the dropdown? Do
you then display 10,000+ records in the dropdown?

Have you executed this query by itself?

What happens if you separate the insert from the Select? Maybe make the
select a query, then do the insert off the query.

How many records do you append each time you run the query? It appears
you append all summary line item records. That sounds ineffecient. Why
not do the append just for the job?

[pmacdiddie]

pm********@gmai l.com wrote:

Thanks,

This only returns 10 - 20 rows. I will break out into select and
benchmark the time. Everything is indexed that is criteria or sorted.
I will also try Tony's adding Relationship as there is none there.

P

salad wrote:

pm********@gmai l.com wrote:

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, tblJobsLineItem s has 150,000
records.
>
INSERT INTO tmpRaItemsInUse Totals ( [Item Number], SumOfQuantity )
SELECT DISTINCTROW tblJobsLineItem s.[Item Number],
Sum(tblJobsLine Items.Quantity) AS SumOfQuantity
FROM tblJobs INNER JOIN tblJobsLineItem s ON tblJobs.Job_Num =
tblJobsLineItem s.Job_Num
WHERE (((tblJobs.Conf irmed)<>"no") AND ((tblJobs.SetCa ll) Between
[Forms]![frmJobs]![SetCall] And [Forms]![frmJobs]![fldRTSCall])) OR
(((tblJobs.Conf irmed)<>"no") AND ((tblJobs.fldRT SCall) Between
[Forms]![frmJobs]![SetCall] And [Forms]![frmJobs]![fldRTSCall])) OR
(((tblJobs.Conf irmed)<>"no") AND
((tblJobs.SetCa ll)<[Forms]![frmJobs]![SetCall]) AND
((tblJobs.fldRT SCall)>[Forms]![frmJobs]![fldRTSCall]))
GROUP BY tblJobsLineItem s.[Item Number];
>
Any ideas?
>

Are Item Number, Confirmed, SetCall, fldRTSCAll all indexed?

Once the append is performed, do you need to requery the dropdown? Do
you then display 10,000+ records in the dropdown?

Have you executed this query by itself?

What happens if you separate the insert from the Select? Maybe make the
select a query, then do the insert off the query.

How many records do you append each time you run the query? It appears
you append all summary line item records. That sounds ineffecient. Why
not do the append just for the job?

Wow, what a difference. This difference between the Select and Append
was negligible. Re-arranging the syntax relating to the "No" criteria
was also negligible.

Adding the relationship between the tables took the elasped time from
over 5 seconds, to 0.515625 secs. That is great. Many thanks to
Allen and the rest. And sorry about the mis-quote above.

P