B4J Question Filter running slow

[aaronk]

Hi,

I am trying to create a small filter to send a firebase notification pending if the value isn't already in the MySQL database.

My B4J is receiving a heap of UDP messages all the time from devices in the field. (approx 30-40 per second, maybe more.)

Depending on the message that is received, will depend if it will send a firebase message. As I don't want to send the firebase notification every time, I want to check the MySQL database if the value exists and it will then send the push notification only if something else cleared the flag first.

Here is what I am doing:

Sub CheckFirebase(ID As String, Topic As String) As Boolean

    Dim returnValue As Boolean
    Dim sql As SQL = pool.GetConnection
    Try
        'work with sql
        Topic = Topic.ToUpperCase
    
        Dim num As Int = sql.ExecQuerySingleResult("SELECT count(*) FROM firebase_notifications WHERE ID = '" & ID & "' AND topic = '" & Topic & "'")
        If num = 0 Then
            ' no notifications enable
            returnValue = False
        Else
            ' notifications are enable
            returnValue = True
        End If
    Catch
        Log(LastException.Message)
    End Try
    sql.Close
    
    ' will return value
    Return returnValue
    
End Sub

Sub RemoveFirebaseFilter(ID As String, topic As String)

    Dim sql As SQL = pool.GetConnection
    Try
        'work with sql
        sql.ExecNonQuery("DELETE FROM `filter` WHERE `ID` = '" & ID & "' AND `topic` = '" & topic.ToUpperCase & "'")
    Catch
        Log(LastException.Message)
    End Try
    sql.Close

End Sub

Sub AddFirebaseFilter(ID As String, topic As String)

    Dim sql As SQL = pool.GetConnection
    Try
        'work with sql
        sql.ExecNonQuery("INSERT INTO `filter`(`timestamp`,`ID`,`topic`) VALUES('" & DateTime.Now & "','" & ID & "','" & topic.ToUpperCase & "')")
    Catch
        'handle failure
    End Try
    sql.Close
    
End Sub

Everytime the UDP message arrives, I am running the function like:

RunFirebase("12AB10012")
RunFirebase("12AB20012")

This will check the MySQL table and check if the user has subscribed to this topic.

If they have subscribed to this topic, it will check to see if this push notification was the last message to be sent for this ID & topic. If it was the last message for this ID & topic then it needs to ignore the message. (if the topic is in the database for this ID then ignore the message.)

If it's not the last message for this ID, then it will add this new flag to the MySQL database, and will delete the old flag.

Otherwise if I run the function multiple times with the same message, then it will send the firebase push notification multiple times, which I don't want.

So basically the topic needs to be different before it can send it again.

My B4A, B4i apps connect to this same B4J app and receives data and does other things in it.

When I run the above, the B4A/B4i apps get commands from this B4J very very slow. Seems to lag for some reason.

If I change the function like:

Sub RunFirebase(msg As String)
    
If msg.SubString2(2,4) = "AB" Then
    ' message contains value
        
    If msg.SubString2(4,5) = "1" Then
        ' check to see if the MySQL table contains this item

            ' run firebase function to send the push notification
            ' code here to send firebase notication (removed it while posting it to the forum since this part is working)
    End If
    
    If msg.SubString2(4,5) = "2" Then
        
            ' run firebase function to send the push notification
            ' code here to send firebase notication (removed it while posting it to the forum since this part is working)
        End If
    End If   
End If

End Sub

This seems to run fast and works. However it doesn't check to see if the push notifications needs to be sent or not and just sends it.

Can't work out why it's running slow when checking the MySQL server, which is running on the same server.

The B4A/B4i apps are communicating to this same B4J app and requesting data other data in the MySQL using my B4J app and it works fine.

For some reason this filter I created (as above) doesn't seem to work that well for some reason. It does work, but very very slow. 5 minutes later the push notification will come through.

I am using the MySQL database so I can save the flags incase I reboot the server etc and it can remember the values.

The only other thing I was thinking of was to load the values from the MySQL database into a map or list, when it first starts.
Then check the map/list rather than sending the request to the MySQL each time, but wasn't sure if this would speed things up, or would have the same result or run out of memory since this map/list could get quite big overtime.

The firebase_notifications table has 6540 rows.
Where the filter has 13156 rows.

Anyone got any ideas on how to make it work faster, or know of anything I might be doing wrong in my above code that could cause it to run slow ?

 

[Erel]

Dim num As Int = sql.ExecQuerySingleResult("SELECT count(*) FROM firebase_notifications WHERE ID = '" & ID & "' AND topic = '" & Topic & "'")

This should be considered broken code. Why write complicated and vulnerable code instead of writing:

