Update Statement Running Very Slowly On MySQL Server

[icycold68]

Hello,

I am running an update statement as part of an SQL query to update some values in a database. The database is quite large (around 230,000 records), so I appreciate updating such a large data set is going to take time, but the website currently hangs for what seems like 4-5 minutes before completing the updates, which is simply too long from a usability point-of-view.

Is it possible to do anything to speed up the update statement please?

I have included reference to the snippet of code containing the update statement below:

Dim intCounter As Integer
Dim strSQL4 As String

Dim myCommand4 As New MySqlCommand

If objDT.Rows.Count > 0 Then
For intCounter = 0 To objDT.Rows.Count - 1
objDR = objDT.Rows(intCounter)

strSQL4 = "UPDATE tbl_sales SET distance_temp = " & objDR("Distance") & " WHERE PostCode = '" & objDR("Postcode") & "'"


myCommand4.Connection = conn
myCommand4.CommandText = strSQL4
myCommand4.ExecuteNonQuery()

Next
End If

Many thanks!

 

[icycold68]

In follow up to my original question, I have decided to try and execute one update statement only to reduce the round trips the server needs to make to the database. I am, however, having problems with constructing a multiple update statement. I thought it would be possible to do this using the code below, but this seems to generate an error. Any ideas how I could do this please?

If objDT.Rows.Count > 0 Then
For intCounter = 0 To objDT.Rows.Count - 1
objDR = objDT.Rows(intCounter)

If y = 0 Then

strSQL4 = strSQL4 & "UPDATE tbl_sales SET distance_temp = " & objDR("Distance") & " WHERE PostCode = '" & objDR("Postcode") & "'"

ElseIf y < objDT.Rows.Count - 1

strSQL4 = strSQL4 & "AND SET distance_temp = " & objDR("Distance") & " WHERE PostCode = '" & objDR("Postcode") & "'"

End If

y += 1

Next
End If

The error generated is shown below:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' SET distance_temp = 14.1998099070215 WHERE PostCode = 'NG31', SET distance_temp' at line 1

 

[IanRyder]

Hi,

It took me a minute or so to try and figure our what you were trying to do but got there in the end. Your work around SQL statement will not work since when you run through your logic you will end up with a statement like this:-

UPDATE tbl_sales SET distance_temp = 1.4545454 WHERE PostCode = 'AB21' AND SET distance_temp = 1.4545454 WHERE PostCode = 'AB22'

This is an invalid SQL statement since as you can see you have multiple SET and WHERE statements.

You could modify your statement to use OR conditions in the WHERE clause to select multiple PostCode's but you can only do this if you know the distance_temp variable is going to be the same value for each PostCode. If not, then you are going to have to revert back to your original SQL statement.

With regards to your original post and SQL statement, the obvious thing to check for is that the PostCode field in the database is indexed. Secondly, move your myCommand4.Connection = conn outside of the for loop. You do not need to set it every time.

Hope that helps,

Cheers,

Ian

 

[icycold68]

Thank you for your advice Ian.

I have done as you suggested and moved the myCommand4.Connection = conn outside of the for loop, which makes it a tiny bit faster.

How would I index the PostCode field in the database if needs be please?

 

[IanRyder]

Hi,

I have never used MySQL so I will need to base my post on SQL server. Hopefully, you can interpret my comments to check your MySQL database?

1) Open your MySQL Database that you are using and expand the tables collection.
2) Select your tbl_sales table, right click your mouse and select Design to get to your table definition.
3) Right click your mouse on any field definition and click Indexes/Keys.
4) Have a look at the indexes listed for the table to see if your PostCode field is indexed or not. If NOT then continue on with this note.
5) In the dialog box, click on Add to add a new index.
6) On the right hand side of the newly created index click on the Column's property and click the ellipses that appears to the right.
7) Change the column name to your Postcode field and click OK.
8) Decide whether your index should be unique or not and set the correct property.
9) Change the Name of the index to something more explanatory if you wish (not necessary).
10) Click close, to close the Indexes/Keys dialog box and click Save to update your table definition.

