I received some excellent EE help from kselvia, I tried to revisit this with him, but I it seems he may be unavailable and this project is critical., I must resolve. In short, we do electronic trading. This solution houses what we call latency messages it is used as a viewpoint on all the trading traffic within our infrastructure, such that we can look at the traffic and identify/resolve any latencies, as they occur
you know.... why is trading taking so long on that endpoint and that client? that sort of thing
Within the structure Ive got two tables -- the 'maintable' holds all latency messages for orderno's across the varying msgSources (exchange logs, gateway logs, pricefeed logs, etc.) It houses an average of 180 or so million records at a time, 7 days of data. the format is simple:
ID int/identity value
Latency decimal 17(30,0)
sample record: 653610778 2008-02-06 00:00:00 05Grsmd57 A 17000 OA
These are the indexes: idx1, nonclustered, LatencyType, Latency(-)
idx2, nonclustered, OrderNo, TSDate, MsgSource
idx3, nonclustered, TSDate, LatencyType, Latency(-)
idx4, nonclustered, TSDate, OrderNo, Latency
idx5, nonclustered, LatencyType, MsgSource, TSDate, Latency(-)
The 2nd table is the 'maptable'. It holds an avg of 3M or so records at a time, only a single day. format is simple:
sample record: 2008-02-06 00:00:00 06Grtdfw4 AAAA MA1.MARSPY
These are the indexes: idx1, nonclustered, client
idx2, nonclustered, endpoint
idx3, clustered unique pk on OrderNo
Unfortunately, there is no way to get the EndPoint and/or Client into the maintable, so I have to join to the maptable on orderno, such that i can get the latency statistics out by endpoint and/or client.
Given the volume of data, my indexes are paramount . Right now, I THINK my indexes on the maintable are sufficient, but I am questioning whether the maptable is indexed properly.
The ONE procedure that accesses this data does a join on the two tables it is dynamic:
CREATE procedure PROCNAME (
@tsDate datetime = null,
@latencytype char(50) = null,
@topcount int = 50,
set nocount on
declare @chardate varchar(50)
declare @sql varchar(8000)
if @tsdate is null
select @chardate = convert(varchar(11), DATEADD(day, DATEDIFF(day, 0,max(tsDate)), 0))
select @chardate = convert(varchar(11), DATEADD(day, DATEDIFF(day, 0,max(@tsDate)), 0))
If @latencytype is null set @latencytype = 'OA,OT,OJ,XC,XJ,XT,XJ,RA,RT,RJ'
select @sql = IsNull(@sql+' UNION ' + char(10),'') +
'select top '+convert(varchar,@topcount)+ IsNull (' Endpoint=''' + @endpoint + ''',','') + IsNull ( ' Client='''+@client+ ''',','') +
' e.OrderNo,MsgSource,Latency,LatencyType,tsDate from database.dbo.maintable e WITH (NOLOCK)' +
Case When @endpoint Is Not Null Or @client Is Not Null
Then 'INNER JOIN database.dbo.maptable m ON e.orderno = m.orderno '
+ IsNull ( ' AND m.EndPoint = ''' + @endpoint+'''', '' ) + IsNull (' AND m.Client = ''' + @client +'''' , '' )
Else ' ' End +
' where LatencyType =''' + substring(@latencytype, number * 3 + 1, 2) + '''' +
IsNull ( ' and tsDate >= ''' + @chardate + ''' ' , '' ) +
ISNULL(' and MsgSource='''+@msgSource+'''','') + ' ORDER BY Latency DESC '
From master..spt_values where type = 'P' and number < (len(@latencytype) + 1) / 3
Select @sql = 'SELECT * FROM ( ' + @sql + ') d ORDER BY ' + case when len(@latencytype) = 2 then ''
Else 'LatencyType, ' end + ' Latency DESC'
This proc makes the stats available thru a front end initially, they were all flying so we deployed, and now some are done instantly, others never complete. See here, sample execs, runtimes on each (note those I killed):
exec procedurename @tspdate='2/5/08',@msgsource='A' --:08
exec procedurename @msgsource='A' --:08
exec procedurename @latencytype = 'oa' -- killed at 2:59
exec procedurename @tsdate='2/05/08',@latencytype = 'oa' --:00
exec procedurename @tsdate=null,@latencytype = 'oa' --killed at 2:26
exec procedurename @tsdate='2/6/08',@latencytype = 'oa',@client='dowbigs'--killed at 3:19
exec procedurename @tsdate='2/4/08' -- :01
exec procedurename @tsdate='2/5/08',@latencytype = 'oa',@client='dev1'--:00
exec procedurename @tsdate='2/6/08',@latencytype = 'oa',@client='dev1',@msgsource='a' -- :00
exec procedurename @latencytype='OA', @topcount=100, @msgsource='D' -- 1:18
exec procedurename @endpoint='aaaa',@sdate='2/5/08'--:32
I realize there are a number of other details out there... but, just given this, does anybody have any advice or suggestions on how I can improve procedure runtime? Is it the proc, or the indexes, or both? Is anyone able to advise? Are there any other details I can provide? (i.e., showplan output, print @sql,?)
I know you hear this a lot, but it truly is quite urgent. Like I said, initially it all was beautiful instantaneous results on every procedure execution. So, we deployed. Now its out there, and they cant use it. I really am grateful for any direction or insight at all.
Any thoughts at all?