due to formula "IIf ( "argue", 'true', 'false')" makes my query slow

[mukesh]

One another problem

I have used a formula in the calculated field of a query as -

IIf( [A] <=[b], (IIf([A]<=[C], [A], [C]), (IIf ([b]<=[C], [C], [b]))

the above formula is working properly to give the lowest of [A], [b] and [C] but the problem is that the query is some slow as compared to other query not having such formula. At present, there is only one record in the query to calculate and to show. I fear if what will happen if there will be lots of records.

what to do

can i use - min([A], [b], [C]) - like expression

[PEB]

Strange!

In one record to have slow performance! Expressions like this I use in big tables like 500 000 records and I don't have a big slow down!

Maybe IIF isn't your problem?

:)

[mukesh]

Strange!

In one record to have slow performance! Expressions like this I use in big tables like 500 000 records and I don't have a big slow down!

Maybe IIF isn't your problem?

:)

I expressed what i felt. Well your post give me confidence on this type of formula. I want to aske whether your table contians - IIf (IIf (IIf())) - type of formula which is complex.

[PEB]

See this! This returns result no for more than a few seconds! And in the concerned tables / queries there is 30000 - 40000 records!

SELECT CROSSTAB_NORMS.[0] AS Продукт, arround(IIf([4]="Qty_Piece",Sum([Cadeaux_Commandes01].[Qty])*[CROSSTAB_NORMS].[Qty],Sum([Cadeaux_Commandes01].[Qty])/param_info(show_el_id(16,[1]),0,"COL01N0012")*[CROSSTAB_NORMS].[Qty]),1) AS Result
FROM Cadeaux_Commandes01 INNER JOIN CROSSTAB_NORMS ON Cadeaux_Commandes01.ART = CROSSTAB_NORMS.[1]
WHERE (((CROSSTAB_NORMS.[2])="Conditionnement") AND ((CROSSTAB_NORMS.[3])="Atelier") AND ((IIf(([5] Is Null) Or ([5]="All_markets"),True,IIf(Mid([5],1,2)=Mid([n°Cde],1,2),True,False)))=True))
GROUP BY CROSSTAB_NORMS.[0], CROSSTAB_NORMS.[1], CROSSTAB_NORMS.[4], CROSSTAB_NORMS.Qty;
 

[mukesh]

See this! This returns result no for more than a few seconds! And in the concerned tables / queries there is 30000 - 40000 records!

SELECT CROSSTAB_NORMS.[0] AS Продукт, arround(IIf([4]="Qty_Piece",Sum([Cadeaux_Commandes01].[Qty])*[CROSSTAB_NORMS].[Qty],Sum([Cadeaux_Commandes01].[Qty])/param_info(show_el_id(16,[1]),0,"COL01N0012")*[CROSSTAB_NORMS].[Qty]),1) AS Result
FROM Cadeaux_Commandes01 INNER JOIN CROSSTAB_NORMS ON Cadeaux_Commandes01.ART = CROSSTAB_NORMS.[1]
WHERE (((CROSSTAB_NORMS.[2])="Conditionnement") AND ((CROSSTAB_NORMS.[3])="Atelier") AND ((IIf(([5] Is Null) Or ([5]="All_markets"),True,IIf(Mid([5],1,2)=Mid([n°Cde],1,2),True,False)))=True))
GROUP BY CROSSTAB_NORMS.[0], CROSSTAB_NORMS.[1], CROSSTAB_NORMS.[4], CROSSTAB_NORMS.Qty;
 

Oh its amazing. Now I think that time to be taken by MS Access for such type of query is one or two seconds irrespective of the numbers of records. Thanks I will go ahead with this formula. thanks a lot. I will first feed numbers of recods in the table and watch the speed. Thax a lot