B4J Question SQL BeginTransaction...TransactionSuccessful

[cyiwin]

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.

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]

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.

 

[LucaMs]

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?

 

[cyiwin]

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:

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...

 

[cyiwin]

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.

 

[cyiwin]

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?

 

[Erel]

It seems like some network error. I recommend you to use a connection pool when you work with remote databases. This way the pool will manage the connections for you.

 

[cyiwin]

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.

 

[eps]

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?

 

[eps]

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)

 

[cyiwin]

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.

 

[cyiwin]

Also the server company is Web Hosting Hub and I'm using their phpMyAdmin if that makes any difference.

 

[cyiwin]

I see my mistake.
I was using

SQL1.Close

instead of

pool.GetConnection.Close

Now with the correct close my program is free to run while the SQL DB updates.

I need an embarrassed smiley.

 

[cyiwin]

I wanted to post a basic Batch SQL example in case anyone needs...

#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