Querying real time data from an SQL database sudden latency problem.

Question

We are testing an application that is supposed to display real time data for multiple users on a 1 second basis. New data of 128 rows is inserted each one second by the server application into an SQL datatbase then it has to be queried by all users along with another old referential 128 rows.

We tested the query time and it didn't exceed 30 milliseonds; also the interface function that invokes the query didn't take more than 50 milliseconds with processing the data and all

We developed a testing application that creates a thread and an SQL connection per each user. The user issues 7 queries each 1 second. Everything starts fine, and no user takes more than 300 milliseconds for the 7 data series ( queries ). However, after 10 minutes, the latency exceeds 1 second and keeps on increasing. We don't know if the problem is from the SQL server 2008 handling multiple requests at the same time, and how to overcome such a problem.

Here's our testing client if it might help. Note that the client and server are made on the same 8 CPU machine with 8 GB RAM. Now we're questioning whether the database might not be the optimal solution for us.

   class Program
{
    static void Main(string[] args)
    {   
        Console.WriteLine("Enter  Number of threads");
        int threads = int.Parse(Console.ReadLine());
        ArrayList l = new ArrayList();
        for (int i = 0; i < threads; i++)
        {
            User u = new User();
            Thread th = new Thread(u.Start);
            th.IsBackground = true;
            th.Start();
            l.Add(u);
            l.Add(th);
        }
        Thread.CurrentThread.Join();
        GC.KeepAlive(l);
    }
}
class User
{
    BusinessServer client ; // the data base interface dll
    public static int usernumber =0 ;

    static TextWriter log;
    public User()
    {
        client = new BusinessServer(); // creates an SQL connection in the constructor
        Interlocked.Increment(ref usernumber);
    }

    public static void SetLog(int processnumber)
    {
        log = TextWriter.Synchronized(new StreamWriter(processnumber + ".txt"));
    }
    public void Start()
    {
        Dictionary<short, symbolStruct> companiesdic = client.getSymbolData();
        short [] symbolids=companiesdic.Keys.ToArray();
        Stopwatch sw = new Stopwatch();
        while (true)
        {

            int current;
            sw.Start();
            current = client.getMaxCurrentBarTime();
            for (int j = 0; j < 7; j++)
            {   
                client.getValueAverage(dataType.mv, symbolids,
                    action.Add, actionType.Buy,
                    calculationType.type1,
                    weightType.freeFloatingShares, null, 10, current, functionBehaviour.difference); // this is the function that has the queries

            }
            sw.Stop();
            Console.WriteLine(DateTime.Now.ToString("hh:mm:ss") + "\t" + sw.ElapsedMilliseconds);
            if (sw.ElapsedMilliseconds > 1000)
            {
                Console.WriteLine("warning");
            }
            sw.Reset();

            long diff = 0;//(1000 - sw.ElapsedMilliseconds);
            long sleep = diff > 0 ? diff : 1000;
            Thread.Sleep((int)sleep);
        }
    }



}

Answer 1

I would suspect the query itself. While it may not take much time on an empty database, as the amount of data grows it may require more and more time depending on how the look up is done. Have you examined the query plan to make sure that it is doing index lookups instead of table scans to find the data? If not, perhaps introducing some indexes would help.

Answer 2

Warning: this answer is based on knowledge of MSSQL 2000 - not sure if it is still correct.

If you do a lot of inserts, the indexes will eventually get out of date and the server will automatically switch to table scans until the indexes are rebuilt. Some of this is done automatically, but you may want to force reindexing periodically if this kind of performance is critical.