Query Speed issue 1

[bkdobs]

I have a Price table related to part number where each part could have prices from different vendors ... the price table was set up with an active flag, Vendor and buy and sell prices. I made a query which creates a distinct record of 2 buy and sell costs plus the minimum between them so it has 6 costs all together but am finding this is extremly slow ... without making the price table into a flat file with all 6 possible prices is there a better way to associated this data?

Part# Buy$ Sell$ Vendor
A 10 12 X
A 9 11 Y


Query
Part# B1 S1 B2 S2 MB MS
A 10 12 9 11 9 11


The query is used by a screen which can be toggled between
Vendor X, Y or Minimum ... The intent is that at some point there could be more vendors added so I am trying to minimize rebuilding logic surrounding these prices

I am using the following query;

SELECT DISTINCT x.part, Nz((SELECT A.buy FROM Price A WHERE A.part=x.part AND A.vendor="X" and a.active=true),-1) AS wBuy, Nz((SELECT A.Sell FROM Price A WHERE A.part=x.part AND A.vendor="X" and a.active=true),-1) AS wSell, Nz((SELECT A.buy FROM Price A WHERE A.part=x.part AND A.vendor="Y" and a.active=true),-1) AS bBuy, Nz((SELECT A.sell FROM Price A WHERE A.part=x.part AND A.vendor="Y" and a.active=true),-1) AS bSell, Nz((fMin(wBuy,bBuy)),0) AS mBuy, Nz((fMin(wSell,bSell)),0) AS mSell
FROM part AS x
WHERE (((x.part)=getglobal("Part")));

 

[PHV]

You may try something like this:
SELECT x.part, Nz(A.buy,-1) AS wBuy, Nz(A.Sell,-1) AS wSell, Nz(B.buy,-1) AS bBuy, Nz(B.sell,-1) AS bSell
,Nz((fMin(wBuy,bBuy)),0) AS mBuy, Nz((fMin(wSell,bSell)),0) AS mSell
FROM (part AS x
LEFT JOIN (SELECT part, buy, Sell FROM Price WHERE vendor='X' AND active=True
) AS A ON x.part=A.part)
LEFT JOIN (SELECT part, buy, Sell FROM Price WHERE vendor='Y' AND active=True
) AS B ON x.part=B.part
WHERE x.part=getglobal('Part')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bkdobs]

After digging deaper I think I found the speed killer ... the main subform query has an order by clause which is taking 5 seconds to process ... because a requery is forced on each user keystroke I am getting this annoying pause.

Even though the field in the order by is indexed and I have a Select top N blah it appears that access wants to completly reorder the table before it can select anything ... I thought I would just change this field to be the primary index but it has duplicates ... I have vendors that either choose to use same part number for a different part or the same part as another vendor.

I was thinking of using a compond index but not sure how that will work in with the existing screens.

would you have any other suggestions?

 

[PHV]

would you have any other suggestions?
Without the SQL code of the main subform query, no.

 

[bkdobs]

SELECT OEMCross.OEM, OEMCross.OEMNUM, OEMCross.part, Price.buy, Price.sell, Price.PriceType, Price.PrintFlg, Price.PictureFile AS Picture
FROM Price INNER JOIN OEMCross ON Price.part = OEMCross.part
WHERE (((Left([oemCross].[srchoemnum],Len(getGlobal("OemNum")))=getglobal("OEMNUM"))<>False) AND((Price.Deleted)=False))
ORDER BY OEMCross.OEMNUM;

srchoemnum is a pure alphanumeric version of oemnum ... part is a common part number shared by several oem's

OemCross has an autonumber as its primary key.

 

[PHV]

You may try this:
FROM OEMCross INNER JOIN Price ON OEMCross.part = Price.part
WHERE OEMCross.srchoemnum Like getGlobal("OemNum") & '*' AND Price.Deleted=False

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bkdobs]

OK being a SQL newbie I tried to apply one of the examples above to another table but cannot get the SQL editor to save my code ... I currently am using an ADO object with a for next loop to resolve this record but thinking a query should work alot quicker?

I have a table that has;

Part as string, PartType as integer, price as double
Primary index is Part & part-type

there are 10 part-types but not every part has all 10 prices

I want to have a select query that uses part as a lookup and returns all 10 values

select nz(A.price,0) as prcA,
nz(B.price,0) as prcB,
nz(C.price,0) as prcC,
nz(D.price,0) as prcD,
nz(E.price,0) as prcE,
nz(F.price,0) as prcF,
nz(G.price,0) as prcG,
nz(H.price,0) as prcH,
nz(I.price,0) as prcI,
nz(J.price,0) as prcJ
FROM ??? A.PartType = 0,
B.PartType = 1,
C.PartType = 2,
D.PartType = 3,
E.PartType = 4,
F.PartType = 5,
G.PartType = 6,
H.PartType = 7,
I.PartType = 8,
J.PartType = 9
WHERE ?.Part = getPart("");

 

[bkdobs]

I posted a variation to this question in the SQL forum and the solution is to use a Crosstab ... TEK-TIPS rocks!