Re: Usage of Views

From: payyans (payyans_at_discussions.microsoft.com)
Date: 07/16/04 

Date: Fri, 16 Jul 2004 08:28:02 -0700

When I created intermediary tables my process got completed in 10hours for 5million records, but, when changed to views it took 20hours. Can you please suggest any way to understand the details about the optimized query?

"Geoff N. Hiten" wrote:

> SQL should get this right. Those are treated same as derived columns and
> layering of views corresponds to a forced order of operations. I once wrote
> a commission calculation system using a lot of intermediate views, mainly to
> deal with grouping issues, and I never got a bogus calculation result.
>
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
>
> I support the Professional Association for SQL Server
> www.sqlpass.org
>
> "payyans" <payyans@discussions.microsoft.com> wrote in message
> news:B48C5A8F-DB2A-4DDF-A239-E4A2630CF8FF@microsoft.com...
> > Thanks for the info. Each view performs different complex arithmetic
> operations. Would there be any chance of optimizer applying the operations
> on wrong set of intermediary data?
> >
> > "payyans" wrote:
> >
> > > For a data analysis project, to avoid the creation of so many
> intermediary tables, I chose to CREATE VIEWS dynamically. Each VIEW becomes
> input to another CREATE VIEW. I have such 10 levels of Views. The execution
> plan of SELECT statement of the final view does not show any details of
> intermediary views.
> > >
> > > My question is that does SQL Server 2000 Enterprise Edition, optimize
> and generate the output for each VIEW in the order it was created, or does
> the optimizer re-write the entire query as a single query to create the
> final desired output?
> > >
> > >
>
>
>

Relevant Pages

  • Re: Help with using Alias Fields in a expressions.
    ... Interesting...but it seems that the query is optimized quite well. ... query optimizer seems quite smart in this case. ... sub-quires like I did does run very well in JET, and also sql server. ...
    (microsoft.public.sqlserver.server)
  • Re: Setup index
    ... nonclustered index to retrieve data is often the best answer when there is a ... What you are seeing is the normally good behaviour of the optimizer. ... Wayne Snyder, MCDBA, SQL Server MVP ... if I change the query to: ...
    (microsoft.public.sqlserver.server)
  • Re: collation for multiple language in a column
    ... The query returns 100 rows, and without the collate clause, it would have ... COLLATE clause, SQL Server needs to add a Sort operator to the plan. ... is such that the TOP 100 restriction makes it very easy for the optimizer ...
    (microsoft.public.sqlserver.server)
  • Re: index views ?
    ... You can create indexed view in SQL Server 2000, ... With Enterprise Edition the Query ... Optimizer will look to use indexed views even if the view isn't directly ...
    (microsoft.public.sqlserver.datawarehouse)
  • Re: sql query help join
    ... You want an MDX query instead? ... The desired output, given the sample. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)