×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

UNION ALL or procedure, which one is faster

UNION ALL or procedure, which one is faster

UNION ALL or procedure, which one is faster

(OP)
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

RE: UNION ALL or procedure, which one is faster

I think it would depend on how you are using it smile 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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close