Another big delay

Hi again, this is something that i can't understand yet, i mean do
querys thinking in whole sets and not just row by row, look i have to
do this

Table A = 88,400 rows, 3 columns (id,date,minid)
Table B = 7,7887 rows, 2 columns (corpo_id, minid)

the goal is ¿how many distincts minid's are in the table A group by
corpo_id? i mean this for example:

Table A
1, 2008-06-03 00:00:00, 123456789
2, 2008-06-03 00:00:00, 333333333
3, 2008-06-03 00:00:00, 123456789
4, 2008-06-03 00:00:00, 123456789
5, 2008-06-03 00:00:00, 999999999
6, 2008-06-03 00:00:00, 999999999
7, 2008-06-03 00:00:00, 999999999
8, 2008-06-03 00:00:00, 999999999
9, 2008-06-03 00:00:00, 123456789
10, 2008-06-03 00:00:00, 123456789

Table B
baz, 123456789
baz, 333333333
ekt, 999999999

then the result for baz must be: 2
and for ekt: 1

so i did this:

SELECT ****(DISTINCT(A.MINid)) AS 'TTAL_BAZ'
FRM TABLE_A AS A
JIN TABLE_B AS B N A.MINid = B.MIN_id
WHERE B.corpo_id = 'BAZ'

SELECT ****(DISTINCT(A.MINid)) AS 'TTAL_BAZ'
FRM TABLE_A AS A
JIN TABLE_B AS B N A.MINid = B.MIN_id
WHERE B.corpo_id = 'EKT'

my problem is the time of response, this takes more than 23 minutes,
¿how can i do this faster?

Please help this is really urgent, thanks in advanced.

I may not understand your problem properly, but I've always found it best to
use temp tables when the problem is complex and reduce the data sets along
the lines of

#
# firstly get a list of minid's in Table A
#
select distinct(minid) into #a from TableA
#
# next match them to Table B and count them.
#
select corp_id,count(b.minid)
from #a a,TableB b
where a.minid=b.minid

this should have a sub-second run time.


Jorge Reyes wrote:

Hi again, this is something that i can't understand yet, i mean do
querys thinking in whole sets and not just row by row, look i have to
do this
>
Table A = 88,400 rows, 3 columns (id,date,minid)
Table B = 7,7887 rows, 2 columns (corpo_id, minid)
>
the goal is ¿how many distincts minid's are in the table A group by
corpo_id? i mean this for example:
>
Table A
1, 2008-06-03 00:00:00, 123456789
2, 2008-06-03 00:00:00, 333333333
3, 2008-06-03 00:00:00, 123456789
4, 2008-06-03 00:00:00, 123456789
5, 2008-06-03 00:00:00, 999999999
6, 2008-06-03 00:00:00, 999999999
7, 2008-06-03 00:00:00, 999999999
8, 2008-06-03 00:00:00, 999999999
9, 2008-06-03 00:00:00, 123456789
10, 2008-06-03 00:00:00, 123456789
>
Table B
baz, 123456789
baz, 333333333
ekt, 999999999
>
then the result for baz must be: 2
and for ekt: 1
>
so i did this:
>
SELECT ****(DISTINCT(A.MINid)) AS 'TTAL_BAZ'
FRM TABLE_A AS A
JIN TABLE_B AS B N A.MINid = B.MIN_id
WHERE B.corpo_id = 'BAZ'
>
SELECT ****(DISTINCT(A.MINid)) AS 'TTAL_BAZ'
FRM TABLE_A AS A
JIN TABLE_B AS B N A.MINid = B.MIN_id
WHERE B.corpo_id = 'EKT'
>
my problem is the time of response, this takes more than 23 minutes,
¿how can i do this faster?
>
Please help this is really urgent, thanks in advanced.