How to Save DB Hits
As i am working on a scoring engine, the logic is as follows
User configure the rules and define a Query against that rule like
Age > 20 and Age <30 then score 10
select age from application where id = 'xyz'
if there 500 rules then system needs to make 500 DB calls and every time execute defined query. So I want to reduce these DB calls and want to handle it in XML. Can you please suggest how i can proceed and reduce these DB calls.
Application contain multiple applicants data (personal detail, address, employment etc.) and assets and financial purchased data.
I look forward to your cooperation and a prompt response in this regard.
It depends on how many calls you've make with time. And also don't execute plain queries, use stored procedures as much as possible. And also if you could index your page then too many calls not make more performance drops as well.
Looking at your question, I wonder that do you want to store those details in an XML and process. Actually there are lots of advanced XML parsers are available nowadays, or else you can write your own. If your database in a remote location then it may be a good choice to move with XML. Only overhead with remote databases is data passing, processing will anyhow utilize the same.
Where are the rules stored?
How are they defined?
Presumably there's a syntax to them.
Are all these rules run against a single application id? That is, is that SQL the same for each rule? I'm guessing not, but then I don't see quite how this works...or what it is trying to achieve.
Rules stored in Database, Rules are configurable and we build dynamic SQL against each rule.
Rules are executed against each application, each rule has its on SQL. Its a scoring engine to check the credit worthiness of an applicant but i do not want to limiting for this application. In future i can use for some other applications too.
I'll need to think for a bit, as I'm deep in test cases and I really ought to be doing the stuff my client pays me for..:)