Обсуждение: How do I optimize this?
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
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 > > > >
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