avoid to scan column with include_name

[Steff94190]

Hi,

I have many table and each of them have lot of column and when I run automigration, alembic take a lot of time in include_name to scan all column.

I want to known if it's possible to avoid to scan columns in include_name when we run automigration.

I don't have other idea to speed up this part on process.

thanks,

[zzzeek]

Hi -

There's nothing particularly time consuming in the "include_name" process, autogenerate is not necessarily optimized for lightning speed however under normal circumstances the time spent in "include_name" time is negligible, here's a demo:

from alembic.autogenerate.api import AutogenContext
from unittest.mock import Mock
import timeit


def a_column_filter(name, type_, parent_names):
    return name == "some name"


context_w_no_filters = AutogenContext(Mock(opts={}, as_sql=False), Mock())

context_w_one_filter = AutogenContext(
    Mock(opts={"include_name": a_column_filter}, as_sql=False), Mock()
)


def run_filters(context, colname):
    context.run_name_filters(
        colname,
        "column",
        {"table_name": "some_table", "schema_name": "some_schema"},
    )


print(
    "context with no filters:",
    timeit.timeit(
        "run_filters(context_w_no_filters, 'some name')",
        "from __main__  import run_filters, context_w_no_filters",
        number=1000000,
    )
)

print(
    "context with one filter:",
    timeit.timeit(
        "run_filters(context_w_one_filter, 'some name')",
        "from __main__  import run_filters, context_w_one_filter",
        number=1000000,
    )
)

running the include_name filter one million times without any filters takes .5 seconds, about .6 seconds for one filter present:

$ PYTHONPATH=~/dev/sqlalchemy/lib/ python test3.py 
context with no filters: 0.5202265119878575
context with one filter: 0.5965606899699196

A database that has 5000 tables with 200 columns each , which would be immense and for which the reflection alone would take many minutes with current implementations, has 1M columns, so that's half a second of overhead.

so we'd need to look at what your actual include_name filters are doing and see if that can be improved.

[Steff94190]

Hi Thanks for your reply,

This is my include_name function. I juste add time to see time between execution of object.

SCHEMAS_TO_MIGRATE = ["common_layer", "layer_1", "layer_2"]
TABLES_TO_NOT_MIGRATE = ["l1_tab_master", "mv_tbl__l1_site_tab__0", "mv_tbl__l1_call_action_kpis__0"]

def include_name(name, type_, parent_names):
    tn = datetime.now()
    print(f"[{tn.strftime('%H:%M:%S')}] {type_} - {name}")
    if type_ == "schema":
        return name in SCHEMAS_TO_MIGRATE
    if type_ == "table":
        return name not in TABLES_TO_NOT_MIGRATE
    else:
        return True

bellow the log

[15:15:46] schema - datalake_mb
[15:15:46] schema - datalake_adevinta
[15:15:46] schema - layer_2
[15:15:50] table - l1_ad_action_kpis
[15:15:50] table - l2_daily_vacation_rental_host_kpis
[15:15:50] table - l2_jira_kpis
[15:15:50] table - l2_pro_user_monthly_all_dimensions_kpis_over_5_months
[15:15:50] table - l2_autocharting_monthly_kpis_per_network_per_option_over_9_months
[15:16:27] column - pattern_id

and we see the gab between the last table and first column before filter. For me it very huge.

[zzzeek]

that's unlikely to be your include_name function, it's probably something to do with reflecting the database columns itself. what kind of database is this (oracle?) and how many columns are in this table ? are any other kinds of features in use, like remote database links, check constraints, etc. ?

[Steff94190]

Now I use Redshift database and sqlalchemy-redshift dialect.