While x do y Wend - Query Function Speed up tips (1 Viewer)

[Vagus14]

Good morning everyone,

I was wondering if someone could give me a few tips on speeding up a function I made in VBA for a query. The function pulls from a table by Dlookup and determines if a a item is like the wildcard in the table. It pulls 5 times because the wildcard is different in various cases. Here's a sample below, is there anyway to make this more efficient?

Function PartPricingRate(strRate As String) As String
'35 Variables
AC_Category_S35_1 = "S35-1"
AC_Category_S35_2 = "S35-2"
AC_Category_S35_3 = "S35-3"
AC_Category_S35_4 = "S35-4"
AC_Category_S35_5 = "S35-5"
'50 Variables
AC_Category_S50_1 = "S50-1"
AC_Category_S50_2 = "S50-2"
AC_Category_S50_3 = "S50-3"
AC_Category_S50_4 = "S50-4"
AC_Category_S50_5 = "S50-5"
'S35 Parts

While strRate Like DLookup("[PN_Identifier]", "[tblRateLocal]", "[PN_AC_Category] = '" & AC_Category_S35_1 & "'")
strRate = DLookup("[PN_Rate]", "[tblRateLocal]", "[PN_AC_Category] = '" & AC_Category_S35_1 & "'")
PartPricingRate = strRate
Wend

While strRate Like DLookup("[PN_Identifier]", "[tblRateLocal]", "[PN_AC_Category] = '" & AC_Category_S35_2 & "'")
strRate = DLookup("[PN_Rate]", "[tblRateLocal]", "[PN_AC_Category] = '" & AC_Category_S35_2 & "'")
PartPricingRate = strRate
Wend

While strRate Like DLookup("[PN_Identifier]", "[tblRateLocal]", "[PN_AC_Category] = '" & AC_Category_S35_3 & "'")
strRate = DLookup("[PN_Rate]", "[tblRateLocal]", "[PN_AC_Category] = '" & AC_Category_S35_3 & "'")
PartPricingRate = strRate
Wend

While strRate Like DLookup("[PN_Identifier]", "[tblRateLocal]", "[PN_AC_Category] = '" & AC_Category_S35_4 & "'")
strRate = DLookup("[PN_Rate]", "[tblRateLocal]", "[PN_AC_Category] = '" & AC_Category_S35_4 & "'")
PartPricingRate = strRate
Wend

While strRate Like DLookup("[PN_Identifier]", "[tblRateLocal]", "[PN_AC_Category] = '" & AC_Category_S35_5 & "'")
strRate = DLookup("[PN_Rate]", "[tblRateLocal]", "[PN_AC_Category] = '" & AC_Category_S35_5 & "'")
PartPricingRate = strRate
Wend

'S50 Parts
While strRate Like DLookup("[PN_Identifier]", "[tblRateLocal]", "[PN_AC_Category] = '" & AC_Category_S50_1 & "'")
strRate = DLookup("[PN_Rate]", "[tblRateLocal]", "[PN_AC_Category] = '" & AC_Category_S50_1 & "'")
PartPricingRate = strRate
Wend

While strRate Like DLookup("[PN_Identifier]", "[tblRateLocal]", "[PN_AC_Category] = '" & AC_Category_S50_2 & "'")
strRate = DLookup("[PN_Rate]", "[tblRateLocal]", "[PN_AC_Category] = '" & AC_Category_S50_2 & "'")
PartPricingRate = strRate
Wend

While strRate Like DLookup("[PN_Identifier]", "[tblRateLocal]", "[PN_AC_Category] = '" & AC_Category_S50_3 & "'")
strRate = DLookup("[PN_Rate]", "[tblRateLocal]", "[PN_AC_Category] = '" & AC_Category_S50_3 & "'")
PartPricingRate = strRate
Wend

While strRate Like DLookup("[PN_Identifier]", "[tblRateLocal]", "[PN_AC_Category] = '" & AC_Category_S50_4 & "'")
strRate = DLookup("[PN_Rate]", "[tblRateLocal]", "[PN_AC_Category] = '" & AC_Category_S50_4 & "'")
PartPricingRate = strRate
Wend

While strRate Like DLookup("[PN_Identifier]", "[tblRateLocal]", "[PN_AC_Category] = '" & AC_Category_S50_5 & "'")
strRate = DLookup("[PN_Rate]", "[tblRateLocal]", "[PN_AC_Category] = '" & AC_Category_S50_5 & "'")
PartPricingRate = strRate
Wend

PartPricingRate = strRate

End Function

Table that the code pulls from is attached.

 

