• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 444
  • Last Modified:

message latency calculation

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:
orderno,msgtype,msgid,timestampdate,timestampms

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:

select datediff(ms,timestampdate,timestampms)
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
0
dbaSQL
Asked:
dbaSQL
  • 12
  • 6
1 Solution
 
OtanaCommented:
I think you should get the difference between the highest timestampms and the lowest, per order, to get the total ms it took to get from A to Z.

Datediff won't work because it calculates the difference between two timestamps, not between a date and an int.
0
 
OtanaCommented:
Try this:

SELECT MAX(timestampms) - MIN(timestampms) FROM OrderTable GROUP BY orderno
0
 
dbaSQLAuthor Commented:
>>Datediff won't work because it calculates the difference between two timestamps, not between a date and an int.

oh gosh.  i hate that i didn't see that myself.  ugh...
back shortly...
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
OtanaCommented:
And my query should off course be:

SELECT orderno, MAX(timestampms) - MIN(timestampms) AS latency FROM OrderTable GROUP BY orderno
0
 
dbaSQLAuthor Commented:
well, the logic works, but... i need to go a step or two further.  all i have now is a great big list of the time values (what it took to get from A to Z, per order)

first thing, i'd want the orderno in there, too.  i've done that.  here we go:

orderno      timediff
25Uu69ljr    4000
25Uu65lvs  1818000      
25Uu69ljt     4000      
25Uu5qyz1  7424000      
25Uu5qyzc  10034000      
25Uu69ljv     4000      
25Uu65lv8   70000      
25Uu69ljx    0      
25Uu65lwu   3000      
25Uu69ljp      4000      
25Uu5qz06   11870000      

i am sure that when the parties that be take a look at these numbers, they're going to say, ok, that one is bad, how long did it take to get from O to A, or from A to C.... you know, they'll see big numbers and want to go in and see details per msgType for the varying orderno's
you know.... so they can see where all the time is being spent

what do you think?  can i further aggregate it by msgtype, per order  ?
0
 
OtanaCommented:
Inthat case, you should join the table a couple of times to itself, always on orderno = orderno, and the first time msgtype = 'O', second time = 'A', etc...

SELECT
  t2.timestampms - t1.timestampms AS [O-A],
  t3.timestampms - t2.timestampms AS [A-C],
  t3.timestampms - t1.timestampms AS [O-C]
FROM OrderTable t1
LEFT JOIN OrderTable t2 ON t1.orderno = t2.orderno and t2.msgtype = 'A'
LEFT JOIN OrderTable t3 ON t1.orderno = t3.orderno and t2.msgtype = 'C'
WHERE t1.msgtype = 'O'

You can extend this for all msgtypes.
0
 
dbaSQLAuthor Commented:
i'll try this now, Otana, back shortly.
0
 
OtanaCommented:
Be sure to place an index on orderno and msgtype, otherwise these joins could become very slow with millions of records.
0
 
dbaSQLAuthor Commented:
i've just learned we only care about O to A and X to C, per orderno
that will likely change later, gut that is what i've just been told
0
 
dbaSQLAuthor Commented:
indexes already in place (yesterday)

what do you think about O to A and X to C ?
0
 
dbaSQLAuthor Commented:
yeah, this is getting there.  right now i've got this format:

OrderNo          O-A
25Uu651v8     2000

can you help me get this:
OrderNo          O-A         X-C
25Uu651v8     2000       20000

(obviously, i just threw those numbers in there, i'm just looking for that format)
0
 
dbaSQLAuthor Commented:
i'm not sure how to get X to C in there, Otana
0
 
dbaSQLAuthor Commented:
Otana, is this correct?  :

SELECT t1.OrderNo,
  t2.timestampms - t1.timestampms AS [O-A],
  t3.timestampms - t1.timestampms AS [X-C]
FROM OrderTable t1
LEFT JOIN OrderTable t2 ON t1.orderno = t2.orderno and t2.msgtype = 'A'
LEFT JOIN OrderTable t3 ON t1.orderno = t3.orderno and t3.msgtype = 'C'
WHERE t1.msgtype = 'O'

i'm getting results, but way different from the O to A numbers.  just curious if i'm doing this properly
0
 
dbaSQLAuthor Commented:
No, that can't be right.  i took O-A out of it, and just did X-C by itself, those numbers are much more similar to O-A.  much more reasonable

my two left joins is screwing up the X-C resultset
do you see what i should do?
0
 
dbaSQLAuthor Commented:
is anyone able to advise?  i just need to combine both of these into one query:

SELECT t1.OrderNo,
  t2.timestampms - t1.timestampms AS [O-A]
FROM OrderTable t1
LEFT JOIN OrderTable t2 ON t1.orderno = t2.orderno and t2.msgtype = 'A'
WHERE t1.msgtype = 'O'

SELECT t1.OrderNo,
  t2.timestampms - t1.timestampms AS [X-C]
FROM OrderTable t1
LEFT JOIN OrderTable t2 ON t1.orderno = t2.orderno and t2.msgtype = 'C'
WHERE t1.msgtype = 'X'


i need this resultset:
OrderNo          O-A         X-C
25Uu651v8     2000       20000


i'm sorry, this is very, very urgent.  any sugguestions?
0
 
dbaSQLAuthor Commented:
anything at all?
0
 
OtanaCommented:
Sorry I didn't answer sooner, I didn't have time to check my computer this weekend. To get X in there, I think you could do this (you can use as much joins as you like, always link them on orderno and the msgtype you want the new link to display, afterwards you can combine each of these columns any way you like):

SELECT t1.OrderNo,
  t2.timestampms - t1.timestampms AS [O-A],
  t3.timestampms - t4.timestampms AS [X-C]
FROM OrderTable t1
LEFT JOIN OrderTable t2 ON t1.orderno = t2.orderno and t2.msgtype = 'A'
LEFT JOIN OrderTable t3 ON t1.orderno = t3.orderno and t3.msgtype = 'C'
LEFT JOIN OrderTable t4 ON t1.orderno = t4.orderno and t4.msgtype = 'X'
WHERE t1.msgtype = 'O'
0
 
dbaSQLAuthor Commented:
No worries, Otana.  I was able to get it together.  I do appreciate you getting back to me on this.
Thanks much.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 12
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now