0

I appears that using bind variables in my queries is hurting performance, some examples by as much as a factor of 5. The following example takes on average about 0.5 seconds to complete.

string strId = "abcd";
using (var db = _conn.OpenDbConnection())
{
    var sql = "SELECT count(*) FROM table WHERE idNum= :ID";
    var r = db.QueryScalar<int>(sql, new { ID = strID.ToUpper() });
    return r >= 1;
}

When I run the following code it takes around 0.1 seconds to complete.

string strId = "abcd";
using (var db = _conn.OpenDbConnection())
{
    var sql = string.Format("SELECT count(*) FROM table WHERE idNum= '{0}'", strID.ToUpper());
    var r = db.QueryScalar<int>(sql);
    return r >= 1;
}

Am I using something incorrectly here that is causing the performance issue?

This is in C# against an Oracle database using version 3.9.71.0 of ServiceStack.OrmLite

Scott
  • 21,211
  • 8
  • 65
  • 72
mattnaik
  • 43
  • 1
  • 4
  • I would check for column statistics on the idNum column. It could be that there is an index, but you haven't collected accurate statistics. Showing some of the DDL for the table would be helpful (but wouldn't mention stats). –  Jan 03 '15 at 18:34
  • The code above is a generalized example but I get the same results on narrow tables, wide tables, views, tables with only a few records, tables with hundreds of thousands of records. All examples involved columns without indexes in the WHERE clause. – mattnaik Jan 05 '15 at 14:00
  • I believe the version of ServiceStack.OrmLite that I am using uses Devart dotConnect for Oracle as a provider. – mattnaik Jan 06 '15 at 18:03

0 Answers0