-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!

Larnu
  • 76,706
  • 10
  • 34
  • 63
Adarsh H D Dev
  • 568
  • 6
  • 25
  • 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. – Larnu 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..? – Larnu 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.

Larnu
  • 76,706
  • 10
  • 34
  • 63
Adarsh H D Dev
  • 568
  • 6
  • 25