B4J Question [ABMaterial] I need to load a table with more than 60,000 records

[Johan Hormaza]

I need to load a table with more than 60,000 records, which ABMTable can not do, and if it does it is too slow and, sometimes, the application hangs.
Is there another type of table much simpler and lighter that you can use?
Thank you





Johan.

 

[OliverA]

Not an answer, just a question. Why do you need to load 60000 records?

 

[Johan Hormaza]

Not an answer, just a question. Why do you need to load 60000 records?

My client asks for it that way.

 

[Harris]

My client asks for it that way.

Your client does not know what he/she is asking for...

 

[Johan Hormaza]

Your client does not know what he/she is asking for...

What are you suggesting?

 

[Harris]

Is there another type of table much simpler and lighter that you can use?

Yes, it is called ABMTable (sound familiar) - with pagination - or filters. No one wants to (or can) browse 60K of records...

 

[OliverA]

What is the client going to do with that? Scroll through it? What is the purpose of so many records at one time?

 

[Harris]

I think this was covered in the dummies sessions...
If not, post some code and let's see what we can do...

 

[Johan Hormaza]

This is the time when the table is filled with jRDC2

Sub LLenaTabla
    Dim req As DBRequestManager = CreateRequest
    Dim cmd As DBCommand = CreateCommand("consultDatabase",Array(Archivo))
    Wait For (req.ExecuteQuery(cmd,0,Null)) JobDone(j As HttpJob)
    If j.Success Then
        req.HandleJobAsync(j, "req")
        Wait For (req) req_Result(res As DBResult)
        CargarTable(res,TablaPrincipal)
    Else
        Log("Error de consulta de base de datos")
    End If
    j.Release
End Sub


Private Sub CargarTable(resultado As DBResult, TableV As ABMTable)' As ResumableSub
    Dim ui As Int = 0
    TableV = page.Component("TablaPrincipal")
    TableV.Clear
    Dim cols, cell,cell2 As List
    cols.Initialize
    cell.Initialize
    cell2.Initialize
    For c = 0 To resultado.Columns.Size -1
        cols.Add(resultado.Columns.GetKeyAt(c))
    Next
    TableV.SetHeaders(cols)
    cell = resultado.Rows
    For Each Record() As Object In cell
        Dim R(resultado.Columns.Size) As String
        Dim FieldV As String
        For col = 0 To resultado.Columns.Size - 1
            FieldV = Record(col)
            R(col) = FieldV
        Next
        cell2.Add(R)
        TableV.AddRow("table" & ui ,r)
        ui = ui + 1
    Next
    TableV.SetFooter("Usuario: " & Archivo, 12,"headerfooter")
    TableV.Refresh
End Sub

 

[Harris]

This is the time when the table is filled with jRDC2

What is the SQL statement for "consultDatabase"? I suspect it is "Select * from TableA" - or something to that effect...
Try to include a "where" clause (ie - where record_date >= DateTime.Now - (30 * DateTime.TicksPerDay) ). This will reduce the total records to something more manageable (less than 60K).

Your sub "CargarTable", however correct, does nothing to LIMIT the number of records in view at any given time.
This topic was covered in lessons 3 and 3a of the dummies session (which I note you liked).
It covers how to, where to and why we use pagination, filters and LIMITS to show (manage) records from our (perhaps large) table Resultset.
You could use these principals to modify your code above and achieve what you need...

A quick example. Yes, more code but much better control - and fast!:

