Calling Functions From Queries = Poor Performance?

[Rawb]

Hey all, long time no see.



I've run into an issue where it is necessary to run a VBA Function inside a SQL Query and my problem is simple: The performance is HORRIBLE! The Query only returns 33 Records and takes almost 3 seconds to run!!

Is there any way I can improve performance on the Function/Query?

Query:

SELECT 
  [SFGMaster].[SFGMGroupNo] AS [Group No], 
  [SFGMaster].[SFGMPartNo] AS [Part No], 
  IIf(
    Int([PartMaster].[PartGlassThick]*25.4)=3,
    3.2,
    IIf(
      [PartMaster].[PartGlassThick]>0.315,
      Int([PartMaster].[PartGlassThick]*25.4)+1,
      Int([PartMaster].[PartGlassThick]*25.4)
    )
  ) & 
  "MM " & 
  GetShortDesc([PartMaster].[PartNumber]) AS [Description] 
FROM 
  SFGMaster 
  INNER JOIN 
    PartMaster 
  ON 
    [SFGMaster].[SFGMPartNo] = [PartMaster].[PartNumber] 
WHERE 
  [PartMaster].[PartGlassType]<>"G" AND 
  [PartMaster].[PartGlassType]<>"I" AND 
  [PartMaster].[PartGlassType]<>"L"

VBA Function:

Public Function GetShortDesc(GlassType As String) As Variant ' Because it can return Boolean or String!
  On Error GoTo Error_GetShortDesc

  Dim dbsdb1 As DAO.Database

  Dim rstGlassFlat As DAO.Recordset

  Dim boolGlassAssy As Boolean
  Dim strCriteria As String

  Set dbsdb1 = CurrentDb()

  Set rstGlassFlat = dbsdb1.OpenRecordset("SELECT [PartMaster].[PartNumber], UCase([Colors].[ColorDescription]) AS [GlassColor] FROM PartMaster INNER JOIN Colors ON [PartMaster].[PartGlassColor] = [Colors].[ColorCode] WHERE [PartMaster].[PartNumber]='" & GlassType & "' AND NOT [PartMaster].[PartGlassType]='I' AND NOT [PartMaster].[PartGlassType]='L' AND NOT [PartMaster].[PartGlassType]='G' ORDER BY Len([PartMaster].[PartNumber]), [PartMaster].[PartNumber]", dbOpenForwardOnly)

  GetShortDesc = False

  If rstGlassFlat.RecordCount = 0 Then
    Dim rstGlassAssy As DAO.Recordset

    Set rstGlassAssy = dbsdb1.OpenRecordset("SELECT [BomTable].[BomParentPart], [BomTable].[BomComppart], [PartMaster_1].[PartGlassType] FROM (PartMaster INNER JOIN BomTable ON [PartMaster].[PartNumber] = [BomTable].[BomParentPart]) INNER JOIN PartMaster AS [PartMaster_1] ON [BomTable].[BomComppart] = [PartMaster_1].[PartNumber] WHERE [PartMaster].[PartNumber]='" & GlassType & "' AND [PartMaster_1].[PartGlassType] Is Not Null ORDER BY Len([BomTable].[BomParentPart]), [BomTable].[BomParentPart], [BomTable].[BomSeq]", dbOpenForwardOnly)
    boolGlassAssy = True

    If rstGlassAssy.RecordCount = 0 Then
      GetShortDesc = "UNKNOWN"
    Else
      If rstGlassAssy("PartGlassType") = "L" Then
        GetShortDesc = "LAM "
      Else
        GetShortDesc = "IG "
      End If

      Do While Not rstGlassAssy.EOF
        GetShortDesc = GetShortDesc & rstGlassAssy("BomcompPart") & " / "

        rstGlassAssy.MoveNext
      Loop

      GetShortDesc = Left(GetShortDesc, Len(GetShortDesc) - 3)
    End If
  Else
    GetShortDesc = rstGlassFlat("GlassColor")
  End If

FunctionClosing:
  If boolGlassAssy = True Then
    boolGlassAssy = False

    rstGlassAssy.Close

    Set rstGlassAssy = Nothing
  End If

  rstGlassFlat.Close

  Set rstGlassFlat = Nothing

  Set dbsdb1 = Nothing

  Exit Function

