SQLTeam.com | Weblogs | Forums

How to optimise the table performanace (90 lacs records)

Hi,

I have a table with 5000 users along with 6 years of attendance data. If I want to retrieve user data from the table it takes time to load.

Table Structure

Usercode, Rosterdate, Shift code, AttendanceCode

Case 1

If i want to retrieve the shift information from 1-jun-2020 to 10-jun-2020 for 5000 users,

would take time to retrieve the data in SQL server.

what kind of index should i update in above table?

Regards
Sethu

index on the date columns in filter will definitely help.

Welcome! Is this for reporting purposes?

HI,

Thanks.

I did index on following columns

Usercode, Rosterdate
Rosterdate alone

But no helps.

Regards
Sethu

hi

indexing based on how you are going to query the data !!!
example if you use user = 'Sam' index on user = 'Sam' ( filtered index )

please google search !!! lots lots lots of things ideas are there

below is one such link
hope it helps :slight_smile: i did not take a look into it !!!

ok. thanks

You should create a unique clustered index on ( Rosterdate, Usercode ).

A non-clustered index isn't likely to help with very many queries. Unless it is a completely covering index, but that likely means you'd have to repeat every column in the table.