467,116 Members | 1,221 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,116 developers. It's quick & easy.

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

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
Sep 14 '06 #1
  • viewed: 3187
Share:
4 Replies
PEB
Expert 1GB
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?

:)
Sep 14 '06 #2
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.
Sep 14 '06 #3
PEB
Expert 1GB
PEB
See this! This returns result no for more than a few seconds! And in the concerned tables / queries there is 30000 - 40000 records!

Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM Cadeaux_Commandes01 INNER JOIN CROSSTAB_NORMS ON Cadeaux_Commandes01.ART = CROSSTAB_NORMS.[1]
  3. 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))
  4. GROUP BY CROSSTAB_NORMS.[0], CROSSTAB_NORMS.[1], CROSSTAB_NORMS.[4], CROSSTAB_NORMS.Qty;
  5.  
Sep 14 '06 #4
See this! This returns result no for more than a few seconds! And in the concerned tables / queries there is 30000 - 40000 records!

Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM Cadeaux_Commandes01 INNER JOIN CROSSTAB_NORMS ON Cadeaux_Commandes01.ART = CROSSTAB_NORMS.[1]
  3. 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))
  4. GROUP BY CROSSTAB_NORMS.[0], CROSSTAB_NORMS.[1], CROSSTAB_NORMS.[4], CROSSTAB_NORMS.Qty;
  5.  
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
Sep 14 '06 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

9 posts views Thread by Ed_No_Spam_Please_Weber@Weber_Spam_Not_Enterprises | last post: by
5 posts views Thread by Ellen Manning | last post: by
reply views Thread by innocentchick@gmail.com | last post: by
9 posts views Thread by Bob Darlington | last post: by
2 posts views Thread by Ukar | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.