Charindex in inner join affects the perfomance

  • Hi,

    I have a procedure which has below query,

    Select a.column,count(distinct(c.column))

    from table1 a with (nolock)

    inner join table2 b with(nolock) on a.id=b.id

    inner join table3 c with (nolock) on charindex(c.colum,a.column)>0

    group by a.column

    table1 has almost 36,000 rows and query takes almost 40 seconds to load resultset.

    a.column has fts index installed on it and it is xml datatype.

    I tried to split the joins but results vary due to grouping.

    How can I rewrite this query to make it more fast???

  • The first thing that jumps out at me is the

    INNER JOIN table3 c ON CHARINDEX(c.Column,a.Column)>0

    Joining on a calculated value is just terrible for performance, because you can't index the column. Any chance you could post the table definitions and maybe some sample data (doesnt need to be real, but representative).

  • Hi,

    I'm using sys.dm_fts_parser to get values to table3.column. User provides the text to search and parser finds diffrnet combination of words.

    XML datatype is used in charindex comparision from table1 and it has 2 more columns in table. One is primary key which is identity column and other has foreign key.

    XML has <Product>

    <Product_name>details</Product_Name>

    <Product_tag>details</Product_tag>

    <Product_Description>details </Product_Description>

    ......it goes on for some more.....................

  • What if you shred the XML to a table, and index it? Then the join can use the index for the subsequent query.

  • Hi,

    I have already tried creating primary and secondary(value) xml indexes on the table1.column but have no impact.

  • a.column has fts index installed on it and it is xml datatype.

    1. Full Text Indexing isn't going to help you here

    2. returning XML data in a SQL query is generally slow.

    3. Grouping by an XML value is going to completely slow you down.

    inner join table3 c with (nolock) on charindex(c.colum,a.column)>0

    You're essentially saying WHERE c.colum is LIKE %<XML Value>% - this is killing you too.

    count(distinct(c.column))

    You should post some DDL and explain exactly what you are trying to do. There is really no way to speed this up without understanding that. This query needs to be re-written.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • Does this help a bit?

    Select a.mycolumn,count(distinct(c.mycolumn))

    from table1 a with (nolock)

    inner join table2 b with(nolock) on a.id=b.id

    inner join table3 c with (nolock) on a.mycolumn LIKE '%' + c.mycolumn + '%'

    group by a.mycolumn

    If you're using FTS, you should be using FTS functions instead of normal string functions.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • HI,

    Thanks for your response.

    I'll explain in more detail. We are trying to provide custom rank to a search.

    Table2 and table3 are temp tables and we are using FTS functions to populate it with the words provided by user.

    that INNER JOIN is as below,

    INNER JOIN @TEMP2 ON CHARINDEX(COLUMN1,CONVERT(NVARCHAR(MAX),XML_COLUMN))>0

    LIKE also doesn't work as it slows down due to nvarchar(max).

  • HI,

    Thanks for your response.

    I'll explain in more detail. We are trying to provide custom rank to a search.

    Table2 and table3 are temp tables and we are using FTS functions to populate it with the words provided by user.

    SELECT a.productid,count(distinct(b.rank),max(rank/a.productid)

    from table1 a with (nolock)

    inner join table2 b on a.productid=b.productid

    inner join table3 c on charindex(c.productname,convert(nvarchar(max),productxml))>0

    group by a.productid

    LIKE also doesn't work as it slows down due to nvarchar(max).

  • Please, read these articles and come back.

    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 10 posts - 1 through 9 (of 9 total)

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