Delay in query output

Category: sql server streaminsight


dontdisplay on Wed, 28 Nov 2012 17:31:29


I have an application that collects real-time tick data for index futures.  I am seeing delays in the output of my queries.  The best I can tell is the delay occurs when no ticks have been added to the stream for a second or more.   Once a new tick gets added to the stream then the output from a second or more ago is available for processing.  I would have expected the output to be available once the current second has completed...not when a new record is added a second or more later.  

Is this a case where I need to manually add CTI's every second?

Here is the code I'm using:

                var sourceLevel1 = app.DefineObservable(() => tickStream.GetStream).ToPointStreamable(
                                                r => PointEvent<Tick>.CreateInsert(r.QDateTime, r),

                // create level 1 second bars
                var queryLevel1 = from sec in sourceLevel1.TumblingWindow(TimeSpan.FromSeconds(1))
                         select new SecBar { 
                             QDateTime = sec.Min(min => min.QDateTime),
                             H = sec.Max(mh => mh.Last),
                             L = sec.Min(ml => ml.Last),
                             BidVol = sec.Sum(bv => bv.BidVol),
                             AskVol = sec.Sum(av => av.AskVol),
                             TotVol = sec.Sum(tv => tv.BidVol+tv.AskVol),
                             BidCount = sec.Sum(bc => bc.BidCount),
                             AskCount = sec.Sum(ac => ac.AskCount),
                             TotCount = sec.Sum(tc => tc.BidCount + tc.AskCount),
                             CumVol = sec.Max(cv => cv.CumVol),
                             Delta = sec.Sum(av => av.AskVol) - sec.Sum(bv => bv.BidVol)

                var sinkLevel1 = app.DefineObserver(() => Observer.Create<SecBar>(x => ProcessLevel1Output.ProcessSecBar(x)));
                IDisposable processLevel1 = queryLevel1.Bind<SecBar>(sinkLevel1).Run("SecBar");


DevBiker on Wed, 28 Nov 2012 23:41:07

When using AdvanceTimeSettings, CTIs are only added when you have active events in the stream. If you have no events enqueued, the time won't be advanced. It doesn't run on a clock. Instead, when you add a new event that is past the time window for the duration (or number of events for event count), a CTI is then enqueued. This blog entry talks about how some of this works and may help you understand what's going on.

So yes, you would need to add your CTIs in "manually" with your source.

dontdisplay on Thu, 29 Nov 2012 15:34:37

Thank you for the reply.  

I implemented the solution outlined in your blog.  However, now output stops being generated anywhere between 3 and 120 seconds of starting the application.  It is as if the CTI's are being ignored after a short period of time.  

The data feed I have issues a timestamp every 1 second.  I am using this as the source timestamp of the CTI.  I noticed when market activity picks up, the output query no longer generates records.  Are there performance issues related to the method of inserting cti's outlined in your blog?


DevBiker on Thu, 29 Nov 2012 15:53:38

The method in my blog is really most appropriate for LinqPad. I pointed to it because it also talks about how IncreasingStartTime and the declarative CTIs work.

In your app, you'll have to have your source add the CTIs. You can do this a couple of ways ... either have your Observable source add them or by merging with an observable that generates CTIs and then calls ToStreamable(). The first method is shown in the sample on this blog entry ... the TypedEvent class will create your CTI or Insert event as appropriate.

If you have a separate observable that generates CTIs every second and you then use Merge(), you will need to be careful of CTI violations - whenever you are handling CTIs yourself, rather than using your AdvanceTimeSettings, you need to be careful of this. A CTI violation will abort the query and you'll get nothing. That, in fact, may be what's happening when your query dies. You'd have to attach the Event Flow Debugger or write code to the diagnostic views to be sure.

dontdisplay on Thu, 29 Nov 2012 19:17:40

Thanks again for your reply.  

I'm going to pass on StreamInsight for now.  I can do everything I need much faster/simpler using List<T>. 

When I have more time I will be back to work on StreamInsight since there is no doubt it will be very effective for what I'm doing.

Thanks again.