CODEWITHSUNDEEP
sqlsql-servert-sql
AristoSci
AristoSci

Reputation: 13

How TOP affects SQL performance

I'm at a bit of a loss here. I have two nearly identical queries:

select
    top 301
    *
from
    view
where
    columnA like '%val%'
    and
    columnB > '12/12/2019 00:00:00'
order by
    columnC

and

select
    *
from
    view
where
    columnA like '%val%'
    and
    columnB > '12/12/2019 00:00:00'
order by
    columnC

When I run these two queries they work fine with the date provided. If I look back a little in history, the first query will effectively timeout my application, but the second query works fine.

I did some trouble shooting, and it appears the following must be true to time out the app.

  1. top # must be included
  2. order by must be on columnC
  3. columnB must be sufficiently far in the past.

Due to how my application is set up, the set up for the query will always include these three elements, and I am not able to move any of these elements into the view itself, so they will always be full table scans.

I am in the process of trying to optimize my view, but I simply do not understand how adding top # to the query is causing the application to time out the way that it is. Any help understanding how top affects SQL performance would be appreciated.

Additional Notes:

I have noticed that adding TOP does change the execution plan a little. The largest difference I see in the two is that without top, the execution plan runs parallelism pretty much across every join, and this is not the case for the query containing top.

Upvotes: 1

Views: 141

Answers (2)

Masoud R
Masoud R

Reputation: 114

If you are trying to show pages with limited number of record in your application i'd suggest you use OFFSET Fetch.

1- T-SQL Querying: TOP and OFFSET-FETCH
Source: 1

Upvotes: 0

gotqn
gotqn

Reputation: 43666

What you are seeing in your execution plan is normal. If you check the docs you can see there are several constructs that inhibit parallelism:

  • scalar UDFs
  • remote query
  • dynamic cursors
  • recursive queries
  • Multi-statement table-valued functions
  • TOP keyword

So, adding a TOP may not look like something big, but it will completely change the execution plan in some cases.

Sometimes, one technique to optimize view execution is to materialize it. There are a lot of limitations again about what can exist in an indexed view, but if your view can be materialized, you should try it.

Upvotes: 1

Related Questions