B4J Question SQL BeginTransaction...TransactionSuccessful

cyiwin

Active Member
Licensed User
Longtime User
Inserting rows works fine, but I can't get BeginTransaction and TransactionSuccessful to make it faster. I've tried updating my SQL DataBase with and without these commands and it takes the same amount of time either way. I update 200 rows and it locks up my program (Not Responding) for about 30 seconds each time.

B4X:
Sub SQL_Write_AMT

If SQL1.IsInitialized = False Then    'in case the connection closes
    SQL1.Initialize2("com.mysql.jdbc.Driver", "jdbc:mysql://mywebsite/site", "username", "password")
End If

SQL1.BeginTransaction

Try
   
    For i = 0 To AMT_Size-1            '199
        SQL1.ExecNonQuery("INSERT IGNORE INTO amt_test_bot SET trade_id = '" & AMT_Trade_ID(i) & "', date_time = '" & AMT_Date_Time(i) & _
        "', coin_pair = '" & AMT_CoinPair(i) & "', pri_coin = '" & AMT_Pri_Coin(i) & "', sec_coin = '" & AMT_Sec_Coin(i) & _
        "', market_id = '" & AMT_MarketID(i) & "', trade_type = '" & AMT_Trade_Type(i) & "', price = '" & AMT_Trade_Price(i) & _
        "', quantity = '" & AMT_Quantity(i) & "', total = '" & AMT_Total(i) & "', price_deviation = '" & Price_Deviation(AMT_MarketID(i)) & _
        "', loss_coin = '" & Loss_Coin(AMT_MarketID(i)) & "'")
    Next
   
    SQL1.TransactionSuccessful
   
Catch
    Log(LastException)
    SQL1.Rollback
End Try
End Sub

Also if I stop the program during the Not Responding time, I get a partial write to the DataBase so it doesn't seem to be writing all or none. Can anyone see what I'm doing wrong?
 

Daestrum

Expert
Licensed User
Longtime User
If I remember correctly from SQL (many years ago),
If you are looking for performance increase, look at a batch update.
Your code it hitting the database multiple times inserting 1 record at a time, whereas the batch saves them up , then applies in one hit.
If any record in the batch fails, all the batch is rolled back
http://www.mkyong.com/jdbc/jdbc-preparedstatement-example-batch-update/. explains quite well.
 
Upvote 0

LucaMs

Expert
Licensed User
Longtime User
Personally I would use ExecNonQuery2, for reasons of clarity and to avoid problems with quotes and spaces (this has nothing to do with the speed problem, I know).

You could use a service module.

Moreover, it is an entry in a remote db: is the connection ok?
 
Upvote 0

cyiwin

Active Member
Licensed User
Longtime User
Thanks for the replies!

LucaMs: I definitely see what you mean. I'm a little new to SQL but I was thinking it may be better to identify each column by name in case I later decide to add extra columns. The connection to the server seems great.

Daestrum: It looks to me like BeginTransaction and TransactionSuccessful is a sort of a batch write. I rewrote the code using AddNonQueryToBatch and ExecNonQueryBatch and it did the exact same thing. It inserted one row at a time to the DataBase.

new code:

B4X:
Sub SQL_Write_AMT

If SQL1.IsInitialized = False Then    'in case the connection closes
    SQL1.Initialize2("com.mysql.jdbc.Driver", "jdbc:mysql://mywebsite/site", "user", "password")
End If

Try
  
    For i = 0 To AMT_Size-1            '199
        SQL1.AddNonQueryToBatch("INSERT IGNORE INTO " & table_name & " VALUES (?,?,?,?,?,?,?,?,?,?,?,?)", Array As Object(AMT_Trade_ID(i), AMT_Date_Time(i), AMT_CoinPair(i), _
        AMT_Pri_Coin(i), AMT_Sec_Coin(i), AMT_MarketID(i), AMT_Trade_Type(i), AMT_Trade_Price(i), AMT_Quantity(i), AMT_Total(i) _
        , Price_Deviation(AMT_MarketID(i)), Loss_Coin(AMT_MarketID(i))))
    Next
  
    SQL1.ExecNonQueryBatch("SQL")
  
Catch
    Log(LastException)
    SQL1.Rollback
End Try  

End Sub

Not sure what to try next...
 
Last edited:
Upvote 0

cyiwin

Active Member
Licensed User
Longtime User
It looks like AddNonQueryToBatch...ExecNonQueryBatch will probably work. It still takes 30 seconds for the SQL DB to update but my program doesn't have to wait. Good enough for me, thanks guys.
 
Upvote 0

cyiwin

