sql server - Is it normal for SQL Paging to take Longer (x15~x20) than Getting All Data? -

i have view around 16k rows , takes around 5 seconds data.

i've decided implement "loading" application gui wouldn't freeze , user able work/look with/at provided data in datagridview.

i've noticed if use sql paging getting data takes around 90 seconds(1,5 minutes) it's counterproductive.

now wonder if it's normal , why use if is?

i've tried 3 ways of sql paging:

i'm using 160 testing purposes!

declare @int_percentage int = 1  while @int_percentage <= 100 begin     select o.*, p.percentage     vappointmentdetailswithcomments o     left join (select appointmentid, ntile(100) over(order appointmentid) percentage                 vappointmentdetailswithoutcomments) p on p.appointmentid = o.appointmentid     p.percentage = @int_percentage      set @int_percentage = @int_percentage + 1 end --------------------------------------------------------------------------------------------------- declare @int_percentage int = 1, @int_appointmentid int = 0  while @int_percentage <= 100 begin     select top 160 *     vappointmentdetailswithcomments     appointmentid > @int_appointmentid      set @int_percentage = @int_percentage + 1     set @int_appointmentid = @int_appointmentid + 161 end --------------------------------------------------------------------------------------------------- declare @int_percentage int = 1, @int_currentstartingrowindex int = 1  while @int_percentage <= 100 begin     exec spgetrows @int_startingrowindex = @int_currentstartingrowindex, @int_maxrows = 160      set @int_percentage = @int_percentage + 1     set @int_currentstartingrowindex = @int_currentstartingrowindex + 160 end --------------------------------------------------------------------------------------------------- select * vappointmentdetailswithcomments 

procedure:

create procedure [dbo].[spgetrows]  (     @int_startingrowindex int,     @int_maxrows int )  declare @int_firstid int  -- getting 1'st id set rowcount @int_startingrowindex select @int_firstid = appointmentid vappointmentdetailswithoutcomments order appointmentid  -- setting rowcount max set rowcount @int_maxrows  -- getting data >= @int_firstid select * vappointmentdetailswithcomments appointmentid >= @int_firstid  set rowcount 0  go 

with results:

tables , views creation , filling data:

for xml path in "vappointmentdetailswithcomments" main performance problem

create table [dbo].[appointment](     [id] [int] identity(1,1) not null,     [number] [int] not null,  constraint [pk_appointment] primary key clustered  (     [id] asc )with (pad_index  = off, statistics_norecompute  = off, ignore_dup_key = off, allow_row_locks  = on, allow_page_locks  = on) on [primary] ) on [primary]  go  alter table [dbo].[appointment] add  constraint [df_appointment_number]  default ((0)) [number] go --------------------------------------------------------------------------------------------------- create table [dbo].[comment](     [id] [int] identity(1,1) not null,     [appointment_id] [int] not null,     [text] [nvarchar](max) not null,     [time] [datetime] not null,  constraint [pk_comment] primary key clustered  (     [id] asc )with (pad_index  = off, statistics_norecompute  = off, ignore_dup_key = off, allow_row_locks  = on, allow_page_locks  = on) on [primary] ) on [primary]  go  alter table [dbo].[comment]  check add  constraint [fk_comment_appointment] foreign key([appointment_id]) references [dbo].[appointment] ([id]) go  alter table [dbo].[comment] check constraint [fk_comment_appointment] go  alter table [dbo].[comment] add  constraint [df_comment_text]  default (n'some random comment testing purposes') [text] go  alter table [dbo].[comment] add  constraint [df_comment_time]  default (getdate()) [time] go --------------------------------------------------------------------------------------------------- create view [dbo].[vappointmentdetailswithcomments] select a.id appointmentid, (k.comments + char(13) + char(10)) comment appointment left join     (select a.id,         (select stuff             ((select replace(char(13) + char(10) + k.text, char(7), '')         comment k         k.appointment_id = a.id         , k.text != ''         order k.time xml path, type ).value('.[1]', 'nvarchar(max)'), 1, 1, '')) comments     appointment a) k on k.id = a.id  go --------------------------------------------------------------------------------------------------- create view [dbo].[vappointmentdetailswithoutcomments] select a.id appointmentid appointment  go --------------------------------------------------------------------------------------------------- set nocount on  begin tran  declare @int_appointmentid int = 1,          @int_tempcomment int while @int_appointmentid <= 16000  begin      insert appointment values (@int_appointmentid)      set @int_tempcomment = 1      while @int_tempcomment <= 5     begin         insert comment (appointment_id) values (@int_appointmentid)          set @int_tempcomment = @int_tempcomment + 1     end  set @int_appointmentid = @int_appointmentid + 1  end  commit tran  go 

execution plans: fast(fetchall) slow(top)

part of performance issue because there no index on comment table appointment_id column. clustered index on appointment_id , changing primary key index non-clustered, select query vappointmentdetailswithcomments elapsed time reduced 5 seconds down 3.5 on test box. below script create clustered index , recreate primary key non-clustered index.

alter table dbo.comment drop constraint fk_comment_appointment;  alter table appointment drop constraint pk_appointment;  alter table appointment add constraint pk_appointment     primary key nonclustered(id);  alter table dbo.comment      add constraint fk_comment_appointment foreign key(appointment_id)     references dbo.appointment (id);   create clustered index cdx_comment_appointment_id on comment(appointment_id); go 

the string concatentation of comments expensive operation perform in t-sql. suggest on application side, expect sub-second 16k rows. avoid need jump through hoops on sql side simple join comments:

create view dbo.vappointmentdetailswithindividualcomments select a.id appointmentid, k.text, k.time dbo.appointment  left join dbo.comment k         on k.appointment_id = a.id         , k.text <> ''; go  select appointmentid, text, time dbo.vappointmentdetailswithindividualcomments order time; go 

regarding pagination techniques listed, first perform progressively poorer further result set due scan of appointments.

the query second missing order appointment_id. order by required top deterministic results. however, method have merit pagination performance perspective because perform index seek on appointment table, providing consistent performance regardless of position within result set.

set rowcount deprecated bottom line is perform first query (progressively worse).