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

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

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

Show all headers | View raw

Hi Erland:

I am not using ROW_NUMBER() in an attempt to order the result set.  I
agree that would be bad.  The problem to which I referred is that the
commercial system depends on the row including an ordinal number.
They shouldn't do that, but they do, so I need to return an ordinal.
ROW_NUMBER() works great to solve that issue.

I'm actually glad that the optimizer can't recast computation order in
this case, because it works much faster with the CTE and the plan
looks good.  WIthout the CTE, the plan is horrible (scans on nearly
every table).

Thanks,

Bill


On Oct 4, 12:22 am, Erland Sommarskog <esq...@sommarskog.se> wrote:
> bill (billmacle...@gmail.com) writes:
> > 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.
>
> What problem? You row_number() because you want number rows in your result
> set, or you use the number to filter later. If you are using row_number()
> with the intention that you will get a certain order in the output, you
> have a bug you need to fix.
>
> > I think with a hard coded WHEREclause, 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.
>
> It is impossible to tell without seeing the query, but adding row_number
> in a CTE can prevent the optimizer from recasting computation order,
> since that would affect the result from row_number().
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Back to comp.databases.ms-sqlserver | Previous | Next — Previous 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