CODEWITHSUNDEEP
spring-mvcweb-applicationstomcat7
JMD
JMD

Reputation: 69

Web Application taking long time to execute

Hi I am writing a web application and it connects to 700 Databases and executes a basic SELECT query.

For example:

There is a button to retrieve Managers of each branch. There are 700 branches of a company and each of the branch details are stored in separate databases. Select query retrieves 1 record from each of the database and returns the Manager of that branch. So executing this code takes a long time. I cannot make the user wait till such time (30 minutes)

Due to memory constraints I cannot use multi threading.

Note: This web application uses Spring MVC. Server Tomcat7.

Any workaround possible?

Upvotes: 0

Views: 463

Answers (1)

user4113344
user4113344

Reputation:

With that many databases to query, the only possible solutions I can see is caching. If real time is not a concern (note that 30 minutes of execution will push you out of real time anyway), then you might explore the following possibilities, all of which require centralizing data into a single, logical or physical database:

  • Clustering: put the database servers in a huge cluster, which is configured for performance hence uses caching internally. Depending upon licence costs, this solution might be too impractical or even too expensive.

  • Push data to a central database: all of the 700 database servers would push the data you need to a central database that your application will use. You can use database servers' replication features (such as in MSSQL or PostgreSQL) or scheduled data transfers. This method requires administrative access to the database servers to either configure replication or drop scripts to run on a scheduled basis.

  • Pull data from a central database host: have a centralized host fetch the required data into a local database, the tables of which are updated through scheduled data transfers. This is the simplest method. Its drawback is that real time querying is impossible.

It is key to transfer only the data you need. Make your select statements as narrow as possible to limit execution time.

The central database could be your web application server or a distinct machine if your resource constraints are tight. I've found PostgreSQL, with little effort, has an excellent compatibility with MSSQL. Without further information it's difficult to be more accurate.

Upvotes: 2

Related Questions