Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Better performance of creating packages containing many functions [CORE4880] #5175

Closed
firebird-issue-importer opened this issue Jul 21, 2015 · 6 comments

Comments

@firebird-issue-importer

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

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jul 21, 2015

Modified by: @pavel-zotov

Attachment: time-of-creation-standalone-functions-vs-pakage-with-same-number-of-funcs.zip [ 12781 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jul 21, 2015

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Aug 3, 2015

Modified by: @asfernandes

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 3.0 RC 1 [ 10584 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Aug 24, 2015

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: No test => Done successfully

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Aug 24, 2015

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Sep 14, 2015

Modified by: @dyemanov

summary: Increase speed of creating package when number of its functions more than several hundreds => Better performance of creating packages containing many functions

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants