无法从视图创建表时，如何最快从仅含单个视图的数据库中获取DataFrame

Nice question! When you only have read access to a SQL view (and can’t create tables to optimize) and need to pull data into a DataFrame as fast as possible, the key is to minimize data transfer and let the database do the heavy lifting first. Here are my go-to, production-proven approaches:

• Avoid SELECT * — Fetch only needed columns + filter early
  The single biggest win is limiting the data you pull. Views often include columns you don’t need, and filtering rows in the SQL query (instead of after fetching) cuts down on network traffic drastically.
  Example with pandas:

  import pandas as pd
  from sqlalchemy import create_engine
  
  engine = create_engine("your_database_connection_string")
  # Target only required columns and filter rows at the database level
  query = """
      SELECT user_id, transaction_date, total_amount
      FROM your_view
      WHERE transaction_date BETWEEN '2023-01-01' AND '2023-12-31'
        AND total_amount > 100
  """
  df = pd.read_sql(query, engine)
  

• Use chunked/streamed reads for large datasets
  If the view returns millions of rows, loading all data into memory at once is slow (and can cause crashes). Use chunking or streaming to process data in batches:

  # Chunked reading with pandas
  chunk_size = 20_000
  df_list = []
  for chunk in pd.read_sql(query, engine, chunksize=chunk_size):
      df_list.append(chunk)
  final_df = pd.concat(df_list, ignore_index=True)
  
  # Streaming with SQLAlchemy (lower memory footprint)
  with engine.connect() as conn:
      result = conn.execution_options(stream_results=True).execute(query)
      final_df = pd.DataFrame(result.fetchall(), columns=result.keys())
  

• Push computations to the database
  Instead of fetching raw data and aggregating/transforming it in Python, let the database handle those operations. Databases are optimized for SQL operations like grouping, sorting, and aggregating.
  Example: Instead of pulling all transaction data to calculate monthly totals, do it in SQL:

  SELECT DATE_TRUNC('month', transaction_date) AS month,
         SUM(total_amount) AS monthly_total,
         COUNT(*) AS transaction_count
  FROM your_view
  GROUP BY DATE_TRUNC('month', transaction_date)
  ORDER BY month
  

  The resulting DataFrame will be tiny compared to the raw dataset, making the fetch almost instant.

• Optimize your database driver
  Choose a high-performance driver for your database:

  • PostgreSQL: Use psycopg2-binary (faster than the default psycopg2)
  • MySQL: Use mysql-connector-python or pymysql (avoid older drivers like MySQLdb)
  • SQL Server: Use pyodbc with a modern ODBC driver

• Minimize data type conversion overhead
  Ensure SQL returns data types that align with pandas to avoid slow conversions:

  • For date/time columns, return standard SQL date types (not strings) so pandas can parse them directly
  • For numeric columns, avoid storing them as strings in the view (if possible)

Final Tip #

Always profile your query first! Use EXPLAIN in your SQL client to see how the database processes the view query — sometimes views have underlying inefficiencies you can work around by rewriting the query (e.g., avoiding unnecessary joins in the view logic if you don’t need those columns).

内容的提问来源于stack exchange，提问作者kiddbenji