SQL Server Performance

Is using a like the best way

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by sexydog, Feb 22, 2007.

  1. sexydog New Member

    Hi

    I have the following stored proc where all the parameters are optional. is the following the best way to implement a search on a view .

    declare @sourceID as varchar(500)
    declare @targetID as varchar(500)
    declare @deskID as varchar(500)
    declare @messageType as varchar(500)

    set @sourceID = '%'
    set @targetID = '%'
    set @deskID = '36'
    set @messageType = '%'


    select *
    from viewGetSourceTargetRouteList
    where cast(sourceID as varchar(500)) like @sourceID and
    cast(targetID as varchar(500)) like @targetID and
    cast(deskID as varchar(500)) like @deskID and
    messageType like @messageType



    thanks
    DJ
  2. ranjitjain New Member

    Hi,
    Why are you accepting all ID values in varchar format when it is stored in tables as numeric.
    Also Like will hurt your query performance badly
  3. Roji. P. Thomas New Member

  4. madhuottapalam New Member

    you don't need to CAST here .... Read about STR() function in BOL

    check this...

    Declare @Tab Table (ID int, Name varchar(10))
    insert into @Tab select 101,'SexyDog'
    insert into @Tab select 202,'SexyCat'
    insert into @Tab select 301,'SexyRat'
    insert into @Tab select 411,'SexyRat'
    insert into @Tab select 10011,'SexyRat'

    select *from @Tab where str(id) like '%1%'
    select *from @Tab where str(id) like '%2%'
    select *from @Tab where str(id) like '%00%'

    Madhu

Share This Page