Databases
StackOverflow2013
Postsdbo
POQuerying real time data from an SQL database sudden latency problem
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POQuerying real time data from an SQL database sudden latency problem
    primarykey
    data
    text
    <p>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. </p> <p>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</p> <p>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.</p> <p>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.</p> <pre><code> 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 &lt; 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&lt;short, symbolStruct&gt; 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 &lt; 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 &gt; 1000) { Console.WriteLine("warning"); } sw.Reset(); long diff = 0;//(1000 - sw.ElapsedMilliseconds); long sleep = diff &gt; 0 ? diff : 1000; Thread.Sleep((int)sleep); } } } </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USmustafabar
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POQuerying real time data from an SQL database sudden latency problem
      1. This table or related slice is empty.
      1. VTUpMod
    1. COHow do you know it's the database slowing you down?
      singulars
      1. POQuerying real time data from an SQL database sudden latency problem
      1. USJohnOpincar
    2. COIs GC adding to the latency? Every query is creating new objects which will be collected by GC.
      singulars
      1. POQuerying real time data from an SQL database sudden latency problem
      1. USShashikant Kore
    3. COJohn, what else could be the problem? Shashikant, We use the GC.keepalive(l) to keep the thread from being terminated when the calling thread dies.
      singulars
      1. POQuerying real time data from an SQL database sudden latency problem
      1. USmustafabar
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload