it might be the indexes, it might be the procedure

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
TSdate  smalldatetime
OrderNo varchar(100)
MsgSource char(1)
Latency decimal 17(30,0)
LatencyType char(2)

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:

MsgDate   smalldatetime
OrderNo   varchar(100)
EndPoint varchar(8)
Client varchar(32)

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,
@msgsource char(1)=null,
@endpoint varchar(8)=null,
@client varchar(32)=null
)
as
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))
from database.dbo.maintable
else
   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'
 
exec (@Sql)
--print (@Sql)
GO

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?