SQL Server Performance

Unexpected behavior of @var is null

Discussion in 'SQL Server 2005 General Developer Questions' started by Arhamg, May 21, 2008.

  1. Arhamg New Member

    i have a procedure with a query havin 3 inner joins in which i have used the syntax
    table1 tbl1 inner join table2 tbl2 on tbl1.col1 = tbl2.col1 and (@Var is null or tbl2.col2 = @Var)
    in one of the joins.
    when this proc is called from my commandobj.fill(DS) it takes around 2 mins to execute and when i change it and use coalesce it runs in some 100 millisecs.[*-)].
    can somebody please explain this behavoir and i have checked the executions plan but could not find any major differences.
  2. Adriaan New Member

    First of all, move and (@Var is null or tbl2.col2 = @Var) to the WHERE clause (where it belongs).
    If you're not returning values from tbl2, then you can remove that table from the FROM clause, and use a subquery:

    FROM tbl1
    WHERE (@var IS NULL)
    OR (tbl1.col1 IN (SELECT tbl2.col1 FROM tbl2 WHERE tbl2.col2 = @var))
  3. Arhamg New Member

    thanks a lot Adriaan for your reply, [:)]
    but can u or somebody else please explain the reason behind this difference in execution time of the queries.
    i guess even if i have misplaced the filter criteria it should not atleast kill the query.
  4. Kewin New Member

    There's nothing wrong in an ANSI inner join to provide criteria in the join clause instead of in the where clause.
    As written by the op is perfectly valid.

    The deciding factor in any case is the plan generated. If they are the same, then there's no difference.
    Note though, that there may be situations where one or the other variation will yield different plans, and one may then be 'better' than the other, but it's not a black or white thing.
    Now, for the original question. You say that the plans are (almost) identical..?
    This for me indicates that the differences aren't in the query, but more likely on a higher level.
    How do you time it? Until some result is rendered on screen?
    Sometimes the percieved performance can vary a lot depending on all the factors that need to be considered when timing from execution of a query up to the rendered result. You have lots of stuff in between after the server is done with the query, such as network, client processing etc etc.
  5. Adriaan New Member

    One more thing to check is the data type of the variable, which must match the column data type to avoid implicit conversion. Adding COALESCE probably converts the variable (one-time) rather than the column value (on each row).
  6. Arhamg New Member

    thanks Kewin for your reply,
    i time the proc execution by seeing the RPC:Completed event in profiler.
    and at that time there are no other activities on the DB as well.both the proc calls are made in more or less the same environment.
    and Adriaan the data types of the column and variable are same ie. smallint.
    this my exact query for your referenceSelect VU
    .UserId UserId ,VU.FullName FullName,VU.SOEId SOEID from dbo.VwRoleEntity R with(NoExpand)
    inner join dbo.VwUser VU With (NoExpand) on VU.UserId=R.UserId and (@StdRoleId is null or R.StdRoleId = @StdRoleId)
    Inner join dbo.PutStringToTable(@UserIds,',') as Roletbl
    on Cast(Roletbl.Str as Int) = VU.UserId And VU.DeletedFlag=@FalseFlag
    i have a clustered index on StdRoleId column of the view
  7. FrankKalis Moderator

    I would probably rewrite the query this way:
    VU.UserId UserId ,VU.FullName FullName,VU.SOEId SOEID
    dbo.VwRoleEntity R with(NoExpand)
    dbo.VwUser VU With (NoExpand) on VU.UserId=R.UserId
    dbo.PutStringToTable(@UserIds,',') as Roletbl on Roletbl.STR = VU.UserId
    (@StdRoleId is null or R.StdRoleId = @StdRoleId) And

    Also, since dbo.PutStringToTable is apparently a TVF, why don't you use an appropriate data type for the STR column in order to get rid of this unnecessary CAST(... as INT) in the second JOIN?
  8. FrankKalis Moderator

    Btw, you might also want to experiment with
    (@StdRoleId is null or R.StdRoleId = @StdRoleId)
    changed to
    (R.StdRoleId = @StdRoleId or @StdRoleId is null)
    Depending on whichever situation is more common.
  9. FrankKalis Moderator

    another btw....
    Can you please post the complete body of your procedure? Might be that you are a victim of parameter sniffing.
  10. Adriaan New Member

    It would be interesting to know how you use COALESCE in the actual procedure, as you claim that this makes a big difference. I assume you're forcing a zero on the variable when NULL. I would apply the COALESCE on the variable before the SELECT statement, not inside it.
    You say there's a clustered index on the StdRoleId column of the view, so I assume there isn't one on the underlying table. Can there be NULLs on the StdRoleId column of the view (for instance for outer joins)?
  11. Arhamg New Member

    Select VU
    .UserId UserId ,VU.FullName FullName,VU.SOEId SOEID from dbo.VwUser VU With (NoExpand)
    Inner join dbo.[PutStringToTablewithPK](@StdRoleId,',') as Roletbl
    on Roletbl.Str = VU.UserId And VU.DeletedFlag=0 where
    @StdRoleId is null or exists
    (select 1 from dbo.VwRoleEntity R with(NoExpand) where R.StdRoleId = @StdRoleId and VU.UserId=R.UserId)
    this is my latest query and it gives the results in a flash.but as mentioned in my initial post i want to know that how does changing to coalesce, the proc exec time (when called from vb code) is significantly reduced.
    To add more to confusion, when i pick up the proc call from profiler and run it directly both the queries run fine.
  12. Kewin New Member

    The 'added confusion' seems to further prove that the fault most likely isn't with SQL Server or it's ability to resolve the query, but somewhere higher in the middle tiers or code/presentation layer.
    The messy part here is that there are views and functions involved - that and the calling layers/frameworks ability to resolve different syntaxes may have something to do with the observed differences.

Share This Page