How code is executed....

Stainless_Steel

Hi all,

I do some software development as a hobby, but now one job in work requires me to use the skills I have learned over the years.

I use VB.NET and SQL Server 2008.

My application does simple insert statements to SQL server table (called "Raw_Inputs") every 50ms or so. This process is kicked off by an event handle.

I am going to have another thread run every 10s or so that is kicked off by a timer. This thread performs calculations on the Raw_Inputs table and inserts a summary records into a different table (called "Summary_Data").

My idea is to then delete all data from the "Raw_Inputs" table and everything kicks off again.

My question is: what will happen if an insert statement gets triggered by the event at the same time as the timer trigger? How does sql server handle simultaneous queries?

Also, how does Windows handle my application if the input event is triggered multiple times with a couple of ms of each other? Does it simply queue up the events?

The reason I don't do the summary calculations for each input is because it allows me to group into fewer summary records by having more input records available. Also, it seems more efficient to me to do it that way.

All comments welcome.

TonyStark

My reading of this is that there is one database server. In the application there are two threads. One to insert data and the other that runs a report to process the data.

Does the processing of the data take a long time?

Depending on the scale, It would seem to me something more suited to using a stored procedure and handling the computation when an item is inserted and then cascading an update to the summary table.

Stainless_Steel

Hi tony,

Yes your assumptions are correct.

The problem with doing the computation for each insert is that it can take 100ms. The input event is happening every 50ms.

Cheers

TonyStark

Stainless_Steel wrote: »

Hi tony,

Yes your assumptions are correct.

The problem with doing the computation for each insert is that it can take 100ms. The input event is happening every 50ms.

Cheers

I was just rethinking this...Why not a SQL Server Agent job to do the computation? I guess what I am trying to get at is how mission critical is the computation?

Stainless_Steel

SQL server agent is not an option unfortunately....using the express edition.

The computation is important. The computation creates the summary data which is then queried by the clients. The raw inputs are not queried by clients. I need the summary data to be near real time. A 10s lag is ok. Nothing longer though.

TonyStark

Stainless_Steel wrote: »

SQL server agent is not an option unfortunately....using the express edition.

The computation is important. The computation creates the summary data which is then queried by the clients. The raw inputs are not queried by clients. I need the summary data to be near real time. A 10s lag is ok. Nothing longer though.

Whats the overhead with the clients as they make the request for the information doing the compute? Then it becomes more of a query optimization problem than something that is linked to the application.

Does the update to the "SummaryTable" involve changing the data from the source table where the data is inserted? ie. is the summarytable separate?

amen

I am going to have another thread run every 10s or so that is kicked off by a timer. This thread performs calculations on the Raw_Inputs table and inserts a summary records into a different table (called "Summary_Data").

My idea is to then delete all data from the "Raw_Inputs" table and everything kicks off again.

You have to got be very careful here. You only want to delete data that been processed for the summary record.

You need to do some reading on Locks and Database Isolations levels.

GreeBo

Do you have an idea of how long the processing of the summary is going to take each time? That could really influence your timings (and your options).

If the calculations are fast you could just do them on demand, there is little point updating them every 10s if no one reads them more often than every hour.

I'm not sure I follow why you are deleting the raw_inputs each time, are you just processing updates to the summary table, or calculating each time? If your summary table contains averages, means, distributions etc then this could get interesting for you.

If it doesnt have these types of calculations I think I'd just trigger the update via stored proc on the summary table for each update, again really depends on what and how long the summary calculations take.

Stainless_Steel

Thanks for all the posts.

The requirements changed today. In short the input events have reduced by a factor of 5!

So I have done some coding. I am able to do the summary calculations as the records are inserted.

Question is this now: let's say hypothetically an input event is triggered a couple of ms after the previous event, does windows still call my sub routine for the second event or is it missed?

amen

does windows still call my sub routine for the second event or is it missed?

well it depends what you are doing and how you are listening for the triggering event.