Need help to simplify query

[VBANewBie :)]

Hello guys , I have a query which need to simplify and still give the same result but faster , Please find attached query " Result " which joined with 4 sub queries , I need to cancel those 4 sub queries and still get the same result.
Note: The attached database is a sample db , the main db became UGLY because of so many queries depend on the same way of joining Basically depend on one or two tables but with multiple criteria.

Thanks in Advance

 

[NauticalGent]

Giving this thread a "bump" as it seems to have fallen through the cracks. I cannot take a look until later today but somebody else might see it and assist.

 

[arnelgp]

see Query1.

i think, your technique, is much more acceptable.

 

[Uncle Gizmo]

Giving this thread a "bump" as it seems to have fallen through the cracks.

Too much Red Wine?

 

[NauticalGent]

Too much Red Wine?

Always!

 

[VBANewBie :)]

see Query1.

i think, your technique, is much more acceptable.

Thanks for reply , I agree with you , my way is more acceptable because we are looking for a way to make it faster and i don’t think your way will pull it .
But in the end my technique caused a disaster here

Problem with very slow query

Hi Guys , I have a very slow query (Even slower in splitted database_be) tried every thing to speed it up but in no vain , Please find attached the query mentioned name ResultFinal . Thanks in advance

www.access-programmers.co.uk

Hope someone can give us a better idea , Thanks

 

[VBANewBie :)]

Up

 

[arnelgp]

you can Remove the Grouping on sub1, sub2, sub3, sub4 queries.
this will make your query much faster.

 

[MajP]

Why do you need to get rid of the "sub queries"? By the way those are not subqueries, those are intermediate queries. @arnelgp demonstrated this with subqueries in his first reply and that will be super slow. There is no way if you remove the grouping as @arnelgp stated and kept your design that this would not be very fast.

 

[VBANewBie :)]

you can Remove the Grouping on sub1, sub2, sub3, sub4 queries.
this will make your query much faster.

Ok I will , Thanks

 

[VBANewBie :)]

Why do you need to get rid of the "sub queries"? By the way those are not subqueries, those are intermediate queries. @arnelgp demonstrated this with subqueries in his first reply and that will be super slow. There is no way if you remove the grouping as @arnelgp stated and kept your design that this would not be very fast.

Thanks for the information , This design is very slow already with considerable set of data , Thats why i’m looking for new design to make it faster , Could you please look at this thread you will see the result of my design so slow

Problem with very slow query

Hi Guys , I have a very slow query (Even slower in splitted database_be) tried every thing to speed it up but in no vain , Please find attached the query mentioned name ResultFinal . Thanks in advance

www.access-programmers.co.uk

Thanks in Advance

 

[MajP]

I that thread there was a lot of suggestions made. Do you have an updated database that incorporated those fixes?

 

[cheekybuddha]

Add an index to Table1.PO.

That will speed things up.

None of the sub/intermediate queries require any GROUPing.

 

[MajP]

I think if you want help you need to re state your question very clearly and provide a concrete version of the database. This simple query in no way would be slow even with the grouping left and without proper indices. Remove the grouping and index the table will help, but I sense that is not the real question. So this example really provides no insight into whatever you are really asking.

 

[Minty]

As the others have said - remove the grouping - it's not doing anything.
In addition to the index on Table1.PO , add an index also to the [Type] field.

Without seeing a lot more data and how this is actually being run, I don't see much other scope for changing it.

 

[VBANewBie :)]

ok thanks to all , I will try in the main database and see how it goes

 

[VBANewBie :)]

Guys someone gave me this solution (Thanks a lot to him) would it be faster than my queries ? (In Theory)

SELECT t2.PoNumber
     , Nz(MAX(IIF(t1.Type='Material', t1.Code, NULL)), 'None') AS MatCode
     , Nz(MAX(IIF(t1.Type='Material', t1.Item, NULL)), 'None') AS Mat
     , MAX(IIF(t1.Type='Material', t1.Cons, NULL)) AS MatCons

     , Nz(MAX(IIF(t1.Type='MasterPatch', t1.Code, NULL)), 'None') AS MastCode
     , Nz(MAX(IIF(t1.Type='MasterPatch', t1.Item, NULL)), 'None') AS Masterr
     , MAX(IIF(t1.Type='MasterPatch', t1.Cons, NULL)) AS MasterCons

     , Nz(MAX(IIF(t1.Type='Stretch', t1.Code, NULL)), 'None') AS StrCode
     , Nz(MAX(IIF(t1.Type='Stretch', t1.Item, NULL)), 'None') AS Str
     , MAX(IIF(t1.Type='Stretch', t1.Cons, NULL)) AS StrCons

     , Nz(MAX(IIF(t1.Type='Package', t1.Code, NULL)), 'None') AS PacCode
     , Nz(MAX(IIF(t1.Type='Package', t1.Item, NULL)), 'None') AS Package
     , MAX(IIF(t1.Type='Package', t1.Cons, NULL)) AS PacCons

     , Nz(IIF([MatCons] <> 0, [MasterCons] / [MatCons], NULL), 0) AS MasterPercent

FROM Table2 t2
LEFT JOIN Table1 t1 ON t2.PoNumber = t1.Po
GROUP BY t2.PoNumber

Thanks

 

[MajP]

I highly doubt it.
Your original design used the basic of basic select queries with some aliases. Now you have 12, IIF functions and then taking the Max of that. Your original design properly indexed without the grouping should be nearly instantaneous even with 100s of thousands of records. It is about as simple as a query can get.

 

[VBANewBie :)]

I highly doubt it.
Your original design used the basic of basic select queries with some aliases. Now you have 12, IIF functions and then taking the Max of that. Your original design properly indexed without the grouping should be nearly instantaneous even with 100s of thousands of records. It is about as simple as a query can get.

Thanks for your reply , I doubted it as well so i will stick to my basic design with removing grouping and try to add index to the filtered field , Thanks for your patience