title: How to optimize this slow MSSQL query?
body: <p>Here is my mssql query: </p>
  
  <pre><code>    SELECT  ROW_NUMBER() OVER(ORDER BY ii.item_id ASC) AS Row,
          ii.item_id, 
          ii.item_description, 
          ii.item_function_id, 
          ib.brand_name, 
          if.name, 
          ipr.part_number, 
          ipr.default_part_number, 
          imn.model_name , 
          imn.is_default,
          img.model_group_name, 
          ims.model_series_name , 
          imf.model_family_name, 
          icg.name as cig_name, 
          ips.proxy_set_name, 
          icn.cartridge_name, 
          (SELECT SUM((quantity - quantity_on_hold - quantity_unposted) * uofm) FROM warehouse_inventory where  sage_item_id = ii.item_id) as qty, 
          ip.item_price FROM ii  
  
  LEFT JOIN ib ON ib.brand_id = ii.brand_id
  LEFT JOIN if ON if.id = ii.item_function_id
  LEFT JOIN ipr ON ipr.item_id = ii.item_id
  
  LEFT JOIN imn ON imn.model_id in 
      (SELECT  frv.child FROM frv WHERE frv.id = 4 AND frv.parent in 
          (SELECT  frv.parent FROM frv WHERE frv.id = 5 AND frv.child = ii.item_id ) )
  
  LEFT JOIN img on img.model_group_id in 
          (SELECT  frv.parent FROM frv WHERE frv.id = 5 AND frv.child = ii.item_id ) 
  
  LEFT JOIN imf on imf.model_family_id in 
      (SELECT  frv.child FROM frv WHERE frv.id = 15 AND frv.parent in
              (SELECT  frv.parent FROM frv WHERE frv.id = 5 AND frv.child = ii.item_id ) ) 
  
  LEFT JOIN ims on ims.model_series_id in 
      (SELECT  frv.parent FROM frv WHERE frv.id = 9 AND frv.child in
          (SELECT  frv.child FROM frv WHERE frv.id = 4 AND frv.parent in 
              (SELECT  frv.parent FROM frv WHERE frv.id = 5 AND frv.child = ii.item_id ) ) )
  
  LEFT JOIN icg ON icg.item_compatible_group_id in 
      (SELECT  frv.parent FROM frv WHERE frv.id = 25 AND frv.child = ii.item_id )
  
  LEFT JOIN ips ON ips.proxy_set_id in 
      (SELECT  frv.parent FROM frv WHERE frv.id = 2 AND frv.child = ii.item_id )
  
  LEFT JOIN icn ON icn.cartridge_id in 
      (SELECT  frv.child FROM frv WHERE frv.id = 13 AND frv.parent = ii.item_id )
  
  LEFT JOIN ip ON ip.item_id = ii.item_id AND ip.sales_interface_id = 40
  
  WHERE ii.is_deleted = 'false' 
  ORDER BY ii.item_id  ASC
  OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY
  </code></pre>
  
  <p>The task is to get from db top 100 items and to put them in a table. The problem is: the columns <code>part_number</code> and <code>model_name</code> have multiple values for the same item_id, so when executing this query, if an item have 100 different model_names then it is displayed 100 times. And because of this "OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY" is not working. Is any way to fix this and optimize the query ? </p>
  
  <p>I'm working with coldfusion and there is a way to hide all duplicates() but the query is not working when I try to display 100 different items(getting the 503 error). For Example, when I set "OFFSET 0 ROWS FETCH NEXT 5000 ROWS ONLY" I get only 39 distinct rows to show in table.</p>