Post reply

Is this efficient use of SQL ?

  • March 6, 2009 at 12:37 pm

    #201041

    select proemail

    from pro

    join res on pro.proln=res.resln and

    pro.profn=res.resfn and

    pro.Promi=res.resmi

    where (contains (resnar, '"ts/sci"

    and "top secret"

    and "clearance"'))

    and ProEmail like '%@%'

    and Prodt = '2009/03/06'

    group by proemail

    order by proemail

    Thanks

  • March 6, 2009 at 1:35 pm

    #955239

    This will lead to table or index scans

    and ProEmail like '%@%'

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • March 6, 2009 at 1:54 pm

    #955258

    The 'PRO' table is not FTI.

    Is it worth making it FTI to swap the 'like' to a 'contains' ?

    Thanks for the feedback !

  • March 6, 2009 at 2:09 pm

    #955267

    Unfortunately there is not a concrete answer on that as it really depends on the size of the table. If you are looking at a relatively small table my guess is that you will find that the FTI will not bring any added performance. That changes when the numbers get larger. What that point is? I don't know honestly but it is worth testing.

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • March 6, 2009 at 2:13 pm

    #955270

    At the moment, i have about 1 million records and the search takes under 5 seconds, using '%like%'.

  • March 6, 2009 at 2:17 pm

    #955275

    My guess is that the FTI will perform better, and I believe you would limit those reads a bit as well. 🙂

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • March 6, 2009 at 5:06 pm

    #955342

    DavidB (3/6/2009)

    My guess is that the FTI will perform better, and I believe you would limit those reads a bit as well. 🙂

    Quite likely, but I'd test it very thoroughly. Have you looked at the execution plan? Are you getting scans?

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

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

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