To Store or Not to Store, that is the Question (1 Viewer)

S

Simon_MT

Registered User.
Local time
Today, 14:00
Joined
Feb 26, 2007
Messages
2,177
I have expressions to concatenate Sizes

Code: 
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
 
P

plog

Banishment Pending
Local time
Today, 08:00
Joined
May 11, 2011
Messages
11,645
I reckon that by doing so I acheived a perform gain.
Click to expand...

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

spikepl

Eledittingent Beliped
Local time
Today, 15:00
Joined
Nov 3, 2010
Messages
6,142
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 :D

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

Simon_MT

Registered User.
Local time
Today, 14:00
Joined
Feb 26, 2007
Messages
2,177
It can't because a circular work of art uses Height only, 2D uses Height & Width whilst sculpture is three dimensional.

Simon
 
spikepl

spikepl

Eledittingent Beliped
Local time
Today, 15:00
Joined
Nov 3, 2010
Messages
6,142
...so? Did you try it?
 
gemma-the-husky

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 14:00
Joined
Sep 12, 2006
Messages
15,653
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.
 
P

plog

Banishment Pending
Local time
Today, 08:00
Joined
May 11, 2011
Messages
11,645
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.
 
S

Simon_MT

Registered User.
Local time
Today, 14:00
Joined
Feb 26, 2007
Messages
2,177
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

spikepl

Eledittingent Beliped
Local time
Today, 15:00
Joined
Nov 3, 2010
Messages
6,142
In other words, you still did not try what I suggested.
 
S

Simon_MT

Registered User.
Local time
Today, 14:00
Joined
Feb 26, 2007
Messages
2,177
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:

Code: 
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
 
Last edited:
gemma-the-husky

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 14:00
Joined
Sep 12, 2006
Messages
15,653
plog said:
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.
Click to expand...




ah. I see, said the blind man.
 
You must log in or register to reply here.

Similar threads

N
Chart Error: Expression is typed incorrectly, or is too complex to be evaluated
Replies
8
Views
456
Pat Hartman
P
S
Design Thoughts for Sub-Forms
Replies
5
Views
760
MajP
MajP
L
To Update Query or Not To Update Query
Replies
8
Views
576
Pat Hartman
P
D
There is life in the old dog yet
Replies
3
Views
552
Pat Hartman
P
M
how to write a value in a calculated field & and how to show the a field in another field with a specific condition
Replies
6
Views
893
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom