How to optimise the table performanace (90 lacs records)

SQL Server Administration
1

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

2

index on the date columns in filter will definitely help.

3

Welcome! Is this for reporting purposes?

4

HI,

Thanks.

I did index on following columns

Usercode, Rosterdate
Rosterdate alone

But no helps.

Regards
Sethu

5

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

6

ok. thanks

7

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.