Speed up this very short piece of SQL?

[plenderj]

I have the following code in an XML Web Service, and need to speed it up a bit. It takes about half a second to complete, and its called about 17000 times in a row. Any thoughts?

<WebMethod()> Public Function UpdateProductInLocalDB(ByVal strDetails As String) As Boolean
        Try            
            Dim strTemp() As String = Split(strDetails, vbLf)
            Dim strSQLStatement As String, mySqlConnection As SqlConnection
            If strTemp.GetUpperBound(0) = 12 Then
                Dim strProductDetails = "'" & strTemp(0) & "', " & "'" & strTemp(1) & "', " & "'" & strTemp(2) & "', "
                strProductDetails &= "'" & strTemp(3) & "', " & "'" & strTemp(4) & "', " & "'" & strTemp(5) & "', "
                strProductDetails &= "'" & strTemp(6) & "', " & strTemp(7) & ", " & "'" & strTemp(8) & "', "
                strProductDetails &= "'" & strTemp(9) & "', " & "'" & strTemp(10) & "', " & "'" & strTemp(11) & "'"
                strSQLStatement = "DELETE FROM tblProducts WHERE strProductReference = '" & strTemp(0) & "';"
                strSQLStatement &= "INSERT INTO tblProducts VALUES (" & strProductDetails & ");"
                mySqlConnection = New SqlConnection(strConnectionString) : mySqlConnection.Open()
                Dim mySQLCommand As New SqlCommand(strSQLStatement, mySqlConnection)
                mySQLCommand.ExecuteNonQuery() : mySqlConnection.Close() : mySqlConnection = Nothing
                Return True
            Else
                Throw New Exception("strTemp.GetUpperBound(0) != 12")
            End If
        Catch ex As Exception
            ErrorLog(Now & vbCrLf & ex.ToString & vbCrLf & vbCrLf & strDetails & vbCrLf & vbCrLf & strConnectionString & vbCrLf & vbCrLf)
            Return False
        End Try
    End Function

[techgnome]

What about moving the ExecuteNoQuery out of the loop, and create two SQL strings, one with the Delete in it - using "WHERE strProductReference IN (...)" and one with all of the INSERTS in it. Then execute the DELETE SQL, then the INSERT.

Although, I wonder why you are deleting and inserting when a simple update looks like it would suffice.

TG

[plenderj]

Because there is a possibility a product may not yet exist inside the DB. I've modified the code a bit, dumping the entire contents of the table first, and then passing the details to an SP.
I'll post the code in a sec...

[plenderj]

<WebMethod()> Public Sub UpdateProductInLocalDB(ByVal strDetails As String)
        Try
            Dim strSQLStatement As String, mySqlConnection As SqlConnection
            Dim mySQLCommand As SqlCommand
            If Not blnCleared(0) Then
                blnCleared(0) = True
                strSQLStatement = "DELETE FROM tblProducts"
                mySqlConnection = New SqlConnection(strConnectionString) : mySqlConnection.Open()
                mySQLCommand = New SqlCommand(strSQLStatement, mySqlConnection)
                mySQLCommand.ExecuteNonQuery() : mySqlConnection.Close() : mySqlConnection = Nothing
                UpdateProductInLocalDB(strDetails)
            Else
                Dim strTemp() As String = Split(strDetails, vbLf)
                Dim strProductDetails = "'" & strTemp(0) & "', " & "'" & strTemp(1) & "', " & "'" & strTemp(2) & "', "
                strProductDetails &= "'" & strTemp(3) & "', " & "'" & strTemp(4) & "', " & "'" & strTemp(5) & "', "
                strProductDetails &= "'" & strTemp(6) & "', " & strTemp(7) & ", " & "'" & strTemp(8) & "', "
                strProductDetails &= "'" & strTemp(9) & "', " & "'" & strTemp(10) & "', " & "'" & strTemp(11) & "'"                
                strSQLStatement = "{CALL SQLAddProductToDB(" & strProductDetails & ")}"
                mySqlConnection = New SqlConnection(strConnectionString) : mySqlConnection.Open()
                mySQLCommand = New SqlCommand(strSQLStatement, mySqlConnection)
                mySQLCommand.ExecuteNonQuery() : mySqlConnection.Close() : mySqlConnection = Nothing
            End If
        Catch ex As Exception
            ErrorLog(Now & vbCrLf & ex.ToString & vbCrLf & vbCrLf & strDetails & vbCrLf & vbCrLf & strConnectionString & vbCrLf & vbCrLf)
        End Try
    End Sub

