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

Question

Post reply

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

ahmed_elbarbary.2010

Hall of Fame

Points: 3118
More actions
February 20, 2021 at 11:18 pm

#3847872
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)
```

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 1

ahmed_elbarbary.2010 wrote:

where CompanyId=1234 and SearchParts='A5ghf7598fdmlcpghjk'

Have you got an index on SearchParts, CompanyId (with the Keys in THAT order provided that CompanyID has many rows and SearchParts is reasonably unique)? If the combination of SearchParts and CompanyId IS Unique then be sure to create a UNQIUE Index.

If

CompanyId=1234 and SearchParts='A5ghf7598fdmlcpghjk'

is unique then, with that index, retrieval will be "instant"

Michael L John One Orange Chip Points: 27038 More actions · Answer 2

Can you attach the execution plan?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 3

ahmed_elbarbary.2010 wrote:

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)

Dude! You've posted the same question as you did at the following link! How about providing us with the information that I asked for on that previous post? No one is going to be able to help you without that information other than to suggest the right kind of index, which you offered no proof as having.

https://www.sqlservercentral.com/forums/topic/how-to-write-query-generate-unique-id-for-concate-both-columns-search-parts-and

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)