I have a performance issue with a few methods in my Service Layer. Methods that are causing me troubles have some logic, but it's db connection that takes over 95% exec time. The problem is unfortunately hardware, the machine is just slow. But, let me explain the software problem first:
The scenario goes like this (simplified):
Company sells cars, there's
Car entity and
PurchaseOrder entity. In the application, there is a
Car view with all
PurchaseOrders. There is a function to determine
PurchaseOrdersStatus. It wouldn't be slow, but it must query the "slow" database a few times. Queries are very simple, like:
FooId exists in
If the car has a few purchase orders - the query is veeery fast. But when the car has hundreds of orders - it's getting very slow. Cause this query must be performed many many times. I came up with the solution:
- Query all
- Store this list of ids in the memory
- In those hundreds of calls I'm checking if the id exists in the memory, which makes it way faster.
My question is, is there a pattern of "prequering" data from database, putting it into memory?
In my data persistence classes, like
CarData I literally have "Initialization" method and member lists with ids. But I'm not sure if this is the right way to do it.
I'm doing one big query in the fast database, it's really fast even for a lot of records. To get "the status" I need to do a few more queries, that are in tables totally not connected to each other, some of which are in very slow db (btw. The db architecture is really terrible, but it's a legacy db, so all I can do is complain). It's possible to do the cross-database one query (built of subqueries, like
... WHERE carId IN (select cardId from ... - but it's really slow.
EDIT2: My proposed solution
In my project I have a few
FooData classes, some of them have corresponding
FooCache class. Cache looks similar to Data, it has DataContext, Collections (cached list of ids or whatever I need) and one method
Initialize. I inject
FooData via IoC (so every time
FooData is created), BUT I don't initialize it. Only in methods where I need caching I call
FooCache.Initialize(); at the beginning.
FooData before going to the database, checks if FooCache is initialized. If it isn't, it goes to the db.