Error_GetShortDesc:
  GetShortDesc = False

  Debug.Print Err.Number & ": " & Err.Description ' No MsgBox to prevent Query interruption!

  Resume FunctionClosing
End Function

[JoeM]

The performance is HORRIBLE! The Query only returns 33 Records and takes almost 3 seconds to run!!

I don't know if I would call that "HORRIBLE" (I think I could live with three seconds!). I have queries that take minutes to run (mostly because they are linking to huge SQL databases and filtering and grouping data).

Note that the number of records returned is really inconsequential to the time it takes to run. What is more telling is how large is the data sets that you are running these queries against. If the underlying tables you are running these against have a ton of records, I would say that performance doesn't look too bad.

I'm not saying that there aren't faster more efficient ways of getting what you want. There may be. But you really haven't said/described exactly what this function does, so I don't know if I can really comment too much on that. It looks like you may be building some recordsets in the function and getting information from them? Maybe you create those as queries instead and link those queries into your main query and get your information that way, and see if that is any better. Otherwise, you are rebuilding recordsets for every single record in query, which may not be too efficient.

[Rawb]

JoeM, thanks for the quick reply.

What the Function does is to take a specific type of glass-like material and return a basic description.

For "flat" glass (glass composed of a single pane), this description is composed of the thickness of the glass (in millimeters - converted from inches, ugh!) and the color. For glass assemblies (insulated and laminated glass that are made up of multiple individual panes that are "sandwiched" together), the description is a shorthand with the assembly type ("IG" for Insulated and "LAM" for Laminated) along with the different component panes each separated by a "/".

Example "flat" glass result: 155 (internal part number) = "3.2MM CLEAR"
Example glass assembly: 011 (internal part number) = "IG 155 / 155"

In situations where the delay is "hidden," I'd be fine with the delay - In fact, I'm using this Function call in a Report's SQL Query (there it takes about 10 seconds to load the Report, not sure how much of that is from the Function) and I'm fine with the delay there. The problem is that THIS Query is behind a Combo Box on a Form so the delay is very noticeable (and annoying!).

Well, the total number of Records in the two Tables directly linked to the Query are currently as such: SFMaster = 41 Records, PartMaster = 453. Personally, I don't consider those all that large (although the PartMaster Table was designed almost completely without normalization).

As for the Queries in the Function: The first Recordset (rstGlassFlat) should never return more than a single Record, while the second (rstGlassAssy) will never return more than 3-4 Records. Each Recordset Query is designed to return the absolute minimum number of Records and Fields possible. In fact, the second Recordset isn't even run unless the first one fails to return a Record.

The total number of Records in rstGlassFlat's Query:
PartMaster = 453
Colors = 24

The Total number of Records in rstGlassAssy's Query:
PartMaster = 453
BomTable = 177
Colors = 24

[Rawb]

Oh, also, I'm not sure that the number of Records is really inconsequential in situations where you're running a Function like this: The function has to be run on each Record returned (in my understanding), so the more Records returned, the more Function calls.

[JoeM]

Oh, also, I'm not sure that the number of Records is really inconsequential in situations where you're running a Function like this: The function has to be run on each Record returned (in my understanding), so the more Records returned, the more Function calls.

I guess that depends on the order of how Access processes things, whether it does the calculations first, then applies the criteria, or vice versa. I am not certain which it does first, or if it differs in different scnarios. I remember being surprised one time when I had a query returning errors and I couldn't figure out why. The were records in an underlying query that were being excluded (some NULL values), so they should have not been part of the calculations, but they were giving Access fits. I actually had to update my calculation to account for NULL values, even though my criteria was excluding them! Access does not always follow the same logical order one would expect in processing. I don't pretend to understand the logic behind their logic!

I'm having a hard time visualizing your scenario and process without seeing your data tables and expected results (I am a very visual person). I would ask you to upload a sample database if you could, but I cannot download it anyway from my current location (corporate policy!). However, I will offer up this advice, and hope it makes sense. It sounds like you might be able to use some look-up tables that you could link to the query for parts of your descriptions? Also, any numerical conversion (inches to millimeters) should be able to be handled by a simple calculation or Function.

In fact, the second Recordset isn't even run unless the first one fails to return a Record.

