Others than Doevent, How to load large data

[nUflAvOrS]

Hi All,
I would like to load large amount of data from database. Thus, I'm using Doevents to tie up system resources for a long period.However, by using Doevents would increase loading time of record.Hereon, beside that doevents what else of method have same feature with doevent or better solution of my condition.


Thanks a lot

[RhinoBull]

How do you load your data initially and how much? You may as well present your code [if possible].

[leinad31]

Why are you loading large amount of data from database? Computations? Have you considered processing on the database using stored procedures to reduce traffic?

[RhinoBull]

Or at least filtering so you get fewer records...

[nUflAvOrS]

Thanks Leinad and RhinoBull with extremely Fasy Reply..
I post one of my code

Public Sub LoadAllMachine()
Dim rsGetMachinePos As adodb.Recordset
Dim rsGetStatus As adodb.Recordset
Dim strStream As adodb.Stream
Dim PictureShow As String
Dim i As Integer
Dim vbalvalue As Double, TotalProgress As Double
i = 1
    clearAllMachine
    Set rsGetMachinePos = SQLSELECT("ObjLeft,ObjTop,ObjWidth,ObjLength,Mac_Code,Status,Condition,MachineImage", "Machine_Desc", " WHERE Loc = '" & frmLoadMachineLoc.lvwShowBlock.SelectedItem.Text & "' AND ObjLeft > 0", "", "", "Maintenance")
        Do While Not rsGetMachinePos.EOF
                'DoEvents
                With frmProgressbar
                    .Show
                    .ZOrder (0)
                    .FormName = frmLoadMapofMachine
                    If i = rsGetMachinePos.RecordCount Then
                         vbalvalue = 100
                        .vbalProgressBar1.Value = vbalvalue
                        .vbalProgressBar1.Text = vbalvalue
                    Else
                        vbalvalue = 100 / rsGetMachinePos.RecordCount
                        TotalProgress = TotalProgress + vbalvalue
                        .vbalProgressBar1.Value = Fix(TotalProgress)
                        .vbalProgressBar1.Text = Fix(TotalProgress) & " %"
                    End If
                    
                    .lblProgressbar.Caption = "    Loading Data : " & rsGetMachinePos.Fields(4) & Space(10) & i & " of " & rsGetMachinePos.RecordCount
                End With
                Me.Enabled = False
                mdiForm2.Enabled = False
            Me.Enabled = False
            mdiForm2.Enabled = False
            With lblMachine(i)
                
                Load lblMachine(i)
                .Move rsGetMachinePos.Fields(0), rsGetMachinePos.Fields(1), rsGetMachinePos.Fields(2), rsGetMachinePos.Fields(3)
                .AutoRedraw = True
                .Visible = True
                .Appearance = 0
                .BorderStyle = 1
                .ZOrder (1)
                .MouseIcon = ImageList1.ListImages.Item(1).Picture
                .MousePointer = 99
               
            PictureShow = IIf(IsNull(rsGetMachinePos.Fields(7)), "", rsGetMachinePos.Fields(7))
              If Not PictureShow = "" Then
                    Set strStream = New adodb.Stream
                    strStream.Type = adTypeBinary
                    strStream.Open
                    strStream.Write rsGetMachinePos.Fields(7).Value
                    strStream.SaveToFile "C:\Temp.bmp", adSaveCreateOverWrite
                   .Picture = LoadPicture("C:\Temp.bmp")
                   .PaintPicture .Picture, 0, 0, .ScaleWidth, .ScaleHeight
                   .Tag = rsGetMachinePos.Fields(4)
                    Kill "C:\Temp.bmp"
               End If
                
                    Set rsGetStatus = SQLSELECT("Status", "WoList", " WHERE Status <> 'Completed' AND Mac_code = '" & rsGetMachinePos.Fields(4) & "'", "", "", "Maintenance")
                    If Not rsGetStatus.EOF Then
                        SetupBackground i, rsGetMachinePos.Fields(6), rsGetStatus.Fields(0), rsGetMachinePos.Fields(5), rsGetMachinePos.Fields(0) - 50, rsGetMachinePos.Fields(1) - 50, rsGetMachinePos.Fields(2) + 100, rsGetMachinePos.Fields(3) + 100
                        .Tag = rsGetStatus.Fields(0) & "," & rsGetMachinePos(4)
                    Else
                        SetupBackground i, rsGetMachinePos.Fields(6), "", rsGetMachinePos.Fields(5), rsGetMachinePos.Fields(0) - 50, rsGetMachinePos.Fields(1) - 50, rsGetMachinePos.Fields(2) + 100, rsGetMachinePos.Fields(3) + 100
                        .Tag = "OK" & "," & rsGetMachinePos(4)
                    End If
                
            End With
            i = i + 1
            rsGetMachinePos.MoveNext
        Loop
        rsGetMachinePos.Close
        Unload frmProgressbar
 
End Sub

SP is one of the choice suggested from leinad, but i need to take long time for recode my whole system. I had promised my superior to test run the system on 1st day of the next year. I think would change whole code and reducing loading time by SP in next version.

[leinad31]

The query is not the only bottleneck, its setting up the picturebox array. Consider loading by demand, show only a subset of the data at any given time and load only their pictures instead of everything... the other data show as text until picture required (picture bytes queried separately). That way you also minimize the recordsets you need to return.

[nUflAvOrS]

Thanks advice from leinad31. That was one of the part of my query.
The main problem is if I'm using Doevents to hold the resources it would create longer time to display record. If I do not use the Doevents as the code of my previous post, It would not show the data like

.lblProgressbar.Caption = "    Loading Data : " & rsGetMachinePos.Fields(4) & Space(10) & i & " of " & rsGetMachinePos.RecordCount

at run time.

I want my label to show which record is retrieving.

Thanks leinad :-)

[leinad31]

DoEvents is there to allow other activities such as repaints. If you don;t want to run DoEvents every iteration then use a counter and Mod; If (counter Mod 100) = 0 Then DoEvents. Other than that, you'll have to redesign the process cause right now your really just prioritizing the progress bar rather than optimizing the picture download process.

[nUflAvOrS]

Thanks Leinad31 , Can you providing some tips for me or example how could optimizing the picture download process.