Optimizing SQL Server query

Question

SELECT TOP 10 dic.item_cd,
              dic.item_knm,
              dic.item_enm
FROM tbus.dt_item_com dic
INNER JOIN tbus.mfProd mfp ON dic.item_cd LIKE mfp.prodId
INNER JOIN tbus.plu_category plc ON mfp.prodType LIKE plc.plc_prodType
WHERE (dic.item_knm LIKE '%%'
  OR (dic.item_enm LIKE '%%')
  AND (dic.item_cd NOT IN
         (SELECT TOP 0 dic.item_cd
          FROM tbus.dt_item_com dic
          INNER JOIN tbus.mfProd mfp ON dic.item_cd LIKE mfp.prodId
          INNER JOIN tbus.plu_category plc ON mfp.prodType LIKE plc.plc_prodType
          WHERE (dic.item_knm LIKE '%%'
                 OR dic.item_enm LIKE '%%')
          ORDER BY dic.item_cd DESC))
ORDER BY dic.item_cd DESC

This is the SQL query I'm trying to optimize.

What this query does is Inner Join three tables by item names (currently it's just '%%'. It's gonna be something like '%apple%' later on'). And, cuz this is for paging, it has NOT IN syntax in there too.

The problem here is there are two duplicated INNER JOIN & WHERE phrases - one in inside of NOT IN and the other in outside. How can I remove this duplicated codes and make it faster :( ?

(It's based on SQL Server 2000)

Answer 1

Let me answer a different question (as per the comments): How to do paging on SQL Server? You normally use the row-Number function:

select * from (
 select *, row_number() over(order by something) r
 from T
) x
where r >= 10 and r < 20

Then, all your problems go away.