if statments with select case making query slow

[TIbbs]

I have a query that returns a few calculated fields and also uses functions, I am having a problem with the speed that the query runs.
Part of the data is external to our system, since we receive it from the supplier in an Excel spreadsheet and as they are constantly changing the way they assign codes to their products, many of the queries need on the fly functions to assign the values to the filed columns.
One of my queries has the following fields:

Field1--- |Description 1----------|--Field2-|----Description2
A --------|(Myfunction([Level1])|----A ----|----Myfunction([Field1], [Field2])
B --------|-----------------------|----B-----|
C --------|-----------------------|----Z-----|

The first function Myfunction(level1) is not a problem since that for every letter I assign escription using select case statements.

The second function is more complicated and is where i am getting a bit stuck. the code is as follow:

Public Function Myfunction(strField1 As String, strField2 As String) As String

If strField1 = "A" Or strField1 = "B" Or strField1 = "C" Or strField1 = "D" Then

Select Case strField2
Case "A"
Myfunction = "Text1"

Case "B"
Myfunction = "Text2"

Case "C"
Myfunction = "Text3"

Case Else
Myfunction = " "
End Select
Else
Myfunction= " "
End If

End Function

Problem is that I noticed that the query runs a lot slower since I started using the If statement, that was not the case with the first function all created with a Select Case, and I still have another 5 if statements to programme all ranging to up to 8 case declarations for every description.
So I would have:

If Field1=E OR F OR H
case statement
end case

else
If Field1=M OR N OR P
case statement
end case
.
.
.
.
end If
What would be the best way to tackle this?

 

[namliam]

If you are looking for pure speed IIF is the way to go in the SQL itself tho it is much harder to maintain and read.

Functions will be slow...
You can try using
If ... then
elseif ... then
elseif ... then
endif

Tho in your example you are putting in the some stuff in your case as in the if... so why do it twice?

 

[TIbbs]

problem is every letter has it's own description. It's all dependent on a code that the supplier uses, but as they change all the time we decided to do them on the fly and change them with VBA for ease of maintenance, . So the code goes :

AAAA

• field 1 is assigned the first letter of the code that applies to a specific description of the product. The letters of the code goe from A-S and some start with numbers from 1-9.
• For each first letter a description 1 is attached to it.
• Field2 is assigned the 2nd letter of the code and they go from A-Z but they are dependent on the first letter to give the correct Description2. So a product that has the 1st letter A followed by any letters A-Z will have a different Description2 from a product with initial letter S followed by any letters from A-Z.

The third and fourth letter are dependent on the first letter as well and will have their own description as well.
A temp table is then created from that query which is used for reporting.

 

[namliam]

How about making a table out of it? easy to maintain and FAST as a bat out of hell

 

[Pat Hartman]

My choice is a lookup table also. You will be amazed at the speed difference.

 

[DCrake]

Simple Software Solutions

As a postscript to this issue mailman suggested using IIF comparison, this actually would run slower as immediate ifs perform both the true and the false statement then make a descision which one to use.

CodeMaster:

 

[namliam]

As a postscript to this issue mailman suggested using IIF comparison, this actually would run slower as immediate ifs perform both the true and the false statement then make a descision which one to use.

CodeMaster:

?? ??

IIF slower than a function? I have never seen this happen! But definatly a good thing to know...

So this:
Iif(X = 1, Y*2, Z*3)
would be slower than
Function (X,Y,Z)

Public Function (X,Y,Z)
    If X = 1 then
       Function = Y * 2
    else
       Function = Z * 3
    end if
End Function

I know the example is somewhat bizar... But thats the basic of it right?

 

[TIbbs]

My choice is a lookup table also. You will be amazed at the speed difference.

Yes but the problem remains the same I still have to program the permutations for each level of the class code, taking to the fact that a code will have up to 4 descriptions depending on the position of the letter and it's relationship to the other letters, it would not be possible to input each one of them manually, and the table would not comply with normalization, I would end up with the following format:

ClassCode---level1---Description1----Level2------Description2----Level3

Which if it was normalized would follow the following line:
ClassCode---Level---Description---- Type

That is not the main problem, it's the different permutations that assign the codes their description, it was not provided to us in a table, but simply as a refence for documentation.
I tried using IIf and yest it was too slow
Still the values need to be available for the users without them having to enter the data in.
That's why so far only the Select case provides me with some of the options I need.

 

[namliam]

If worst comes to worst the FULL code should be a unique discription right?? So make a table that relates on the full code, Instead of parts of it.

Alternatively you can make some leveling system in your related table
Code - Level1 Description - Level2 Description
A - Some text - Some other text

Yet another alternative
Code - Level - Description
A - 1 - Some text
A - 2 - Some Other Text

All in all quite some options to create a related table.

 

[DCrake]

Simple Software Solutions

Mailman - can't be bothered reversing the word - In your example, and all iif statements Access will calculate what Y*2 equals then what Z*3 equals THEN evaluate what X equals and give you the appropriate response.

Lets suppose you have nested IIF's each true and false statement will be evaluated before the decision is made as to what value you should expect back. If you have nested IIF's in both the True and False operator then things will certainly slow down. Functions are far better. They are more easily to manage and more flexible.

David

 

[namliam]

Functions are far better. They are more easily to manage and more flexible.

Offcourse and AMEN to that...

But I have allways thought that IIF were faster than Functions, taking a slight hit on performance to increase maintainebilty.... But now I understand IIF is just plain allround worse than functions, therefor no reason to ever use them (allmost)

 

[Pat Hartman]

IIf() works differently in queries than it does in VBA. In queries, it works more like a nested if where the evaluation stops when a true condition is met (or the true path is followed if that is necessary) whereas in VBA, every part of the IIf() is evaluated so you would NEVER use it in VBA particularly if you were testing for 0 to avoid a divide by 0 problem.

IIf(nz(fld1,0) = 0, 0, fld2/fld1) --- would fail in VBA with a divide by 0 error if fld1 is null or 0 but not fail in a query.