Query optimization

Question

Hello.

I have such a query. But it looks a bit cumbersome, and I might need to nest the query a few more times. Is there a way to optimize it?

select * from
(select *,row_number()over(partition by deptno order by eid) as rownumber from
(select * from MyTable where Left(op,2) = 'al' ) s ) t where rownumber < 4;

Any help is appreciated.

Answer 1

Hi @ooj-4672

You can learn to use CTE. It can make your code intuitive.

https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver16

Just like this.

;with T1 as(
  select * from MyTable where Left(op,2) = 'al' 
),T2 as(
  select *,row_number()over(partition by deptno order by eid) as rownumber from T1)
select * from T2 where rownumber < 4;

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

I think a query like this would benefit from this index:

CREATE INDEX MyIndex ON MyTable(op, deptno, eid)

And, very important, the condition

left(op,2) = 'a1'

should be rewritten as

op LIKE 'a1%'

The optimizer is not able to understand that the expression with left is good for seeking the index.