[jleach]

Ouch! DLookup in a query function? That's rough... for every single record that the query has to process (which could be much, much more than it even returns), that DLookup needs to be called, and a DLookup in itself creates it's own recordset to retrieve the value. DLookups should never be used in any place where it needs to be called more than a (very) small handful of times.

Whenever I used to find myself wanting to use DLookup in a set of data (as opposed to a one-off call), I'd ask myself: "how is the value I'm trying to get related to the data I have, and how can I join that data into my query and reference it via SQL instead of having to call a DLookup to get it?"

You're most certainly much better off to include those tables in your main query as joins than run a dlookup to obtain the info.

 

[Minty]

I'm with Jack - "Holy mega slow effectively loads of queries Batman!"

If you post up the tables with some sample data, I'm sure that can be directly written into a query, or at very least a recordset process.

 

[plog]

I really don't know what you are trying to accomplish with this monstrosity. It seems a simple query could accomplish the same thing.

Also, why are you using a While loop? That things only going to run once right? I mean it returns whatever it finds when it does, so no need for a while.

Lastly since all your While codes are essentially the same--except for a variable that is essentially the value--you can consolidate them all into one. I mean, when you store the value of the variable in the name, you aren't really using a variable. You should have a generic variable that stores the value you are working with, then make your while loops generic enough to work with that generic variable.

 

[Vagus14]

Everyone,

Thank you for your replies! To address some reponses:
1) @Jleach amd @Minty putting this into a query and using dlookup functions will be an issue as my query gets the "Too complex" to run if I use too many if statements. I also wanted a table where the user could put in wildcards.* To address some of the speed issues I have the table locally embedded in the tool that gets refreshed on a timer by the *server table.
2) @Plog that's a good idea to remove the whiles. I will give this a shot. I don't really understand the generic variable, can you give an example?

Everyone I really appreciate your helping out!

 

[jleach]

A well designed query should rarely contain an IIF statement. I'm entirely convinced that if your data is reasonably normalized, there should be no reason you can't do this via SQL.

What, exactly - in normal, non-database terms - are you trying to accomplish with your query?

 

[Vagus14]

@jleach. I wanted a table where the user could put in rates by a wildcard and the query would pick up on those rates. The user would be able to adjust the wildcard and there could be an unlimited amount of wildcards by rate. The wildcard would look at the part number and pull the rate.

 

[plog]

This is just for informational purposes, I truly believe SQL is the answer for this issue.

However if code was the way to go, you would be much better served with an array and a for loop to work through that array:

Dim categories As Variant
categories = Array("S35-1", "S-35-2", ..., "S50-5")
  ' array to hold all categories to check

For Each category In categories
  ' generic While code would go here, I just used a MsgBox to show what category is currently being used in each iteration
        MsgBox(category)

Next category

 

[Vagus14]

@plog wow thank you.... I heard about arrays but didn't really know how to use them. Let me give this a shot. I'll post code here if I succeed or have issues.

 

[plog]

Let me give this a shot.

You missed the most important part of my post. That was to explain an efficient way of coding, not an efficient way to solve this issue. I believe SQL is the path to take for this issue.

 

[Minty]

Thank you for your replies! To address some reponses:
1) @Jleach amd @Minty putting this into a query and using dlookup functions will be an issue as my query gets the "Too complex" to run if I use too many if statements. I also wanted a table where the user could put in wildcards.* To address some of the speed issues I have the table locally embedded in the tool that gets refreshed on a timer by the *server table.!

Again - every time you use a DLookup in a query - it can almost always be replaced with a join to a another static table.

Hence asking for sample data and the objectives.

 

[Vagus14]

@Plog I have tried the SQL approach and due to the amount of wildcards I get the "too complex". How would I code this in SQL so I don't get this error? Can I do it without if statements?

 

[Vagus14]

@Minty thank you for the advice. I am an average SQL user. How would I go about doing the join to that table I posted above without using the If statement? I need to do the join based on the wildcard column in the table and pull the rate. (I think plog is going to answer as well)

 

[plog]

First you would have to telll me what you are trying to do and what you hope to end up with. I don't fully understand this, but looking at your code, it looks like its a hack around something that can be done with SQL.

So post 2 sets of data:

A. Starting sample data from relevant tables (e.g. tblRateLocal). Include table and field names and enough data to cover all cases.

B. Expected results based on A. Show me what data you hope to end up with based on the data in A.

 

[jleach]

What happens if there's rate that matches more than one wildcard?

For example: you have two wildcard rates entered: S3-###-### for $10.99 and S3-ASD-### for $11.99... presumably the latter takes precedence as it's more specific?

