I'm developing a Flask application that uses SQLAlchemy (with the pyodbc driver) to execute a stored procedure on the SQL Server (MS SQL Server). The stored procedure dynamically constructs and executes a SQL query based on a provided table name and a CSV string of company names. Here’s the stored procedure:

CREATE PROCEDURE spGetDetailsByCompanyNames
    @table_name VARCHAR(255),
    @names NVARCHAR(MAX)
AS
BEGIN
    DECLARE @SQL AS NVARCHAR(MAX);
    SET @SQL = 'SELECT * FROM [Master].[' + @table_name + '] WHERE [Franchisee Name] IN (' + @names + ');';
    EXEC sp_executesql @SQL;
END;

In my Flask application, I call this procedure using SQLAlchemy’s text() with bound parameters. For example, my function is:

def get_details_by_name(table_name, company_names, dates, db):
    """
    Execute a stored procedure to retrieve details by company names from a specified table.

    Args:
        table_name (str): The name of the table to query.
        company_names (list): List of company names to filter the results.
        Dates (list): List of columns to parse as dates.
        Db: Database engine connection object.

    Returns:
        Pd.DataFrame or None: DataFrame containing the query results, or None if an error occurs.
    """
    try:
        query = text("EXEC spGetDetailsByCompanyNames @table_name=:table_name, @names=:names")
        query = query.bindparams(table_name=table_name, names=company_names)
        
        with db.connect() as conn:
            return pd.read_sql(query, conn, parse_dates=dates)
    except (sqlalchemy.exc.DatabaseError, TypeError) as e:
        logging.error(f"Query execution failed: {e}")
        return None

I prepare the company names using a helper function that converts a list into a CSV string (e.g., "'CompanyA','CompanyB','CompanyC'").

I use Flask-Executor to run multiple calls concurrently (one for each view).

futures = {
                "StoreDetails": executor.submit(fetch_store_details),
                "ProjectDetails": executor.submit(fetch_project_details),
                "ContractDetails": executor.submit(fetch_contract_details),
                "Context": executor.submit(fetch_context),
                #"SalesData": executor.submit(fetch_sales_data),
            }

            # Collect results after all tasks complete
            results = {key: future.result() for key, future in futures.items()}

This is how everything is initialized:

db = SQLAlchemy()

executor = Executor()

def create_app():
    '''
    Create and return a Flask application instance with the necessary configurations loaded from environment variables. 
    The application includes a secret key and a SQL Server connection string built using the configuration values for hostname, 
    database, username, and password. SQLAlchemy is initialized with the app, and the routes are imported. 
    '''
    # Initialize Flask app
    app = Flask(__name__)

    # Load configuration from environment variables
    app.config.from_object(Config)
    
    app.secret_key = app.config["SECRET_KEY"]

    params = urllib.parse.quote_plus(
        "Driver={ODBC Driver 17 for SQL Server};"
        f"Server=tcp:{app.config.get('HOSTNAME')},1433;"
        f"Database={app.config.get('DATABASE')};"
        f"Uid={app.config.get('USERNAME')};"
        f"Pwd={app.config.get('PASSWORD')};"
        "Encrypt=yes;"
        "TrustServerCertificate=yes;"
        "Connection Timeout=240;"
    )

    connection_string = f"mssql+pyodbc:///?odbc_connect={params}"
    app.config['SQLALCHEMY_DATABASE_URI'] = connection_string
    app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
    
    db.init_app(app)
    executor.init_app(app)  # Initialize Flask-Executor

What I'm Seeing: Concurrency works (all calls are fired simultaneously), but the overall speed of fetching the data from the database is slower than I'd like, especially given that the dynamic SQL is used for each table. Sometimes, it is as quick as 1-2 seconds. It often takes 6 seconds, and in some really bad cases, it takes 20+ seconds. (I think this could be due to coworkers running large SQL queries, but it logically shouldn't be the case.)

Question: Does anyone have suggestions for how I can speed up the database fetching? My manager has instructed me not to use caches. I have tried doing simple SELECT * statements instead of the stored procedure and filtering using Pandas, but the times are still roughly the same. This app is deployed on Azure web apps. The speed on the web app mirrors the speed I see when I locally deploy and test. If anyone knows of a way to speed up the process of grabbing the views from the stored procedure and/or making the times less volatile, it would be greatly appreciated. Thank You!