Обсуждение: How do I optimize this?

Поиск
Список
Период
Сортировка

How do I optimize this?

От
Wei Weng
Дата:
Hi all.

I have the following scenario:

A table T (int t1; int t2; ... int t10; int tkey)
A table D (int da; int db),

And I have the following query
update T set t1 = b.aa FROM (select COUNT(1) as aa FROM D WHERE D.da = 
<count>) as b where tkey = <value>;
update T set t2 = b.aa FROM (select COUNT(1) as aa FROM D WHERE D.da = 
<count>) as b where tkey = <value>;
...
update T set t10 = b.aa FROM (select COUNT(1) as aa FROM D WHERE D.da = 
<count>) as b where tkey = <value>;

The queries are run on the same <value>. Is there anyway to optimize this???


Thanks
Wei

Re: How do I optimize this?

От
Wei Weng
Дата:
I made a mistake in the queries:

They should be

update T set t1 = b.aa FROM (select COUNT(1) as aa FROM D WHERE D.da = 
1) as b where tkey = <value>;
update T set t2 = b.aa FROM (select COUNT(1) as aa FROM D WHERE D.da = 
2) as b where tkey = <value>;
...
update T set t10 = b.aa FROM (select COUNT(1) as aa FROM D WHERE D.da = 
10) as b where tkey = <value>;

Thanks
Wei

On 03/17/2009 05:43 PM, Wei Weng wrote:
> Hi all.
>
> I have the following scenario:
>
> A table T (int t1; int t2; ... int t10; int tkey)
> A table D (int da; int db),
>
> And I have the following query
> update T set t1 = b.aa FROM (select COUNT(1) as aa FROM D WHERE D.da = 
> <count>) as b where tkey = <value>;
> update T set t2 = b.aa FROM (select COUNT(1) as aa FROM D WHERE D.da = 
> <count>) as b where tkey = <value>;
> ...
> update T set t10 = b.aa FROM (select COUNT(1) as aa FROM D WHERE D.da 
> = <count>) as b where tkey = <value>;
>
> The queries are run on the same <value>. Is there anyway to optimize 
> this???
>
>
> Thanks
> Wei
>
>
>
>

Re: How do I optimize this?

От
Richard Huxton
Дата:
Wei Weng wrote:
> I made a mistake in the queries:
> 
> They should be
> 
> update T set t1 = b.aa FROM (select COUNT(1) as aa FROM D WHERE D.da =
> 1) as b where tkey = <value>;
> update T set t2 = b.aa FROM (select COUNT(1) as aa FROM D WHERE D.da =
> 2) as b where tkey = <value>;
> ...
> update T set t10 = b.aa FROM (select COUNT(1) as aa FROM D WHERE D.da =
> 10) as b where tkey = <value>;

You should be able to generate all the counts from one scan:

UPDATE T set t1 = b.a1, t2 = b.a2 ...
FROM ( SELECT   sum(CASE WHEN D.da=1 THEN 1 ELSE 0 END) AS a1,   sum(CASE WHEN D.da=2 THEN 1 ELSE 0 END) AS a2,   ...
FROMD
 
) AS b
WHERE tkey = <value>

You might also want to look at the crosstab functions in the tablefunc
contrib module (see appendix F of the manuals).

--  Richard Huxton Archonet Ltd