Hi, I have this query:
select x.a, y.b from table1 x join table2 y on x.id = y.id where y.type in ('xxx', 'xxxx', 'xxxxx') and x.c = 'something'
table x is very big, 30 million rows. y.id is the key of table y.
The execution of this query is a few second, but when I iterate through the resultset, it takes me 10 minutes. This is too long. There are 800000 records in the resultset.
If I do it in this way:
select x.a, y.b from table1 x join table2 y on x.id = y.id where y.type in ('xxx') and x.c = 'something'
UNION
select x.a, y.b from table1 x join table2 y on x.id = y.id where y.type in ('xxxx') and x.c = 'something'
UNION
select x.a, y.b from table1 x join table2 y on x.id = y.id where y.type in ('xxxxx') and x.c = 'something'
The exeuciton will take me 5 minutes, and iteration through the resultset is just 1 second.
Why like that?
And any way to minimize both statement execution time and resultset iteration time?
Thanks very much~~!!
Howie