If you can use lookup tables, perhaps the NZ function would be handy here. You could tell it that if you do not find a matching record from one lookup table, lookup the next one (http://www.techonthenet.com/access/f...dvanced/nz.php.

[Rawb]

JoeM,

I understand about the visualization thing. I'm the exact same way.

I'll go ahead and put together a small sample database to try and help you out for later, but I'm not sure how much info I can actually put into it. The original setup tended to mix in costing all over the place and I don't want to hand that kind of data out willy-nilly. I'll see what I can do though.

Business:
What my business does is cut and process glass for other businesses. If a company builds car windows, for example, they need to purchase the finished glass from somewhere. That somewhere is us!

Raw, untempered glass is purchased in huge "stock plates." As we get orders for the various finished products, we store those orders in a database. Then, each day we we release whole "groups" of orders to the shop floor. Each group is a combination of all the orders that use a specific type of glass - any one group can have anywhere up to about two dozen different orders in it. We then run that "group" through a program that figures out how to best fit the glass onto our stock plates to be cut.

Once the glass is cut, the pieces are run through the various other processes we are asked to perform: smoothing and polishing edges, drilling screw/rivet holes, fretting/frosting the glass, tempering it to whatever standards the customer wants, and if necessary, assembling the pieces to make insulated, laminated, or "bulletproof" bullet resistant glass.

Our database is designed to be able to track these orders (both as individual orders and as a whole group) as they travel through the shop floor. This allows us to calculate breakage/rejections at each individual step and come up with efficiency yields on our materials.

Database:
PartMaster - A list of all the different "parts" used at each plant. These parts include the different types of glass as well as the various assembly materials, etc.

• PartNumber - The "number" of each part stored. For glass, this is a 3-4 character string composed mostly of numbers. Text, Primary Key.
• PartGlassThick - If the part is a glass (or glass-like) material, this is it's thickness in inches. Unfortunately, the glass we buy is measured in millimeters so we need to convert this number a lot! Decimal - 3.3 format
• PartStatus - Whether the part is active or inactive. Text (I know, I know!)
• PartGlassType - If the part is a glass material, this is the type of material it is. This can be either "A"nnealed, "T"empered, "L"aminated, "I"nsulated, or "G"PC (Glass Polycarbonate - used for bullet resistant glass). Left empty for non-glass parts. Text.
• PartGlassColor - If the part is a glass material, this is the "code" of that color. Text, Foreign Key -> [Colors].[ColorCode].

SFGMaster - The only really "normalized" Table used in the Query. It stores the "group" and yield information for glass that we release to the shop floor. There's a "detail" Table that stores each order released and ties it to this Table, but it's not included here.

• SFGMID - The unique ID for the specific release "group." Long Integer, Primary Key, Foreign Key -> [SFGDetail].[SFGDSFMID]
• SFGMGroupNo - The release group number itself. Text
• SFGMPartNo - The glass part number used in this release. Because the orders are coverted to their "A"nnealed part numbers before grouping, this is stored separately to simplify Queries (this conversion is actually pretty complex for a look-up). Text
• SFGMYield - The "yield" of the release group (how much waste will be generated by the cutting operation even if there is no breakage on the shop floor). Decimal - 3.2 format
• SFGMSqFt - The total Sq. Ft. the release group will use. Used along with [SFGMaster].[SFGMYield] to calculate waste. Decimal - 7.2 format

Colors - The list of available glass material colors.

• ColorCode - The "code" used to identify the color. Text, Primary Key, Foreign Key -> [PartMaster].[PartGlassColor]
• ColorDesc - The name of the color. Text

BomTable - A cross-reference table listing all the different materials (not just glass materials) that go into the different glass assemblies.

• BomPartMaster - The part "number" used to identify the finished product. Text, Primary Key, Foreign Key -> [PartMaster].[PartNumber]
• BomSequence - The ordering of the parts for assembly. As this number shows you how the parts fit together - lower numbers are the "outside" of the finished product while higher numbers move ever "inward". Integer, Primary Key
• BomCompPart - The part number of the assembly component. Not limited to glass materials - includes spacing materials, laminates, etc. Text, Primary Key, Foreign Key -> [PartMaster].[PartNumber]