message latency calculation
Posted on 2007-09-28
i've got a very small table, it contains message latency information. we do electronic trading. we want to load all messages here and walk thru them, monitoring latencies. a trade/order has many messages, each with different msgTypes -- O, A, X, C, T, etc.
anyway, the table is this:
timestampdate is a smalldatetime, i just store the current minute
timestampms is the number of milliseconds (possibly mIcroseconds) since that minute
(i say 'possibly' as all values are given to me via the front end, i've heard both -- micro and milliseconds)
this is paramount for business reasons, i'm getting a TON of data today, and am going to need to report latencies w/that data. i figured i'd get started this morning by just doing the timediff between timstampdate & timestampms in milliseconds, like this:
from ..... where .....
it fails on me w/arithmetic overflow error converting expression to data type datetime
surely this is due to the lacking conversion -- timestampdate is SMALLDATETIME, timestampms is INT
if i could get some help with this conversion, i need to get the latency, or just the time diff, from point A to Z, per orderno. for example, this is a sample message, placed (msgtype = O), accepted (msgtype=A), cancel requested (msgtype = X), and cancel confirmed (msgtyp=C) :
orderno msgtype msgID timestampdate timestampms
25Uu65lv8 O 25Uu65lv8 2007-09-25 07:34:00 25976000
25Uu65lv8 A 25Uu65lv8@700088474 2007-09-25 07:34:00 25979000
25Uu65lv8 X 25Uu65lv9 2007-09-25 07:34:00 26043000
25Uu65lv8 C 25Uu65lv9@C 2007-09-25 07:34:00 26046000
every order has varying messages, each message has a different msgType, and msgID, but the orderno is the same for each of its associated messages. can anybody advise on the best way to gen the timediff between timestampDate and timestampMS for all msgTypes, per orderno?
maybe i'm not explaining this well, i hope this makes sense
ps. when i say 'small' table, i'm only talking ddl. the intent is to store several million records here daily.
so phsyically, not very small at all