-1

I have SQL where am using row offset to perform pagination, which is taking around 10minutes to return 32 records, but without using row offset my query giving 500+ records within second.

So I want to understand what could be have been lead to this issue. Could anyone help.

Thank you!

Thom A
  • 88,727
  • 11
  • 45
  • 75
Adarsh H D Dev
  • 588
  • 7
  • 29
  • 2
    Could you please check if there is an index on the affected column and if not, create it and try agin? – Jonas Metzler Apr 27 '22 at 06:41
  • 3
    Please only tag the actual version of SQL Server you are using. – Dale K Apr 27 '22 at 07:06
  • There are actually loads of articles around about this being a performance issue, google "sql server best performing paging" – Dale K Apr 27 '22 at 07:45
  • Pretty sure SQL Server 2008 didn't support `OFFSET`, so are you *sure* you're using SQL Server 2008? 2008 is also *completely* unsupported, for almost 3 years, so I strongly suggest you sort out that upgrade path ASAP. – Thom A Apr 27 '22 at 07:51
  • See https://stackoverflow.com/questions/70519518/is-there-any-better-option-to-apply-pagination-without-applying-offset-in-sql-se/70520457#70520457 – Charlieface Apr 27 '22 at 10:29
  • To Answer your queries @JonasMetzler we have all the required index, if remove "offset 0 fetch next 10 rows only" from my select statement it works just fine, only "row offset" feature is causing trouble. – Adarsh H D Dev Apr 27 '22 at 12:21
  • @Larnu Am using the 2019 SQL server – Adarsh H D Dev Apr 27 '22 at 12:23
  • Are you sure about your indexes? Did you have a look to the execution plan? – Jonas Metzler Apr 27 '22 at 12:25
  • *"Am using the 2019 SQL server"* then why did you tag [[tag:sql-server-2008]] and why is [[tag:sql-server-2014]] still tagged..? – Thom A Apr 27 '22 at 12:26

1 Answers1

0

My user database(App) and system database (temp db) were using different compatibility, as soon I set the compatibility same as system database, it worked like charm.

Thom A
  • 88,727
  • 11
  • 45
  • 75
Adarsh H D Dev
  • 588
  • 7
  • 29