When search on long text on table have 40 milion rows it is very slow

Question

I work on SQL server 2012 I need to search on table partswithcompany that have 40 million rows .

when make select SearchParts, CompanyId from partswithcompany where CompanyId=1234 and SearchParts='A5ghf7598fdmlcpghjk'

it is very slow to re but it take too much long time when make select data from table or when using where condition is also very slow

so i think more then i get idea to use hash byte column so How to do that please ?

if you have any good idea to enhance performance i can accept it

create table #partswithcompany
(
SearchParts nvarchar(200),
CompanyId int
)
insert into #partswithcompany (SearchParts,CompanyId)
values
('A5ghf7598fdmlcpghjk',1234),
('AKLJGSA7598fdmlcpghjk',5870),
('KHJLFFS8fdmlcpghjk',123345),
('A5ghf7598f7GGHYUTYA',3456),
('A5ghfJKKJGHHGghjk',9081818)

Rijwan Ansari · Answer

1:- Check Indexes. 2:- There should be indexes on all fields used in the WHERE and JOIN portions of the SQL statement 3:- Limit Size of Your Working Data Set. 4:- Only Select Fields You select as Need. 5:- Remove Unnecessary Table and index 6:- Remove OUTER JOINS. 7:- Remove Calculated Fields in JOIN and WHERE Clauses. Another way you can limit the results if it is not required load all the data at once.

Kiran Mohanty · Answer

It's better to go with pagination . At a time you choose 50 records to filter data and display it in UI. When user clicks on page 2 or 3, filter data based on Row_Num() starting from 51 or 101 respectively. something like this. SELECT * FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, * FROM Orders WHERE OrderDate >= '1980-01-01' ) AS RowConstrainedResult WHERE RowNum >= 51 AND RowNum ORDER BY RowNum

Sachin Singh · Answer

Indexes on necessary columns will help. also,don't use Unnecessary data types , if smallint works then don't use int etc. if nothing works then instead of normailization , denormalize tables , remove foreign keys and create a single table. (Note:- this is only recommended for analytical purposes)

Priyanka K S · Answer

You can use Full Text Search. Enable FT Index on your table, and then follow the FTS hints to search the text. reference : https://www.sqlshack.com/hands-full-text-search-sql-server/

Rajanikant Hawaldar · Answer

https://www.c-sharpcorner.com/article/why-is-my-sql-server-query-slow/