Re: problem with subqueries

On Sun, 6 Oct 2002 pete@phillipsfamily.freeserve.co.uk wrote:

> Hi guys. Thanks for the rapid replies so far.
>
> To answer some of the questions:
>
> >you did not indicate an explicit join - or even a "from" clause for that
> >matter- in the example of your create view statement.
>
> My original post was a simplified version. Here is the actual view
> creating statement:
>
>     create view monthord as select ord_date, extract (month from ord_date)
>     as month, extract (year from ord_date) as year,r_region,
>     number_of_items from orders,customer where ccode = codenum;
>
>
> >But it appears to me that you are reinventing the wheel.  Isn't this
> >query the equivalent of a grouped aggregation
>
> Yes - but again I was simplifying - I want to run a sub query for each
> region, so I get output like this:
>
> year   month     Reg1    Reg2   Reg3   Reg4
> -----  -----     ----    ----   -----  ----
> 1999   Jan     20    45     10     27
> 1999   Feb     30    43     18     37
> ...
> 2002   Oct     7    89     60     17
>
> The subquery I have tried to run is actually this (there is probably a
> way to do this all in SQL, but at present I would like to just
> understand why my subqueries take so long).

Well, you're running <n> subqueries for each row in monthcustomer
because the distinct happens afterwards in your query.  So if you've
got 4 regions and 1 total and 100,000 rows in monthcustomer, you're
looking at something on the order of 500,000 subqueries.  Doing the
distinct before that step should lower the number to
((#year/month combinations) * (#regions+1)).

In any case, you may be better off with one of:

a) Doing something programatic to turn a result set like:year|month|region|value1999|Jan  |1     |201999|Jan  |2
|45...
into the form you want.  The above can be gotten by group by
probably and would require no subqueries.

b) Keeping a summary table that you update via triggers.  Thisrequires a bit of work to get the triggers, but it
probablymakesthe query faster.