Tunning

  • Hi All,

    I have a table tab1 with col1 as varchar(100) having non clustered index on it.

    Col1 just has these kind of values in it:

    Col1

    ------------------------------------------

    Employee2

    Internet_proxy

    Ent Std Users - Caravan2

    ESP_GENERALUSERS

    GAKDST3

    Developers_G

    SMS_WORKSTATION_P

    MSDN_Developers

    IRLANSEC

    APPS_INF

    ATION1_CG

    INFORES

    DL MSDN Developers

    Please let me know what is the best way to make use of the index in the following sitiuation :

     

    declare @col1   varchar(100)

    Set @col1  = ',Employee2,Internet_proxy,Ent Std Users - acct,Caravan2,ESP_GENERALUSERS,DL TGAKDST3,Developers_G,SMS_WORKSTATION_P,MSDN_Developers,IRLANSEC,APPS_INF...ATION1_CG,INFORES,DL MSDN Developers,'

    select * from tab1

    where  @group_name_list like '%,' + col1 + ',%'

    Thanks.

     

  • This was removed by the editor as SPAM

  • I don't think the above query should work ok.

    Try checking the query first.

  • I agree. Check the query and then repost and we can help. One thing that you do need to understand, if you want to check for any of those values in the column you need ORs.

    where col1 like '%mygroup1%'

     OR col1 like '%mygroup2%',

    OR ...

     

  • Thanks All,

    The actuall query is very big, I just wanted to summarize it in this post and get some suggestions. I am sorry if I have confused anyone.

    But I did find a answer to it. For that I had to use a function called ParseString which I got it from a friend of mine. Here's the function :

    ================================================

    CREATE function dbo.ParseString (@string varchar(8000),@sep char(1) = ',')

    returns @stringlist table(string varchar(100))--, Row_Cnt INT IDENTITY (1,1) primary key clustered(String, Row_Cnt) ) -- primary key clustered)

    as

    /*

    Purpose:                       parse character  values passed in a comma delimited string

                                        into a table variable

    Input:                            comma delimited string of character values

    Output:                         result set of individual string values

    */

     

    begin

    declare @position smallint

    declare @sepString varchar(10)

    set @sepString = '%' + @sep + '%'

    --separate individual strings using the comma delimiter and

    --insert the values into the @stringlist table variable

    set @position = 1

    if patindex(@sepString, @string) > 0            --mulitple values in list

                begin

                while (@position > 0)

                begin

                            insert into @stringlist values (rtrim(ltrim((substring(@string, 1, patindex(@sepString, @string) - 1)))))

                            set @string= stuff(@string, 1, patindex(@sepString, @string), '')

                            set @position = patindex(@sepString, @string)

                end

                --insert the last value from the input parameter to the table variable

                insert into @stringlist values (rtrim(ltrim(@string)))

                end

    else

                --only one value in input list

                insert into @stringlist values (rtrim(ltrim(@string)))

     

    return

     

    end

    ================================================

    This is what I did :

    declare @col1   varchar(100)

    Set @col1  = ',Employee2,Internet_proxy,Ent Std Users - acct,Caravan2,ESP_GENERALUSERS,DL TGAKDST3,Developers_G,SMS_WORKSTATION_P,MSDN_Developers,IRLANSEC,APPS_INF...ATION1_CG,INFORES,DL MSDN Developers,'

    select * from tab1

    where  col1 in (Select String From dbo.ParseString(@col1,',')

    =======================================

    This fixed my problem.

    Thanks for your help and sorry for the confusion.

     

  • The other option is to use dynamic SQL

    declare @col1   varchar(100)

    set @sql nvarchar(1000)

    Set @col1  = ',Employee2,Internet_proxy,Ent Std Users - acct,Caravan2,ESP_GENERALUSERS,DL TGAKDST3,Developers_G,SMS_WORKSTATION_P,MSDN_Developers,IRLANSEC,APPS_INF...ATION1_CG,INFORES,DL MSDN Developers,'

    set @sql= 'select * from tab1

    where  col1 IN ''' + replace(@group_name_list,',',''',''') + ''''

    execute (@sql)


    Simon Sabin
    SQL Server MVP

    http://sqlblogcasts.com/blogs/simons

Viewing 6 posts - 1 through 5 (of 5 total)

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