And the SP:

CREATE PROCEDURE SQLAddProductToDB 
	@param0 varchar(50), @param1 varchar(512), 
	@param2 varchar(512), @param3 varchar(512), 
	@param4 varchar(512), @param5 varchar(512), 
	@param6 varchar(512), @param7 int, 
	@param8 varchar(512), @param9 varchar(512), 
	@param10 varchar(512), @param11 varchar(512)
AS
	INSERT INTO tblProducts VALUES (@param0, @param1, @param2, @param3, @param4, @param5, @param6, @param7, @param8, @param9, @param10, @param11);
GO

[techgnome]

What DBMS are you using? If you are doing a stored procedure, it would be more efficient to do

IF EXISTS (SELECT * FROM tblProducts  WHERE strProductReference = @IDParam)
  BEGIN
    UPDATE tblPRoducts 
     SET ......

  END
ELSE
  BEGIN
    INSERT INTO tblProducts
      (Fields...)
    VALUES
      (Parameters...)
  END

That's how we do it here. Updates if the record is there, inserts it if not.

TG

EDIT: I see now, it looks like you are using MySQL, no? Not sure if it supports the EXISTS keyword. Even still, you could do a Count..... no, wait.... grrr....... Let me think this over.....

[plenderj]

Using MS SQL 2000. The reason I don't want to check if the record exists first, is that, well, why bother?
If the record does exist, we've just performed a search, and then will have to update it - i.e. another search and modification.
If the record does not exist, we've just performed a search, and then we have to insert a new record.

Surely better to just delete the record if its there, and then insert...?

[plenderj]

Okay now this takes 2.5 seconds per 1% of the products listing, which is 17000 products, which equates to 170 products per 2.5 seconds which equates to on average 68 products per second inserted into the DB.

VB.NET Application to Insert Data:

