title: Please advise about how to I improve the execution time of this SQL
body: <p>How can I improve the execution time of this SQL </p>
  
  <pre><code>declare   @Nemo as varchar(30)
  
  set @Nemo = 'TELH14F'
  
  UPDATE Nemos
  SET    MPV = px,
         SaleContracts = SC
  FROM   (SELECT TOP 1 sum (Qty) SC,
                       Px
          FROM   marketState
          WHERE  Nemo = @Nemo
                 AND side = 2
          GROUP  BY Px
          ORDER  BY px) src
  WHERE  Nemo = @Nemo
  
  UPDATE Nemos
  SET    MPC = px,
         BuyContracts = SC
  FROM   (SELECT TOP 1 sum (Qty) SC,
                       Px
          FROM   marketState
          WHERE  Nemo = @Nemo
                 AND side = 1
          GROUP  BY Px
          ORDER  BY px DESC) src
  WHERE  Nemo = @Nemo 
  </code></pre>
  
  <p>I have tried merging this two updates on the same table Nemos and change the "select top 1" to a MAX or MIN function with no success</p>
  
  <p><strong>EDIT - ADDED</strong></p>
  
  <p>I have also looked at the <strong>execution plans</strong> of this query, and that is the reason for me asking how to improve it</p>
  
  <p><strong>The GOAL is that the MarketState table is only selected/read once and merge the 2 updates into a single one.</strong></p>
  
  <p>Something similar to this</p>
  
  <pre><code>update Nemos SET  MPV = pxV , SaleContracts =SC, MPC = pxC, BuyContracts =BC
  FROM
  (Select top 1 sum (Qty) as SC, px as pxV 
  from marketState where Nemo = @Nemo and side=2 
  group by Px order by px ) srcA
   -- UNION ALL--
  (Select top 1 sum (Qty) as BC, Px as pxC
  from marketState where Nemo = @Nemo and side=1 
  group by Px order by px desc ) srcB
  where Nemo = @Nemo
  </code></pre>
  
  <p>How Can I avoid that UNION or make this work with a <code>MIN</code>,<code>MAX</code> aproach (keeping in mind that the column <code>Qty</code> is summed but the column <code>px</code> is not)</p>
  
  <p><strong>THESE ARE THE TABLE AND INDEXES CREATION SCRIPTS</strong></p>
  
  <pre><code>CREATE TABLE [dbo].[Nemos](
      [Nemo] [varchar](50) NOT NULL,
      [Description] [varchar](50) NULL,
      [ProductId] [varchar](10) NULL,
      [ExpirationDate] [datetime] NULL,
      [CurrentValue] [numeric](21, 5) NULL,
      [SaleContracts] [int] NULL,
      [MPV] [numeric](21, 5) NULL,
      [BuyContracts] [int] NULL,
      [MPC] [numeric](21, 5) NULL,
      [Active] [bit] NULL,
      [ContractQuantity] [numeric](18, 2) NULL,
      [NemoFactor] [int] NULL,
      [MinFactor] [numeric](6, 2) NULL,
      [Factor] [numeric](6, 2) NULL,
      [Delta] [numeric](6, 2) NULL,
      [TipoDelta] [varchar](5) NULL,
      [ContractType] [varchar](50) NULL,
      [FinalTradingDate] [datetime] NULL,
      [CompGroup] [varchar](50) NULL,
      [TypeDelivery] [varchar](5) NULL,
      [Decimals] [int] NULL,
      [BeginTradingDate] [datetime] NULL,
      [PxRef] [numeric](21, 5) NULL,
      [BuyQty] [int] NULL,
      [SaleQty] [int] NULL,
      [LastUpdate] [datetime] NULL
  ) ON [PRIMARY]
  
  GO
  
  SET ANSI_PADDING OFF
  GO
  
  ALTER TABLE [dbo].[Nemos] ADD  DEFAULT (getdate()) FOR [BeginTradingDate]
  GO
  
  ALTER TABLE [dbo].[Nemos] ADD  DEFAULT (getdate()) FOR [LastUpdate]
  GO
  /****** TABLE NEMOS INDEXES ******/
  
  CREATE NONCLUSTERED INDEX [Nemos_Nemo] ON [dbo].[Nemos] 
  (
      [Nemo] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  GO
  
  /***************************** marketState **************/
  CREATE TABLE [dbo].[marketState](
      [Nemo] [varchar](50) NULL,
      [OrderNumber] [varchar](50) NULL,
      [Px] [numeric](21, 5) NULL,
      [Qty] [int] NULL,
      [side] [int] NULL,
      [owner] [varchar](50) NULL,
      [LastUpdate] [datetime] NULL,
      [Consecutivo] [int] IDENTITY(1,1) NOT NULL,
   CONSTRAINT [PK_marketState] PRIMARY KEY CLUSTERED 
  (
      [Consecutivo] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  
  /****** MARKETSTATE STATE INDEXES ******/
  ALTER TABLE [dbo].[marketState] ADD  CONSTRAINT [PK_marketState] PRIMARY KEY CLUSTERED 
  (
      [Consecutivo] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  GO
  
  CREATE NONCLUSTERED INDEX [Nemo_Side] ON [dbo].[marketState] 
  (
      [Nemo] ASC,
      [side] ASC
  )
  INCLUDE ( [Px],
  [Qty]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  GO
  
  CREATE NONCLUSTERED INDEX [MS_OrderNumber] ON [dbo].[marketState] 
  (
      [OrderNumber] ASC
  )
  INCLUDE ( [owner]) WITH (PAD_INDEX  = ON, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
  GO
  </code></pre>
  
  <p>The core idea is to calculate the <strong>BestBuyingPrice = MPC</strong> and the <strong>BestSellingPrice = MPV</strong> for a Derivative (nemo..tecnico).</p>
  
  <p>When <strong>Side = 1</strong> its a <strong>BuyingOrder</strong> and when <strong>Side = 2</strong> its a <strong>SellingOrder</strong> </p>
  
  <p>When two or more orders have exactly the same price its quantities need to be added(sum)
  see the image below </p>
  
  <p><img src="https://i.stack.imgur.com/mVNVT.png" alt="VisualExample of calculations"></p>