Active Member
Licensed User
Longtime User
My last code post works in Debug mode but when I run it in Release mode I get:

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
The last packet successfully received from the server was 58,444 milliseconds ago. The last packet sent successfully to the server was 20 milliseconds ago.
..............
Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
..............
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.
..............
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
The last packet successfully received from the server was 58,444 milliseconds ago. The last packet sent successfully to the server was 20 milliseconds ago.
..............
Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.

What could cause the connection to close in release mode?

In other SQL subroutines I call RS.Close to close the cursor but I don't call SQL1.Close, shouldn't my connection with the DB server stay open?
 
Upvote 0

cyiwin

Active Member
Licensed User
Longtime User
Using the connection pool fixed my connection issues, thanks.

I still have the 30 second (Not Responding) problem though. I don't know why sending 200 rows of data would cause such a long hang. I've tried using SQL1.BeginTransaction...SQL1.TransactionSuccessful, SQL1.AddNonQueryToBatch...SQL1.ExecNonQueryBatch("SQL") and SQL1.ExecNonQuery without any batch attempts. All cause my program to hang for the same amount of time while the data is written to my remote DB. It's as if the batch commands are having no effect.
 
Upvote 0

eps

Expert
Licensed User
Longtime User
Have you looked at the indexes on the table in question? This usually gives the best performance increase when working with databases.

You mention a remote DB, I think, so how do you know that it's the SQL that is taking the time and not the network traffic?
 
Upvote 0

eps

Expert
Licensed User
Longtime User
What about breaking out the array part and setting discrete values before the SQL statement and then inserting these into the table? Ideally you need to have as clean/unfussy SQL as possible.

What I mean is :

set value1 = AMT_whatever(i)

and then pass value1 to the SQL as opposed to AMT_whatever(i)
 
Upvote 0

cyiwin

Active Member
Licensed User
Longtime User
Thanks for the advice Eps.

Have you looked at the indexes on the table in question?
I had the main column set to PRIMARY and UNIQUE but I forgot INDEX. Unfortunately no change though.
What about breaking out the array part and setting discrete values before the SQL statement and then inserting these into the table?
I gave it a shot but no change.
how do you know that it's the SQL that is taking the time and not the network traffic?
I don't know how SQL works in the background. I was thinking the program should just send the data and keep moving on. I wonder what would cause a program to go non responsive until it has confirmed the DB has been updated?

Edit: I have noticed, as my program goes non responsive, the SQL server takes the whole time to update. I can keep refreshing the DataBase and watch the 200 rows slowly updating. When it is done updating my program goes back to visually running. The timer on the program skips several seconds indicating that it was counting the whole time but other parts of my program didn't run and causes issues.
 
Last edited:
Upvote 0

cyiwin

Active Member
Licensed User
Longtime User
Also the server company is Web Hosting Hub and I'm using their phpMyAdmin if that makes any difference.
 
Upvote 0

cyiwin

Active Member
Licensed User
Longtime User
I see my mistake.
I was using
B4X:
SQL1.Close
instead of
B4X:
pool.GetConnection.Close
Now with the correct close my program is free to run while the SQL DB updates.

I need an embarrassed smiley.
 
Upvote 0

cyiwin

Active Member
Licensed User
Longtime User
I wanted to post a basic Batch SQL example in case anyone needs...
B4X:
#Region  Project Attributes
    #MainFormWidth: 600
    #MainFormHeight: 400
    #AdditionalJar: sqlite-jdbc-3.7.2
    #AdditionalJar: mysql-connector-java-5.1.31-bin.jar
#End Region

Sub Process_Globals
    Private fx As JFX
    Private MainForm As Form
    Dim pool As ConnectionPool
End Sub

Sub AppStart (Form1 As Form, Args() As String)
    MainForm = Form1
    'MainForm.RootPane.LoadLayout("Layout1") 'Load the layout file.
    MainForm.Show
    pool.Initialize("com.mysql.jdbc.Driver", "jdbc:mysql://serversite/site", "user", "pword")
    SQL_Write_Data
End Sub

Sub SQL_Write_Data
    Log("SUB: SQL_Write_Data")
    Try  
        Dim SQL1 As SQL = pool.GetConnection
        Dim Value(5) As String
        For i = 0 To 199
            For j = 0 To 4
                Value(j) = Rnd(0, 1000)
            Next
            SQL1.AddNonQueryToBatch("INSERT IGNORE INTO test_table VALUES (?,?,?,?,?,?)", Array As Object(i, Value(0), Value(1), Value(2), Value(3), Value(4))) 
            Log("Values " & i & " entered")
        Next
        SQL1.ExecNonQueryBatch("SQL")
        pool.GetConnection.Close
    Catch
        Log(LastException)
    End Try
End Sub

Sub SQL_NonQueryComplete (Success As Boolean)
    Log("NonQuery: " & Success)
    If Success = False Then Log(LastException)
End Sub
 
Upvote 0
Top