SQL Server Performance

View Behaviour ?

Discussion in 'T-SQL Performance Tuning for Developers' started by seanlo@jdg.co.za, Dec 19, 2005.

  1. seanlo@jdg.co.za New Member

    Does anyone have a good link on how views actuall get executed and their behaviour?

    I have come across some strange behaviour.
    Firstly from a sql profiler trace i traced the followign sql.

    Select * from View2 where A = ? and B = ?.
    If i do the same select it takes 1 second, this sql was taking very long.

    I then found out that if i opened the view source t-sql and appended the where clause in it it took very long.

    How does this behaviour work , why does a direct select on the view with the where clause take 1 second but executing the view source with the where clause so long ?

    WHen you place a where clause on a view what actually happens ?
    Why does the t-sql captured not seem to behave as captured but as though it literally executed the view source code with an appended where cluase??

    Also i found that if your view includes a select top 1, where clauses dont work on the view ? It is almost as thought the same top 1 row is returned and that the where cluase does not matter.

    Any idea how this works , i know that the where cluase is valid , if i simply remove the top 1 from the view it works but with top 1 (even though the where cluse only returns 1 row) it does not seem to work, it seems as though the same first row is always returned and then the where clause is tested???

    ANyway i am trying to understand how a where cluase on a view really works ?

    Thanx
  2. FrankKalis Moderator

  3. seanlo@jdg.co.za New Member

    Hi Frank

    No nothing has changed.
    The main issue is it seems as though it is not selecting from the view with a where clause even thought that is the sql captured by profiler, it is as though it is executing the t-sql source for the view wit an appended where clause somewhere ?

    Thanx
  4. FrankKalis Moderator

Share This Page