Public Sub sqlAddProductToDB(ByVal product As clsProduct)
        If blnHaveCleared(0) Then
            Dim strSQLStatement As String = "DELETE FROM tblProducts"
            Dim sqlCommand As New SqlCommand(strSQLStatement, New SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=dbShop;Data Source=JAMIE02;Packet Size=4096;Workstation ID=JAMIE02;"))
            sqlCommand.ExecuteNonQuery() : sqlCommand = Nothing
            blnHaveCleared(0) = True
        End If
        [b]myUpdateService.UpdateProductInLocalDB(product.ToString())[/b]
    End Sub

myUpdateService.UpdateProductInLocalDB is a web reference to an XML Web Service running locally. This is the UpdateProductInLocalDB() method:

<WebMethod()> Public Sub UpdateProductInLocalDB(ByVal strDetails As String)
        Try
            Dim strSQLStatement As String, mySqlConnection As SqlConnection
            Dim mySQLCommand As SqlCommand            
            Dim strTemp() As String = Split(strDetails, vbLf)
            Dim strProductDetails = "'" & strTemp(0) & "', " & "'" & strTemp(1) & "', " & "'" & strTemp(2) & "', "
            strProductDetails &= "'" & strTemp(3) & "', " & "'" & strTemp(4) & "', " & "'" & strTemp(5) & "', "
            strProductDetails &= "'" & strTemp(6) & "', " & strTemp(7) & ", " & "'" & strTemp(8) & "', "
            strProductDetails &= "'" & strTemp(9) & "', " & "'" & strTemp(10) & "', " & "'" & strTemp(11) & "'"
            [b]strSQLStatement = "EXECUTE SQLAddProductToDB " & strProductDetails & ""[/b]
            mySqlConnection = New SqlConnection(strConnectionString) : mySqlConnection.Open()
            mySQLCommand = New SqlCommand(strSQLStatement, mySqlConnection)
            mySQLCommand.ExecuteNonQuery() : mySqlConnection.Close() : mySqlConnection = Nothing
        Catch ex As Exception
            ErrorLog(Now & vbCrLf & ex.ToString & vbCrLf & vbCrLf & strDetails & vbCrLf & vbCrLf & strConnectionString & vbCrLf & vbCrLf)
        End Try
    End Sub

The SQLAddProductToDB SP is stored in the MS SQL 2000 Server running locally, and is defined as follows:

CREATE PROCEDURE SQLAddProductToDB 
	@param0 varchar(50), @param1 varchar(512), 
	@param2 varchar(512), @param3 varchar(512), 
	@param4 varchar(512), @param5 varchar(512), 
	@param6 varchar(512), @param7 int, 
	@param8 varchar(512), @param9 varchar(512), 
	@param10 varchar(512), @param11 varchar(512)
AS
	INSERT INTO tblProducts VALUES (@param0, @param1, @param2, @param3, @param4, @param5, @param6, @param7, @param8, @param9, @param10, @param11);
GO

Ahhh

[techgnome]

Originally posted by plenderj
Using MS SQL 2000. The reason I don't want to check if the record exists first, is that, well, why bother?
If the record does exist, we've just performed a search, and then will have to update it - i.e. another search and modification.
If the record does not exist, we've just performed a search, and then we have to insert a new record.

Surely better to just delete the record if its there, and then insert...?

That's the beauty of the EXISTS, it actualy doesn't do a search, it checks the index tables (you DO have an index on the table, right?) and it is faster, because no data is actualy returned, it's not a full lookup. Besides the DELETE is a look up too. It's got to check to see if the record is there to be deleted. 6 of one half-dozen of another. It's been my experience that using EXISTS is faster than looking for the record. I know I've seen an article somewhere regarding the efficientcies of SELECT COUNT (*) vs IF EXISTS when determining if a record is there.

And if you do have indexes on the table, the DELETE/INSERT combo is going to degrade performance since it will need to update statistics after each call.

TG
Just my $0.02 worth

[plenderj]

Hmmmmmm an Index table you say... I'll have to investigate this...

[hellswraith]

It isn't the statement, it is the web service call itself that is taking a while.

What you should do instead is pass in an array of details (1700 of them) to the web service, and loop through them in the service. MUCHO faster. Do a timer, you will see.

May not be completely accurate, but you get my point:

<WebMethod()> Public Function UpdateProductInLocalDB(ByVal strDetails() As String) As Boolean
        Try            
            For Each strDetail As String In strDetails
            Dim strTemp() As String = Split(strDetail, vbLf)
            Dim strSQLStatement As String, mySqlConnection As SqlConnection
            If strTemp.GetUpperBound(0) = 12 Then
                Dim strProductDetails = "'" & strTemp(0) & "', " & "'" & strTemp(1) & "', " & "'" & strTemp(2) & "', "
                strProductDetails &= "'" & strTemp(3) & "', " & "'" & strTemp(4) & "', " & "'" & strTemp(5) & "', "
                strProductDetails &= "'" & strTemp(6) & "', " & strTemp(7) & ", " & "'" & strTemp(8) & "', "
                strProductDetails &= "'" & strTemp(9) & "', " & "'" & strTemp(10) & "', " & "'" & strTemp(11) & "'"
                strSQLStatement = "DELETE FROM tblProducts WHERE strProductReference = '" & strTemp(0) & "';"
                strSQLStatement &= "INSERT INTO tblProducts VALUES (" & strProductDetails & ");"
                mySqlConnection = New SqlConnection(strConnectionString) : mySqlConnection.Open()
                Dim mySQLCommand As New SqlCommand(strSQLStatement, mySqlConnection)
                mySQLCommand.ExecuteNonQuery() : mySqlConnection.Close() : mySqlConnection = Nothing
                
            Else
                Throw New Exception("strTemp.GetUpperBound(0) != 12")
            End If
            Loop
            Return True
        Catch ex As Exception
            ErrorLog(Now & vbCrLf & ex.ToString & vbCrLf & vbCrLf & strDetails & vbCrLf & vbCrLf & strConnectionString & vbCrLf & vbCrLf)
            Return False
        End Try
    End Function

[plenderj]

Yeah I'd already thought of that and I'll be putting that in

[nemaroller]

Well, after techgnome's and hells suggestion.... which was killer... the only remaining suggestion would probably gain you 1/10th a second, if that...

Dim strProductDetails = "'" & strTemp(0) & "', " & "'" & strTemp(1) & "', " & "'" & strTemp(2) & "', "
                strProductDetails &= "'" & strTemp(3) & "', " & "'" & strTemp(4) & "', " & "'" & strTemp(5) & "', "
                strProductDetails &= "'" & strTemp(6) & "', " & strTemp(7) & ", " & "'" & strTemp(8) & "', "
                strProductDetails &= "'" & strTemp(9) & "', " & "'" & strTemp(10) & "', " & "'" & strTemp(11) & "'"
                strSQLStatement = "DELETE FROM tblProducts WHERE strProductReference = '" & strTemp(0) & "';"
                strSQLStatement &= "INSERT INTO tblProducts VALUES (" & strProductDetails & ");"

That's a heck of a lot of string to throw together....

Stringbuilder or string.concat would make me feel more comfortable and warm and fuzzy inside.....

[plenderj]

Ahhhh. Nice one thanks

[tcorey08]

I'll offer that to improve your overall code performance, try this approach instead of the string concatenation:

<WebMethod()> Public Sub UpdateProductInLocalDB(ByVal strDetails As String)
    Dim myConnection As SqlConnection = New SqlConnection(strConnectionString)
    Dim myCommand As SqlCommand
    Try
        If Not blnCleared(0) Then
            blnCleared(0) = True
            myCommand = new SqlCommand("DELETE FROM tblProducts", myConnection)
            myCommand.Connection.Open()
            myCommand.ExecuteNonQuery()
            UpdateProductInLocalDB(strDetails)
        Else
            Dim strTemp() As String = Split(strDetails, vbLf)
            myCommand = new SqlCommand("SqlAddProductToDB", myConnection)
            myCommand.CommandType = CommandType.StoredProcedure
            
            myCommand.Parameters.Add("@param0", SqlDbType.VarChar, 50).Value = strTemp(0)
            myCommand.Parameters.Add("@param1", SqlDbType.VarChar, 512).Value = strTemp(1)
            myCommand.Parameters.Add("@param2", SqlDbType.VarChar, 512).Value = strTemp(2)
            myCommand.Parameters.Add("@param3", SqlDbType.VarChar, 512).Value = strTemp(3)
            myCommand.Parameters.Add("@param4", SqlDbType.VarChar, 512).Value = strTemp(4)
            myCommand.Parameters.Add("@param5", SqlDbType.VarChar, 512).Value = strTemp(5)
            myCommand.Parameters.Add("@param6", SqlDbType.VarChar, 512).Value = strTemp(6)
            myCommand.Parameters.Add("@param7", SqlDbType.Int).Value = CType(strTemp(7), Integer)
            myCommand.Parameters.Add("@param8", SqlDbType.VarChar, 512).Value = strTemp(8)
            myCommand.Parameters.Add("@param9", SqlDbType.VarChar, 512).Value = strTemp(9)
            myCommand.Parameters.Add("@param10", SqlDbType.VarChar, 512).Value = strTemp(10)
            myCommand.Parameters.Add("@param11", SqlDbType.VarChar, 512).Value = strTemp(11)
            
            myCommand.Connection.Open()
            myCommand.ExecuteNonQuery()
        End If
    Catch ex As Exception
        ErrorLog(Now & vbCrLf & ex.ToString & vbCrLf & vbCrLf & strDetails & vbCrLf & vbCrLf & strConnectionString & vbCrLf & vbCrLf)
    Finally
        myConnection.Close()
        myConnection.Dispose()
        myConnection = Nothing
    End Try
End Sub

This will result in one connection object being handled throughout the procedure, and the parameterized version of the SqlCommand object will enable type casting/checking to insure conformity with the Stored Procedure.