[RESOLVED] About the data structure of Krool's VBFlexGrid

[SearchingDataOnly]

Krool has designed a clever data structure for his VBFlexGrid, which allows VBFlexGrid to not only have higher performance, but also take up less space. This is extremely valuable to me.

Currently, VBFlexGrid can easily handle 100 cols * 300,000 rows, but I need a grid that can handle 1,024 cols * 300,000 rows, so I need to further reduce the memory usage of VBFlexGrid, which means that I need to further optimize the data structure of the VBFlexGrid.

Here's the cell data structure of the VBFlexGrid (TCELLFMTG):

Private Type TCELLFMTG
TextStyle As Integer ' As FlexTextStyleConstants
Alignment As Integer ' As FlexAlignmentConstants
Picture As IPictureDisp
PictureRenderFlag As Integer
PictureAlignment As Integer ' As FlexPictureAlignmentConstants
BackColor As Long
ForeColor As Long
ToolTipText As String
ComboCue As Long ' As FlexComboCueConstants
Checked As Integer
FloodPercent As Integer
FloodColor As Long
FontName As String
FontSize As Single
FontStyle As Integer
FontCharset As Integer
End Type

Private Type TLPCELLFMTG
TextStyle As Integer ' As FlexTextStyleConstants
Alignment As Integer ' As FlexAlignmentConstants
lpPicture As LongPtr
PictureRenderFlag As Integer
PictureAlignment As Integer ' As FlexPictureAlignmentConstants
BackColor As Long
ForeColor As Long
lpToolTipText As LongPtr
ComboCue As Long ' As FlexComboCueConstants
Checked As Integer
FloodPercent As Integer
FloodColor As Long
lpFontName As LongPtr
FontSize As Single
FontStyle As Integer
FontCharset As Integer
End Type

In TCELLFMTG, Krool uses Integer instead of Enum (Long type), which saves two bytes of space.

My questions are:
(1) Why didn't ComboCue change to Integer? Is it for the "byte alignment" of type-def(UDT)?

(2) If I change the Enum-def (Long type) to Byte-type, can I further reduce the storage space occupied by TCELLFMTG? Will the change to Byte type affect the performance of VBFlexGrid due to the large amount of conversion work between Byte type and Integer/Long type?

(3) Is there a more efficient data storage structure that allows VBFlexGrid to handle 1,024 cols * 300,000 rows of data with less than 1,500 MB of memory? (Of course, it would be nice to reduce the memory usage while further improving the performance) Thanks.

[VanGoghGaming]

Unless you have an even number of consecutive Integers in the UDT you're not saving any space because the last odd Integer will be padded to make it four bytes. The same goes for Byte.

The only way to work efficiently with such large amounts of data as yours is not to load it all at once. Only load rows into the grid as you scroll through it. This may be easier said than done though.

[SearchingDataOnly]

Unless you have an even number of consecutive Integers in the UDT you're not saving any space because the last odd Integer will be padded to make it four bytes. The same goes for Byte.

Thank you, VanGoghGaming. Does this mean that putting four Bytes together consecutively can save space?

The only way to work efficiently with such large amounts of data as yours is not to load it all at once. Only load rows into the grid as you scroll through it. This may be easier said than done though.

This is a solution, but it will greatly increase the development workload and even require redesigning the software architecture.

[VanGoghGaming]

Thank you, VanGoghGaming. Does this mean that putting four Bytes together consecutively can save space?

Yes, that's how it works. Make sure you don't run into overflows as Byte can only hold numbers up to 255.

[SearchingDataOnly]

Yes, I can be sure that these enum values will not exceed 255. However, the space saved by changing 4 Integers to Bytes is still very limited. I hope there can be a more efficient data structure that can solve my problem once and for all.

[VanGoghGaming]

Well you may squeeze a couple more bytes by changing those color values to bytes since you only need 3 bytes for a color, not 4.

Also changing "Picture As IPictureDisp" to "lpPicture As LongPtr" doesn't save any space but may cause memory leaks since the Picture won't be disposed of automatically anymore.

