LangChain SQLAgent is taking more time for simple Queries

[SRID45]

Checked other resources

• I added a very descriptive title to this question.
• I searched the LangChain documentation with the integrated search.
• I used the GitHub search to find a similar question and didn't find it.

Commit to Help

• I commit to help with one of those options 👆

Example Code

import oracledb
import os
from dotenv import load_dotenv
from langchain_community.utilities.sql_database import SQLDatabase
from langchain_openai import AzureChatOpenAI
from langchain_community.callbacks.manager import get_openai_callback
from sqlalchemy import create_engine
from langchain_community.agent_toolkits import create_sql_agent
from langchain_core.prompts import ChatPromptTemplate, MessagesPlaceholder


def app_genai(input_data):
    load_dotenv()
    current_schema = 'SCOTT'
    oracledb.init_oracle_client(os.environ["ORACLE_CLIENT_PATH"])
    db_engine = create_engine(os.environ["ORACLE_CONN_STR"], )
    db = SQLDatabase(db_engine, schema=current_schema, lazy_table_reflection=True)

    llm = AzureChatOpenAI(
        openai_api_version=os.environ["AZURE_OPENAI_API_VERSION"],
        azure_deployment=os.environ["AZURE_OPENAI_CHAT_DEPLOYMENT_NAME"], )

    system = """You are an agent designed to interact with a ORACLE database.
    Given an input question, create a syntactically correct ORACLE query to run, then look at the results of the query and return the answer.
    Unless the user specifies a specific number of examples they wish to obtain, always limit your query to at most {top_k} results.
    You can order the results by a relevant column to return the most interesting examples in the database.

    Here is the schema of the tables in the database:
    {schema}

    The schema name for all the tables is SCOTT. Hence append the schema name for every table usage. The above schema information contains the key value pairs where table name is the first key and its value is the another key value pair with columns details
    where column name is the key and its value is another key value pair with column type and key type. The key type indicates whether
    the column is a primary key or foreign key or has an index on it. The NO-KEY indicates that the column is neither primary key nor foreign key.
    The NO-INDEX indicates that the column does not have an index on it. 

    Please utilize the primary key, foreign key, and index columns while performing joins or at where clauses as per necessary
    as it results in good performance of the query.

    The questions related to Employee
    So please utilize these and build the queries and leverage the EMPLOYEE_ID column if available in table

    Never query for all the columns from a specific table, only ask for the relevant columns given the question.
    You have access to tools for interacting with the database.
    Only use the given tools. Only use the information returned by the tools to construct your final answer.
    You MUST double check your query before executing it. If you get an error while executing a query, rewrite the query and try again.

    DO NOT make any DML statements (INSERT, UPDATE, DELETE, DROP etc.) to the database.

    If the question does not seem related to the database, just return "I don't know" as the answer.

    return oracle sql query results in JSON format only

    Here are some examples of user inputs and their corresponding SQL queries:"""

    prompt = ChatPromptTemplate.from_messages(
        [("system", system), MessagesPlaceholder("agent_scratchpad"), ("human", "{input}")]
    )

    agent = create_sql_agent(
        llm=llm,
        db=db,
        prompt=prompt,
        verbose=True,
        max_iterations=10,
        handle_parsing_errors=True,
        agent_type="openai-tools",
    )

    message = input_data.get("message", "")

    with get_openai_callback() as cb:
        result = agent.invoke(
            {"top_k": 5, "schema": "SCOTT", "input": message})
    print("LLM Result: ", result)
    print(cb)

    return result


# LOCAL FUNCTION CALL
import datetime
timestamp = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
print(f"[{timestamp}]")
json_data = {"message": "Get list of employess"}
response = app_genai(json_data)
print(f"[{timestamp}]")
print(response)

Description

we are trying to create oracle chatbot using langchain and SQLAlchemy.

while executing the above api call, its taking more time for query generation and execution.

Could you please suggest me , how to improve the performance of the api call using langchain agents to get the sql results fastly.

please find the below detail output, while calling the API

[2024-07-08 22:18:54]
select sys_context( 'userenv', 'current_schema' ) from dual
select sys_context( 'userenv', 'current_schema' ) from dual
select sys_context( 'userenv', 'current_schema' ) from dual

            begin
               :trans_id := dbms_transaction.local_transaction_id( TRUE );
            end;

