I have a table with 50M plus data.
I need to fetch 4 columns and show on datatable order by 'name_voter' column.
First, I started with basic query such as
select id,name_voter,home_street_address_1, home_address_city from base_voter where deleted_at is null order by home_street_address_1 offset @pagesize * (@pagenumber - 1) rows fetch next @pagesize rows only option(recompile)
With the increase in data, it started to become slow. So, I decided to create an index as
create index IX_base_voter_name_voter_asc_deleted_at on base_voter(name_voter asc,deleted_at) include(home_street_address_1, home_address_city) where deleted_at is null
It makes quite faster but not enough, so one of my friends suggest to create additional table as:
create table base_voter_name_voter ( sr_no bigint not null, base_voter_id int, constraint pk_name_voter_sr_no primary key (sr_no) ) go
and need to run following query on every insertion/deletion in the base_voter table
create procedure procUpdateSortDetails as begin truncate table base_voter_name_voter insert into base_voter_name_voter(sr_no,base_voter_id) select row_number() over(order by name_voter asc),id from base_voter with(nolock) where deleted_at is null end go
After this the execution time is acceptable, but still, I am not convinced to run that query on every update.
1) Is there any other way to make this faster without updating this additional table on every insertion manually need to be automatically updated something as in index.
2) Isn't index itself should be enough to handle tasks like this? Am I doing it incorrectly or only this much index is capable
3) Is any other way to deal with such problems, to make the query faster even for the last page?
4) If there is also some other way of representing data so that this query might not be needed. Please, mention those.
My data table is something like
name_voter (asc) | home_street_address_1 | home_address_city
Please, suggest some way to tackle this problem.