[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

message latency calculation

Posted on 2007-09-28
18
Medium Priority
?
440 Views
Last Modified: 2007-10-01
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
Comment
Question by:dbaSQL
  • 12
  • 6
18 Comments
 
LVL 11

Expert Comment

by:Otana
ID: 19978097
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
 
LVL 11

Expert Comment

by:Otana
ID: 19978117
Try this:

SELECT MAX(timestampms) - MIN(timestampms) FROM OrderTable GROUP BY orderno
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19978149
>>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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 11

Expert Comment

by:Otana
ID: 19978180
And my query should off course be:

SELECT orderno, MAX(timestampms) - MIN(timestampms) AS latency FROM OrderTable GROUP BY orderno
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19978204
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
 
LVL 11

Accepted Solution

by:
Otana earned 2000 total points
ID: 19978291
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
 
LVL 17

Author Comment

by:dbaSQL
ID: 19978314
i'll try this now, Otana, back shortly.
0
 
LVL 11

Expert Comment

by:Otana
ID: 19978319
Be sure to place an index on orderno and msgtype, otherwise these joins could become very slow with millions of records.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19978364
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
 
LVL 17

Author Comment

by:dbaSQL
ID: 19978380
indexes already in place (yesterday)

what do you think about O to A and X to C ?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19978404
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
 
LVL 17

Author Comment

by:dbaSQL
ID: 19978491
i'm not sure how to get X to C in there, Otana
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19978616
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
 
LVL 17

Author Comment

by:dbaSQL
ID: 19978651
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
 
LVL 17

Author Comment

by:dbaSQL
ID: 19978852
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
 
LVL 17

Author Comment

by:dbaSQL
ID: 19981279
anything at all?
0
 
LVL 11

Expert Comment

by:Otana
ID: 19989029
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
 
LVL 17

Author Comment

by:dbaSQL
ID: 19989885
No worries, Otana.  I was able to get it together.  I do appreciate you getting back to me on this.
Thanks much.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question