I have requirement where the number of inputs are around 1 million records. For each of these records I have to do a SQL lookup against a table which has around 500 million records. Since the table is large, we are not using document cache. For a 1000 records it took around 1 - 2 minutes to execute the map shape. How exactly the sql lookup work?
How much can connection pool setting help in sql lookup function? Is there any other workaround to increase the performance?