Submitted by: @pavel-zotov

Attachments:
time-of-creation-standalone-functions-vs-pakage-with-same-number-of-funcs.zip

Mentioned below batches do following:

1) make database with page_size = 8K and change its FW to OFF;
2) fill temporary script with either 'create package' (plus 'package body' -- with trivial functions) or 'create function' (standalone, also trivial). Because every batch accept single argument (%1, default = 10), one may to call these batches with such argument and receive script with corresponding number of functions. For example, call with argument = 1500 will produce SQL script with creation 1500 functions (inside package or standalone - depending on what batch is called).
3) run ISQL and measure time of creating DB objects from script.

Batch for creating standalone functions (name = `fnc.bat`):
(it is supposed that ISQL and GFIX can be found in PATH variable)
=== begin of `fnc.bat` ===
@echo off
setlocal enabledelayedexpansion enableextensions
cls

@Rem #⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠ NUMBER OF FUNCTIONS TO CREATE #⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠
set fq=%1

if .%fq%.==.. set fq=10
set /a k=100000 + %fq%
set log=%~n0_!k:~1,5!.log

del %~dp0%~n0.fdb 2>nul
echo create database 'localhost:%~dp0%~n0.fdb' page_size 8192; show version;|isql -q 1>%log% 2>&1
gfix -w async localhost:%~dp0%~n0.fdb
::gstat -h localhost:%~dp0%~n0.fdb

set sql=%~n0.tmp
del %sql% 2>nul
echo set list on;>>%sql%
echo select current_time as "FUNC_DDL_start" from rdb$database;>>%sql%
echo set term ^^;>>%sql%
for /l %%i in (1, 1, %fq%) do (
set /a k=100000 + %%i
echo create function fn_!k:~1,5! returns int as>>%sql%
echo begin>>%sql%
echo return %%i;>>%sql%
echo end^^>>%sql%
)
echo set term ;^^>>%sql%
echo select current_time as "FUNC_DDL_finish" from rdb$database;>>%sql%
echo commit;>>%sql%
echo select current_time as "COMMIT_finish" from rdb$database;>>%sql%
::echo show func;>>%sql%

isql localhost:%~dp0%~n0.fdb -i %sql% 1>%log% 2>&1
del %~dp0%~n0.fdb
del %sql%
=== end of `fnc.bat` ===

Batch for creating PACKAGE (with both header and body) and required number of functions in it (name = `pkg.bat`):

=== begin of `pkg.bat` ===
@echo off
setlocal enabledelayedexpansion enableextensions
cls
set fq=%1
if .%fq%.==.. set fq=10
set /a k=100000 + %fq%
set log=%~n0_!k:~1,5!.log

del %~dp0%~n0.fdb 2>nul
echo create database 'localhost:%~dp0%~n0.fdb' page_size 8192; show version;|isql -q 1>%log%
gfix -w async localhost:%~dp0%~n0.fdb
::gstat -h localhost:%~dp0%~n0.fdb

set sql=%~n0.tmp
del %sql% 2>nul
echo set list on;>>%sql%
echo select current_time as "PKG_HEAD_start" from rdb$database;>>%sql%

echo set term ^^;>>%sql%
echo create package huge as>>%sql%
echo begin>>%sql%
for /l %%i in (1, 1, %fq%) do (
set /a k=100000 + %%i
echo function fn_!k:~1,5! returns int;>>%sql%
)
echo end^^>>%sql%
echo select current_time as "PKG_BODY_finish" from rdb$database^^>>%sql%

echo create package body huge as>>%sql%
echo begin>>%sql%
for /l %%i in (1, 1, %fq%) do (
set /a k=100000 + %%i
echo function fn_!k:~1,5! returns int as>>%sql%
echo begin>>%sql%
echo return %%i;>>%sql%
echo end>>%sql%
)
echo end^^>>%sql%

echo set term ;^^>>%sql%
echo select current_time as "BODY_finish" from rdb$database;>>%sql%

echo commit;>>%sql%
echo select current_time as "COMMIT_finish" from rdb$database;>>%sql%

::echo show package huge;>>%sql%

isql localhost:%~dp0%~n0.fdb -i %sql% 1>%log% 2>&1
del %~dp0%~n0.fdb
del %sql%
=== end of `pkg.bat` ===

When running these batches with different number of functions to be created, one may to watch that package is created much slower (more than 5 times) than standalone functions. Furthermore, increasing this number (especially after 2000) also increase this difference: it seems that time of package creation grows in non-linear manner.

Please see in attached file logs for different number of objects, i.e.: 'fnc_00750.log' & 'pkg_00750.log' -- logs for creation of 750 standalone functions vs package with 750 functions in it.

It will be nice if creation of package with big number of procedures / functions in it will be comparable by its time with similar action for standalone DB objects.

Commits: c272477 FirebirdSQL/fbt-repository@6ebe7cd