performance: large number of SQL queries for one ping request

[tiborsimko]

Doing a reana-client ping leads currently to 12 SQL queries related to user tokens and resources, see below. This seems to be too much. We should step through the ping query, check the DB connection handling there, check whether some info cannot be reused, etc, in order to bring the number of DB queries down to a reasonable minimum.

P.S. Obtained using engine = create_engine(SQLALCHEMY_DATABASE_URI, echo=True in reana-db/database.py.

$ kubectl logs reana-server-6f8dbd9dfb-gw52m rest-api | grep ^INFO
INFO  [sqlalchemy.engine.base.Engine] select version()
INFO  [sqlalchemy.engine.base.Engine] {}
INFO  [sqlalchemy.engine.base.Engine] select current_schema()
INFO  [sqlalchemy.engine.base.Engine] {}
INFO  [sqlalchemy.engine.base.Engine] SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
INFO  [sqlalchemy.engine.base.Engine] {}
INFO  [sqlalchemy.engine.base.Engine] SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
INFO  [sqlalchemy.engine.base.Engine] {}
INFO  [sqlalchemy.engine.base.Engine] show standard_conforming_strings
INFO  [sqlalchemy.engine.base.Engine] {}
INFO  [sqlalchemy.engine.base.Engine] BEGIN (implicit)
INFO  [sqlalchemy.engine.base.Engine] SELECT __reana.user_.created AS __reana_user__created, __reana.user_.updated AS __reana_user__updated, __reana.user_.id_ AS __reana_user__id_, __reana.user_.email AS __reana_user__email, __reana.user_.full_name AS __reana_user__full_name, __reana.user_.username AS __reana_user__username 
INFO  [sqlalchemy.engine.base.Engine] {'token_1': <psycopg2.extensions.Binary object at 0x7f46c1b3ca80>, 'type_1': 'reana'}
INFO  [sqlalchemy.engine.base.Engine] SELECT __reana.user_token.created AS __reana_user_token_created, __reana.user_token.updated AS __reana_user_token_updated, __reana.user_token.id_ AS __reana_user_token_id_, __reana.user_token.token AS __reana_user_token_token, __reana.user_token.status AS __reana_user_token_status, __reana.user_token.user_id AS __reana_user_token_user_id, __reana.user_token.type_ AS __reana_user_token_type_ 
INFO  [sqlalchemy.engine.base.Engine] {'param_1': UUID('00000000-0000-0000-0000-000000000000'), 'type_1': 'reana', 'param_2': 1}
INFO  [sqlalchemy.engine.base.Engine] SELECT __reana.user_token.created AS __reana_user_token_created, __reana.user_token.updated AS __reana_user_token_updated, __reana.user_token.id_ AS __reana_user_token_id_, __reana.user_token.token AS __reana_user_token_token, __reana.user_token.status AS __reana_user_token_status, __reana.user_token.user_id AS __reana_user_token_user_id, __reana.user_token.type_ AS __reana_user_token_type_ 
INFO  [sqlalchemy.engine.base.Engine] {'param_1': UUID('00000000-0000-0000-0000-000000000000'), 'type_1': 'reana', 'param_2': 1}
INFO  [sqlalchemy.engine.base.Engine] SELECT __reana.user_token.created AS __reana_user_token_created, __reana.user_token.updated AS __reana_user_token_updated, __reana.user_token.id_ AS __reana_user_token_id_, __reana.user_token.token AS __reana_user_token_token, __reana.user_token.status AS __reana_user_token_status, __reana.user_token.user_id AS __reana_user_token_user_id, __reana.user_token.type_ AS __reana_user_token_type_ 
INFO  [sqlalchemy.engine.base.Engine] {'param_1': UUID('00000000-0000-0000-0000-000000000000'), 'status_1': 'active', 'type_1': 'reana'}
INFO  [sqlalchemy.engine.base.Engine] SELECT __reana.user_token.created AS __reana_user_token_created, __reana.user_token.updated AS __reana_user_token_updated, __reana.user_token.id_ AS __reana_user_token_id_, __reana.user_token.token AS __reana_user_token_token, __reana.user_token.status AS __reana_user_token_status, __reana.user_token.user_id AS __reana_user_token_user_id, __reana.user_token.type_ AS __reana_user_token_type_ 
INFO  [sqlalchemy.engine.base.Engine] {'param_1': UUID('00000000-0000-0000-0000-000000000000'), 'status_1': 'active', 'type_1': 'reana'}
INFO  [sqlalchemy.engine.base.Engine] SELECT __reana.user_token.created AS __reana_user_token_created, __reana.user_token.updated AS __reana_user_token_updated, __reana.user_token.id_ AS __reana_user_token_id_, __reana.user_token.token AS __reana_user_token_token, __reana.user_token.status AS __reana_user_token_status, __reana.user_token.user_id AS __reana_user_token_user_id, __reana.user_token.type_ AS __reana_user_token_type_ 
INFO  [sqlalchemy.engine.base.Engine] {'param_1': UUID('00000000-0000-0000-0000-000000000000'), 'type_1': 'reana', 'param_2': 1}
INFO  [sqlalchemy.engine.base.Engine] SELECT __reana.user_token.created AS __reana_user_token_created, __reana.user_token.updated AS __reana_user_token_updated, __reana.user_token.id_ AS __reana_user_token_id_, __reana.user_token.token AS __reana_user_token_token, __reana.user_token.status AS __reana_user_token_status, __reana.user_token.user_id AS __reana_user_token_user_id, __reana.user_token.type_ AS __reana_user_token_type_ 
INFO  [sqlalchemy.engine.base.Engine] {'param_1': UUID('00000000-0000-0000-0000-000000000000'), 'type_1': 'reana', 'param_2': 1}
INFO  [sqlalchemy.engine.base.Engine] SELECT __reana.user_token.created AS __reana_user_token_created, __reana.user_token.updated AS __reana_user_token_updated, __reana.user_token.id_ AS __reana_user_token_id_, __reana.user_token.token AS __reana_user_token_token, __reana.user_token.status AS __reana_user_token_status, __reana.user_token.user_id AS __reana_user_token_user_id, __reana.user_token.type_ AS __reana_user_token_type_ 
INFO  [sqlalchemy.engine.base.Engine] {'param_1': UUID('00000000-0000-0000-0000-000000000000'), 'type_1': 'reana', 'param_2': 1}
INFO  [sqlalchemy.engine.base.Engine] SELECT __reana.user_token.created AS __reana_user_token_created, __reana.user_token.updated AS __reana_user_token_updated, __reana.user_token.id_ AS __reana_user_token_id_, __reana.user_token.token AS __reana_user_token_token, __reana.user_token.status AS __reana_user_token_status, __reana.user_token.user_id AS __reana_user_token_user_id, __reana.user_token.type_ AS __reana_user_token_type_ 
INFO  [sqlalchemy.engine.base.Engine] {'param_1': UUID('00000000-0000-0000-0000-000000000000'), 'type_1': 'reana', 'param_2': 1}
INFO  [sqlalchemy.engine.base.Engine] SELECT __reana.user_resource.created AS __reana_user_resource_created, __reana.user_resource.updated AS __reana_user_resource_updated, __reana.user_resource.user_id AS __reana_user_resource_user_id, __reana.user_resource.resource_id AS __reana_user_resource_resource_id, __reana.user_resource.quota_limit AS __reana_user_resource_quota_limit, __reana.user_resource.quota_used AS __reana_user_resource_quota_used 
INFO  [sqlalchemy.engine.base.Engine] {'param_1': UUID('00000000-0000-0000-0000-000000000000')}
INFO  [sqlalchemy.engine.base.Engine] SELECT __reana.resource.created AS __reana_resource_created, __reana.resource.updated AS __reana_resource_updated, __reana.resource.id_ AS __reana_resource_id_, __reana.resource.name AS __reana_resource_name, __reana.resource.type_ AS __reana_resource_type_, __reana.resource.unit AS __reana_resource_unit, __reana.resource.title AS __reana_resource_title 
INFO  [sqlalchemy.engine.base.Engine] {'param_1': UUID('c0323d27-5bca-4b89-bab2-1daaa288ea42')}
INFO  [sqlalchemy.engine.base.Engine] SELECT __reana.resource.created AS __reana_resource_created, __reana.resource.updated AS __reana_resource_updated, __reana.resource.id_ AS __reana_resource_id_, __reana.resource.name AS __reana_resource_name, __reana.resource.type_ AS __reana_resource_type_, __reana.resource.unit AS __reana_resource_unit, __reana.resource.title AS __reana_resource_title 
INFO  [sqlalchemy.engine.base.Engine] {'param_1': UUID('52221086-9f91-4c61-a02f-6b2cb67dae10')}
INFO  [sqlalchemy.engine.base.Engine] ROLLBACK