Member Avatar for AncieV

I would like to know how to increase the speed of data loading into flexgrid from Database(SQL Server).currently im using a loop but this takes a lot of time when there are alot of data...pls give me a solution to this problem..

Thank you

  • 4 Contributors
  • 3 Replies
  • 210 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by PoisonedHeart

Dani AI

Generated

— the biggest wins come from reducing per-row work (both on the DB side and the COM/UI side) rather than micro-optimizing the loop. asked for code; and pointed at UI redraw/DoEvents approaches which help responsiveness, but the fastest, most repeatable improvements are: (1) fetch rows in bulk, (2) limit what the database returns, and (3) bind or use a grid that supports bulk/virtual loads.

A fast pattern with ADO is to fetch into a Variant array with GetRows (one COM call) and then write the array into the grid with tight loops on the client side. GetRows returns a columns-by-rows array, so iteration is cheap compared with per-record COM calls:

Dim rs As ADODB.Recordset
Dim arr As Variant
rs.Open sql, conn, adOpenForwardOnly, adLockReadOnly
arr = rs.GetRows()   ' arr(fieldIndex, rowIndex)
rs.Close

For r = 0 To UBound(arr, 2)
  For c = 0 To UBound(arr, 1)
    Grid.TextMatrix(r + firstRow, c) = arr(c, r)
  Next
Next

Notes: GetRows uses more memory (the whole result in RAM) but greatly reduces interaction overhead. For very large result sets, load in chunks (GetRows(n)) or implement server-side paging.

Limit results server-side and add appropriate indexes. Examples:

-- SQL Server 2012+
SELECT cols FROM MyTable
WHERE ...
ORDER BY keycol
OFFSET @offset ROWS FETCH NEXT @pageSize ROWS ONLY;

-- SQL Server 2005+ (ROW_NUMBER)
WITH T AS (
  SELECT cols, ROW_NUMBER() OVER (ORDER BY keycol) rn FROM MyTable WHERE ...
)
SELECT cols FROM T WHERE rn BETWEEN @start AND @end;

Finally, prefer binding a data-aware grid or a third-party grid with virtual mode when possible (fewer client loops). Quick checklist: select only needed columns, use a forward-only/read-only recordset, fetch in bulk, and add server-side paging/indexes. These changes typically reduce load time far more than UI-only tricks.

Recommended Answers

All 3 Replies

Member Avatar for AndreRet

Ancie, please show us the loop code you are using to load the grid. Your problem might lie there.

Member Avatar for vb5prgrmr

If the form is shown, you might be able to use LockWindowUpdate API in the following manner

LockWindowUpdate Grid.hWnd
'do loading here
LockWindowUpdate 0

Another method would be to hide the grid that you are loading and show a dummy grid along with a progress bar...

Good Luck

Member Avatar for PoisonedHeart

You might as well try to make the control invisible first, then when all the data has been loaded, set the control's visibility to true, for some controls, it increases the speed...

and just to add up if the loop makes your app hang...then try to insert a DoEvents keyword before the loop statement ending keyord...

For i = 0 to rs.RecordCount -1

'Your code here....to populate the flexgrid

DoEvents
Next i

or with the While loop statements, just add the DoEvents keyword before the Loop keyword

Do NOT While rs.EOF

'Your code here....to populate the flexgrid

DoEvents
Loop

NOTE: please take note of your usage of the DoEvents keyword, especially when you are performing long executing codes, it might perform undesireable effects...just like this one:

Private Sub Form_Load()
'While the loop executes, try to close the form, and notice that the loop still being executed even if the form has been unloaded...
Me.Show
For i = 0 To 10000
    Me.Caption = i
    DoEvents
Next i
End Sub

Anyway, the decision is all yours, of what technique you would use...Happy Coding!

Edited by PoisonedHeart because: wrong spelling and grammars...
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.