When search on long text on table have 40 milion rows it is very slow so what I do ?

Question

1.44/5 (2 votes)

See more:

I work on SQL server 2012 I need to search on table partswithcompany that

have 40 million rows .

SQL

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

it is very slow to retrieve data

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

SQL

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

What I have tried:

SQL

alter table #partswithcompany add NotMappedCode int null

update s set s.NotMappedCode=   abs(CONVERT(int, HASHBYTES('SHA2_512', searchpart + cast(DistributorId as varchar(10)))))  from #partswithcompany s

Posted 20-Feb-21 12:22pm

ahmed_sa

Updated 20-Feb-21 15:02pm

Patrice T

v4

Add a Solution

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Patrice T · Answer 1 · 2021-02-20T14:56:00

Quote:
When search on long text on table have 40 milion rows it is very slow so what I do ?

When will you learn 'Database Design' properly ? As you have been told many times.

Database design is much more than table creation.

In a technical book, when you search something, you can read the whole book, or go to the index at the end and select pages matching a keyword.
Same techniques exist with databases and choosing how to combine them with the flat database is 'database design', and the design also depend on the usage of the database.

So I can tell you to use indexes, but if you don't understand how to use them, chances are that you will get a mess.

Gerry Schmitz · Answer 2 · 2021-02-20T15:02:00

Solution 2

I've used it and it works (real-time customer service address look ups and real-time stock headline analysis). You just have to put the effort in to learn it.

Full-Text Search - SQL Server | Microsoft Docs[^]

Posted 20-Feb-21 15:02pm

Gerry Schmitz

When search on long text on table have 40 milion rows it is very slow so what I do ?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

When search on long text on table have 40 milion rows it is very slow so what I do ?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us