Private Sub LoadUsers(fromPage As Int)

    ' Here is where we populate the ABMtable with records from our MySQL table
    ' Since we don't want all the records initially (could be hundreds), we will use pagination to limit what is fetched from the server
    ' Use the query to order your table if you desire

    ' Get the ABMTable component...
    Dim tblUsers As ABMTable = page.Component("tblUsers")  ' get the table defined in ConnetPage...
    
    ' create a SQL cursor
    Dim SQL As SQL = DBM.GetSQL
    
    ' get number of users (select count query) - used in table footer and for pagination....
    ' Note: default Filter var is - " WHERE UserActive <> 99 "
    
    Dim numusers As Int = DBM.SQLSelectSingleResult(SQL, "SELECT Count(UserId) as IDS FROM users " & Filter)
    
    '**********************************************************************************************************
    '**********************************************************************************************************
    '
    ' Get a list of records - LIMIT these by pagination (page number) and number of records to show - iRecs (global var set to 4 currently)
    ' NOTE: We are selecting "certain" fields from the table - and in a certain order - the same order as title headers
    ' tblUsers.SetHeaders( Array As String ("ID"  , "Access Type"   , "Person's Name"    , "Login (username)"    , "Password"    ,"Photo", "Active" ,"Open/Edit", "Delete"  ))
    '
    '**********************************************************************************************************
    '**********************************************************************************************************
    Dim qry As String =  "SELECT UserID, UserType,UserName,UserLogin,UserPassword,s1,UserActive  FROM users "& Filter & " LIMIT " & ((fromPage - 1) * iRecs) & ", "&iRecs
    Dim users As List = DBM.SQLSelect(SQL, qry , Null)
    If users.Size = 0 And fromPage > 1 Then
        ' we are on a page without any lines (maybe removed by other user?)
        DBM.CloseSQL(SQL)
        fromPage = fromPage - 1
        LoadUsers(fromPage)
        Return
    End If
    
    ' if your search returns no results - reset filter and try again...
    If users.Size = 0 And Filter.Length > 3 Then

        Dim searchUser As ABMInput = page.Component("searchUser")
        searchUser.Text = ""
        searchUser.Refresh
        page.ShowToast("srch1","toastgreen"," No Records Found! Click Search Again...",2000,False)
    End If
    Log(" Search was  Found: "&qry)
    
    tblUsers.SetFooter("Total number of users: " & numusers, 12,"bgc")
    
    ' clear any old stuff in the table so we may add new...
    tblUsers.Clear
    
    
    For i = 0 To users.Size - 1
    
        ' our list is a map of field names  and values
        ' we create a new map for each list item
        Dim tblFields As Map = users.Get(i)
        '
        Dim rCellValues As List
        Dim rCellThemes As List
        rCellValues.Initialize
        rCellThemes.Initialize

        '**********************************************************************************************************
        '**********************************************************************************************************
        '**********************************************************************************************************
        ' In this example, we shall add ABMTable fields by users.field name...
        ' It does not matter in what order we add items now since our query defined the order based on our headers...
        '
        '**********************************************************************************************************
        '**********************************************************************************************************

        For j = 0 To tblFields.Size - 1  ' set the var - Value of map...
            
            ' This "var" is used for each "GetKeyAt" that follows....
            Dim var As String
            var =     tblFields.GetValueAt(j)
            If var = "null" Then             ' init as string to avoid null_pointer error
                var = ""
            End If

            If tblFields.GetKeyAt(j) = "usertype" Then       '  if users.field name = "this name" then.....
                Dim ut As Int =  tblFields.Get("usertype")
                Select Case ut
                    Case 0
                        rCellValues.Add("Guest")
                    Case 1
                        rCellValues.Add("Admin")
                    Case 2
                        rCellValues.Add("User")
                End Select
                rCellThemes.Add("nocolor")
                Continue
            End If
            

            If tblFields.GetKeyAt(j) = "userid" Then
                rCellValues.Add(var)
                rCellThemes.Add("nocolor")
                Continue
            End If
            
                
            If tblFields.GetKeyAt(j) = "username" Then
                Dim pd As ABMLabel
                pd.Initialize(page,"usrn"&j, var ,ABM.SIZE_H5,False,"")
                rCellValues.Add(pd)
                rCellThemes.Add("nocolor")
                Continue
            End If

            If tblFields.GetKeyAt(j) = "userlogin" Then
                Dim pd As ABMLabel
                pd.Initialize(page,"usrl"&j,var,ABM.SIZE_PARAGRAPH,False,"")
                rCellValues.Add(pd)
                rCellThemes.Add("nocolor")
                Continue
            End If

            '  These are our person pictures...
            ' Make sure you have a folder "dumb" under "images" - which contain the images supplied with this lesson
            ' The table was initially populated using the method "checkpics" found above...
            '  Notice how some images are round and larger than others....
            ' the "var" contains the image name...
            If tblFields.GetKeyAt(j) = "s1" Then
                    Try
                        Dim img As ABMImage
                        img.Initialize(page , "img"&j ,"../images/dumb/"&var, 1.0)

                        If i Mod 2 = 0 Then
                           img.IsCircular = True
                           img.SetFixedSize( 150,130)
                          
                        Else
                           img.SetFixedSize( 100,80)
                           img.IsCircular = False
                        End If   
                              
                        rCellValues.Add(img)
                        rCellThemes.Add("nocolor")
                    Catch
                        rCellValues.Add("No Photo Assigned")
                        rCellThemes.Add("nocolor")
                    End Try
                    Continue
            End If


            If tblFields.GetKeyAt(j) = "userpassword" Then
                Dim pd As ABMLabel
                pd.Initialize(page,"usrp"&j,var,ABM.SIZE_PARAGRAPH,False,"")
                rCellValues.Add(pd)
                rCellThemes.Add("nocolor")
                Continue
            End If


            If tblFields.GetKeyAt(j) = "useractive" Then
                Dim sw As ABMSwitch
                Dim st As Boolean = (var > 0)
                sw.Initialize(  page,"usrsw"&j,  "On", "Off",  st,"switch")
                sw.Enabled = False
                rCellValues.Add(sw)
                rCellThemes.Add("nocolor")
                Continue
            End If


        Next
        
        ' add our edit and delete buttons to each row of the table
        ' the edit button is blue (by default - no theme)
        
        Dim btnEdit As ABMButton
        btnEdit.InitializeFloating(page, "btnEdit", "mdi-action-visibility", "")
        
        ' TIP: uncomment the line below and watch the height of each row shrink.  Use small buttons for more rows per page when required
        ' btnEdit.Size = ABM.BUTTONSIZE_SMALL
        
        rCellValues.Add(btnEdit)
        rCellThemes.Add("openedit")

        ' the delete button is red (uses a theme - btnred).  Make the button size small - just for the heck of it...
        Dim btnDelete As ABMButton
        btnDelete.InitializeFloating(page, "btnDelete", "mdi-action-delete",  "btnred")
        'btnDelete.Size = ABM.BUTTONSIZE_SMALL
        rCellValues.Add( btnDelete)
        rCellThemes.Add( "openedit")
        
        ' Now add a ABMTable row with the list items.  This list must match the number of headers you defined for this abmtable!
        tblUsers.AddRow("uid_"&i,  rCellValues)

        ' make sure you have as many items in rCellThemes as in rCellValues!  Must follow IMMEDIATELY AFTER AddRow!
        tblUsers.SetRowThemes( rCellThemes)
    Next

    ' refresh the table!!!
    tblUsers.Refresh
    
    DBM.CloseSQL(SQL)
    
    
    ' Here we are setting the pagination component.
    ' It determines how many pages we have - and which page is currently in view.
    Dim pagination As ABMPagination = page.Component("pagination")
    
    If (numusers Mod iRecs > 0) Or (numusers = 0) Then
        numusers = numusers/iRecs + 1
    Else
        numusers = numusers/iRecs
    End If

    
    pagination.SetTotalNumberOfPages(numusers)
    pagination.SetActivePage(fromPage)
    pagination.Refresh
    
    Log(" Finished loading users")
    
    
