i am currently creating a sql agent that can query a database , i am going through 2 issues ,
issue 1: i have created a Json file which contains all the tables names and all the columns for each table giving a description for each and the respective joins and i vectorized it using an embedding model and i gave a tool to the agent to be able to query the AI search index to enrich my agent’s context when tackling user input , my issue is its not following all the rules , in some cases it needs to pre aggregate so it doesn’t hit a “fan-out” issue where the values would become inflated , i have added this rule in the Json file as a rule to follow when dealing with the specific tables and in its prompt and it keeps ignoring it even with a temperature = 0 which i thought would force it to adhere to the prompt ,while it is for most of it but that last bit about pre aggregation it never seems to want to follow
issue 2 : the agent never seems to follow the same approach to answer the question , which is expected but in some cases it would go down a path where its so inefficient it takes it more then 4 mins to gather an answer and for the same question it can sometimes take less then a minute .
last question : what is the limitation for a one agent system to tackle such a task ? what is the best approach used for production grade agents of this sort ?
i would really appreciate all the help i could get as i hit a wall and i cant seem to figure it out