[OptionBase1]

I have a vague memory of someone on this site talking about writing their own replacement of FarPoint Spread's grid control several years ago using VB6 - which would presumably have accommodated much more than 100 columns - and that they would release the code for it at some point. No idea if that ever happened or not, but it might be worth Searching for to see whatever became of it.

[Krool]

What about to avoid any custom cell formatting?
Then you save a whole struct per cell.

[ahenry]

Have you looked into using the IVBFlexDataSource interface?

[SearchingDataOnly]

Well you may squeeze a couple more bytes by changing those color values to bytes since you only need 3 bytes for a color, not 4.

Also changing "Picture As IPictureDisp" to "lpPicture As LongPtr" doesn't save any space but may cause memory leaks since the Picture won't be disposed of automatically anymore.

Thank you, VanGoghGaming.

[SearchingDataOnly]

What about to avoid any custom cell formatting?
Then you save a whole struct per cell.

Thank you, Krool. But then the value of the grid-control is lost.

I was thinking that maybe we could use "FontType As Integer" instead of "FontName As String; FontSize As Single; FontStyle As Integer; FontCharset As Integer;"

[SearchingDataOnly]

Have you looked into using the IVBFlexDataSource interface?

My question has nothing to do with IVBFlexDataSource, thank you, ahenry.

[SearchingDataOnly]

There doesn't seem to be a better solution at the moment. I'm going to close this thread.

[SearchingDataOnly]

Remove duplicate comments

[Krool]

Thank you, Krool. But then the value of the grid-control is lost.

I was thinking that maybe we could use "FontType As Integer" instead of "FontName As String; FontSize As Single; FontStyle As Integer; FontCharset As Integer;"

I don't get it. You have soo many cells. Does each of them needs a different font?
Maybe look at the general Font/FontFixed property.

[SearchingDataOnly]

I don't get it. You have soo many cells. Does each of them needs a different font?
Maybe look at the general Font/FontFixed property.

Yes, not every cell needs a different font, but in VBFlexGrid's TCellFmtg, setting either kind of property (such as BackColor or ForeColor), the Font property takes up 16 bytes of memory space.

I redesigned a data structure based on VBFlexGrid's, don't know if this new data structure will be faster and take up less memory space.

Private Type TCELLFONT
    FontName As String
    FontSize As Single
    FontStyle As Integer
    FontCharset As Integer
End Type

Private Type TCELLSTYLE
    TextStyle As Integer ' As FlexTextStyleConstants
    Alignment As Integer ' As FlexAlignmentConstants
    PictureRenderFlag As Integer
    PictureAlignment As Integer ' As FlexPictureAlignmentConstants
    BackColor As Long
    ForeColor As Long
    ComboCue As Long ' As FlexComboCueConstants
    Checked As Integer
    FloodPercent As Integer
    FloodColor As Long
    FontType As Integer                 '--- Index of m_arrFontTypes array (FontName + FontSize + FontStyle + FontCharset) ---
End Type

Private Type TCELLAUXDATA
    Picture As IPictureDisp
    ToolTipText As String
    Tag As Variant
End Type

Private Type TLPCELLAUXDATA
    lpPicture As LongPtr
    lpToolTipText As LongPtr
    lpTag As LongPtr
End Type

Private Type TCELLPROPS
    AuxData As TCELLAUXDATA
    Style As TCELLSTYLE
End Type

Private Type TLPCELLPROPS
    lpAuxData As LongPtr
    lpStyle As LongPtr
End Type

Private Type TCELL
    Text As String
    lpProps As LongPtr
End Type

Private m_arrFontTypes() As TCELLFONT

In the above design, TCELLFMTG is decomposed into TCellAuxData and TCellStyle, and FontType is used instead of FontName, FontSize, FontStyle and FontCharset.

[SearchingDataOnly]

Remove duplicate comments

[OptionBase1]

Good work here, nice project.