2

Scenario

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.

My question:

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.

  • 1
    Not directly related, but you must append the key column (Id) in your sort as a tie breaker in case two rows have the same home_street_address_1. Otherwise you could get missing and duplicated rows across pages. – David Browne - Microsoft Nov 27 '18 at 13:17
2

Try this index:

create index IX_base_voter_home_street_address_1_asc
on base_voter(home_street_address_1 asc)
include(id, name_voter, home_address_city, deleted_at)
where deleted_at is null
  • still taking a lot of time around 20 sec – Saroj Shrestha Nov 28 '18 at 7:30
  • Yes,@SarojShrestha , this index is better than previous one.i don't know why you mark it answer in your "delete question."Can you say what data type is home_street_address_1 ? – KumarHarsh Dec 1 '18 at 17:02
0

Truncating and inserting data in base_voter_name_voter is really very bad bad idea.

Instead create History Table of base_voter. Create Update Trigger on base_voter. Delete the rows from base_voter and insert the deleted rows in History table.

Therefore no need of Filtered index on Base_voter .Also base_voter will hold less records.

and your index become,

create index IX_base_voter_home_street_address_1_asc
on base_voter(home_street_address_1 asc)
include(id, name_voter, home_address_city, deleted_at)

So same query with this index will be far far better.

IMO,this is best way of soft deleting data.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.