Dim num As Int = sql.ExecQuerySingleResult2("SELECT count(*) FROM firebase_notifications WHERE ID =? AND topic =?, Array (ID, Topic))

???

sql.ExecNonQuery("INSERT INTO `filter`(`timestamp`,`ID`,`topic`) VALUES('" & DateTime.Now & "','" & ID & "','" & topic.ToUpperCase & "')")

Same as above.

Add some logs to measure the various steps and find which one is slow.

Dim n As Long = DateTime.Now

    Dim sql As SQL = pool.GetConnection
  Log("A: " & (DateTime.Now - n))
    Try
        'work with sql
        sql.ExecNonQuery("INSERT INTO `filter`(`timestamp`,`ID`,`topic`) VALUES('" & DateTime.Now & "','" & ID & "','" & topic.ToUpperCase & "')")
Log("B: " & (DateTime.Now - n))
    Catch
        'handle failure
    End Try
    sql.Close
Log("C: " & (DateTime.Now - n))
    
End Sub

 

[aaronk]

This should be considered broken code. Why write complicated and vulnerable code instead of writing:

I wrote this by mistake. I have fixed it.

Add some logs to measure the various steps and find which one is slow.

Each seem to range between 150-500ms.

So by the time it finishes running the function (start to finish) it will take approximately 400-700ms.

The B4J app is running on the same server as the MySQL database, so it should be connecting and checking the MySQL fairly quickly but still takes 150-500ms per request.

I counted each request to see how many are actually being processed, and there seems to be approximately 500 requests per minute coming in which runs this function. (Approximately 8-9 requests per second).

I think since it's taking 400-700ms to finish running the function, the rest are buffering up and making everything run slow and lag.

I tried the following experiment..
When the B4J app runs, it will load all the records from the MySQL database into a list in the B4J app.
Rather than checking the MySQL each time, I have made it check the list to see if it exists.

By doing this it seems to take 0-2ms to process the function.

Based on my experiment, it seems that the MySQL requests are making it run slow for some reason.

I did have it connecting to a SQLite database (before I tried using MySQL), but had the same result where it took 400-700ms to process the function.

By checking the list is fine (as its working a lot faster), but now just need to work out how to save the data to the MySQL database in case I need to stop the B4J app, and when I run the B4J app again it can re-load the values back into the list.

 

[Erel]

Why is it a filter and not a regular handler?

The problem is in the database.

Do you have indices on the relevant columns?

 

[OliverA]

The problem is in the database.

Do you have indices on the relevant columns?

Besides that, you can try going totally async

Sub CheckFirebase(ID As String, Topic As String) As ResumableSub

    Dim returnValue As Boolean = False ' For all failures, return False
    
    pool.GetConnectionAsync("SQL1")
    wait for SQL1_ConnectionReady (Success As Boolean, sql As SQL)
    
    If Success Then
        Try
            'work with sql
            Topic = Topic.ToUpperCase
            Dim sf As Object = sql.ExecQueryAsync("SQL", "Select ID FROM firebase_notifications WHERE ID = ? AND topic = ? LIMIT 1", Array As Object (ID, Topic.ToUpperCase))
            Wait For (sf) SQL_QueryComplete (Success As Boolean, rs As ResultSet)
            If Success Then
                If rs.NextRow Then
                    returnValue = True
                End If
            End If
        Catch
            Log(LastException.Message)
        End Try
    End If
    sql.Close
    ' will return value
    Return returnValue
    
End Sub

Sub RemoveFirebaseFilter(ID As String, topic As String) As ResumableSub

    Dim batchSuccess As Boolean = False
    
    pool.GetConnectionAsync("SQL1")
    wait for SQL1_ConnectionReady (Success As Boolean, sql As SQL)
    
    If Success Then
        Try
            'work with sql
            sql.AddNonQueryToBatch("DELETE FROM filter WHERE ID = ? AND topic = ?", Array As Object (ID, topic.ToUpperCase))
            Wait For (sql.ExecNonQueryBatch("SQL")) SQL_NonQueryComplete (Success As Boolean)
            batchSuccess = Success
        Catch
            Log(LastException.Message)
        End Try
    End If
    sql.Close

    If Not(batchSuccess) Then
        'Handle failure
    End If
    
    Return batchSuccess 'Resumable Sub wants a return value
End Sub

Sub AddFirebaseFilter(ID As String, topic As String) As ResumableSub
   
    Dim batchSuccess As Boolean = False
    
    pool.GetConnectionAsync("SQL1")
    wait for SQL1_ConnectionReady (Success As Boolean, sql As SQL)
    
    If Success Then
        Try
            'work with sql
            sql.AddNonQueryToBatch("INSERT INTO `filter`(`timestamp`,`ID`,`topic`) VALUES(?, ?, ?)", Array As Object (DateTime.Now, ID, topic.ToUpperCase))
            Wait For (sql.ExecNonQueryBatch("SQL")) SQL_NonQueryComplete (Success As Boolean)
            batchSuccess = Success
        Catch
            Log(LastException.Message)
        End Try
    End If
    sql.Close

    If Not(batchSuccess) Then
        'Handle failure
    End If
    
    Return batchSuccess 'Resumable Sub wants a return value.
End Sub

Note: untested. This should also answer your question from this thread: https://www.b4x.com/android/forum/threads/sql-background-thread.116286/
Note2: @Erel -> Looks like "backticks" mess with the editors string recognition. In the preview (and maybe once I post), I noticed that the SQL statements for both RemoveFirebaseFilter and AddFirebaseFilter did not show up as Strings. Once I removed the backticks from the query in RemoveFirebaseFilter, the query string showed up as a String. No clue if this is something that can be fixed or if this is just how it is.

 

[Erel]

As this is a server handler it is correct to use the synchronous methods as each handler runs in its one thread anyway.

Looks like "backticks" mess with the editors string recognition. In the preview (and maybe once I post), I noticed that the SQL statements for both RemoveFirebaseFilter and AddFirebaseFilter did not show up as Strings. Once I removed the backticks from the query in RemoveFirebaseFilter, the query string showed up as a String. No clue if this is something that can be fixed or if this is just how it is.

The JavaScript parser is quite simplistic and can't handle more complex cases.

 

[OliverA]

As this is a server handler it is correct to use the synchronous methods as each handler runs in its one thread anyway.

Hm, I thought he is using UDP. Does listening to a UDP socket create handlers like jServer?

The JavaScript parser is quite simplistic and can't handle more complex cases.

Understood. Thanks for the answer.

 

[Erel]

You are correct. I got confused from the "filter" term.

In that case it makes sense to use async methods. Checking the db indices is still important as 500ms per request is too slow.

 

[aaronk]

I guess I should have called it a 'flag' then calling it a 'filter'.

OliverA is correct, a UDP comes in and runs the function which will check the flag to see if it exists in the list (Or MySQL before I loaded it all into a list). If it does exist in the flag then ignore the message, else I need to add it to the flag and then send the push notification.

I agree, 500ms per request is slow.

Is there anything I need to do in MySQL to make the database work faster? I only installed MySQL, setup a user account, created a database and used B4J to create the table etc.

Do you have indices on the relevant columns?

I did make a few changes to the table over the past few days..
I have 2 Colums in this table:

So sorting this with a basic query should be fairly fast from what I can see.
SELECT count(*) FROM filter WHERE Location=.....
Then if the count value is 0 then I am running the add query, else I am running the delete query (to delete the old flag) and then running the add query to add the new flag.

So it should run fairly quickly from my understanding. When I run the same query from phpmyadmin it runs the query in less then 1-2ms. I am guessing since I am running 8-9 query's per second from the B4J app the MySQL server can't keep up and causing it run slow.

Unless there is a setting somewhere in the MySQL server I need to change? Or maybe I am looking at it wrong and there is something else I should be looking at?

 

[Erel]

You haven't posted the updated query so we cannot say too much.
Your assumption is wrong. The problem is not with comparing count to 0. The problem is to get the number of matches. If there are no indices on the relevant columns then the full table will be scanned on each query.

 

[aaronk]

You haven't posted the updated query so we cannot say too much.

I think I make the change to do it like the following..
However, I have deleted this code as I made it load into a list now. But I think I was doing it like the following.

Sub Check(Location As String) As Boolean
    Dim num As Int = 0
    Dim sql As SQL = pool.GetConnection
   
    Try
        'work with sql
        num = sql.ExecQuerySingleResult2("SELECT count(*) FROM filter WHERE Location=?",Array(Location))
    Catch
        'handle failure
        Log(LastException.Message)
    End Try
    sql.Close
   
    If num = 0 Then
        Return False
    Else
        Return True
    End If
End Sub

 

[Erel]

Why is location a string?

You need to do some tests and understand where is the bottleneck. Maybe it is in the query. Maybe in pool.GetConnection.
Maybe change the query to:

SELECT location FROM filter WHERE Location = ?

And use ExecQuery2.

 

[aaronk]

Why is location a string?

This is a string as it's selecting text from the SQL database. Maybe I shouldn't be using a string, and half the problem?

You need to do some tests and understand where is the bottleneck. Maybe it is in the query. Maybe in pool.GetConnection.

Silly question, how can I test the pool.GetConnection? Do you mean double check that the connection is still valid or mean something else?

Maybe change the query to:

SELECT location FROM filter WHERE Location = ?

I will give it a short and see what happens.

 

[Erel]

illy question, how can I test the pool.GetConnection?

Dim n As Long = DateTime.Now
 Dim sql As SQL = pool.GetConnection
Log("1 :" & (DateTime.Now - n))

Measure the query as well.