Parameter efficiency

[Shaggy Hiker]

In the interest of wringing a bit of extra speed out of some code, I stumbled across something that surprised me: It looks like using a parameterized query in a SQL query is faster than concatenating values directly into the string. I haven't tested this, but I was wondering if anybody could back that up?

It doesn't seem reasonable, to me, but perhaps it is.

[Shaggy Hiker]

After a bit of minor testing using these two functions:

    Private Sub test1(ByVal arg As Object)
        Dim x As Integer
        Dim n As Long
        Dim mx As Integer = 0
        Dim y As Integer = 0
        Dim dt As New DataTable
        Dim drL As New List(Of DataRow)
        Dim st1 As String

        Using cn As New SqlClient.SqlConnection(mConString)
            cn.Open()
            Using cmd As SqlClient.SqlCommand = cn.CreateCommand
                For x = 1 To 1000
                    cmd.CommandText = "SELECT RUID FROM HATCH_RearingUnit WHERE TerminationEventID = '" & Guid.Empty.ToString & "'"
                    Dim obj As Object = cmd.ExecuteScalar
                    n += 1
                Next x
            End Using
        End Using
    End Sub

    Private Sub test2(ByVal arg As Object)
        Dim x As Integer
        Dim n As Long
        Dim mx As Integer = 0
        Dim y As Integer = 0
        Dim dt As New DataTable
        Dim drL As New List(Of DataRow)
        Dim st1 As String

        Using cn As New SqlClient.SqlConnection(mConString)
            cn.Open()
            Using cmd As SqlClient.SqlCommand = cn.CreateCommand
                cmd.Parameters.AddWithValue("@p1", Guid.Empty)
                For x = 1 To 1000
                    cmd.CommandText = "SELECT RUID FROM HATCH_RearingUnit WHERE TerminationEventID = @p1"
                    Dim obj As Object = cmd.ExecuteScalar
                    n += 1
                Next x
            End Using
        End Using

    End Sub

It appears that I was kind of wrong, though only slightly. The concatenated version beat the parameterized version in every test, though the difference was extremely trivial. Upon further review, I realized that I had messed up the query and as I wrote it, it would never return anything because I was using the wrong field in the WHERE clause for the test. Therefore, I changed the field to one that would return something. The second test showed a very murky picture. Both tests were run twice in this pattern:

Test1
Test2
Test2
Test1

Once I had changed to using a query that would return something, neither test won all tries, and the variability in timing for the different tests in a series was greater than the difference between the tests. Therefore, there doesn't appear to be any real difference in performance between concatenated and parameterized queries.

This test didn't perfectly mimic the situation where I thought I was seeing a significant advantage for parameterized queries, so if there is any evidence that others have one way or the other, I'd be interested in seeing it.

[techgnome]

It used to be the case... in prior versions of SQL Server, prior to 2005, sprocs and parameterized queries had their execution plans cached on the first run... which was fine, as long as the first time you ran it, it was for the best case, creating the most efficient plan... that's no longer the case, with execution plans no longer being cached, and the sprocs no longer being pre-compiled. There is still some caching going on, but it's now at the connection level as long as the connection string doesn't change.

I would say the two are probably on par performance wise... I've gotten mixed results... it seems to be data-dependent. For me though parameters weren't always about performance and more about sanity and data protection from a mis-concatenation.

So, your results don't exactly surprise me.

-tg

[TysonLPrice]

Your comment about SPROCS not being pre-compiled caught me off guard. Looking into it I guess they never were, based on the post. Learn something new everyday.

http://www.scarydba.com/2009/09/30/p...-fact-or-myth/

[wild_bill]

I have seen cases where specifying the parameter type causes the query to execute quicker. This was several years ago, so I don't have the example.

[Shaggy Hiker]

I thought I had seen such a case in my own code. However, I think I changed more than one thing and it got faster. Since I don't remember what the other thing I changed was, it's just as likely that either:

1) It was the thing I don't remember that had the effect.
2) I'm making it all up anyways.

[techgnome]

3) you were hallucinating.

-tg

[Shaggy Hiker]

I left that one out, just in case.