End Sub

 

[Johan Hormaza]

What is the SQL statement for "consultDatabase"? I suspect it is "Select * from TableA" - or something to that effect...
Try to include a "where" clause (ie - where record_date >= DateTime.Now - (30 * DateTime.TicksPerDay) ). This will reduce the total records to something more manageable (less than 60K).

Your sub "CargarTable", however correct, does nothing to LIMIT the number of records in view at any given time.
This topic was covered in lessons 3 and 3a of the dummies session (which I note you liked).
It covers how to, where to and why we use pagination, filters and LIMITS to show (manage) records from our (perhaps large) table Resultset.
You could use these principals to modify your code above and achieve what you need...

A quick example. Yes, more code but much better control - and fast!:

Private Sub LoadUsers(fromPage As Int)

    ' Here is where we populate the ABMtable with records from our MySQL table
    ' Since we don't want all the records initially (could be hundreds), we will use pagination to limit what is fetched from the server
    ' Use the query to order your table if you desire

    ' Get the ABMTable component...
    Dim tblUsers As ABMTable = page.Component("tblUsers")  ' get the table defined in ConnetPage...
   
    ' create a SQL cursor
    Dim SQL As SQL = DBM.GetSQL
   
    ' get number of users (select count query) - used in table footer and for pagination....
    ' Note: default Filter var is - " WHERE UserActive <> 99 "
   
    Dim numusers As Int = DBM.SQLSelectSingleResult(SQL, "SELECT Count(UserId) as IDS FROM users " & Filter)
   
    '**********************************************************************************************************
    '**********************************************************************************************************
    '
    ' Get a list of records - LIMIT these by pagination (page number) and number of records to show - iRecs (global var set to 4 currently)
    ' NOTE: We are selecting "certain" fields from the table - and in a certain order - the same order as title headers
    ' tblUsers.SetHeaders( Array As String ("ID"  , "Access Type"   , "Person's Name"    , "Login (username)"    , "Password"    ,"Photo", "Active" ,"Open/Edit", "Delete"  ))
    '
    '**********************************************************************************************************
    '**********************************************************************************************************
    Dim qry As String =  "SELECT UserID, UserType,UserName,UserLogin,UserPassword,s1,UserActive  FROM users "& Filter & " LIMIT " & ((fromPage - 1) * iRecs) & ", "&iRecs
    Dim users As List = DBM.SQLSelect(SQL, qry , Null)
    If users.Size = 0 And fromPage > 1 Then
        ' we are on a page without any lines (maybe removed by other user?)
        DBM.CloseSQL(SQL)
        fromPage = fromPage - 1
        LoadUsers(fromPage)
        Return
    End If
   
    ' if your search returns no results - reset filter and try again...
    If users.Size = 0 And Filter.Length > 3 Then

        Dim searchUser As ABMInput = page.Component("searchUser")
        searchUser.Text = ""
        searchUser.Refresh
        page.ShowToast("srch1","toastgreen"," No Records Found! Click Search Again...",2000,False)
    End If
    Log(" Search was  Found: "&qry)
   
    tblUsers.SetFooter("Total number of users: " & numusers, 12,"bgc")
   
    ' clear any old stuff in the table so we may add new...
    tblUsers.Clear
   
   
    For i = 0 To users.Size - 1
   
        ' our list is a map of field names  and values
        ' we create a new map for each list item
        Dim tblFields As Map = users.Get(i)
        '
        Dim rCellValues As List
        Dim rCellThemes As List
        rCellValues.Initialize
        rCellThemes.Initialize

        '**********************************************************************************************************
        '**********************************************************************************************************
        '**********************************************************************************************************
        ' In this example, we shall add ABMTable fields by users.field name...
        ' It does not matter in what order we add items now since our query defined the order based on our headers...
        '
        '**********************************************************************************************************
        '**********************************************************************************************************

        For j = 0 To tblFields.Size - 1  ' set the var - Value of map...
           
            ' This "var" is used for each "GetKeyAt" that follows....
            Dim var As String
            var =     tblFields.GetValueAt(j)
            If var = "null" Then             ' init as string to avoid null_pointer error
                var = ""
            End If

            If tblFields.GetKeyAt(j) = "usertype" Then       '  if users.field name = "this name" then.....
                Dim ut As Int =  tblFields.Get("usertype")
                Select Case ut
                    Case 0
                        rCellValues.Add("Guest")
                    Case 1
                        rCellValues.Add("Admin")
                    Case 2
                        rCellValues.Add("User")
                End Select
                rCellThemes.Add("nocolor")
                Continue
            End If
           

            If tblFields.GetKeyAt(j) = "userid" Then
                rCellValues.Add(var)
                rCellThemes.Add("nocolor")
                Continue
            End If
           
               
            If tblFields.GetKeyAt(j) = "username" Then
                Dim pd As ABMLabel
                pd.Initialize(page,"usrn"&j, var ,ABM.SIZE_H5,False,"")
                rCellValues.Add(pd)
                rCellThemes.Add("nocolor")
                Continue
            End If

            If tblFields.GetKeyAt(j) = "userlogin" Then
                Dim pd As ABMLabel
                pd.Initialize(page,"usrl"&j,var,ABM.SIZE_PARAGRAPH,False,"")
                rCellValues.Add(pd)
                rCellThemes.Add("nocolor")
                Continue
            End If

            '  These are our person pictures...
            ' Make sure you have a folder "dumb" under "images" - which contain the images supplied with this lesson
            ' The table was initially populated using the method "checkpics" found above...
            '  Notice how some images are round and larger than others....
            ' the "var" contains the image name...
            If tblFields.GetKeyAt(j) = "s1" Then
                    Try
                        Dim img As ABMImage
                        img.Initialize(page , "img"&j ,"../images/dumb/"&var, 1.0)

                        If i Mod 2 = 0 Then
                           img.IsCircular = True
                           img.SetFixedSize( 150,130)
                         
                        Else
                           img.SetFixedSize( 100,80)
                           img.IsCircular = False
                        End If  
                             
                        rCellValues.Add(img)
                        rCellThemes.Add("nocolor")
                    Catch
                        rCellValues.Add("No Photo Assigned")
                        rCellThemes.Add("nocolor")
                    End Try
                    Continue
            End If


            If tblFields.GetKeyAt(j) = "userpassword" Then
                Dim pd As ABMLabel
                pd.Initialize(page,"usrp"&j,var,ABM.SIZE_PARAGRAPH,False,"")
                rCellValues.Add(pd)
                rCellThemes.Add("nocolor")
                Continue
            End If


            If tblFields.GetKeyAt(j) = "useractive" Then
                Dim sw As ABMSwitch
                Dim st As Boolean = (var > 0)
                sw.Initialize(  page,"usrsw"&j,  "On", "Off",  st,"switch")
                sw.Enabled = False
                rCellValues.Add(sw)
                rCellThemes.Add("nocolor")
                Continue
            End If


        Next
       
        ' add our edit and delete buttons to each row of the table
        ' the edit button is blue (by default - no theme)
       
        Dim btnEdit As ABMButton
        btnEdit.InitializeFloating(page, "btnEdit", "mdi-action-visibility", "")
       
        ' TIP: uncomment the line below and watch the height of each row shrink.  Use small buttons for more rows per page when required
        ' btnEdit.Size = ABM.BUTTONSIZE_SMALL
       
        rCellValues.Add(btnEdit)
        rCellThemes.Add("openedit")

        ' the delete button is red (uses a theme - btnred).  Make the button size small - just for the heck of it...
        Dim btnDelete As ABMButton
        btnDelete.InitializeFloating(page, "btnDelete", "mdi-action-delete",  "btnred")
        'btnDelete.Size = ABM.BUTTONSIZE_SMALL
        rCellValues.Add( btnDelete)
        rCellThemes.Add( "openedit")
       
        ' Now add a ABMTable row with the list items.  This list must match the number of headers you defined for this abmtable!
        tblUsers.AddRow("uid_"&i,  rCellValues)

        ' make sure you have as many items in rCellThemes as in rCellValues!  Must follow IMMEDIATELY AFTER AddRow!
        tblUsers.SetRowThemes( rCellThemes)
    Next

    ' refresh the table!!!
    tblUsers.Refresh
   
    DBM.CloseSQL(SQL)
   
   
    ' Here we are setting the pagination component.
    ' It determines how many pages we have - and which page is currently in view.
    Dim pagination As ABMPagination = page.Component("pagination")
   
    If (numusers Mod iRecs > 0) Or (numusers = 0) Then
        numusers = numusers/iRecs + 1
    Else
        numusers = numusers/iRecs
    End If

   
    pagination.SetTotalNumberOfPages(numusers)
    pagination.SetActivePage(fromPage)
    pagination.Refresh
   
    Log(" Finished loading users")
   
   
End Sub

Thank you! It's what I'm doing ...
And I am using the client jRDC2