In which case, I think you might be able to start out with something like:

(this is a rough draft approach which is not technically complete, nor guaranteed to work depending on how your pricing needs to be applied: for think-tank purposes only)

1) Query a rank for each wildcard: strip out the separators, and rank on a percentage of specified characters vs the total length of the wildcard. Example S3-XX-XX would have a rank of 33 as 33% of it's characters are specified.
2) Look up wildcard rates for products by joining your product table to your rates table using a LIKE operator as the join criteria (if you can... not sure about joining on a LIKE - not something I recall having to do before).
3) Sort the results by descending rank. Then you can grab the highest record for any given part number and theoretically get the best matched price.

That makes a lot of assumptions, but maybe gives you an idea of the right path?

I'd also note that if this data is contained elsewhere (SQL Server, for example), I'd be much more inclined to utilized the more advanced engine there instead of trying to do this in Access (features such as ROW_NUMBER OVER() can be useful for stuff like this, but there's no real Access equiv)

 

[Minty]

I'll duck out now - but you are in good hands. (Much safer than mine - i'm off to the pub for a minor celbration )

 

[Mark_]

For myself, I'd include a [PN_AC_Category] in the same table that holds [PN_Identifier]. Your logic for how you determine which category a given product is in seems... suspect. I'd make the end user specify so you don't have any issue with order of records for your test.

That would also allow you to join your files based on [PN_AC_Category] and always get the correct rate.

Worst case, when updating records use logic similar to what you have for filling in the category and make sure its saved with the record. That way if they change rates you will always get the most current.

 

[Vagus14]

@plog

Attached I have provided what you requested: Table A is the Rate table and B are the results that i want to achieve. Thank you again as I am always interested in improving.

I also took the array advice and this did seem to improve the query a little bit. Code below:

Function TestPNPricingRate(strRate As String) As String

Dim Categories As Variant
Categories = Array("S50-1", "S50-2", "S35-1", "S35-2")

For Each Category In Categories
If strRate Like DLookup("[PN_Identifier]", "[tblTemp_Rates_A]", "[PN_AC_Category] = '" & Category & "'") Then
strRate = DLookup("[PN_Rate]", "[tblTemp_Rates_A]", "[PN_AC_Category] = '" & Category & "'")
TestPNPricingRate = strRate
End If
Next Category

'Blank Calculation
If TestPNPricingRate = "" Then
strRate = DLookup("[PN_Rate]", "[tblTemp_Rates_A]", "[PN_AC_Category] = '" & "Blank" & "'")
TestPNPricingRate = strRate
End If

'Returns Orginal Value if Null but not used as Null will be Blank.
'PartPricingRate = strRate

End Function
Function TestPNModelLookup(strModel As String) As String

Dim Categories As Variant
Categories = Array("S50-1", "S50-2", "S35-1", "S35-2")

For Each Category In Categories
If strModel Like DLookup("[PN_Identifier]", "[tblTemp_Rates_A]", "[PN_AC_Category] = '" & Category & "'") Then
strModel = DLookup("[PN_AC_Category]", "[tblTemp_Rates_A]", "[PN_AC_Category] = '" & Category & "'")
TestPNModelLookup = strModel
End If
Next Category

'Blank Calculation
If TestPNModelLookup = "" Then
strModel = DLookup("[PN_AC_Category]", "[tblTemp_Rates_A]", "[PN_AC_Category] = '" & "Blank" & "'")
TestPNModelLookup = strModel
End If

'Returns Orginal Value if Null but not used as Null will be Blank.
'PartModelLookup = strModel

End Function

 

[Vagus14]

For myself, I'd include a [PN_AC_Category] in the same table that holds [PN_Identifier]. Your logic for how you determine which category a given product is in seems... suspect. I'd make the end user specify so you don't have any issue with order of records for your test.

That would also allow you to join your files based on [PN_AC_Category] and always get the correct rate.

Worst case, when updating records use logic similar to what you have for filling in the category and make sure its saved with the record. That way if they change rates you will always get the most current.

Hey Mark, I have [PN_AC_Category] and [PN_Identifier] in the same table.. We're just trying to figure out now how to improve the performance by still having the user access to adjust the wildcards in the table that pop out that rates in the query. I believe prog will provide some help through SQL. I tried Dlookup. Thank you as well for some insight.

 

[Vagus14]

I'll duck out now - but you are in good hands. (Much safer than mine - i'm off to the pub for a minor celbration )

Haha nice. Well thank you for chiming in I appreciate it.