Connection Pool Issue

Been a long time since I wrote my last Blog. Been busy with present project and also setting up my house with my wife. Aaah! Finally I am back to where I belong:). Now the problem statement.

In my present project, we extract data from PSFT database using SQL. These queries are called by Functoid in the Maps. The number of these functoids is about 40 for each map. We are using OracleClient class to Fill the dataset with the return value. Database connection is closed and opened after every query. Lately the transaction volume has increased and is projected to increase further. This increase in volume is returning "connection pool" error. Because a file can have multiple transactions and each transaction can potentially open 40 connections at a time. We talking about thousands per day.

After some brain storming sessions, we came up with the following solution.

It will be a 3 tier solution.

First layer will be windows service which will create a dataset from all the possible queries. The second layer will be a webservice that will work like a wrapper and pass the resulting XML dataset from webservice to the Orchestration. Orchestration will be third layer that will call the webservice and grab the XML file with the values that will be then mapped using existing map. This way we will able to limit the connection pool from 1 per transaction to 1 per file. Since a file can have anywhere till 1000 transactions. This is a huge reduction.

Below is my "brain dump".

Windows Service:

1) Will do the processing and all the queries reside here.
2) Uses Config file for queries with use of array so that new queries can be added easily
3) Pool at specific intervals
4) Load the data at first initialization and after that just validates it if it is latest.
5) Validation will be done on bases of some flags
6) Once change detected the whole dataset is loaded.
7) Dataset is per site.
8) Can have single window service per site or one for all sites

Web Service:

1) Acts as wrapper to pass the dataset
2) Runs the validation on data set whenever called and based on changes will re-load the dataset using windows service.
3) Will be called from Aggregator using Expression shape.
4) Call will be made per file that coming in to orchestration
5) Every time web service is called it will invoke validation method that will in turn look for “flags”. That way we will avoid loading data all the time when webservice is called.
6) Each call of webservice will do data validation and not data upload. Unless it needs to.
7) This way we open one connection per file and that too 99% of time will load no data. Just do the validation

Orchestration

1) With the present design, use expression shape.
2) Can pass System. XML as parameter
3) If use call orchestration, will have to have 3 different schemas to pass as parameter that can be appended by the Mapping dataset then