Real time data query due to sudden SQL database latency issue

We are testing an application that needs to display real-time data for multiple users for 1 second. New data of 128 rows is inserted every second by the server application into the SQL database, then it must be requested by all users along with the other old reference 128 rows.

We checked the request time and did not exceed 30 milliseconds; also the interface function that calls the request takes no more than 50 milliseconds with data processing and all

We have developed a test application that creates a thread and SQL connection for each user. The user issues 7 requests every 1 second. Everything starts out fine and no user takes more than 300 milliseconds for 7 rows of data (queries). However, after 10 minutes, the latency exceeds 1 second and continues to rise. We do not know if the issue is related to SQL Server 2008 processing multiple requests at the same time and how to deal with such a problem.

Here's our testing client if he can help. Note that the client and server are both running on the same 8-processor machine with 8 GB of RAM. Now we ask if 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);
        }
    }



}

      

+1


a source to share


2 answers


I would suspect the request itself. While this may not take long on an empty database as the amount of data grows, it may take longer and longer depending on how the search is performed. Have you analyzed your query plan to make sure it is looking for an index lookup instead of scanning a table to find data? If not, maybe introducing some indexes will help.



0


a source


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



If you make a lot of attachments, the indexes will eventually become outdated and the server will automatically switch to table scans until the indexes are rebuilt. Some of these are done automatically, but you might want to re-index if this kind of performance is critical.

+1


a source







All Articles