ansaurus

Question

SQL Server 2005 T-SQL Problem : Can you trust the Query Optimizer ? I know I can't !

Answer 1

+2 A:

Is there a reason you can't use index hints (as in your second query) when you're using the variable (in the third query)? It's odd that the query optimizer makes such a bad decision when there's an index available, but it only knows a limited amount about your data, and it chooses as best it can.

Some statistics on the indexed columns might help you out, actually - they keep track of the data, the data layout, and some other information about what the table actually contains, whereas the indexes themselves are only built on top of the table metadata, and the query optimizer doesn't choose on the data itself (unless there are statistics there to help it do so).

Have you run the "Database Tuning Advisor" on the query? Highlighting the query and selecting "Analyze Query in Database Engine Tuning Advisor" from the "Query" menu in SSMS will use the table data to suggest some statistics for you - that might make a huge difference.

rwmnau 2010-01-11 07:13:15

I want to confirm that "Database Tuning Advisor" is not suggesting new statistics / indexes to be created, thus no improvements can be done to the current indexing schema. All the indexes are in place - as example, the 2nd query is returning in 55 milliseconds ! It's only a matter of the query optimizer changing the execution plan ( and choosing a wrong one ) when using a variable parameter in the query.

Adrian S. 2010-01-11 16:07:47

Are there statistics available on the "UserID_i" column? SQL Server makes the best guess it can, but I don't believe it uses variable values when it calculates the execution plan - it generates a plan using the variable, and then substitutes in the value when it's actually executed, which can lead to a less-optimal plan than when there's a hard-coded value. Have you tried the index tuner on the query when it has a variable in it? It may have different recommendations. Also, here's a great blog for all thing indexing: http://www.sqlskills.com/BLOGS/KIMBERLY/category/Indexes.aspx

rwmnau 2010-01-12 01:06:10

Answer 2

A:

EDIT:

First, you need a covering index on (ParentID_i, ID_i). Do you have one?

Second:

I am trying to obtain all the sites with depth = 0 which are having subsites accessible by a user.

This description doesn't match the queries you provided here.

This will return all sites w/ depth = 0 (ie, no more parents) that have subsites accessible to the user:

; WITH Site_R AS (
SELECT s.ID_i
     , s.ParentID_i
  FROM Site_T s
     , UserSiteRight_T usr
 WHERE usr.SiteID_i = s.ID_i 
   AND usr.UserID_i = @UserID_i -- plus any other filters
 UNION ALL
SELECT s.ID_i
     , s.ParentID_i
  FROM Site_T s
     , Site_R sr
 WHERE s.ID_i = sr.ParentID_i
)
SELECT DISTINCT ID_i
  FROM Site_R 
 WHERE ParentID_i IS NULL

Is this the result set you want?

Don't add any unnecessary columns to the recursive CTE. Join them in later, post-recurse, post-reduce.

Peter 2010-01-12 01:50:08

Peter, please check the example under the diagram in my previous post : http://stackoverflow.com/questions/1994254 I am trying to obtain all the sites with depth = 0 which are having subsites accessible by a user.Imagine a tree where you can get to a leaf you have access to by clicking on the ancestors, although you don't have access to the ancestors.Or imagine a tree where you can click on the + to expand the branch and you can click on the branch name only if you have access to.I tried to use recursion at the beginning but, going through the whole tree, it was taking too long.

Adrian S. 2010-01-12 04:12:44

Answer 3

A:

As Peter was asking why I did not use recursion, I am providing below the recursive cte which will return the correct result :

; WITH Site_R AS (
SELECT s.ID_i
  , sp.Path_v
     , s.ID_i AS SubSiteID_i
  , sp.Path_v AS SubPath_v
  , 0 AS Depth_i
  FROM Site_T s
     , SitePath_T sp
 WHERE sp.SiteID_i = s.ID_i
   AND s.ShortName_v LIKE '[a-y]%'
   AND s.ParentID_i = 1
 UNION ALL
SELECT sr.ID_i
  , sr.Path_v
     , s.ID_i
  , sp.Path_v
  , Depth_i+1
  FROM Site_T s
  , Site_R sr
  , SitePath_T sp
 WHERE sp.SiteID_i = s.ID_i
   AND s.ParentID_i = sr.SubSiteID_i
)
SELECT us.*
     , ( SELECT usr.UserID_i FROM UserSiteRight_T usr WHERE usr.SiteID_i = us.SubSiteID_i AND UseriD_i = 1 ) AS UserID_i
  FROM Site_R us

Result's first rows with added UserSiteRight_T.UserID_i column showing the access to the SubSiteID_i :

ID_i    Path_v      SubSiteID_i SubPath_v       Depth_i     UserSiteRight_T.UserID_i
------- ----------- ----------- --------------- ----------- -----------
2       1.2.        2           1.2.            0           1
3       1.3.        3           1.3.            0           NULL
3       1.3.        4           1.3.4.          1           1
3       1.3.        5           1.3.15863.      1           1
3       1.3.        6           1.3.6.          1           NULL
3       1.3.        7           1.3.6.7.        2           1
3       1.3.        8           1.3.8.          1           1
9       1.9.        9           1.9.            0           NULL
9       1.9.        10          1.9.10.         1           NULL
9       1.9.        11          1.9.10.11.      2           1
9       1.9.        12          1.9.10.12.      2           1
9       1.9.        13          1.9.13.         1           NULL
9       1.9.        14          1.9.13.14.      2           NULL
9       1.9.        15          1.9.13.14.15.   3           1
9       1.9.        16          1.9.13.14.16.   3           1
9       1.9.        17          1.9.13.17.      2           NULL
9       1.9.        18          1.9.13.17.18.   3           1
9       1.9.        19          1.9.19.         1           1
9       1.9.        20          1.9.20.         1           NULL

