CODEWITHSUNDEEP
ruby-on-rails
Tyler DeWitt
Tyler DeWitt

Reputation: 23576

Store Result From DB Query Rails

I've got a legacy database that has a temperamental (at best) connection. The data on this database gets updated maybe once a week. The users of my app just need read access.

The problem I encounter is, occasionally, queries to the DB return nil, so all sorts of bad things happen in my app.

Is there a way I can query the database until I get a valid response, then store that response somewhere in my rails app? That way, the stored version will be returned to my users. Then, maybe once a week, I can re-query the database until it returns a valid object?

To add complications to this, the legacy db is sql server, so I've had to install and use rails-sqlserver, which works pretty well, but might be adding to the problem somehow.

Upvotes: 0

Views: 176

Answers (1)

Veraticus
Veraticus

Reputation: 16064

The problem you'd encounter doing this in the request cycle is that the request that actually fetched the data would probably run glacially slowly (since it would need to request until the result isn't nil, which could potentially take awhile), so your users would hammer the refresh button and just queue more requests until your SQL server or application is inundated.

If I were to do this, I would probably have a Resque task set up to fetch all the data you require (possibly a full dump of the database) every week or every day. Dump the resulting data to a datastore: either your local database, or something like redis or memcached if you don't particularly care about persistence. Because it's asynchronous, you can take as many times as you need to get the data fetch right. On your app side, don't even try to connect to the temperamental database; consider the "middle" database authoritative for all requests. So if the data isn't present there, assume it didn't exist on the SQL server either.

The downside to this method, of course, is that if the SQL server has a very large database, you can't just copy all of it to a more stable middle location. You'd have to choose either a subset of the data or rely on a per-request caching method, as you suggested yourself... but I don't think that's the best way to do this if you can avoid it.

Upvotes: 2

Related Questions