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

  • 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)
  • 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"

  • 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/

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

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

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