Sql statement using parameters is slow, fast without them.

Question

Hello.

I'm having a bit of trouble with the performance of a parameterized sql statement. The table i'm working with has ~150,000 records with each record having ~30 columns.

This statement executes in 3.5 seconds.

    Dim selectstring As String
    selectstring = "SELECT * FROM LineInfo WHERE jobNum=@jobnum and revision_number=@revnum AND lineNum=@linenum;"
    Dim selectCommand As New SqlClient.SqlCommand(selectstring, Singleton.DbConnection)
    selectCommand.Parameters.Add("@jobnum", "testing1")
    selectCommand.Parameters.Add("@revnum", "0")
    selectCommand.Parameters.Add("@linenum", 13)

    Dim da As New SqlClient.SqlDataAdapter(selectCommand)
    Dim ds As New DataSet

    Try
        da.Fill(ds)
        MsgBox("Done.")
        ds.Dispose()
    Catch ex As System.Exception
        MsgBox(ex.Message)
    End Try

    da.Dispose()

This select statement executes in .0015 seconds.

    Dim selectstring As String
    selectstring = "SELECT * FROM LineInfo WHERE jobNum='testing1' and revision_number=0 AND lineNum=13;"

    Dim selectCommand As New SqlClient.SqlCommand(selectstring, Singleton.DbConnection)
    Dim ds As New DataTable
    Dim a As New SqlClient.SqlDataAdapter(selectCommand)

    Try
        a.Fill(ds)
        MsgBox("Done.")
        ds.Dispose()
    Catch ex As System.Exception
        MsgBox(ex.Message)
    End Try

    a.Dispose()

I've read some here on the forum about parameter sniffing but that seems to pertain to stored procedures. I'm not really sure where to go from here so I thought I'd try you all here at stack overflow. Does anyone know what could be causing this issue?

Answer 1

Run SQL Profiler and check the actual SQL that's hitting the database. Take both queries and run them in Query Analyser / SQL Server Management studio with Show Execution Plan enabled and find where the time is being spent.

If the performance on the server is identical, then check your .NET code again. Is there any reason why you're using a DataSet in the top example and a DataTable in the lower one?

Answer 2

I expect your faster query time is because SQL Server has previously cached your exact query. Try changing up the values and see if it still runs to quickly.

Answer 3

Can't say for sure (not really up on all the intricacies of SQL engines), but the first thing that jumps out at me is the use of string parameters for numeric columns:

selectCommand.Parameters.Add("@revnum", "0")

vs.

revision_number=0

I suspect if you used string rather than numeric values in your second example, it would slow down as well... Try it, see what happens.

Answer 4

Have you been able to consistently reproduce the execution times for each version? Is it possible that you ran the parameterized version first and then when you ran the dynamic SQL version the query was already cached?

Answer 5

I'm doubtful this is the answer, because I don't know VB well, but I see that in the first code snippet, the revnum is set as "0" while the linenum is set as 13 (no quotes).

I don't know VB data typing well enough to be be confident, but could there be some weird behavior where the revnum came as a string and had to be converted to an integer in the process of the SQL query?

Answer 6

I would look specifically at

selectCommand.Parameters.Add("@jobnum", "testing1")

I've had experiences where it was converting the entire index into unicode before doing a lookup. That is a likely culprit here. In this case, you want to create a paramter and set it to type ansi string, or similar.

Answer 7

I believe parameter sniffing applies to this as well since SQL Server caches the execution plan for SQL statements just like it does for Stored procs. You may try to clear the statement cache and rerun the statement with the same parameters where it is fast and see if it speeds up.

Answer 8

I had to change the parameter statements to be:

   selectCommand.Parameters.Add("@jobnum", SqlDbType.Char).Value = "testing1"
   selectCommand.Parameters.Add("@revnum", SqlDbType.Int).Value = 0
   selectCommand.Parameters.Add("@linenum", SqlDbType.Int).Value = 13

Changing just the @revnum parameter wasn't enough, I had to change all of them. Whatever the case, it's working and I now know how to deal with it.

Thanks.

Answer 9

I don't know if your .net driver has something similar, but the JDBC driver has an option "sendStringParametersAsUnicode", see http://msdn.microsoft.com/de-de/library/ms378988.aspx.