473,289 Members | 1,810 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

Slow Append Query

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?

Oct 29 '06 #1
4 5718
Not sure I understand the WHERE clause.

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

WHERE (tblJobs.Confirmed <"no")
AND ((tblJobs.SetCall Between [Forms]![frmJobs]![SetCall]
And [Forms]![frmJobs]![fldRTSCall])
OR (tblJobs.fldRTSCall Between [Forms]![frmJobs]![SetCall]
And [Forms]![frmJobs]![fldRTSCall])
OR ((tblJobs.SetCall < [Forms]![frmJobs]![SetCall])
AND (tblJobs.fldRTSCall [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.Confirmed <"no")
AND ((tblJobs.SetCall Is Not Null)
OR (tblJobs.fldRTSCall 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********@gmail.comwrote in message
news:11**********************@h48g2000cwc.googlegr oups.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, 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?

Oct 29 '06 #2
pm********@gmail.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, 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?
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?

Oct 29 '06 #3
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********@gmail.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, 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?
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?
Oct 29 '06 #4

pm********@gmail.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********@gmail.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, 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?
>
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

Oct 29 '06 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Yonatan Goraly | last post by:
I have a query that uses the same view 6 times. It seems that the database engine is calculating the view each time. The result is very poor performance. The same query takes 2 sec with MS SQL,...
4
by: sherkozmo | last post by:
SQL2000 - AccessXP I built an adp file with a stored procedure from SQL as follows: SELECT * FROM Z_mis_sjk_job_code_access WHERE job_code=@JobCode UNION ALL SELECT * FROM...
2
by: Danny | last post by:
I want to extract a subset of fields from one table into another the master table has many fields the subset has about half, but still many. Is there a way I can just append the master into the...
1
by: David Barger | last post by:
Greetings, It appears that an Append Query I run in Access XP is randomly failing to append a field. I have payroll data being entered into a payroll database. This data is exported daily to...
4
by: Bri | last post by:
Hi, First let me explain the process I have going on, then I'll address the problems I'm having: 1) Insert records in a temp table using a query 2) Using a query that joins the temp table with...
2
by: Otto Blomqvist | last post by:
Hello ! I have two tables (which contains individual months' data). One of them contains 500 thousand records and the other one about 40k, 8 columns. When I do a simple query on them...
0
by: Rog | last post by:
I have two append queries that each append records from an SQL table into a temporary Access table in the frontend. All of a sudden one of them has become very slow - it takes a minute before the...
4
by: Ivan Voras | last post by:
I have a simple network protocol client (it's a part of this: http://sqlcached.sourceforge.net) implemented in Python, PHP and C. Everything's fine, except that the Python implementation is the...
4
by: Rick | last post by:
Access2003 in XP I'm using the code below to append any new records from (tbl_From_Mainframe) into (tbl_Appended_Data). It takes more than a minute to search 7000 records for a dozen new records....
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.