Click here to Skip to main content
15,560,678 members
Please Sign up or sign in to vote.
1.44/5 (2 votes)
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
Updated 20-Feb-21 16:02pm
v4

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.
 
Share this answer
 
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[^]
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900