dpgaspar commented on PR #22413: URL: https://github.com/apache/superset/pull/22413#issuecomment-1354462336
posting some test findings performed locally with examples data: ### Without this PR and without the FAB change: `/api/v1/dashboard/`: Response time avg: ~27ms - Query: ``` SQL SELECT anon_1.created_by_id, anon_1.created_by_first_name, anon_1.created_by_last_name, anon_1.changed_by_id, anon_1.changed_by_first_name, anon_1.changed_by_last_name, anon_1.changed_by_username, anon_1.dashboards_created_on, anon_1.dashboards_changed_on, anon_1.dashboards_id, anon_1.dashboards_dashboard_title, anon_1.dashboards_position_json, anon_1.dashboards_css, anon_1.dashboards_certified_by, anon_1.dashboards_certification_details, anon_1.dashboards_json_metadata, anon_1.dashboards_slug, anon_1.dashboards_published, anon_1.dashboards_is_managed_externally, anon_1.dashboards_created_by_fk, anon_1.dashboards_changed_by_fk, ab_user_1.id, ab_user_1.first_name, ab_user_1.last_name, ab_user_1.username, ab_user_1.email, ab_role_1.id AS id_1, ab_role_1.name FROM (SELECT dashboards.uuid AS dashboards_uuid, dashboards.created_on AS dashboards_created_on, dashboards.changed_on AS dashboards_changed_on, dashboards.id AS dashboards_id, dashboards.dashboard_title AS dashboards_dashboard_title, dashboards.position_json AS dashboards_position_json, dashboards.description AS dashboards_description, dashboards.css AS dashboards_css, dashboards.certified_by AS dashboards_certified_by, dashboards.certification_details AS dashboards_certification_details, dashboards.json_metadata AS dashboards_json_metadata, dashboards.slug AS dashboards_slug, dashboards.published AS dashboards_published, dashboards.is_managed_externally AS dashboards_is_managed_externally, dashboards.external_url AS dashboards_external_url, dashboards.created_by_fk AS dashboards_created_by_fk, dashboards.changed_by_fk AS dashboards_changed_by_fk, changed_by.id AS changed_by_id, changed_by.first_name AS changed_by_first_name, changed_by.last_name AS changed_by_last_name, changed_by.username AS changed_by_username, changed_by.password AS changed_by_password, changed_by.active AS changed_by_active, changed_by.email AS changed_by_email, changed_by.last_login AS changed_by_last_login, changed_by.login_count AS changed_by_login_count, changed_by.fail_login_count AS changed_by_fail_login_count, changed_by.created_on AS changed_by_created_on, changed_by.changed_on AS changed_by_changed_on, changed_by.created_by_fk AS changed_by_created_by_fk, changed_by.changed_by_fk AS changed_by_changed_by_fk, created_by.id AS created_by_id, created_by.first_name AS created_by_first_name, created_by.last_name AS created_by_last_name, created_by.username AS created_by_username, created_by.password AS created_by_password, created_by.active AS created_by_active, created_by.email AS created_by_email, created_by.last_login AS created_by_last_login, created_by.login_count AS created_by_login_count, created_by.fail_login_count AS created_by_fail_login_count, created_by.created_on AS created_by_created_on, created_by.changed_on AS created_by_changed_on, created_by.created_by_fk AS created_by_created_by_fk, created_by.changed_by_fk AS created_by_changed_by_fk FROM dashboards LEFT OUTER JOIN ab_user AS changed_by ON dashboards.changed_by_fk = changed_by.id LEFT OUTER JOIN ab_user AS created_by ON dashboards.created_by_fk = created_by.id ORDER BY dashboards.changed_on DESC LIMIT :param_1) AS anon_1 LEFT OUTER JOIN (dashboard_user AS dashboard_user_1 JOIN ab_user AS ab_user_1 ON ab_user_1.id = dashboard_user_1.user_id) ON anon_1.dashboards_id = dashboard_user_1.dashboard_id LEFT OUTER JOIN (dashboard_roles AS dashboard_roles_1 JOIN ab_role AS ab_role_1 ON ab_role_1.id = dashboard_roles_1.role_id) ON anon_1.dashboards_id = dashboard_roles_1.dashboard_id ORDER BY anon_1.dashboards_changed_on DESC ``` `/api/v1/dataset/1`: Response time avg: ~2300ms - Query: ``` SQL SELECT anon_1.database_id, anon_1.database_database_name, anon_1.created_by_id, anon_1.created_by_first_name, anon_1.created_by_last_name, anon_1.changed_by_id, anon_1.changed_by_first_name, anon_1.changed_by_last_name, anon_1.tables_created_on, anon_1.tables_changed_on, anon_1.tables_id, anon_1.tables_description, anon_1.tables_default_endpoint, anon_1.tables_filter_select_enabled, anon_1.tables_offset, anon_1.tables_cache_timeout, anon_1.tables_table_name, anon_1.tables_main_dttm_col, anon_1.tables_database_id, anon_1.tables_fetch_values_predicate, anon_1.tables_schema, anon_1.tables_sql, anon_1.tables_is_sqllab_view, anon_1.tables_template_params, anon_1.tables_extra, anon_1.tables_created_by_fk, anon_1.tables_changed_by_fk, ab_user_1.id, ab_user_1.first_name, ab_user_1.last_name, ab_user_1.username, table_columns_1.uuid, table_columns_1.created_on, table_columns_1.changed_on, table_columns_1.id AS id_1, table_columns_1.column_name, table_columns_1.verbose_name, table_columns_1.is_active, table_columns_1.type, table_columns_1.advanced_data_type, table_columns_1.groupby, table_columns_1.filterable, table_columns_1.description, table_columns_1.is_dttm, table_columns_1.expression, table_columns_1.python_date_format, table_columns_1.extra, sql_metrics_1.created_on AS created_on_1, sql_metrics_1.changed_on AS changed_on_1, sql_metrics_1.id AS id_2, sql_metrics_1.metric_name, sql_metrics_1.verbose_name AS verbose_name_1, sql_metrics_1.metric_type, sql_metrics_1.description AS description_1, sql_metrics_1.d3format, sql_metrics_1.warning_text, sql_metrics_1.expression AS expression_1, sql_metrics_1.extra AS extra_1 FROM (SELECT tables.uuid AS tables_uuid, tables.created_on AS tables_created_on, tables.changed_on AS tables_changed_on, tables.id AS tables_id, tables.description AS tables_description, tables.default_endpoint AS tables_default_endpoint, tables.is_featured AS tables_is_featured, tables.filter_select_enabled AS tables_filter_select_enabled, tables."offset" AS tables_offset, tables.cache_timeout AS tables_cache_timeout, tables.params AS tables_params, tables.perm AS tables_perm, tables.schema_perm AS tables_schema_perm, tables.is_managed_externally AS tables_is_managed_externally, tables.external_url AS tables_external_url, tables.table_name AS tables_table_name, tables.main_dttm_col AS tables_main_dttm_col, tables.database_id AS tables_database_id, tables.fetch_values_predicate AS tables_fetch_values_predicate, tables.SCHEMA AS tables_schema, tables.sql AS tables_sql, tables.is_sqllab_view AS tables_is_sqllab_view, tables.template_params AS tables_template_params, tables.extra AS tables_extra, tables.created_by_fk AS tables_created_by_fk, tables.changed_by_fk AS tables_changed_by_fk, database.uuid AS database_uuid, database.created_on AS database_created_on, database.changed_on AS database_changed_on, database.id AS database_id, database.verbose_name AS database_verbose_name, database.database_name AS database_database_name, database.sqlalchemy_uri AS database_sqlalchemy_uri, database.password AS database_password, database.cache_timeout AS database_cache_timeout, database.select_as_create_table_as AS database_select_as_create_table_as , database.expose_in_sqllab AS database_expose_in_sqllab, database.configuration_method AS database_configuration_method, database.allow_run_async AS database_allow_run_async, database.allow_file_upload AS database_allow_file_upload, database.allow_ctas AS database_allow_ctas, database.allow_cvas AS database_allow_cvas, database.allow_dml AS database_allow_dml, database.force_ctas_schema AS database_force_ctas_schema, database.extra AS database_extra, database.encrypted_extra AS database_encrypted_extra, database.impersonate_user AS database_impersonate_user, database.server_cert AS database_server_cert, database.is_managed_externally AS database_is_managed_externally, database.external_url AS database_external_url, database.created_by_fk AS database_created_by_fk, database.changed_by_fk AS database_changed_by_fk, created_by.id AS created_by_id, created_by.first_name AS created_by_first_name, created_by.last_name AS created_by_last_name, created_by.username AS created_by_username, created_by.password AS created_by_password, created_by.active AS created_by_active, created_by.email AS created_by_email, created_by.last_login AS created_by_last_login, created_by.login_count AS created_by_login_count, created_by.fail_login_count AS created_by_fail_login_count , created_by.created_on AS created_by_created_on, created_by.changed_on AS created_by_changed_on, created_by.created_by_fk AS created_by_created_by_fk, created_by.changed_by_fk AS created_by_changed_by_fk, changed_by.id AS changed_by_id, changed_by.first_name AS changed_by_first_name, changed_by.last_name AS changed_by_last_name, changed_by.username AS changed_by_username, changed_by.password AS changed_by_password, changed_by.active AS changed_by_active, changed_by.email AS changed_by_email, changed_by.last_login AS changed_by_last_login, changed_by.login_count AS changed_by_login_count, changed_by.fail_login_count AS changed_by_fail_login_count , changed_by.created_on AS changed_by_created_on, changed_by.changed_on AS changed_by_changed_on, changed_by.created_by_fk AS changed_by_created_by_fk, changed_by.changed_by_fk AS changed_by_changed_by_fk FROM tables LEFT OUTER JOIN dbs AS database ON tables.database_id = database.id LEFT OUTER JOIN ab_user AS created_by ON tables.created_by_fk = created_by.id LEFT OUTER JOIN ab_user AS changed_by ON tables.changed_by_fk = changed_by.id WHERE tables.id = :id_3) AS anon_1 LEFT OUTER JOIN (sqlatable_user AS sqlatable_user_1 JOIN ab_user AS ab_user_1 ON ab_user_1.id = sqlatable_user_1.user_id) ON anon_1.tables_id = sqlatable_user_1.table_id LEFT OUTER JOIN table_columns AS table_columns_1 ON anon_1.tables_id = table_columns_1.table_id LEFT OUTER JOIN sql_metrics AS sql_metrics_1 ON anon_1.tables_id = sql_metrics_1.table_id ``` ### Without this PR and with the FAB change: `/api/v1/dashboard/`: Response time avg: ~61ms - Query: ``` SQL SELECT anon_1.created_by_id, anon_1.created_by_first_name, anon_1.created_by_last_name, anon_1.changed_by_id, anon_1.changed_by_first_name, anon_1.changed_by_last_name, anon_1.changed_by_username, anon_1.dashboards_created_on, anon_1.dashboards_changed_on, anon_1.dashboards_id, anon_1.dashboards_dashboard_title, anon_1.dashboards_position_json, anon_1.dashboards_css, anon_1.dashboards_certified_by, anon_1.dashboards_certification_details, anon_1.dashboards_json_metadata, anon_1.dashboards_slug, anon_1.dashboards_published, anon_1.dashboards_is_managed_externally, anon_1.dashboards_created_by_fk, anon_1.dashboards_changed_by_fk FROM (SELECT dashboards.uuid AS dashboards_uuid, dashboards.created_on AS dashboards_created_on, dashboards.changed_on AS dashboards_changed_on, dashboards.id AS dashboards_id, dashboards.dashboard_title AS dashboards_dashboard_title, dashboards.position_json AS dashboards_position_json, dashboards.description AS dashboards_description, dashboards.css AS dashboards_css, dashboards.certified_by AS dashboards_certified_by, dashboards.certification_details AS dashboards_certification_details, dashboards.json_metadata AS dashboards_json_metadata, dashboards.slug AS dashboards_slug, dashboards.published AS dashboards_published, dashboards.is_managed_externally AS dashboards_is_managed_externally, dashboards.external_url AS dashboards_external_url, dashboards.created_by_fk AS dashboards_created_by_fk, dashboards.changed_by_fk AS dashboards_changed_by_fk, changed_by.id AS changed_by_id, changed_by.first_name AS changed_by_first_name, changed_by.last_name AS changed_by_last_name, changed_by.username AS changed_by_username, changed_by.password AS changed_by_password, changed_by.active AS changed_by_active, changed_by.email AS changed_by_email, changed_by.last_login AS changed_by_last_login, changed_by.login_count AS changed_by_login_count, changed_by.fail_login_count AS changed_by_fail_login_count, changed_by.created_on AS changed_by_created_on, changed_by.changed_on AS changed_by_changed_on, changed_by.created_by_fk AS changed_by_created_by_fk, changed_by.changed_by_fk AS changed_by_changed_by_fk, created_by.id AS created_by_id, created_by.first_name AS created_by_first_name, created_by.last_name AS created_by_last_name, created_by.username AS created_by_username, created_by.password AS created_by_password, created_by.active AS created_by_active, created_by.email AS created_by_email, created_by.last_login AS created_by_last_login, created_by.login_count AS created_by_login_count, created_by.fail_login_count AS created_by_fail_login_count, created_by.created_on AS created_by_created_on, created_by.changed_on AS created_by_changed_on, created_by.created_by_fk AS created_by_created_by_fk, created_by.changed_by_fk AS created_by_changed_by_fk FROM dashboards LEFT OUTER JOIN ab_user AS changed_by ON dashboards.changed_by_fk = changed_by.id LEFT OUTER JOIN ab_user AS created_by ON dashboards.created_by_fk = created_by.id ORDER BY dashboards.changed_on DESC LIMIT :param_1) AS anon_1 ORDER BY anon_1.dashboards_changed_on DESC ``` `/api/v1/dataset/1`: Response time avg: ~875ms - Query: ``` SQL SELECT anon_1.database_id, anon_1.database_database_name, anon_1.created_by_id, anon_1.created_by_first_name, anon_1.created_by_last_name, anon_1.changed_by_id, anon_1.changed_by_first_name, anon_1.changed_by_last_name, anon_1.tables_created_on, anon_1.tables_changed_on, anon_1.tables_id, anon_1.tables_description, anon_1.tables_default_endpoint, anon_1.tables_filter_select_enabled, anon_1.tables_offset, anon_1.tables_cache_timeout, anon_1.tables_table_name, anon_1.tables_main_dttm_col, anon_1.tables_database_id, anon_1.tables_fetch_values_predicate, anon_1.tables_schema, anon_1.tables_sql, anon_1.tables_is_sqllab_view, anon_1.tables_template_params, anon_1.tables_extra, anon_1.tables_created_by_fk, anon_1.tables_changed_by_fk FROM (SELECT tables.uuid AS tables_uuid, tables.created_on AS tables_created_on, tables.changed_on AS tables_changed_on, tables.id AS tables_id, tables.description AS tables_description, tables.default_endpoint AS tables_default_endpoint, tables.is_featured AS tables_is_featured, tables.filter_select_enabled AS tables_filter_select_enabled, tables."offset" AS tables_offset, tables.cache_timeout AS tables_cache_timeout, tables.params AS tables_params, tables.perm AS tables_perm, tables.schema_perm AS tables_schema_perm, tables.is_managed_externally AS tables_is_managed_externally, tables.external_url AS tables_external_url, tables.table_name AS tables_table_name, tables.main_dttm_col AS tables_main_dttm_col, tables.database_id AS tables_database_id, tables.fetch_values_predicate AS tables_fetch_values_predicate, tables.SCHEMA AS tables_schema, tables.sql AS tables_sql, tables.is_sqllab_view AS tables_is_sqllab_view, tables.template_params AS tables_template_params, tables.extra AS tables_extra, tables.created_by_fk AS tables_created_by_fk, tables.changed_by_fk AS tables_changed_by_fk, database.uuid AS database_uuid, database.created_on AS database_created_on, database.changed_on AS database_changed_on, database.id AS database_id, database.verbose_name AS database_verbose_name, database.database_name AS database_database_name, database.sqlalchemy_uri AS database_sqlalchemy_uri, database.password AS database_password, database.cache_timeout AS database_cache_timeout, database.select_as_create_table_as AS database_select_as_create_table_as , database.expose_in_sqllab AS database_expose_in_sqllab, database.configuration_method AS database_configuration_method, database.allow_run_async AS database_allow_run_async, database.allow_file_upload AS database_allow_file_upload, database.allow_ctas AS database_allow_ctas, database.allow_cvas AS database_allow_cvas, database.allow_dml AS database_allow_dml, database.force_ctas_schema AS database_force_ctas_schema, database.extra AS database_extra, database.encrypted_extra AS database_encrypted_extra, database.impersonate_user AS database_impersonate_user, database.server_cert AS database_server_cert, database.is_managed_externally AS database_is_managed_externally, database.external_url AS database_external_url, database.created_by_fk AS database_created_by_fk, database.changed_by_fk AS database_changed_by_fk, created_by.id AS created_by_id, created_by.first_name AS created_by_first_name, created_by.last_name AS created_by_last_name, created_by.username AS created_by_username, created_by.password AS created_by_password, created_by.active AS created_by_active, created_by.email AS created_by_email, created_by.last_login AS created_by_last_login, created_by.login_count AS created_by_login_count, created_by.fail_login_count AS created_by_fail_login_count , created_by.created_on AS created_by_created_on, created_by.changed_on AS created_by_changed_on, created_by.created_by_fk AS created_by_created_by_fk, created_by.changed_by_fk AS created_by_changed_by_fk, changed_by.id AS changed_by_id, changed_by.first_name AS changed_by_first_name, changed_by.last_name AS changed_by_last_name, changed_by.username AS changed_by_username, changed_by.password AS changed_by_password, changed_by.active AS changed_by_active, changed_by.email AS changed_by_email, changed_by.last_login AS changed_by_last_login, changed_by.login_count AS changed_by_login_count, changed_by.fail_login_count AS changed_by_fail_login_count , changed_by.created_on AS changed_by_created_on, changed_by.changed_on AS changed_by_changed_on, changed_by.created_by_fk AS changed_by_created_by_fk, changed_by.changed_by_fk AS changed_by_changed_by_fk FROM tables LEFT OUTER JOIN dbs AS database ON tables.database_id = database.id LEFT OUTER JOIN ab_user AS created_by ON tables.created_by_fk = created_by.id LEFT OUTER JOIN ab_user AS changed_by ON tables.changed_by_fk = changed_by.id WHERE tables.id = :id_1) AS anon_1 ``` ### With this PR and with the FAB change: `/api/v1/dashboard/`: Response time avg: ~61ms - Query: Same has without this PR and with the FAB change `/api/v1/dataset/1`: Response time avg: ~610ms - Query: ```SQL SELECT anon_1.database_id, anon_1.database_database_name, anon_1.created_by_id, anon_1.created_by_first_name, anon_1.created_by_last_name, anon_1.changed_by_id, anon_1.changed_by_first_name, anon_1.changed_by_last_name, anon_1.tables_created_on, anon_1.tables_changed_on, anon_1.tables_id, anon_1.tables_description, anon_1.tables_default_endpoint, anon_1.tables_filter_select_enabled, anon_1.tables_offset, anon_1.tables_cache_timeout, anon_1.tables_table_name, anon_1.tables_main_dttm_col, anon_1.tables_database_id, anon_1.tables_fetch_values_predicate, anon_1.tables_schema, anon_1.tables_sql, anon_1.tables_is_sqllab_view, anon_1.tables_template_params, anon_1.tables_extra, anon_1.tables_created_by_fk, anon_1.tables_changed_by_fk FROM (SELECT tables.uuid AS tables_uuid, tables.created_on AS tables_created_on, tables.changed_on AS tables_changed_on, tables.id AS tables_id, tables.description AS tables_description, tables.default_endpoint AS tables_default_endpoint, tables.is_featured AS tables_is_featured, tables.filter_select_enabled AS tables_filter_select_enabled, tables."offset" AS tables_offset, tables.cache_timeout AS tables_cache_timeout, tables.params AS tables_params, tables.perm AS tables_perm, tables.schema_perm AS tables_schema_perm, tables.is_managed_externally AS tables_is_managed_externally, tables.external_url AS tables_external_url, tables.table_name AS tables_table_name, tables.main_dttm_col AS tables_main_dttm_col, tables.database_id AS tables_database_id, tables.fetch_values_predicate AS tables_fetch_values_predicate, tables.SCHEMA AS tables_schema, tables.sql AS tables_sql, tables.is_sqllab_view AS tables_is_sqllab_view, tables.template_params AS tables_template_params, tables.extra AS tables_extra, tables.created_by_fk AS tables_created_by_fk, tables.changed_by_fk AS tables_changed_by_fk, database.uuid AS database_uuid, database.created_on AS database_created_on, database.changed_on AS database_changed_on, database.id AS database_id, database.verbose_name AS database_verbose_name, database.database_name AS database_database_name, database.sqlalchemy_uri AS database_sqlalchemy_uri, database.password AS database_password, database.cache_timeout AS database_cache_timeout, database.select_as_create_table_as AS database_select_as_create_table_as , database.expose_in_sqllab AS database_expose_in_sqllab, database.configuration_method AS database_configuration_method, database.allow_run_async AS database_allow_run_async, database.allow_file_upload AS database_allow_file_upload, database.allow_ctas AS database_allow_ctas, database.allow_cvas AS database_allow_cvas, database.allow_dml AS database_allow_dml, database.force_ctas_schema AS database_force_ctas_schema, database.extra AS database_extra, database.encrypted_extra AS database_encrypted_extra, database.impersonate_user AS database_impersonate_user, database.server_cert AS database_server_cert, database.is_managed_externally AS database_is_managed_externally, database.external_url AS database_external_url, database.created_by_fk AS database_created_by_fk, database.changed_by_fk AS database_changed_by_fk, created_by.id AS created_by_id, created_by.first_name AS created_by_first_name, created_by.last_name AS created_by_last_name, created_by.username AS created_by_username, created_by.password AS created_by_password, created_by.active AS created_by_active, created_by.email AS created_by_email, created_by.last_login AS created_by_last_login, created_by.login_count AS created_by_login_count, created_by.fail_login_count AS created_by_fail_login_count , created_by.created_on AS created_by_created_on, created_by.changed_on AS created_by_changed_on, created_by.created_by_fk AS created_by_created_by_fk, created_by.changed_by_fk AS created_by_changed_by_fk, changed_by.id AS changed_by_id, changed_by.first_name AS changed_by_first_name, changed_by.last_name AS changed_by_last_name, changed_by.username AS changed_by_username, changed_by.password AS changed_by_password, changed_by.active AS changed_by_active, changed_by.email AS changed_by_email, changed_by.last_login AS changed_by_last_login, changed_by.login_count AS changed_by_login_count, changed_by.fail_login_count AS changed_by_fail_login_count , changed_by.created_on AS changed_by_created_on, changed_by.changed_on AS changed_by_changed_on, changed_by.created_by_fk AS changed_by_created_by_fk, changed_by.changed_by_fk AS changed_by_changed_by_fk FROM tables LEFT OUTER JOIN dbs AS database ON tables.database_id = database.id LEFT OUTER JOIN ab_user AS created_by ON tables.created_by_fk = created_by.id LEFT OUTER JOIN ab_user AS changed_by ON tables.changed_by_fk = changed_by.id WHERE tables.id = :id_1) AS anon_1 ``` -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: notifications-unsubscr...@superset.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org --------------------------------------------------------------------- To unsubscribe, e-mail: notifications-unsubscr...@superset.apache.org For additional commands, e-mail: notifications-h...@superset.apache.org