How do I optimise the query

[Palak Shah]

Hello folks,

I have prepared one query, but I feel this can be even more optimised! But I'm not sure what can be done

Query

SELECT
  TOP 10 *
FROM
  (
      SELECT
      co.orderid,
      co.orderStatusId,
      co.paymenttransactionstatusid,
      (
          SELECT
          Count(IdProductSelect)
          FROM
          orderproductselect ops
          WHERE
          ops.orderid = co.orderid
          AND co.orderstatusid IN (2, 9)
          AND ops.quantity - ops.splitquantity > 1
      ) AS LineItemsCount
      FROM
      ci_orders co
  ) AS temp
WHERE
  lineitemscount > 1
  AND paymenttransactionstatusid = 1
  AND orderid NOT IN (
      SELECT
      orderid
      FROM
      ordersplit
  )
  AND orderid Not IN (
      SELECT
      ChildOrderId
      FROM
      ordersplit
  )
ORDER BY
  LineItemsCount

Can someone please help

 

[jmcilhinney]

Perhaps you could explain what you're trying to achieve, rather than assuming that we will all work it out from code that you don't think does it as well as possible.

 

[Skydiver]

Also, on the surface, this question is looking to be a SQL language question rather than a C# question. Yes, C# development may need to deal with some SQL because of people's preferred databases, but then C# development may also touch on pixel shaders and I might as well ask questions about how to make an optimal pixel shader.

Anyway, @jmcilhinney 's question about what are you trying to achieve with that query is spot on, and may even steer this back to something more C# specific rather than tangentially related.