SQL Server Performance

Multiple Columns with different Values OR Single Column with Multiple Values?

Discussion in 'SQL Server 2005 General Developer Questions' started by waqar, Aug 21, 2007.

  1. waqar Member

    Hi,

    I have multiple columns in a Single Table and i want to search values in different columns. My table structure is

    col1 (identity PK)
    col2 (varchar(max))
    col3 (varchar(max))

    I have created a single FULLTEXT on col2 & col3.
    suppose i want to search col2='engine' and col3='toyota' i write query as

    SELECT

    TBL.col2,TBL.col3
    FROM

    TBL
    INNER JOIN

    CONTAINSTABLE(TBL,col2,'engine') TBL1
    ON

    TBL.col1=TBL1.[key]
    INNER JOIN

    CONTAINSTABLE(TBL,col3,'toyota') TBL2
    ON

    TBL.col1=TBL2.[key]

    Everything works well if database is small. But now i have 20 millionrecords in my database. Taking an exmaple there are 5million recordwith col2='engine' and only 1 record with col3='toyota', it takesubstantial time to find 1 record.

    I was thinking this i canaddress this issue if i merge both columns in a Single column, but icannot figure out what format i save it in single column that i can usequery to extract correct information.
    for e.g.;
    i was thinking to concatinate both fields like
    col4= ABengineBA + ABBToyotaBBA
    and in search i use
    SELECT

    TBL.col4
    FROM

    TBL
    INNER JOIN

    CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABBToyotaBBA"') TBL1
    ON

    TBL.col1=TBL1.[key]Result = 1 row

    But it don't work in following scenario
    col4= ABengineBA + ABBCorola ToyotaBBA

    SELECT

    TBL.col4
    FROM

    TBL
    INNER JOIN

    CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABB*ToyotaBBA"') TBL1
    ON

    TBL.col1=TBL1.[key]
    Result=0 Row
    Any idea how i can write second query to get result?
  2. martins New Member

    What about creating a composite index on col2 and col3? Maybe that will sort out the problem without you having to try and combine the 2 columns.
  3. waqar Member

    [quote user="martins"]
    What about creating a composite index on col2 and col3? Maybe that will sort out the problem without you having to try and combine the 2 columns.
    [/quote]
    I can try this but i still need to create 2 different CONTAINSTABLE, right?
    if i am not wrong result of both CONTAINSTABLE is independent mean both CONTAINSTABLE will return all records before performing any criteria?
    Because i don't want system to search through 5 million record which may match with first criteria but only return 1 record due to my second criteria.
  4. martins New Member

    I'm not too familiar with the CONTAINSTABLE function, but had a quick look in BOL. As far as I can see you can do it with one CONTAINSTABLE. Since you are not weighting your results, how about doing it without the CONTAINSTABLE function and rather a col2 like '%engine%' and col3 like '%toyota%' in your select statement. Then an index will definitely help.
    Also have a look at your execution plan. Doing it the way you are currently might mean that SQL Server is scanning the table twice.
  5. martins New Member

    Sorry, my bad...you won't be able to do it with one CONTAINSTABLE if the data is in two different columns.
    Also, have a look at using CONTAINS rather than CONTAINSTABLE if at all possible.
  6. satya Moderator

Share This Page