How best can I isolate my application from an unreliable database?

Question

I have a Java SOAP data service which sits on top of a Sybase database which, for reasons out of my control, has unreliable performance. The database is part of a vendor package which has been modified by an internal team and most of the issues are caused by slow response times at certain times of the day.

The SOAP service provides data to a calculation grid and when I request data, I need the response time to be both fast and consistent. The service provides basic CRUD functionality, but the ratio of reads to writes is approximately 100:1.

What is the best strategy to isolate myself from the database's unreliable performance and ensure that the SOAP service is fast and reliable?

Anterior

Is this an internal application? Does another corporate group own the database? – Suggestibility

What do you mean by unreliable? Occasionally slow? Gives errors? Corrupts information? Drops updates? The details would affect what solution is appropriate – Incapable

Answer 1

I have seen this issue a few times, normally with a vendor database.

If this is on Windows, you could create a Windows service as an intermediary between the SOAP service and the database. Then put a message queue (either MSMQ or a JMS implementation such as MQ Series) between the SOAP service and Windows service for asynchronous communications. In this way the database performance issues will no longer affect the SOAP service. This solution does, however, come at the cost of increased complexity.

Note that a .NET web service can be called by, and respond asynchronously to, its clients. I'm not sure if that's possible with a Java SOAP service.

If this is on some flavour of Unix, I assume it has similar functionality to a Windows service - maybe a daemon.

Hearts

Answer 2

Why not use a thread? That way, the application could gently wait even if the database is slow.

Shiksa

Answer 3

RoadWarrior's response is right on. Requests to do any operation get put in a queue. The user comes in once to make the request, and once to pick up the request. This is in fact what is happening on sites like Expedia where it is talking to an unreliable service (the backend). The user's browser is pinging the server until the red light turns green.

Chophouse

Answer 4

How about caching the responses from the web service (either on the client invoking the WS request, or by setting up a proxy web service in between)?

Gun

Answer 5

You could cache the results from the DB if the DB Is not too big.

Glaring

Can you be more specific how to do this? – Anterior

Answer 6

Get the other internal team to tune that database, so everyone using the app benefits. I do love me some indexes!

Suggestibility

What we can control, is pretty well tuned. It is a vendor application however and many of the queries are hard coded in the application and cannot be changed. – Anterior