SELECT CASE BITAND(t.flag, POWER(2, 28)) WHEN 0 THEN 'READ COMMITTED' ELSE 'SERIALIZABLE' END AS isolation_level FROM v$transaction t WHERE (t.xidusn, t.xidslot, t.xidsqn) = ((:xidusn, :xidslot, :xidsqn))
SELECT value FROM v$parameter WHERE name = 'compatible'
SELECT value FROM v$parameter WHERE name = 'compatible'
SELECT value FROM v$parameter WHERE name = 'compatible'
SELECT 1.1 FROM DUAL
SELECT a_tables.table_name
FROM all_tables a_tables
WHERE (coalesce(a_tables.tablespace_name, :coalesce_1) NOT IN (:coalesce_2_1, :coalesce_2_2)) AND a_tables.owner = :owner_1 AND a_tables.iot_name IS NULL AND a_tables.duration IS NULL MINUS SELECT a_mviews.mview_name AS table_name
FROM all_mviews a_mviews
WHERE a_mviews.owner = :owner_2
SELECT a_tables.table_name
FROM all_tables a_tables
WHERE (coalesce(a_tables.tablespace_name, :coalesce_1) NOT IN (:coalesce_2_1, :coalesce_2_2)) AND a_tables.owner = :owner_1 AND a_tables.iot_name IS NULL AND a_tables.duration IS NULL MINUS SELECT a_mviews.mview_name AS table_name
FROM all_mviews a_mviews
WHERE a_mviews.owner = :owner_2
SELECT a_tables.table_name
FROM all_tables a_tables
WHERE (coalesce(a_tables.tablespace_name, :coalesce_1) NOT IN (:coalesce_2_1, :coalesce_2_2)) AND a_tables.owner = :owner_1 AND a_tables.iot_name IS NULL AND a_tables.duration IS NULL MINUS SELECT a_mviews.mview_name AS table_name
FROM all_mviews a_mviews
WHERE a_mviews.owner = :owner_2

Entering new SQL Agent Executor chain...

Invoking: sql_db_list_tables with {}

Invoking: sql_db_query_checker with {'query': 'SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, DEPARTMENT_ID FROM SCOTT.EMPLOYEES ORDER BY EMPLOYEE_ID FETCH FIRST 5 ROWS ONLY'}

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, DEPARTMENT_ID 
FROM SCOTT.EMPLOYEES 
ORDER BY EMPLOYEE_ID 
FETCH FIRST 5 ROWS ONLY;

Invoking: sql_db_query with {'query': 'SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, DEPARTMENT_ID FROM SCOTT.EMPLOYEES ORDER BY EMPLOYEE_ID FETCH FIRST 5 ROWS ONLY'}

ALTER SESSION SET CURRENT_SCHEMA = SCOTT
ALTER SESSION SET CURRENT_SCHEMA = SCOTT
ALTER SESSION SET CURRENT_SCHEMA = SCOTT

[2024-07-08 22:19:26]

System Info

Package versions:

langchain-community==0.2.1
langchain-core==0.2.3
langchain-openai==0.1.8
SQLAlchemy==2.0.29
oracledb==2.2.1

Platform: Windows 10

Python Version: 3.12

[dosubot[bot]]

Hey @SRID45! I'm here to help you with any bugs, questions, or contributions you might have while you wait for a human maintainer. Let's squash those bugs together! 🤖

To improve the performance of the API call using LangChain agents to get SQL results faster when creating an Oracle chatbot with LangChain and SQLAlchemy, you can use a dynamic few-shot prompt with domain-specific knowledge. This approach helps the model make better queries by inserting relevant queries in the prompt that the model can use as reference. Here is how you can set it up:

