SQLTeam.com | Weblogs | Forums

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

Transact-SQL
,
#1

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)
#2

What is the clustered index on this table? If there is no clustered index - create one using both columns with CompanyId as the leading column.

If that cannot be done - then create a non-clustered index the same, with CompanyId first and SearchParts second.

Of course...if more of your queries only use SearchParts, for example - to get a list of companies with that part then using a covering non-clustered index with SearchParts as the first column - and CompanyId as the second column or as an included column.

#3

i create index on both columns and still very slow
so are creating hashbyte Colum generating id for both column
is good idea for performance

#4

Hashbytes will make an even wider column for than what you currently have for your SearchParts column and that means it'll be even slower.

Please post the complete CREATE TABLE code for the table including all indexes and constraints. Then post your code and an actual execution plan as a loadable execution plan that we can drill down into to see what's going on.

If you can't do that, then the best we can do is say that you've done something wrong and you need to keep trying.

#5

i create index on both columns and still very slow

Hmm, if you created an index on ( SearchParts, CompanyId ) (or vice versa), then this query:

SELECT SearchParts, CompanyId from partswithcompany where
CompanyId=1234 and SearchParts='A5ghf7598fdmlcpghjk'

cannot be real slow. There must be something else that is slow.

1 Like
#6

Did you create 1 single index that includes both or 1 index for each?

1 Like