Re: Usage of Views

From: Geoff N. Hiten (SRDBA_at_Careerbuilder.com)
Date: 07/17/04 

Date: Fri, 16 Jul 2004 21:28:25 -0400

Look up SET STATISTICS IO, SET STATISTICS IO an SET STATISTICS TIME
commands. You can use these to break down exactly where your resources are
being consumed to resolve the query. It may not be simple, but this type of
analysis never is. 

-- 
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:FCA13705-7862-48EA-BA69-CA999F25C4C4@microsoft.com...
> 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?
> > > >
> > > >
> >
> >
> >