I have a question about performance issue.
select id, .... from table join ... where condition1
union all
select id, .... from table join ... where condition2
order by 1
there should not be any duplicates in the select statements. then the records will be sorted.
procedure:
create procedure proc
...
begin
for select id, .... from table join ... where condition1 into ....
do suspend;
for select id, .... from table join ... where condition2 into ....
do suspend;
end
then I use select * from proc order by id to get sorted records
which one has better performance? based on my test union all seems faster. thanks
select id, .... from table join ... where condition1
union all
select id, .... from table join ... where condition2
order by 1
there should not be any duplicates in the select statements. then the records will be sorted.
procedure:
create procedure proc
...
begin
for select id, .... from table join ... where condition1 into ....
do suspend;
for select id, .... from table join ... where condition2 into ....
do suspend;
end
then I use select * from proc order by id to get sorted records
which one has better performance? based on my test union all seems faster. thanks
If there is no imperative to use a stored procedure (i.e. it is/isn't SOP at your site) then a straight union all should give the better performance all other things being equal and you should use that. If, however, you are for example creating the SQL statement on the fly and the SP is merely parameterized then in theory the stored procedure may well work faster (known, cached, query plan). Given that the two examples you have presented should not be that far apart in performance I'd be inclined to see if either or them produces ib sort files - maybe that provides a significant difference in timings.