Re: Query Help

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 08/27/04 

Date: Fri, 27 Aug 2004 13:28:08 +0200

On Fri, 27 Aug 2004 09:54:55 +0000 (UTC), Mario Splivalo wrote:

(snip)
>to extract the data from the tables i relay heavy on views.
>But, for some particular querries it turns out to be MUCH faster to do the
>SELECT * INTO #tempTbl FROM vw_someView1, etc... and then do the joins and
>whatever on the #tempTBL and stuff. On the data set that contains like 30k
>rows in master table and around 500k rows in the details table the speed
>improvements are: from around 2 minutes down to 15 seconds.

Hi Mike,

Good to know that. I'll keep it in mind in case I encounter similar
problems with views.

Funny though that the optimizer doesn't consider a plan where the view is
materialized first and then reused multiple times. It looks like an easy
win. I encountered similar situations with derived tables that appeared
multiple times in one query, or when updating multiple columns using ANSI
standard update syntax (ie without FROM clause) - in all these situations,
the subquery was EXACTLY the same (except for the selected column in the
update scenarios), yet the optimizer chose to execute it multiple times.

I'd understand it if I had used different queries, but I think that the
optimizer should be able to compare subqueries used in a query, find which
are equal (possibly excluding the select list in the comparison) and then
consider an execution plan where the subquery gets evaluated only once.

Actually - does anybody know if this behaviour is implemented in (or
planned for) Yukon?

Best, Hugo 

-- 
(Remove _NO_ and _SPAM_ to get my e-mail address)

Relevant Pages

  • Re: Query Help
    ... > multiple times in one query, or when updating multiple columns using ANSI ... yet the optimizer chose to execute it multiple times. ... > consider an execution plan where the subquery gets evaluated only once. ... switch to yukon now. ...
    (microsoft.public.sqlserver.programming)
  • Re: Exists
    ... tested in the subquery and returns that in EXISTS predicate. ... but probably will not in most products with a good optimizer. ... And the next execution of the same query could change the method used, ... based on statistics or a new optimizer or a new access method. ...
    (microsoft.public.sqlserver.programming)
  • Re: Oracle - UNNEST-Hint
    ... >,,The UNNEST hint specifies subquery unnesting. ... Der Optimizer schreibt Dein Statement um. ...
    (de.comp.datenbanken.misc)
  • Re: Make count = 0 where no row is returned.
    ... Correlated subqueries are generally less efficient than joins. ... subquery: for a general correlated subquery, you start from scratch for each ... and finally move down to the next name in the first book. ... to be done by the optimizer if you use subquery, ...
    (microsoft.public.access.queries)