To Store or Not to Store, that is the Question

[Simon_MT]

I have expressions to concatenate Sizes

Size cms: IIf(Not IsNull([Orig Depth]),[Orig Height] & " x " & [Orig Width] & " x " & [Orig Depth],IIf(Not IsNull([Orig Width]),[Orig Height] & " x " & [Orig Width],[Orig Height])) & " cm"

This "calculated" value is utilised in so many queries so I experimented with storing the size in a Field. I reckon that by doing so I acheived a perform gain.

Simon

 

[plog]

I reckon that by doing so I acheived a perform gain.

In the morning I could get a performance gain on my commute by leaving without flushing the toilet. It's not a signficant one, so I flush the toilet. See where I am going?

How horrible is the performance of your queries? What type of gain are you hoping for? Performance is one of the reasons to store calculated values, but not if the result is just shaving a few seconds off a query.

 

[spikepl]

I reckon your expression could possibly be reduced to

([Orig Depth] + " x ") & ([Orig Width] + " x ") & [Orig Height] & " cm"

unless you flush it down the toilet

(The above would work in VBA - perhaps also in SQL)

 

[Simon_MT]

It can't because a circular work of art uses Height only, 2D uses Height & Width whilst sculpture is three dimensional.

Simon

 

[spikepl]

...so? Did you try it?

 

[gemma-the-husky]

iif and switches take time to resolve in queries.

i would have thought you would be better just having a default of 1 for all the optional / not required fields, so that every calculation becomes a * b * c.

 

[plog]

You missed the point that's not really his point. Here's the trees: He's not trying to do multiplication, he's doing concatenation. He literally wants to return something like "8 x 4 x 3" and not 96.

And the forest: His question is about performance.

 

[Simon_MT]

is reply to Spikepl the other method is:

Size cms: iif(Not IsNull([Orig Height]),[Orig Height]) & iif(Not IsNull([Orig Width])," x " & [Orig Width]) & iif(Not IsNull([Orig Depth])," x " & [Orig Depth]) & "cm"

Considering there are about 25,000 records, if I pull up the the OriginalsQuery and all the records it does seem a lot faster using [Orig Size Cms].

Simon

 

[spikepl]

In other words, you still did not try what I suggested.

 

[Simon_MT]

Spike,

The order HAS TO BE Height - Width - Depth because this is the standard intrepretation of dimensions when dealing with works of Art. Beside you need to test for each attribute you could end up with x x cm

Just in case this is the code to construct the field:

Function SetSize()

    Dim SizeHeightValue As Single
    Dim SizeHeightWhole As Integer
    Dim SizeHeightFraction As Integer
    Dim SizeWidthValue As Single
    Dim SizeWidthWhole As Integer
    Dim SizeWidthFraction As Integer
    Dim SizeDepthValue As Single
    Dim SizeDepthWhole As Integer
    Dim SizeDepthFraction As Integer
    Dim SizeResultIns As String
    Dim SizeResultCms As String
    
    
'       First Split the Whole Value and Fraction
'       Convert optimistically (round up) the Fraction into Quarter values

    With CodeContextObject
    
        If IsNull(.[SizeChange]) Or .[SizeChange] = "A" Then
            Exit Function
        Else
            If .[ArtHeight] > 0 Then
                If .[SizeFlag] = "I" Then
                    SizeHeightValue = .[HeightIns]
                ElseIf .[SizeFlag] = "M" Then
                    SizeHeightValue = .[ArtHeight] / 2.54
                End If

                SizeHeightWhole = Int(SizeHeightValue)
                SizeHeightFraction = (SizeHeightValue - SizeHeightWhole) * 100
        
                If SizeHeightFraction > 75 Then
                    SizeResultIns = SizeHeightWhole + 1
                Else
                    SizeResultIns = SizeHeightWhole
                End If
            
                If SizeHeightFraction > 1 And SizeHeightFraction <= 25 Then
                    SizeResultIns = SizeResultIns & Chr(188)
                ElseIf SizeHeightFraction > 25 And SizeHeightFraction <= 50 Then
                    SizeResultIns = SizeResultIns & Chr(189)
                ElseIf SizeHeightFraction > 50 And SizeHeightFraction <= 75 Then
                    SizeResultIns = SizeResultIns & Chr(190)
                End If
                
                SizeResultCms = .[ArtHeight]
            
            End If
        
            If .[ArtWidth] > 0 Then
                If .[SizeFlag] = "I" Then
                    SizeWidthValue = .[WidthIns]
                ElseIf .[SizeFlag] = "M" Then
                    SizeWidthValue = .[ArtWidth] / 2.54
                End If
                
                SizeWidthWhole = Int(SizeWidthValue)
                SizeWidthFraction = (SizeWidthValue - SizeWidthWhole) * 100
        
                If SizeWidthFraction > 75 Then
                    SizeResultIns = SizeResultIns & " x " & SizeWidthWhole + 1
                Else
                    SizeResultIns = SizeResultIns & " x " & SizeWidthWhole
                End If

                If SizeWidthFraction > 1 And SizeWidthFraction <= 25 Then
                    SizeResultIns = SizeResultIns & Chr(188)
                ElseIf SizeWidthFraction > 25 And SizeWidthFraction <= 50 Then
                    SizeResultIns = SizeResultIns & Chr(189)
                ElseIf SizeWidthFraction > 50 And SizeWidthFraction <= 75 Then
                        SizeResultIns = SizeResultIns & Chr(190)
                End If

                    SizeResultCms = SizeResultCms & " x " & .[ArtWidth]
                
            End If
            
            If .[ArtDepth] > 0 Then
                If .[SizeFlag] = "I" Then
                    SizeDepthValue = .[DepthIns]
                ElseIf .[SizeFlag] = "M" Then
                    SizeDepthValue = .[ArtDepth] / 2.54
                End If
                    
                SizeDepthWhole = Int(SizeDepthValue)
                SizeDepthFraction = (SizeDepthValue - SizeDepthWhole) * 100
            
                If SizeDepthFraction > 75 Then
                    SizeResultIns = SizeResultIns & " x " & SizeDepthWhole + 1
                Else
                    SizeResultIns = SizeResultIns & " x " & SizeDepthWhole
                End If

                If SizeDepthFraction > 1 And SizeDepthFraction <= 25 Then
                    SizeResultIns = SizeResultIns & Chr(188)
                ElseIf SizeDepthFraction > 25 And SizeDepthFraction <= 50 Then
                    SizeResultIns = SizeResultIns & Chr(189)
                ElseIf SizeDepthFraction > 50 And SizeDepthFraction <= 75 Then
                    SizeResultIns = SizeResultIns & Chr(190)
                End If

                SizeResultCms = SizeResultCms & " x " & .[ArtDepth]

            End If
        
            If .[ArtHeight] > 0 Then
                If SizeResultIns <> .[Size ins] Or IsNull(.[Size ins]) Then
                    .[Size ins] = SizeResultIns
                End If
                If SizeResultCms <> .[Size cms] Or IsNull(.[Size cms]) Then
                    .[Size cms] = SizeResultCms
                End If
            Else
                    .[Size ins] = Null
                    .[Size cms] = Null
            End If
        End If

        .[SizeFlag] = Null
        .[SizeChange] = Null
        SizeResultIns = ""
        SizeResultCms = ""
    End With
End Function

Simon

 

[gemma-the-husky]

You missed the point that's not really his point. Here's the trees: He's not trying to do multiplication, he's doing concatenation. He literally wants to return something like "8 x 4 x 3" and not 96.

And the forest: His question is about performance.

ah. I see, said the blind man.