Speed to read small table

Question

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;
    }
}

Answer 1

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();
}

Answer 2

From your comment about using SET STATISTICS TIME ON and elapsed execution time of 0 ms, I don't think there is too much you can do from a Sql Server perspective. One thing to double check is making sure you are only selecting the bare minimum amount of data necessary to satisfy the application requirements. Transferring extraneous data will increase latency. You could probably test network latency outside of Sql Server by simply trying to copy a 1mb file from the server where Sql Server resides to the client machine and timing it. Perhaps your network guys could do some diagnostics as well. Also examine how your C# program is consuming your data.

Answer 3

I haven't worked with SqlDependency so this may not be a valid path, but have you considered splitting this into two pieces?

Use a Trigger rule inside the database engine that fires when your new event occurs (ON INSERT and/or ON UPDATE). Either have this trigger do whatever logic your C# code is doing now (if it is database-related), or have it update a staging table that your C# code can then poll for whatever non-data events need to occur as a result of the new records?

This won't remove the delays, but will allow your code to route around them, so it won't lose the transactions that are new.

There is no way to absolutely guarantee that all delays are removed from your logic. If you moved all of the code events inside the database engine and if you moved all of your data into RAM instead of disk, you'd remove the majority of your wait events. But even that won't guarantee a 0ms reaction time 100% of the time.

Answer 4

Adding to the other answers, from a pure database performance perspective (and it seems the database is probably not your main bottleneck), this part of your query:

WHERE [AccountIdent] = 'LB:B' AND [Exchange] = 'FX' AND [Status] = 'Sent' 
ORDER BY [OrderDate],[OrderUTC] DESC

would benefit from an index that looks like this:

CREATE INDEX ...
ON [LB].[Orders]
    ([AccountIdent], [Exchange], [Status], [OrderDate], [OrderUTC] DESC)

And if the WHERE clause is always the same, you could even turn that index into a filtered index (available as of SQL Server 2008, if memory serves)

CREATE INDEX ...
ON [LB].[Orders]
    ([OrderDate], [OrderUTC] DESC)
WHERE ([AccountIdent] = 'LB:B' AND [Exchange] = 'FX' AND [Status] = 'Sent')

Remember to make the index clustered or INCLUDE the column(s) your returning in the SELECT clause. The non-clustered index with the INCLUDE will probably be faster, unless you're collecting every column in the table anyway.

If you're running SQL Server 2014/2016 Enterprise Edition or SQL Server 2016 SP1, you could try making the table in-memory.