If you find that your PostCode field was NOT indexed then this should have a huge impact on your timings. Don't forget this is based on SQL Server so you may need to play a bit to check and sort this in MySQL.

Good luck and cheers,

Ian

 

[icycold68]

Ian, having done some research on indexes, the general advice is that indexing a column will actually make an update statement run more slowly as it has to store extra information for each indexed column. Clearly want I want to do is to make the update statement run more quickly, so is this the best route to be taking?

 

[IanRyder]

Hi,

I have no idea what you have read but your web searches have given you flawed and/or misleading information? If you are updating an index field then this may take some additional time to update a record since the index needs to be updated at the same time.

However, and in your case, what you are doing is indexing your SEARCH field, being the PostCode, so that your SEARCH criteria is more efficient. What you are then actually doing is updating your distance_temp field which has no impact on any defined indexes in your table.

If nothing else, give it a try, and if it does not give you the results you are looking for then you can always look for something else to try.

Cheers,

Ian

 

[icycold68]

Thank you for your help Ian, I indexed the PostCode field and this made it much faster :tennis:

 

[IanRyder]

Hi,

Good to hear and I thought it might.

Cheers,

Ian

 

[icycold68]

One more question...

I would like to pick your brains one more time if I could please Ian.

Is it possible to have an update statement that just checks against the first part of a field? For example what I want to do is to set the value of the distance_temp field for any values in the PostCode column that match the first part of a post code entered by the user. The problem I have is that the post codes stored in the PostCode field of the database are the full post codes and I am only want to compare to the first part of the post code. I am currently using the below code to try to achieve this, but it generates an error saying 'FUNCTION gardman.len does not exist'.

strSQL = "UPDATE tbl_sales SET distance_temp = " & objDR("Distance") & " WHERE substring(PostCode,4,len(PostCode)-3) LIKE '" & objDR("Postcode") & "%'"

Any ideas?

Hi,

Good to hear and I thought it might.

Cheers,

Ian

 

[IanRyder]

Hi,

Yes, if your objDR("Postcode") contains, for instance "AB1", then you can select all postcodes in your table using the following selection statement:-

SELECT Postcode FROM dbo.tblPostcodeAreas WHERE (SUBSTRING(Postcode, 1, 3) = 'AB1')

Just replace AB1 with your postcode object and add this to your update statement.

Hope that helps.

Cheers,

Ian

 

[IanRyder]

Hi,

Sorry I should have also posted the alternative, being:-

SELECT Postcode FROM dbo.tblPostcodeAreas WHERE (Postcode LIKE N'AB1%')

Cheers,

Ian

 

[IanRyder]

Hi,

I just had to check my knowledge on the efficiency of using SUBSTRING vs LIKE in SQL queries with indexed fields. In the two examples given make sure you always try to construct your queries based on the LIKE statement in the second example since using SUBSTRING with an indexed field has been proven to be up to 3 times slower in its execution.

Hope that helps.

Cheers,

Ian

 

[cjard]

You ought to be using parameterized queries, not ones built by concatenating values in directly like you have done here

If your number of rows to update is small, you could execute just one query but I don't think the speed adavantage would be worth it

If you have a large number of rows to update, you should insert them into a temp table first, that has a primary key equal to the main table's primary key and run a query like:

UPDATE (SELECT maintable.distance, temptable.distance as tempdist FROM maintable INNER JOIN temptable ON maintable.id = temptable.id) SET distance = tempdistance

If you don't make the temptable have a primary key you fall into a non-key preserved join trap whereby one row in the main table could link to many rows in the temp table, and the db engine wouldnt know which temp table row's distance to update the main one to.. for updating a select statement such as this the relationship must be 1:1 or MANY:1 where the table to be updated is the many side