Need to Make Query Run Faster - SLQ?

[bassstuf via AccessMonster.com]

I need to make my query run faster and I am not sure if any improvements to
my SLQ can help.

It caculates off of 2 very large Tables
Table 1: Formation - 93521 Records (Query - "Form SN")
Table 2: Shipped Serial Numbers - 35437 Records (Query - "Shipped")

Each Table has a corisponding Query that runs that I use to display the
inital data in a field that I have listed as Combo =Part Number&" "&Serial
Number which I use to like the 2 Queries in another Query called Combo that
link.

The Combo Query takes about a minute to run, and I was wondering if there is
any way to improve the speed...I am a novice with SLQ statements, so the most
help would be great.

SELECT [Form SN].[Assembly Part#], [Form SN].[Assembly S/N], [Form SN].
[Warranty Expiration], [Form SN].[Ship List], [Form SN].[Ship Date] AS
[Delivery Date], Shipped.Customer, Shipped.[Customer PO], Shipped.Order AS
[Order Number], Shipped.Line, Shipped.[Ship Confirm Date] AS [Ship Date],
Shipped.[Ship Method], Shipped.AWB AS [Tracking Number], Shipped.Expr1,
DateAdd("yyyy",2,Shipped![Ship Confirm Date]) AS [Warranty Exp], [RMA Query].
[RMA Ship Date], DateDiff("d",[Ship Date],[RMA Ship Date]) AS [RMA Date Diff],
DateAdd("d",730,[RMA Ship Date]) AS [RMA Exp], DateAdd("d",730,[Ship Date])
AS [Prod Exp], IIf([RMA Date Diff]>0,[RMA Exp],[Prod Exp]) AS [Warranty
Experation]
FROM [RMA Query] RIGHT JOIN ([Form SN] INNER JOIN Shipped ON [Form SN].Expr1
= Shipped.Expr1) ON [RMA Query].Expr1 = Shipped.Expr1
WHERE ((([Form SN].[Assembly Part#])=[Forms]![Combo]![Part Number]) AND ((
[Form SN].[Assembly S/N])=[Forms]![Combo]![Serial Number]));

 

[John Spencer]

You would do better to link on the two fields if possible. If you use the
calculated field "Combo" then you lose the ability to use indexes. Indexes
can have a significant effect on the speed of SQL queries.

It is hard to tell you exactly what to do based on your description. Assuming
indexes on Part Number and Serial number

SELECT *
FROM Table1 INNER JOIN Table2
ON Table1.[Part number] = Table2.[Part Number]
AND Table1.[Serial Number] = Table2.[Serial Number]
WHERE Table1.[Part Number]= 'xyz' and Table2.[Serial number] = 'zz123'

will be a lot faster than

SELECT *
FROM Table1 INNER JOIN Table2
ON Table1.[Part number] & " " & Table1.[Serial Number] =
Table2.[Part Number] & " " & Table2.[Serial Number]
WHERE Table1.[Part Number]= 'xyz' and Table2.[Serial number] = 'zz123'

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[david]

Each Table has a corisponding Query that runs that I use to display the

inital data in a field that I have listed as Combo =Part Number&" "&Serial
Number which I use to like the 2 Queries in another Query called Combo
that
link.

That should be two sentences. If you make it two sentences, and fix
the spelling, it will be clearer. As it is, I can only guess what it means.

The Combo Query takes about a minute to run, and I was wondering

A select query shouldn't take a minute to run, unless you join on
calculated expressions. Then it might take a minute to run.

INNER JOIN Shipped ON [Form SN].Expr1
= Shipped.Expr1) ON [RMA Query].Expr1 = Shipped.Expr1

You need to change the design of your tables so that the join fields
are stored in the tables. You should post your table design so that
people can help with that.

(david)

I need to make my query run faster and I am not sure if any improvements to
my SLQ can help.

It caculates off of 2 very large Tables
Table 1: Formation - 93521 Records (Query - "Form SN")
Table 2: Shipped Serial Numbers - 35437 Records (Query - "Shipped")

Each Table has a corisponding Query that runs that I use to display the
inital data in a field that I have listed as Combo =Part Number&" "&Serial
Number which I use to like the 2 Queries in another Query called Combo
that
link.

The Combo Query takes about a minute to run, and I was wondering if there
is
any way to improve the speed...I am a novice with SLQ statements, so the
most
help would be great.

SELECT [Form SN].[Assembly Part#], [Form SN].[Assembly S/N], [Form SN].
[Warranty Expiration], [Form SN].[Ship List], [Form SN].[Ship Date] AS
[Delivery Date], Shipped.Customer, Shipped.[Customer PO], Shipped.Order AS
[Order Number], Shipped.Line, Shipped.[Ship Confirm Date] AS [Ship Date],
Shipped.[Ship Method], Shipped.AWB AS [Tracking Number], Shipped.Expr1,
DateAdd("yyyy",2,Shipped![Ship Confirm Date]) AS [Warranty Exp], [RMA
Query].
[RMA Ship Date], DateDiff("d",[Ship Date],[RMA Ship Date]) AS [RMA Date
Diff],
DateAdd("d",730,[RMA Ship Date]) AS [RMA Exp], DateAdd("d",730,[Ship
Date])
AS [Prod Exp], IIf([RMA Date Diff]>0,[RMA Exp],[Prod Exp]) AS [Warranty
Experation]
FROM [RMA Query] RIGHT JOIN ([Form SN] INNER JOIN Shipped ON [Form
SN].Expr1
= Shipped.Expr1) ON [RMA Query].Expr1 = Shipped.Expr1
WHERE ((([Form SN].[Assembly Part#])=[Forms]![Combo]![Part Number]) AND ((
[Form SN].[Assembly S/N])=[Forms]![Combo]![Serial Number]));

 

[bassstuf via AccessMonster.com]

I do have Queries joined on caculated expressions but do not know how to make
the join fields stored in table...any help will be appreciated

Each Table has a corisponding Query that runs that I use to display the
inital data in a field that I have listed as Combo =Part Number&" "&Serial
Number which I use to like the 2 Queries in another Query called Combo
that
link.

That should be two sentences. If you make it two sentences, and fix
the spelling, it will be clearer. As it is, I can only guess what it means.

The Combo Query takes about a minute to run, and I was wondering

A select query shouldn't take a minute to run, unless you join on
calculated expressions. Then it might take a minute to run.

INNER JOIN Shipped ON [Form SN].Expr1
= Shipped.Expr1) ON [RMA Query].Expr1 = Shipped.Expr1

You need to change the design of your tables so that the join fields
are stored in the tables. You should post your table design so that
people can help with that.

(david)

I need to make my query run faster and I am not sure if any improvements to
my SLQ can help.

[quoted text clipped - 33 lines]

WHERE ((([Form SN].[Assembly Part#])=[Forms]![Combo]![Part Number]) AND ((
[Form SN].[Assembly S/N])=[Forms]![Combo]![Serial Number]));