Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Unnecessary WHERE on view make a performance problem

Answered Unnecessary WHERE on view make a performance problem

  • Wednesday, March 20, 2013 2:32 PM
     
      Has Code
    Sign In to Vote
    0

    Hi.

    An interesting case. I have a view that return assignments executions by permissions: I know to pull of the connected User from the connection string, and according to the UserID to return him the relevant results to him. If user X run SELECT * from dbo.MyViewWithPermissionCheck he would get the executions of his assignments, and another user, Y, would get another results for the same query.

    One of the columns of the view is UserID, even that the ID will be the same on all records.

    The problem is: 

    If I'll execute:

    SELECT * from dbo.MyViewWithPermissionCheck 
where UserID = 17807

    When the connected user is actually 17807, the query will be run for long time (Tens of seconds). If I will execute it without the WHERE, the query will be completed in 1 second (or less...). as you can understand, the results will be the same in the 2 options (with/without the WHERE).

    Actually it's not a critical issue, because the simple and correct query is without the WHERE, but I guess that this case is good to learn oe or two things about SQL... :-)

    Any ideas?

    Thanks!

    itaigitt, http://copypastenet.blogspot.com

     

All Replies

  • Wednesday, March 20, 2013 2:45 PM
     
     
    Sign In to Vote
    0

    Try creating an indexed view with the index on UserID

     
  • Wednesday, March 20, 2013 2:45 PM
     
     
    Sign In to Vote
    0
    Is the datatype of your userid an integer?
     
  • Wednesday, March 20, 2013 2:57 PM
     
     
    Sign In to Vote
    0

    FarnhamSurrey - the UserId will be the same on all records, Why an index could help?

    I'm Michael - Yep, integer.

    itaigitt, http://copypastenet.blogspot.com

     
  • Wednesday, March 20, 2013 2:59 PM
     
     
    Sign In to Vote
    0
    Can you view the execution plan when you execute your query it will show you the bottleneck.

    Regards, Dinesh

     
  • Wednesday, March 20, 2013 3:15 PM
    Moderator
     
     
    Sign In to Vote
    0

    You may need to share what is in your view definition.

    Do you have a scalar function or other views in your view?  Where do you call this view? Is it in a stored procedure or not (possible parameter sniffing)? 

     
  • Wednesday, March 20, 2013 3:31 PM
     
      Has Code
    Sign In to Vote
    0
     
    CREATE VIEW [dbo].[MyViewWithPermissionCheck]
AS

SELECT	....
		ee.UserID		as [User_ID],
		ee.ExecutionDate as EventExecutionDate
FROM	...
WHERE ee.UserID = dbo.udf_GetUserIdOfSession()
GO

    itaigitt, http://copypastenet.blogspot.com

     
  • Wednesday, March 20, 2013 3:36 PM
    Moderator
     
     Answered
    Sign In to Vote
    1
    The reuse of code idea for this scalar function udf_GetUserIdOfSession is the root of the problem. Can you try to incorporate the logic directly into the view from the function to test the performance? Thanks.
     
  • Wednesday, March 20, 2013 3:38 PM
     
     
    Sign In to Vote
    0
    Jingyang Li - but why the problem is only in the case of WHERE on the view?

    itaigitt, http://copypastenet.blogspot.com

     
  • Wednesday, March 20, 2013 3:48 PM
    Moderator
     
     
    Sign In to Vote
    1
    Can you run both queries in SSMS and include the execution plan in both cases. The reason is indeed in the scalar function and looks like adding WHERE clause to the view changes the execution plan somehow so the function is calculated more times than without where clause.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

     
  • Wednesday, March 20, 2013 4:16 PM
     
     
    Sign In to Vote
    0
    From the exec. plans I see that there is one more call to the table that check the permissions. Does it the reason?

    itaigitt, http://copypastenet.blogspot.com

     
  • Wednesday, March 20, 2013 4:29 PM
     
     
    Sign In to Vote
    0

    I just noticed something about your view definition.

    It says ee.UserID as [User_ID], but when you are querying the view you have UserID = 17807.  Do you have an extra userID defined in the view and do you mean to have your query predicate say user_ID=17807 instead?

     
  • Wednesday, March 20, 2013 4:38 PM
    Moderator
     
     
    Sign In to Vote
    0
    If you remove the WHERE clause from your view, is there any difference?  
     
  • Thursday, March 21, 2013 8:03 AM
     
     
    Sign In to Vote
    0

    I can't remove the WHERE from the view - this is the point of it - to return results for the specific connected user. That why the WHERE in the query on the view is unnecessary, and it should not be there at all.

    I guess, from your responses, that the reasons are one more calls to unnecessary objects in order to check the permissions again for the unnecessary WHERE.

    itaigitt, http://copypastenet.blogspot.com

     
  • Thursday, March 21, 2013 7:22 PM
    Moderator
     
     
    Sign In to Vote
    0

    I just read this very interesting article

    http://www.sqlperformance.com/2013/03/t-sql-queries/the-problem-with-window-functions-and-views and I think it may be relevant for your case or be somewhat of a similar nature.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

     
  • Friday, March 22, 2013 6:13 PM
     
     Answered
    Sign In to Vote
    1

    Create Table valued function instead of scalar valued function. scalar valued function will be invoked for each and every record on the table. take into account the network latency.

    Table valued function will be invoked only once.

    • Marked As Answer by itaigitt Friday, March 29, 2013 7:51 AM
    •