Sql-server – Speed to read small table

csql server

I am trying to react to a Sql Table change very quickly.

I have set-up a SqlDependency in c# and on receipt I query the table for "New" records.

My table contains a few thousand rows. There are 2 areas where delays slip in:

  1. SqlDependency. On Average there is a 3 ms delay before I get notified.

  2. Table Read. I select all rows from the table where a column contains the string "NEW". This table has max 5,000 rows. On average this "read" takes 4.5 ms.

So my minimum time to start acting on new data is 7.5 ms. This is quite long for my application.

Are there any tricks to improve this?

Additional

When I run this query in MS Studio with time statistics I get:

SQL Server parse and compile time: CPU time = 0 ms, elapsed time =
0 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed
time = 0 ms.

(9 row(s) affected)

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

But If run it in c# like this I get average 1.67 ms.

private void button1_Click(object sender, EventArgs e)
{
    DataTable dt = null;

    SqlOMS2 sqlOMS2 = new SqlOMS2();

    Stopwatch sw = new Stopwatch();
    sw.Start();
    int num = 100;

    for (int i = 0; i < num; i++)
    {
        sqlOMS2.GetAnyNewOrders(out dt);
    }

    Console.WriteLine("Time taken: " + sw.ElapsedMilliseconds / (double)num);

    if (dt == null || dt.Rows.Count == 0)
    {

    }
}

public bool GetAnyNewOrders(out DataTable dt)
{
    dt = null;

    try
    {

        string commandText = "SELECT * FROM [" + DBName + "].[LB].[Orders] WHERE [AccountIdent] = 'LB:B' AND [Exchange] = 'FX' AND [Status] = 'Sent'  ORDER BY [OrderDate],[OrderUTC] DESC";

        SqlCommand cmd = new SqlCommand
        {
            Connection = conn,
            CommandText = commandText
        };

        cmd.ExecuteNonQuery();
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        dt = new DataTable();
        ds.Reset();
        da.Fill(ds);
        dt = ds.Tables[0];

        if (dt.Rows.Count == 0)
            return false;

        return true;
    }
    catch (Exception)
    {
        return false;
    }
}

Best Answer

SqlDependency was not really intended for real time type processing. 3 ms is still pretty good.

You should only retrieve required columns and you should use a Reader. DataTable has a lot of overhead. And you should be using Using.

string commandText = "SELECT PK FROM [" + DBName + "].[LB].[Orders] " + Environment.NewLine() + 
                     "WHERE [AccountIdent] = 'LB:B' AND [Exchange] = 'FX' AND [Status] = 'Sent' " + Environment.NewLine() + 
                     "ORDER BY [OrderDate], [OrderUTC] DESC";
using (SqlCommand cmd = conn.CreateCommand())
{
    cmd.CommandText = commandText;
    conn.Open();
    using (SQLdataReader rdr = cmd.ExecuteReader())
    {
        while(rdr.Read())
        {
           list.Add(rdr.GetInt(0));
        }
    }
    conn.Close();
}
Related Question