Re: problem with subqueries

On Sun, 6 Oct 2002 pete(at)phillipsfamily(dot)freeserve(dot)co(dot)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|value
1999|Jan |1 |20
1999|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. This
requires a bit of work to get the triggers, but it probably
makes the query faster.