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

Question

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 :

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 ?

Answer 1

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.