My final result should be a Group By on the first column having last column NOT NULL.
Or the following recursive query :

; WITH Site_R AS (
SELECT s.ID_i
  , sp.Path_v
     , s.ID_i AS SubSiteID_i
  , sp.Path_v AS SubPath_v
  , 0 AS Depth_i
  FROM Site_T s
     , SitePath_T sp
 WHERE sp.SiteID_i = s.ID_i
   AND s.ShortName_v LIKE '[a-y]%'
   AND s.ParentID_i = 1
 UNION ALL
SELECT sr.ID_i
  , sr.Path_v
     , s.ID_i
  , sp.Path_v
  , Depth_i+1
  FROM Site_T s
  , Site_R sr
  , SitePath_T sp
 WHERE sp.SiteID_i = s.ID_i
   AND s.ParentID_i = sr.SubSiteID_i
)
SELECT us.ID_i
  FROM Site_R us
  , UserSiteRight_T usr 
 WHERE usr.SiteID_i = us.SubSiteID_i
   AND UseriD_i = 1
 GROUP BY ID_i

which basically builds the whole tree and selects only the ancestors having SubSiteID_i accessible by UserID_i. Or :

; WITH Site_R AS (
SELECT s.ID_i
     , s.ID_i AS SubSiteID_i
     , 0 AS Depth_i
     , ( SELECT 1 FROM UserSiteRight_T usr WHERE usr.SiteID_i = s.ID_i AND usr.UserID_i = @UserID_i ) AS HasRight_b
  FROM Site_T s
 WHERE s.ShortName_v LIKE '[a-y]%'
   AND s.ParentID_i = @ParentID_i
 UNION ALL
SELECT sr.ID_i
     , s.ID_i
     , Depth_i+1
     , ( SELECT 1 FROM UserSiteRight_T usr WHERE usr.SiteID_i = s.ID_i AND usr.UserID_i = @UserID_i )
  FROM Site_T s
     , Site_R sr
 WHERE s.ParentID_i = sr.SubSiteID_i
   AND ( sr.HasRight_b IS NULL OR Depth_i = 0 )
)
SELECT * FROM Site_R Where HasRight_b IS NOT NULL

Adrian S. 2010-01-12 05:14:26

Last version is the best. Do you have a covering index on Site_T (ParentID_i, ID_i)?

Peter 2010-01-12 12:53:47

Version 1 and 2 have unnecessary joins in the recursive CTE, and unnecessary fields in the SELECT clause. Better to join these tables in post-recurse, and keep the recursion as tight as possible.

Peter 2010-01-12 12:55:03

I've edited my answer.

Peter 2010-01-12 13:50:57

Yes Peter, I do have a covering index on Site_T (ParentID_i, ID_i).Can you please show me the unnecessary join in Version 1 ?

Adrian S. 2010-01-12 15:18:33

SitePath_T. Join it at the very end, post-recurse, post-reduction. It serves no purpose in the CTE, increases the cost of each iteration, and inflates the volume of intermediate data that must be shuffled around. Joining UserSiteRight_T in the CTE *might* be warranted if user rights are selective enough. ie, if UserSiteRight_T reduces the volume of data by 5%, join it once at the end post-recursion. If it reduces the volume of data by 95%, join it on each recursion.

Peter 2010-01-12 16:18:10

Peter, you are right - I should join SitePath_T with the result of the recursive CTE. I was using the SitePath_T just to provide an example and this table will not be used in prod if I will choose this method.

Adrian S. 2010-01-12 18:31:02

In all your examples, you are recursing top down. Why? An index on ParentID_i won't help you there; one on (ID_i, ParentID_i) might offer a minor boost. It might make more sense to recurse bottom up, from child nodes that you know the user has access to, then up to the top. If you absolutely must recurse top down (again, why?), then perhaps you should materialize the results on write rather than recalculate on every read.

Peter 2010-01-12 20:43:52

I already tried recursing down>top, starting with user's accessible nodes and going up the tree following their parents. I also had to add an extra column containing the node depth value so I can avoid getting nodes which already are on a higher branch than the parent, or stop tre recursion if the depth is smaller than the parent depth. But the performance is not to great with this approach ( over a sec ).

Adrian S. 2010-01-14 11:50:22

Answer 4

A:

After reading the above articles ( provided in Update 2 and Update 3 ) I finally understood more about how Sql Server is treating / caching execution plans.

Adding OPTION ( RECOMPILE ) at the end of my SELECT statements will force Sql Server to recalculate the execution plan ( and not using the cached one ) every time the query will be run, thus choosing the best plan matching the variable.

Adrian S. 2010-01-14 12:57:47

ansaurus

tags:

views:

answers:

SQL Server 2005 T-SQL Problem : Can you trust the Query Optimizer ? I know I can't !

But the problem is not the indexing schema.

It's the Query Optimizer !

And I'm happy with this result !

Why the query optimizer is behaving like this ?

How can I enforce the execution plan to be the same as the second fast query ?

UPDATE 1

UPDATE 2

UPDATE 3

related questions