This discussion is archived
3 Replies Latest reply: Dec 9, 2013 4:37 AM by Hoek RSS

Sql Help

NM Newbie
Currently Being Moderated

Hi,

 

I have Query which takes around 12 seconds so it possible to reduce the response time to 2 to 3 seconds.We have Indexes on the Following Columns instrumentid, partnerexid,messagesequence and tibex_bestexrel(13 Millions records) but we retrieve only 16000 records based on where condition.

 

SELECTDISTINCT "PARTICIPANTID", "PARTICIPANTIDMM", "INSTRUMENTID",
               "TIMESTAMP", "BESTEXECSTATUS", "MAXQTY",
               "PRICEIMPROVEMENT", "PAYMENTBYMM",
               "LASTINSTMESSAGESEQUENCE", "LASTREJECTCODE",
               "LASTREJECTREASON", "MEID", "BOARDID", "BESTEXPRIORITY",
               "MESSAGESEQUENCE", "LASTINSTUSERALIAS", "PARTNEREXID",
               "PARTNEREXREQID", "SETTLEMENTLOCATION", "SETTLEMENTCYCLE",
               "ENTERINGPARTICIPANTID", "POSTTRADEVENUETYPE",
               "LASTACTIONPARTICIPANTID", "BESTEXHALTSTATUS", "MMSIDE",
               "LASTINSTTYPE", "PRICEIMPROVEMENTTYPE", "BESTEXRELTYPE"
          FROM tibex_bestexrel a
         WHERE (a.instrumentid, a.partnerexid, messagesequence) IN (
                  SELECT   instrumentid, partnerexid,
                           MAX (messagesequence)
                      FROM tibex_bestexrel b
                     WHERE b.bestexecstatus <>4
                 

GROUP BY b.instrumentid, b.partnerexid);

 

Regards

NM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points