SQLDatabase very slow

[andreabrgit]

Hello. I'm having a very slow time when instantiating a SQLDatabase object (from llama_index.core import SQLDatabase), because the _metadata.reflect method is called inside it.

self._metadata = metadata or MetaData()
# including view support if view_support = true
self._metadata.reflect(
views=view_support,
bind=self._engine,
only=list(self._usable_tables),
schema = self._schema,
)

Since there is a very large table, with several relationships, it is very slow. Is there a way to make the process faster or even persist the metadata that this object generates so that I don't have to do the process all the time?

[dosubot[bot]]

To improve the performance of the _metadata.reflect method in the SQLDatabase class, especially when dealing with large tables, you can consider the following options:

1. Limit Reflected Tables: Use the include_tables or ignore_tables parameters to limit the number of tables being reflected. This can significantly reduce the time taken if you only need to work with a subset of tables.

   def __init__(
           self,
           engine: Engine,
           schema: Optional[str] = None,
           metadata: Optional[MetaData] = None,
           ignore_tables: Optional[List[str]] = None,
           include_tables: Optional[List[str]] = None,
           ...
       ):
       ...
       if include_tables and ignore_tables:
           raise ValueError("Cannot specify both include_tables and ignore_tables")
   
       self._include_tables = set(include_tables) if include_tables else set()
       self._ignore_tables = set(ignore_tables) if ignore_tables else set()
       ...

2. Persist Metadata: While there is no built-in mechanism in the SQLDatabase class to serialize and deserialize metadata for persistence, you can manually serialize the metadata to a file or database and load it when needed. This would avoid the need to reflect the schema every time.

3. Optimize Reflection: If possible, adjust the parameters of the _metadata.reflect method to optimize the reflection process. For instance, setting views=False if you don't need view support might help.

These strategies can help reduce the overhead of reflecting large database schemas and improve the performance of your application ^[1].

To continue talking to Dosu, mention @dosu.

---

^{Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Bug Report | Other}

[andreabrgit]

Thank you for response.

@dosu

I already set include_tables, in this case I defined only one table. But since the table is very large, in terms of number of fields, constraints, FKs and number of records, the process is very slow to reflect all information.

I would like to suggest the LlamaIndex create a way to persist or cache the reflected information. It could be a good feature to avoid repeating this slow process.

I would also like to suggest that LlamaIndex create new arguments on init of SQLDatabase allow passing parameters like "resolve_fks" that would be used on self._metadata.reflect.