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

ahmed elbarbary

5y955

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)

5 Replies

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

Priyanka K S5y ago
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/
+1
Kiran Mohanty5y ago
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 < 100
ORDER BY RowNum
+1
Rajanikant Hawaldar5y ago
https://www.c-sharpcorner.com/article/why-is-my-sql-server-query-slow/
+1
Sachin Singh5y ago
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)
+1
Rijwan Ansari5y ago
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.
+2