Optimize SQL query - one hundred twenty-nine million records

Rupesh Kahane

7y846

I have a table (UserDeptMapping) which conatins 129000000 (one hundred twenty-nine millions records).

Department details table which has 500 entries.

DeptId

WorldLocationId

RegionId

DistrictId

Table UserDeptMapping which contains below data like

Suppose execute below query against Department Id 1 like

Output comes with total 300000 rows which is taking 30 seconds.

How can I optimiase the query., so it will reduce the time.

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Gupta7y ago
No any user will read all the 3,00,000 records at a time, you can use OFFSET and FETCH NEXT. Showing records in chunks will have good readability and will fetch records within seconds/milliseconds
0
Vishal Kadam7y ago
Yes you can try with SQL NoLock hint. It will help to improve select statement performance.
Also go through the link : https://www.mssqltips.com/sqlservertip/2470/understanding-the-sql-server-nolock-hint/
0
Gowtham Ramar7y ago
try with (nolock) with your query
Select * from UserDeptMapping with (nolock) Where MappingId = 2
0