ASP.NET SqlCommand Parameters - Query Speed

Results 1 to 6 of 6

Thread: ASP.NET SqlCommand Parameters - Query Speed

  1. #1
    Join Date
    Dec 1969
    Posts
    166

    Default ASP.NET SqlCommand Parameters - Query Speed

    OK, so this is odd to me. I am looking to help clarify why I am seeing the following results...

    On a large table:

    TABLE1
    _______________
    ID int
    ID2 int
    ID3 int
    col1 varchar
    col2 varchar

    I have constructed a function that returns a SqlDataReader, however, depending on how I set the parameters, I get a huge difference in query execution time.

    This is very slow to execute:

    Public Function getItems(ByVal ID As Integer, ByVal ID2 As Integer, ByVal ID3 As Integer) As SqlDataReader

    Dim strSql As String = ""

    strSql = strSql & "SELECT * FROM TABLE WHERE ID=@ID AND ID2=@ID2 AND ID3=@ID3;"

    Dim con As New SqlConnection(WebConfigurationManager.ConnectionSt rings("MYDBNAME").ConnectionString)
    Dim cmd As New SqlCommand(strSql, con)
    cmd.Parameters.AddWithValue("ID", ID)
    cmd.Parameters.AddWithValue("ID2", ID2)
    cmd.Parameters.AddWithValue("ID3", ID3)

    con.Open()
    Return cmd.ExecuteReader(CommandBehavior.CloseConnection)

    End Function



    This is very slow to execute:

    Public Function getItems(ByVal ID As Integer, ByVal ID2 As Integer, ByVal ID3 As Integer) As SqlDataReader

    Dim strSql As String = ""

    strSql = strSql & "SELECT * FROM TABLE WHERE ID=@ID AND ID2=@ID2 AND ID3=@ID3;"

    Dim con As New SqlConnection(WebConfigurationManager.ConnectionSt rings("MYDBNAME").ConnectionString)
    Dim cmd As New SqlCommand(strSql, con)
    cmd.Parameters.Add("@ID", SqlDbType.Int)
    cmd.Parameters("@ID").Value = ID
    cmd.Parameters.Add("@ID2", SqlDbType.Int)
    cmd.Parameters("@ID2").Value = ID2
    cmd.Parameters.Add("@ID3", SqlDbType.Int)
    cmd.Parameters("@ID3").Value = ID3

    con.Open()
    Return cmd.ExecuteReader(CommandBehavior.CloseConnection)

    End Function


    This is super fast:

    Public Function getItems(ByVal ID As Integer, ByVal ID2 As Integer, ByVal ID3 As Integer) As SqlDataReader

    Dim strSql As String = ""

    strSql = "DECLARE @ID1 int;DECLARE @ID2 int;DECLARE @ID3 int;"
    strSql = strSql & "SET @ID=" & ID & ";"
    strSql = strSql & "SET @ID2=" & ID2 & ";"
    strSql = strSql & "SET @ID3=" & ID3 & ";"
    strSql = strSql & "SELECT * FROM TABLE WHERE ID=@ID AND ID2=@ID2 AND ID3=@ID3;"

    Dim con As New SqlConnection(WebConfigurationManager.ConnectionSt rings("MYDBNAME").ConnectionString)
    Dim cmd As New SqlCommand(strSql, con)

    con.Open()
    Return cmd.ExecuteReader(CommandBehavior.CloseConnection)

    End Function



    Why does declaring the parameters in the sql statement itself speed things up so much versus using the Add and AddWithValue. Is this an OK approach? Pros...cons?

  2. #2
    Join Date
    Dec 1969
    Posts
    2,433

    Default A Couple things....

    You should Never, Ever use a SQL query that selects all columns and all rows. Name the specific columns that you want. What columns are included in the indexes? That will determine the actual speed of the query. What does the SQL query execution plan show you? This will determine where the problems reside. Most likely, lack of an index on one or more columns.

    You should always use a stored procedure, and not build the query in a string like that. That way, SQL Server will cache that execution plan for the next time. When you create a string and pass that string to the execute method, SQL must perform a table scan. Meaning that is must look at each and every row, index or not. Use a stored procedure with input parameters, and SQL Server will perform a seek. huge performance advantages.

    I do not use VB OOP language, I use C#, so I cannot comment on this question. I have always found that VB is a little cumbersome and slow in comparison.
    Why does declaring the parameters in the sql statement itself speed things up so much versus using the Add and AddWithValue?
    Not a good choice for column names, how will you ever know what those columns are related too? I mean, just by looking at them. Troubleshooting, or even writing a query will be tough.

    Andrew SQLDBA

  3. #3
    Join Date
    Dec 1969
    Posts
    166

    Default More info....

    The query and column names are for sample purposes only; the do not reflect the actual schema, just the concept of what I am seeing. I do specify the column names that I need in the query.

    One thing I will note is that I do not have any indexes on the table, but it is still odd to me that the first query is faster. When I run the query in Query Analyzer, it runs fast.

    I'll put it through and see what the query execution plan shows.

  4. #4
    Join Date
    Feb 2009
    Posts
    452

    Default

    You've piqued my interest on this one. The first two rely on ADO.NET Parameter collections and the different methods you used should have negligible differences. In both cases, you're clearly passing integer values too...so I doubt there's any difference in execution once SQL Server gets the prepared statement.

    #3 is pure T-SQL, which should be expected to execute faster. I agree with Andrew that in production systems you'd want to call a stored procedure for a variety of reasons.

    What are your actual execution times? I'd be curious to see a benchmark of T-SQL vs. an SP implementation. On this simple example, I would not be surprised to see pure T-SQL outperform a stored procedure.

    Also, the actual execution plans would be interesting. I suspect the plans are pretty much identical, and your "very slow" part comes from ADO.NET. VB vs. C# should make no difference at all, as the resulting MSIL calls the same .NET libs.

    @Andrew re table scans vs. seeks...you know that's not true. The query optimizer can still choose any execution plan it wants from pure T-SQL and a stored procedure is no guarantee of index seeks.

    Last...for testing purposes be sure to clear the server's cache so you're getting a true picture and not cached execution plans.

    dbcc freeproccache
    go
    dbcc dropcleanbuffers
    go
    checkpoint
    go

    Cheers,
    Ryan


  5. #5
    Join Date
    Dec 1969
    Posts
    166

    Default ...

    Yes, it's got my curiosity as well.

    The method using the AddWithValue was actually timing out. When I added in the declarations in the sql string, it was fast when rendered on the front end aspx page.

    I went back and added a couple of indexes after reviewing the execution plan, ran the query both ways and it was quick for both thereafter.

    If the third method yields that much faster results (less the indexes), why isn't discussed more. All of the .NET books (Wrox) I studied don't mention doing that.

    Is the third method OK to use? Pros...cons?

  6. #6
    Join Date
    Feb 2009
    Posts
    452

    Default

    Method #3 uses native T-SQL variables and the resulting execution plan will be cached in a way to reuse those parameters with other values. You've essentially written it in a way that gets some of the benefit of a stored procedure, a parameterized query.

    If you had written it using concatenation, resulting in:
    Code:
    SELECT * FROM table WHERE ID=1 AND ID2=9 AND ID3=124
    then the cached execution plan could only be used for the exact parameters used...which may or may not be very often.

    I would expect to find it in a T-SQL programming book, but it would often not be used in .NET application code. It's very fast because native T-SQL is parsed very quickly and the execution plan can be reused.

    As a standard practice, I would only use this if I needed a function that was the absolute fastest possible (ie. my app called it 100's of times per minute), and it proved to be the fastest. Otherwise, I'd make a stored procedure along with all of my other queries, that's the standard practice.

    The only pro is: fast.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •