September 3, 2005 at 2:48 am
Dear all,
I have a problem with query in MS Access. My Query run long time to result beacuse of so many 'Left Join'. Performance is very low.How can I do for that? help me
My Query is in the follwing.
SELECT H1.Commodity,D1.Quantity as [3/4],D2.Quantity AS [4/6],D3.Quantity AS ,D4.Quantity AS [8/12],D5.Quantity AS [13/15],D6.Quantity AS [16/20] ,D7.Quantity AS [21/25]
,D8.Quantity AS [26/30],D9.Quantity AS [31/40],D10.Quantity AS [41/50],D11.Quantity AS [51/60] ,D12.Quantity AS [61/70],D13.Quantity AS [71/90],D14.Quantity AS [91/100],D15.Quantity AS [100/200],D16.Quantity AS ,D17.Quantity AS ,D18.Quantity AS FROM
((((((((((((((((((
(SELECT SD.Species, SD.ProductType, Species.SpDesc+' '+ProductType.TypeDesc AS COMMODITY
FROM (SemiFinishedDTl AS SD INNER JOIN Species ON SD.Species=Species.SpID) INNER JOIN ProductType ON SD.ProductType=ProductType.TypeID
GROUP BY SD.Species, SD.ProductType, Species.SpDesc, ProductType.TypeDesc)
H1
LEFT JOIN
(SELECT SD.Species, SD.ProductType, Size.SizeDesc, Sum(SD.Qty) AS Quantity
FROM SemiFinishedDtl AS SD INNER JOIN [Size] ON SD.Size=Size.SizeID
WHERE Size.SizeDesc='3/4'
GROUP BY SD.Species, SD.ProductType, Size.SizeDesc;
) D1
ON H1.Species=D1.Species and H1.ProductType=D1.ProductType)
LEFT JOIN
(SELECT SD.Species, SD.ProductType, Size.SizeDesc, Sum(SD.Qty) AS Quantity
FROM SemiFinishedDtl AS SD INNER JOIN [Size] ON SD.Size=Size.SizeID
WHERE Size.SizeDesc='4/6'
GROUP BY SD.Species, SD.ProductType, Size.SizeDesc;
) D2
ON H1.Species=D2.Species and H1.ProductType=D2.ProductType)
LEFT JOIN
(SELECT SD.Species, SD.ProductType, Size.SizeDesc, Sum(SD.Qty) AS Quantity
FROM SemiFinishedDtl AS SD INNER JOIN [Size] ON SD.Size=Size.SizeID
WHERE Size.SizeDesc='U/5'
GROUP BY SD.Species, SD.ProductType, Size.SizeDesc;
) D3
ON H1.Species=D3.Species and H1.ProductType=D3.ProductType)
LEFT JOIN
(SELECT SD.Species, SD.ProductType, Size.SizeDesc, Sum(SD.Qty) AS Quantity
FROM SemiFinishedDtl AS SD INNER JOIN [Size] ON SD.Size=Size.SizeID
WHERE Size.SizeDesc='8/12'
GROUP BY SD.Species, SD.ProductType, Size.SizeDesc;
) D4
ON H1.Species=D4.Species and H1.ProductType=D4.ProductType)
LEFT JOIN
(SELECT SD.Species, SD.ProductType, Size.SizeDesc, Sum(SD.Qty) AS Quantity
FROM SemiFinishedDtl AS SD INNER JOIN [Size] ON SD.Size=Size.SizeID
WHERE Size.SizeDesc='13/15'
GROUP BY SD.Species, SD.ProductType, Size.SizeDesc;
) D5
ON H1.Species=D5.Species and H1.ProductType=D5.ProductType)
LEFT JOIN
(SELECT SD.Species, SD.ProductType, Size.SizeDesc, Sum(SD.Qty) AS Quantity
FROM SemiFinishedDtl AS SD INNER JOIN [Size] ON SD.Size=Size.SizeID
WHERE Size.SizeDesc='16/20'
GROUP BY SD.Species, SD.ProductType, Size.SizeDesc;
) D6
ON H1.Species=D6.Species and H1.ProductType=D6.ProductType)
LEFT JOIN
(SELECT SD.Species, SD.ProductType, Size.SizeDesc, Sum(SD.Qty) AS Quantity
FROM SemiFinishedDtl AS SD INNER JOIN [Size] ON SD.Size=Size.SizeID
WHERE Size.SizeDesc='21/25'
GROUP BY SD.Species, SD.ProductType, Size.SizeDesc;
) D7
ON H1.Species=D7.Species and H1.ProductType=D7.ProductType)
LEFT JOIN
(SELECT SD.Species, SD.ProductType, Size.SizeDesc, Sum(SD.Qty) AS Quantity
FROM SemiFinishedDtl AS SD INNER JOIN [Size] ON SD.Size=Size.SizeID
WHERE Size.SizeDesc='26/30'
GROUP BY SD.Species, SD.ProductType, Size.SizeDesc;
) D8
ON H1.Species=D8.Species and H1.ProductType=D8.ProductType)
LEFT JOIN
(SELECT SD.Species,SD.ProductType,Size.SizeDesc,Sum(SD.Qty) as Quantity FROM SemiFinishedDtl SD INNER JOIN [Size] ON SD.Size=Size.SizeID
WHERE Size.SizeDesc='31/40'
GROUP BY SD.Species,SD.ProductType,Size.SizeDesc) D9
ON H1.Species=D9.Species and H1.ProductType=D9.ProductType)
LEFT JOIN
(SELECT SD.Species,SD.ProductType,Size.SizeDesc,Sum(SD.Qty) as Quantity FROM SemiFinishedDtl SD INNER JOIN [Size] ON SD.Size=Size.SizeID
WHERE Size.SizeDesc='41/50'
GROUP BY SD.Species,SD.ProductType,Size.SizeDesc) D10
ON H1.Species=D10.Species and H1.ProductType=D10.ProductType)
LEFT JOIN
(SELECT SD.Species,SD.ProductType,Size.SizeDesc,Sum(SD.Qty) as Quantity FROM SemiFinishedDtl SD INNER JOIN [Size] ON SD.Size=Size.SizeID
WHERE Size.SizeDesc='51/60'
GROUP BY SD.Species,SD.ProductType,Size.SizeDesc) D11
ON H1.Species=D11.Species and H1.ProductType=D11.ProductType)
LEFT JOIN
(SELECT SD.Species,SD.ProductType,Size.SizeDesc,Sum(SD.Qty) as Quantity FROM SemiFinishedDtl SD INNER JOIN [Size] ON SD.Size=Size.SizeID
WHERE Size.SizeDesc='61/70'
GROUP BY SD.Species,SD.ProductType,Size.SizeDesc) D12
ON H1.Species=D12.Species and H1.ProductType=D12.ProductType)
LEFT JOIN
(SELECT SD.Species,SD.ProductType,Size.SizeDesc,Sum(SD.Qty) as Quantity FROM SemiFinishedDtl SD INNER JOIN [Size] ON SD.Size=Size.SizeID
WHERE Size.SizeDesc='71/90'
GROUP BY SD.Species,SD.ProductType,Size.SizeDesc) D13
ON H1.Species=D13.Species and H1.ProductType=D13.ProductType)
LEFT JOIN
(SELECT SD.Species,SD.ProductType,Size.SizeDesc,Sum(SD.Qty) as Quantity FROM SemiFinishedDtl SD INNER JOIN [Size] ON SD.Size=Size.SizeID
WHERE Size.SizeDesc='91/100'
GROUP BY SD.Species,SD.ProductType,Size.SizeDesc) D14
ON H1.Species=D14.Species and H1.ProductType=D14.ProductType)
LEFT JOIN
TRpt100200 D15
ON H1.Species=D15.Species and H1.ProductType=D15.ProductType)
LEFT JOIN
(SELECT SD.Species, SD.ProductType, Size.SizeDesc, Sum(SD.Qty) AS Quantity
FROM SemiFinishedDtl AS SD INNER JOIN [Size] ON SD.Size=Size.SizeID
WHERE Size.SizeDesc='B 1'
GROUP BY SD.Species, SD.ProductType, Size.SizeDesc;
) D16
ON H1.Species=D16.Species and H1.ProductType=D16.ProductType)
LEFT JOIN
(SELECT SD.Species, SD.ProductType, Size.SizeDesc, Sum(SD.Qty) AS Quantity
FROM SemiFinishedDtl AS SD INNER JOIN [Size] ON SD.Size=Size.SizeID
WHERE Size.SizeDesc='B 2'
GROUP BY SD.Species, SD.ProductType, Size.SizeDesc;
) D17
ON H1.Species=D17.Species and H1.ProductType=D17.ProductType)
LEFT JOIN
(SELECT SD.Species, SD.ProductType, Size.SizeDesc, Sum(SD.Qty) AS Quantity
FROM SemiFinishedDtl AS SD INNER JOIN [Size] ON SD.Size=Size.SizeID
WHERE Size.SizeDesc='B 3'
GROUP BY SD.Species, SD.ProductType, Size.SizeDesc;
) D18
ON H1.Species=D18.Species and H1.ProductType=D18.ProductType)
look forward ...
September 3, 2005 at 7:03 am
If I read this correctly
H1 gives a list of products identified by species.ID and producttype
D1,D2... gives the amount of a specific size
and you want it presented in 1 line species & producttype its different quantities by size
1)For the D... Size.SizeDesc is not necessary in the select statement
2)What happens if you split you query in multiple queries in Access?
1 for H1
1 for D1, 1 for D2,...
And link them all together?
Is the Access performance analyzer then capable of finding any index suggestions?
3) There is an index on SD.Size=Size.SizeID ?
September 4, 2005 at 10:36 pm
Thank you for your reply.
I get data from the master table SemiFinishDtl, and Reference Tables Species,Size,ProductType.
in SemiFinishedDtl, it includes species,Size,ProductType are foreign Keys
I want to create view that data of Size from SemiFinishedDtl to columns in new view.
I tried to use CrossTab. but it is not ok.
This query give the result that I need but it has bad peroformance.
What any other way can I use?
September 5, 2005 at 3:08 am
why cant you use a crosstab?
Do you have a small example set of data + the results you want from it?
How about processing your field with your values in it into a second calculated field and crosstabbing or grouping/etc. on that instead?
martin
September 16, 2005 at 4:28 am
Hi all
Sorry for my late reply. I have solved this problem in programming C#.Net , not in MS Access. Thanks all for your advice.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply