I've tried both options (True and False) with no result.Quote:
Originally Posted by Krool
Printable View
I've tried both options (True and False) with no result.Quote:
Originally Posted by Krool
IVBFlexDataSource2 can be implemented in addition to a IVBFlexDataSource implementation which is set to .FlexDataSource.Quote:
Originally Posted by Nouyana
So is your IVBFlexDataSource2 impl "together" with IVBFlexDataSource?
Yes. You can see all of my testing code above (here and here) or with the project file in the "FlexDataSource.zip" attachment. The "CreatingDatabase.zip" attachment contains the code for creating the database file I use.Quote:
Originally Posted by Krool
Attachment 187844
Attachment 187845
I don't have this problem when I'm working with a small number of records. This code works fine:Quote:
Originally Posted by Nouyana
Code:Private Const MAX_ROWS = 65000
Implements IVBFlexDataSource
Implements IVBFlexDataSource2
Public RS As DAO.Recordset
'****************************************************************************
' Implements IVBFlexDataSource
'----------------------------------------------------------------------------
Private Function IVBFlexDataSource_GetFieldCount() As Long
IVBFlexDataSource_GetFieldCount = RS.Fields.Count
End Function
Private Function IVBFlexDataSource_GetFieldName(ByVal Field As Long) As String
IVBFlexDataSource_GetFieldName = RS(Field).Name
End Function
Private Function IVBFlexDataSource_GetRecordCount() As Long
IVBFlexDataSource_GetRecordCount = MAX_ROWS
End Function
Private Function IVBFlexDataSource_GetData(ByVal Field As Long, _
ByVal Record As Long) As String
Static PrevRecord As Long
If Record < MAX_ROWS Then
With RS
If (Record = PrevRecord + 1&) Then
.MoveNext
ElseIf (Record = PrevRecord - 1&) Then
.MovePrevious
Else
.MoveFirst
.Move Record
End If
IVBFlexDataSource_GetData = .Fields(Field)
End With
Else
Exit Function
End If
PrevRecord = Record
End Function
Private Sub IVBFlexDataSource_SetData(ByVal Field As Long, _
ByVal Record As Long, _
ByVal NewData As String)
Static PrevRecord As Long
If Record < MAX_ROWS Then
With RS
If (Record = PrevRecord + 1&) Then
.MoveNext
ElseIf (Record = PrevRecord - 1&) Then
.MovePrevious
Else
.MoveFirst
.Move Record
End If
.Edit
.Fields(Field) = NewData
.Update
End With
End If
PrevRecord = Record
End Sub
'****************************************************************************
' Implements IVBFlexDataSource2
'----------------------------------------------------------------------------
Private Function IVBFlexDataSource2_GetFlags() As FlexDataSourceFlags
IVBFlexDataSource2_GetFlags = FlexDataSourceToolTipText
End Function
Private Function IVBFlexDataSource2_GetToolTipText(ByVal Field As Long, _
ByVal Record As Long) As String
Debug.Print "IVBFlexDataSource2_GetToolTipText" 'Works fine!
End Function
Private Sub IVBFlexDataSource2_SetToolTipText(ByVal Field As Long, _
ByVal Record As Long, _
ByVal NewValue As String)
Debug.Print "IVBFlexDataSource2_SetToolTipText"
End Sub
Private Function IVBFlexDataSource2_GetChecked(ByVal Field As Long, _
ByVal Record As Long) As Integer
'
End Function
Private Sub IVBFlexDataSource2_SetChecked(ByVal Field As Long, _
ByVal Record As Long, _
ByVal NewValue As Integer)
'
End Sub
Probably the cause of this problem is the same as with the ToolTips.Quote:
Originally Posted by Nouyana
Finally, the question is: Should I limit the number of records by code, or "virtual mode" can do all the job for me?
Hi, Krool! Can you add the ItemData functionality to the DropDown ComboBoxes? Just an array of long integers. Something like that:
Code:With VBFlexGrid1
.ColComboMode(COL_COMBODROPDOWN) = FlexComboModeDropDown
.ColComboItems(COL_COMBODROPDOWN) = "Arnold|Bob|Charlie"
.ColComboItemsData(COL_COMBODROPDOWN) = "1|2|3"
End With
The MsgBox can be used in a error handler code in some cases. And if Enabled = False, then VBFlexGrid will hang.Quote:
Originally Posted by Krool
Can you automatically set Cancel to True if a modal window is invoked from the BeforeUserResize event?
@Nouyana, i can see your are actively testing this, do you have the same problem like i have?
I found (post 846) that if you use 2 grids in a same form in a MDI environment, you can hang IDE easily.
Send a test project on post 855.
Do you have the same problem under XP ?
No, I use the OCX version.Quote:
Originally Posted by Calcu
You shoul use this compilation utility. It allows you to work in IDE using OCX, and then compile the EXE-file using source Krool's files. But first you have to solve a few puzzles that MountainMan left in his program for some reasons:)Quote:
Originally Posted by Calcu
Thanks, i will take a look at it.
Meanwhile, i'm trying to use the code used on the uctabmdi control, that has this code:
Just Trying to figure where to get the subclass data array :-)Code:'Stop all subclassing
Private Sub Subclass_StopAll()
Dim i As Long
i = UBound(sc_aSubData()) 'Get the upper bound of the subclass data array
Do While i >= 0 'Iterate through each element
With sc_aSubData(i)
If .hWnd <> 0 Then 'If not previously Subclass_Stop'd
Call Subclass_Stop(.hWnd) 'Subclass_Stop
End If
End With
i = i - 1 'Next element
Loop
End Sub
deleted
I tried your demo. For me IVBFlexDataSource2_GetToolTipText fires always. Also at the end of the table.Quote:
Originally Posted by Nouyana
Regarding the slowleness. If I comment out the code on IVBFlexDataSource_GetData in your cFlexDataSource then the grid is fast, always. So it must be something with the .record moving of the DAO db.
Aside from potential slowdowns in DAO-Rs-navigation/caching strategies -Quote:
Originally Posted by Krool
there's one "other problem" with such "huge-sized" DataSources.
When I run similar Code with huge-sized SQLite-Rs (2.5Mio Records x 4 Columns = 10Mio Cells) - then I have:
- a mem-consumption of the SQLite-Rs of about 100MByte (about 10Byte per Rs-Cell on average)
- after the binding went through, the grid causes an additional mem-consumption of about 1GByte (~100 byte per cell)
So, there's probably a 2D-Array-Redim (for Cell-individual Formatting or something) in the Grid-Ctl -
which might be an indirect cause for "slowdowns everywhere" due to straining VBs mem-allocator (CoTaskMemAlloc) -
regarding "additional allocations, which happen after the Binding of such a huge DataSource took place".
Easiest solution perhaps is, to introduce a "Cell-based OwnerDrawing" instead of Flex-internal drawing,
to avoid these Grid-internal allocations for the formatting-infos.
We use this Cell-OwnerDrawing-mode with the vsFlexGrid, when we bind via vsFlexDataSource to "large log-files" for example.
Olaf
Can I somehow set the CellPicture to a PNG image?
I've just tested it under different OS'es. It seems, that this issue is only under WinXP.Quote:
Originally Posted by Krool
It seems so. Can you share some example of good code for IVBFlexDataSource_GetData sub for huge recordsets? Any of ADO or DAO.Quote:
Originally Posted by Krool
The right solution is to limit the number of entries to some reasonable value (~100 000) using IVBFlexDataSource_GetRecordCount and IVBFlexDataSource_GetData. But I do not know how to do it correctly.Quote:
Originally Posted by Schmidt
I'm just testing the capabilities of the Grid as well as its virtual mode.
This one works fast:
EDITED: The PrevRecord variable should not be a Static. It should be a module-level variable because of using in other subs the same way.Code:Private Function IVBFlexDataSource_GetData(ByVal Field As Long, _
ByVal Record As Long) As String
Static PrevRecord As Long
Dim delta&, i As Long
delta = Abs(Record - PrevRecord)
With RS
If (Record > PrevRecord) Then
For i = 1 To delta
.MoveNext
Next
ElseIf (Record < PrevRecord) Then
For i = 1 To delta
.MovePrevious
Next
End If
IVBFlexDataSource_GetData = .Fields(Field)
End With
PrevRecord = Record
End Function
Krool,
how to limit the number of records in IVBFlexDataSource_GetRecordCount and IVBFlexDataSource_GetData subs correctly? What if there are several hundred million records on the server in one table? We should load them in parts.
You misunderstand the approach somehow, because it's not Krools responsibility to "automatically limit the record-amount" -Quote:
Originally Posted by Nouyana
the Grid just reacts accordingly, to what amount you told him you have, in the Interface-Callback.
If you want "paged mode", then it's up to you, to limit the amount of Records
(which you select from a given Table) - via proper SQL-instructions:
- either via Where-Clause (Select <FldList> From Table Where ID > LastPagedID)
- or better, in conjunction with a Top-Clause (Select Top 10000 <FldList> From Table Where...)
Olaf
I didn't say the "automatically" word. I just asked him to help.Quote:
Originally Posted by Schmidt
Yes, something like this. The problem here will arise at the time of moving through the grig, deleting or editing records. Do you have any working example?Quote:
Originally Posted by Schmidt
The only possibility I see here in order not to make mistakes is to get the value of the Primary Key field from the VBFlexGrid. I don't really like to use this way. It would be better to somehow synchronize rows in Recordset and rows in FlexGrid.
Perhaps the main difficulty is that the grid should be loaded dynamically, that is not at the moment when you reached the last record of the current "page".
Each time you bind a Recordset (via a *generic* behaving binding-class) -Quote:
Originally Posted by Nouyana
the whole thing should "just work" (on that concrete Recordset, when the Binding-Class was implemented properly).
Since that is the case (with any given, potentially "paged" Rs),
you will just have to ensure (with your own paging-related code on the outside of the Grid),
that "the next paged Recordset" you retrieve from the DB, is "properly bound again" (via the Binding-Class).
Special care needs to be taken, in case of Delete- or Add-Operations
(where you need to "re-initiate the Binding", to reflect the now reduced or increased RecordCount properly in the Grid) -
note, that the technique shown in the SQLite-Binding assumes, that we have bound "a disconnected Rs".
I have not used DAO for the last 20 years - and don't have any working "binding-examples".Quote:
Originally Posted by Nouyana
(I primarily use SQLite, for which an example exists).
Olaf
I mean "paged" exampleQuote:
Originally Posted by Schmidt
Based on SQLite-Rs?Quote:
Originally Posted by Nouyana
As said - this would come out as a "quite trivial extension" on top of the "non-paged SQLite-Rs-Demo".
Because only a few "page-navigation-buttons" (or a Pages DropDown-List) would have to be added,
which in their Click-Events ensure: "a new selected, record-limited Rs via SQL", which in turn will be bound again -
and then will behave "in the same way as the non-paged Demo is showing already"
(for the given Rs, currently in the binding-context).
Olaf
So, just do it! Let us see your "trivial" approach.Quote:
Originally Posted by Schmidt
I'd rather "you show me" (preferrably in a new thread of yours, initiated in the main-forum),Quote:
Originally Posted by Nouyana
what you made out of my hints, regarding the:
"extension-code needed, to convert an existing non-paged SQLite-Rs-Demo, into a paged one". ;)
(starting with your initial attempt, in a Zip).
Olaf
Ok. In a couple of days.Quote:
Originally Posted by Schmidt
Yes, a row with 4 columns consumes per cell 64 bytes and 20 bytes for a SafeArray on that row.Quote:
Originally Posted by Schmidt
So, 2,500,000 rows * ((4 * 64) + 20) = 690,000,000 bytes (~658 MB)
I will soon optimize the TCELL structure to consume only 52 bytes. This safes in this example 100 MB.
However, the only solution would be to "omit custom cell formatting". Which can be done relatively easy in the VBFlexGrid. (not redim TCOLS structure)
Question is just in what form. Should a FlexDataSource "automatically" "omit custom cell formatting" ? Because why ownerdraw all when we get at least text and can draw based on columns / row formatting ?
Can we combine both: the automatic and the manual ways? The default would be "omit, until using".Quote:
Originally Posted by Krool
When I once was trying to enhance to vsFlexGrid to hold even more variables per cell I didn't use a matrix for the whole grid, but a linked list holding only the elements which actually used at least one of the extended properties.Quote:
Originally Posted by Krool
Using a dictionary with [row & "*" & col] as the key to access the index of the linked list.
I tested VBFlexGrid in depth a few years ago and found that one of the big differences between it and Fapoint-Spread is performance and capacity. Fapoint-Spread can load millions of rows (dozens of columns per row) of data, but the performance is still very good. However, VBFlexGrid, when loading huge data sets, not only consumes more memory (and causes the computer to freeze), but also performs orders of magnitude slower. IMO, an important reason for this is that VBFlexGrid's data structures (such as TCOLS) are not optimized.Quote:
Originally Posted by Krool
Can you explain, what does it mean: "keep the cell texts non-virtual". For me it looks like literally "NoData":Quote:
Originally Posted by Krool
Attachment 187871
In the cFlexDataSource class:
in the form:Code:Private Function IVBFlexDataSource2_GetFlags() As FlexDataSourceFlags
Dim RetVal As FlexDataSourceFlags
If bKeepCellTextsNonVirtual Then
RetVal = RetVal Or FlexDataSourceNoData
Else
RetVal = RetVal And Not FlexDataSourceNoData
End If
IVBFlexDataSource2_GetFlags = RetVal
End Function
Code:Private Sub chkKeepCellTextsNonVirtual_Click()
oDataSource.KeepCellTextsNonVirtual = CBool(chkKeepCellTextsNonVirtual)
Set VBFlexGrid1.FlexDataSource = oDataSource
End Sub
Update released.
Memory consumption of TCELL struct reduced from 64 bytes to 52 bytes.
This means the normal .TextMatrix must be used to populate the cell texts. GetData/SetData is not used on IVBFlexDataSource.Quote:
Originally Posted by Nouyana
Oh, I understand. I can use virtual checkboxes and virtual ToolTips, but not GetData/SetData. Thank you!Quote:
Originally Posted by Krool
Too much memory consumption when using the FlexDataSourceUnboundFixedColumns flag.
Attachment 187876
Better results with v.1.6.5. The previous GIF was with v.1.6.0.Quote:
Originally Posted by Krool
Attachment 187879
It feels like that "empty column" have strings set? (As can be seen by the >)Quote:
Originally Posted by Nouyana
It's very difficult to further reduce TCELL structure.. too many features ;)
Also introducing a "simple mode" is not very easy to achieve w/o usage of conditional compilation. Beside the fact of the additional overhead in code. (Two structures side by side "if else" which to use)
So I keep things as there are...
Info: the grid can't use a simple 2D array. TCOLS has it's sense..
It's optimized for sorting rows.
Yes, but the only one cell.Quote:
Originally Posted by Krool
By the way, what value it would be better to use when I leave the row? "" or vbNullString or what?
Let it be a conditional compilation. Why not?Quote:
Originally Posted by Krool
Let's count a little. In v.1.6.0 it was 614 MB per 1 fixed column with 1 750 000 rows. In v.1.6.5 the same variable is 496 MB. So it was reduced 614-496=118 MB or 70 bytes per cell. It was not only about TCELL structure. And now we have another reason too, I'm sure. You have changed something else.Quote:
Originally Posted by Krool
PS I'm an economist:)