Is predefined query more efficient than ad-hoc SQL query using ADO.NET?

We have a table CroppedImage which has Id, PosX, PosY, Width, Heigth columns with datatype of tinyint and another column CroppedPicture with datatype of varbinary.

Here is ad-hoc way :

  • Using SQL CONVERT function through nHibernate Criterion
  • When to use with clause in sql
  • T-SQL List Dump Devices
  • Column name not found when i use trigger in SQL Server
  • DataGridView live display of datatable using virtual mode
  • SSIS - Converting DT_TEXT(Length 11,000 Characters) to DT_STR and trim to 1,000 characters
  • List<int> GetList = GetTopNecessaryImages();
    for(int i = 0; i < 100; i++){
        com.CommandText = "select * from CroppedImage where Id=" + GetList[i];
        using (SqlDataReader objSqlDtReader = com.ExecuteReader()) 
        {
            while(objSqlDtReader.Read()) 
            {
                 CropImage objCrop = new CropImage ();
                 CropImage.Img = (objSqlDtReader["CroppedPicture"]);
            }
        }
    }
    

    And here is predefined query with parameters.

    List<int> GetList = GetTopNecessaryImages();
    for(int i = 0; i < 100; i++){
        com.CommandText = "select * from CroppedImage where Id=@IdPar";
        com.Parameters.AddWithValue("@IdPar", GetList[i]);
        using (SqlDataReader objSqlDtReader = com.ExecuteReader()) 
        {
            while(objSqlDtReader.Read()) 
            {
                 CropImage objCrop = new CropImage (); 
                 CropImage.Img = (objSqlDtReader["CroppedPicture"]);
            }
        }
    }
    

    Image field is less than 250KB.
    I have tested both of them and saw no difference on my machine however the database administrator tells me that ad-hoc queries eats more memory than predefined one on server which host SQL.
    Is this true ? Does this two options have any kind of difference on performance ?

  • Creating SQL users and restricting their permissions
  • Determine version of SQL Server from ADO.NET
  • Ado.net - the Size property has an invalid size of 0
  • Authentification works when browsed from Visual Studio but not with IIS
  • Query times out when run from ADO.NET but runs fine in Query Analyer
  • TransactionScope Prematurely Completed
  • 2 Solutions collect form web for “Is predefined query more efficient than ad-hoc SQL query using ADO.NET?”

    Actually, your DBA is correct, though the terminology is a bit confusing. It’s more a question of parameterized queries vs. non-parameterized queries.

    In the case of a parameterized query ("select * from CroppedImage where Id=@IdPar"), even if you execute this query multiple times with different parameters, the query only needs to be parsed and compiled once by SQL Server. Any subsequent executions of the query will reuse the same query plan from cache.

    In contrast, a non-parameterized query ("select * from CroppedImage where Id=" + GetList[i]) will be treated as a completely distinct query every time it is executed with a different GetList[i] value. In this case, the query will be parsed and recompiled on the SQL Server every single time. And this will consume more memory, as the database server now has to cache multiple query plans instead of a single one.

    For small amounts of queries, the difference will not be noticeable. But as soon as you have large transaction volume, you may start noticing an effect on both speed and memory.

    Here is an interesting article that provides more details on the matter: Use SQL Parameters to Overcome Ad Hoc Performance Issues.

    The reality is that your DBA is not correct. These are BOTH ad-hoc queries. Ad-hoc queries get an execution plan created for them and they are cached just like the execution plan for a stored procedure. The second method is prefered because you are using parameterized queries and preventing sql injection. What I do see though is you are using AddWithValue. This should be avoided as it sometimes it gets it wrong.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.