SQL Server Performance

Which query should run fast?

Discussion in 'T-SQL Performance Tuning for Developers' started by rohitkochar, Jun 1, 2007.

  1. rohitkochar New Member

    HI all ,
    I have three version of a query that returns the same result .
    Query used in , like and = operator for searching ...

    I ran the three query but got almost the same execution time i.e.

    DBCC DROPCLEANBUFFERS
    Select * from Tb_PPM_CurrentOutstandingAccount where createdby in ('admin')
    --1 mts 58 secs for 1579950 rows ( first time)
    ----2:12 for 1579950 rows (second time)

    DBCC DROPCLEANBUFFERS
    Select * from Tb_PPM_CurrentOutstandingAccount where createdby = ('admin')
    --- 2 mts for 1579950 rows ( first time)
    ----1:56 for 1579950 rows ( second time)

    DBCC DROPCLEANBUFFERS

    Select * from Tb_PPM_CurrentOutstandingAccount where createdby like ('admin')
    --- : 1:59 for 1579950 rows ( first time)
    --- : 1:57 for 1579950 rows ( secondtime)

    While i was thinking my second query which was finding exact match ,
    will run much faster ..it doesnt..?? What could be the reason behind this??

    Rohit Kochar
    MCP .Net(Web and SqlServer)
    Company : TCS ,Noida
  2. Adriaan New Member

    With these fairly simple criteria, the execution plan will be identical. Just ask SQL to show you the execution plans, and compare.

    The variation in time is probably due to external factors.
  3. chaitanya New Member

    I agree with you Rohit, I think second query should be running fast as per my experience. The link below tell you the poor performance of LIKE which i think also applies to IN Keyword.

    http://www.itjungle.com/mgo/mgo120602-story02.html

    Thanks,
    Chaitanya
  4. Adriaan New Member

    Hm, a little testing shows that LIKE indeed costs a little extra. Anyhow, you should use the appropriate operator for what you're doing:

    LIKE --- when you have wildcards
    = --- when there are no wildcards
    IN --- when you have a value list (or a subquery)
  5. rohitkochar New Member

    I agree with you Adriaan...Execution plan was also very similar...
    just filter search percentage was different and that too with a very less percentage..
    What are external factors u talking about ?

    Rohit Kochar
    MCP .Net(Web and SqlServer)
    Company : TCS ,Noida
  6. Adriaan New Member

    SQL Server is not the only process running on the server. You have long running queries here, so other processes on the server can take up some 'clock ticks'.

Share This Page