An SQL View is just a saved SELECT Statement, which helps to mask the
complexity of SQL Statements.
When executing the view the SELECT-Statement in the view is copied into the
SELECT-Statement to be executed at the point where the view is specified ...
and after the optimization starts.
So, the query optimiter "sees" the complete query incl. the "masked"
SELECT-Statement and then can consider in the optimization everything.
When user defined functions or table functions are used in a SELECT
statement, the UDF and UDTFs are black boxes (at least if they include more
than 1 Statement), that means the optimizer cannot have a look into them and
cannot consider the content.
So optimization when using UDFs and UDTFs may not as "optimal" as when using
views.
... but it depends (like always)!
In this way, if a complex query can be performed in a single statement
creating a view might be better then creating an UDTF. If more than 1
statement is needed to get the result a UDTF might be the best solution.
... stored procedures are just "programs" written in SQL (or an HLL)
language, that can be executed with an SQL CALL Statement.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
Modernization ? Education ? Consulting on IBM i
Database and Software Architect
IBM Champion since 2020
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
"Train people well enough so they can leave, treat them well enough so they
don't want to. " (Richard Branson)
"Learning is experience ? everything else is only information!" (Albert
Einstein)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Jay
Vaughn
Sent: Wednesday, 15 January 2025 18:51
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: db2 views
doesn't the sql code in a view execute during runtime just like sql in a
stored procedures?
any advantage in having in one vs the other when considering performance?
thanks
Jay
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
midrange.com
