Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #730

Re: Explicit IN clause vs JOIN. Plan tanks with Join, Why?

Show all headers | View raw

Hi All,

Thanks for the tips!  Sorry that I didn't post sooner, I didn't have
much access the boards over the weekend.

Rebuilding the stats did not work, nor did the NOT IN work.  I need
all the columns from the view, but based on Erland's idea, started
commenting out columns to see if a column or set of columns was
killing performance.

Here's the (surprising to me) answer:  The view contains ROW_NUMBER()
OVER PARTITION BY (some_column . . . ) where the some_column in the
partition is the same as the some_column in the WHERE clause, which is
the same as the some_column in the JOIN.  When I commented out that
ROW_NUMBER(), the plan  for the JOINed version became radically
better.  The optimizer suggested an index, and the JOIN now performs
very well.

But a reasonable question some may ask me is "why the ROW_NUMBER()
function in the first place?"  I realize that order is not
relationally significant.  However, I am working with a commercial
system, and unfortunately order IS significant for parts of it, and I
can't avoid the issue.  The ROW_NUMBER() takes care of the problem.

I think with a hard coded WHERE clause, the ROW_NUMBER() operator was
limiting itself to just the values listed in the partition.  With the
join, the ROW_NUMBER() was operating on the entire result set, even
though it should have limited to jus the values in some_table.

I decided to do that JOINed query without the ROW_NUMBER() in a CTE,
and then do the ROW_NUMBER() on the result of the CTE.

WITH test_cte AS
(
SELECT
 x
,y
,z
,aa
,bb
,some_column
FROM
some_view
 INNER JOIN
some_table
 ON some_view.some_column = some_table.some_column
)
SELECT
*
,row_number() OVER (PARTITION BY some_column ORDER . . . ) AS
ordinal_column
FROM
test_cte


This version returns results very quickly.  I am happily surprised,
because i thought that optimizer would treat the version with a CTE
the same as the version with join and the ROW_NUMBER() function
sELECTed straight from the view.  Apparently, these are treated
differently.

Thanks,

BIll

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Explicit IN clause vs JOIN.  Plan tanks with Join, Why? bill <billmaclean1@gmail.com> - 2011-09-30 00:24 -0700
  Re: Explicit IN clause vs JOIN.  Plan tanks with Join, Why? "Bob Barrows" <reb01501@NOSPAMyahoo.com> - 2011-09-30 07:45 -0400
  Re: Explicit IN clause vs JOIN.  Plan tanks with Join, Why? Hugo Kornelis <hugo@perFact.REMOVETHIS.info.INVALID> - 2011-09-30 21:27 +0200
  Re: Explicit IN clause vs JOIN. Plan tanks with Join, Why? Erland Sommarskog <esquel@sommarskog.se> - 2011-09-30 23:49 +0200
    Re: Explicit IN clause vs JOIN. Plan tanks with Join, Why? bill <billmaclean1@gmail.com> - 2011-10-03 22:02 -0700
      Re: Explicit IN clause vs JOIN. Plan tanks with Join, Why? Erland Sommarskog <esquel@sommarskog.se> - 2011-10-04 07:22 +0000
        Re: Explicit IN clause vs JOIN. Plan tanks with Join, Why? bill <billmaclean1@gmail.com> - 2011-10-04 23:23 -0700

csiph-web