1. Create User Input to SQL Query Examples:

   examples = [
       {"input": "List all artists.", "query": "SELECT * FROM Artist;"},
       {
           "input": "Find all albums for the artist 'AC/DC'.",
           "query": "SELECT * FROM Album WHERE ArtistId = (SELECT ArtistId FROM Artist WHERE Name = 'AC/DC');",
       },
       {
           "input": "List all tracks in the 'Rock' genre.",
           "query": "SELECT * FROM Track WHERE GenreId = (SELECT GenreId FROM Genre WHERE Name = 'Rock');",
       },
       {
           "input": "Find the total duration of all tracks.",
           "query": "SELECT SUM(Milliseconds) FROM Track;",
       },
       {
           "input": "List all customers from Canada.",
           "query": "SELECT * FROM Customer WHERE Country = 'Canada';",
       },
       {
           "input": "How many tracks are there in the album with ID 5?",
           "query": "SELECT COUNT(*) FROM Track WHERE AlbumId = 5;",
       },
       {
           "input": "Find the total number of invoices.",
           "query": "SELECT COUNT(*) FROM Invoice;",
       },
       {
           "input": "List all tracks that are longer than 5 minutes.",
           "query": "SELECT * FROM Track WHERE Milliseconds > 300000;",
       },
       {
           "input": "Who are the top 5 customers by total purchase?",
           "query": "SELECT CustomerId, SUM(Total) AS TotalPurchase FROM Invoice GROUP BY CustomerId ORDER BY TotalPurchase DESC LIMIT 5;",
       },
       {
           "input": "Which albums are from the year 2000?",
           "query": "SELECT * FROM Album WHERE strftime('%Y', ReleaseDate) = '2000';",
       },
       {
           "input": "How many employees are there",
           "query": 'SELECT COUNT(*) FROM "Employee"',
       },
   ]

2. Create an Example Selector:

   from langchain_community.vectorstores import FAISS
   from langchain_core.example_selectors import SemanticSimilarityExampleSelector
   from langchain_openai import OpenAIEmbeddings
   
   example_selector = SemanticSimilarityExampleSelector.from_examples(
       examples,
       OpenAIEmbeddings(),
       FAISS,
       k=5,
       input_keys=["input"],
   )

3. Create a FewShotPromptTemplate:

   from langchain_core.prompts import (
       ChatPromptTemplate,
       FewShotPromptTemplate,
       MessagesPlaceholder,
       PromptTemplate,
       SystemMessagePromptTemplate,
   )
   
   system_prefix = """You are an agent designed to interact with a SQL database.
   Given an input question, create a syntactically correct {dialect} query to run, then look at the results of the query and return the answer.
   Unless the user specifies a specific number of examples they wish to obtain, always limit your query to at most {top_k} results.
   You can order the results by a relevant column to return the most interesting examples in the database.
   Never query for all the columns from a specific table, only ask for the relevant columns given the question.
   You have access to tools for interacting with the database.
   Only use the given tools. Only use the information returned by the tools to construct your final answer.
   You MUST double check your query before executing it. If you get an error while executing a query, rewrite the query and try again.
   
   DO NOT make any DML statements (INSERT, UPDATE, DELETE, DROP etc.) to the database.
   
   If the question does not seem related to the database, just return "I don't know" as the answer.
   
   Here are some examples of user inputs and their corresponding SQL queries:"""
   
   few_shot_prompt = FewShotPromptTemplate(
       example_selector=example_selector,
       example_prompt=PromptTemplate.from_template(
           "User input: {input}\nSQL query: {query}"
       ),
       input_variables=["input", "dialect", "top_k"],
       prefix=system_prefix,
       suffix="",
   )

4. Create the Full Prompt:

   full_prompt = ChatPromptTemplate.from_messages(
       [
           SystemMessagePromptTemplate(prompt=few_shot_prompt),
           ("human", "{input}"),
           MessagesPlaceholder("agent_scratchpad"),
       ]
   )

5. Create the Agent with the Custom Prompt:

   from langchain_community.agent_toolkits import SQLDatabaseToolkit, create_sql_agent
   from langchain_community.utilities.sql_database import SQLDatabase
   from langchain_openai import ChatOpenAI
   
   db = SQLDatabase.from_uri("sqlite:///:memory:")
   llm = ChatOpenAI(model="gpt-3.5-turbo", temperature=0)
   toolkit = SQLDatabaseToolkit(db=db, llm=llm)
   
   agent_executor = create_sql_agent(
       llm=llm,
       toolkit=toolkit,
   )

Additionally, consider the following best practices to further optimize performance:

• Minimize Database Connection Permissions: Ensure that your database connection permissions are scoped as narrowly as possible to mitigate risks.
• Use Query Checking: Implement query validation and error handling to ensure that only valid queries are executed.
• Leverage Vector Stores for High-Cardinality Columns: Create a vector store with distinct proper nouns in the database to ensure correct filtering and querying.

By using a dynamic few-shot prompt with domain-specific knowledge and following these best practices, you can optimize the performance of your API calls, making the SQL queries more efficient and relevant, thus speeding up the process of getting SQL results ^[1][2][3][4].

To continue talking to Dosu, mention @dosu.