Avoiding multiple calls to SQL and persist data

Question

I often received messages objects and for each of them I need to query the database in order to achieve some additional information from one database table. Since we are performing many requests to dabatase that it's very inefficient.

So, how I could improve the performance ? I think I need to persist the table data to a local list or hashtable and update it when some modifications are happened in the database.

Did you actually noticed performance issues? Reading data from one database table shouldn't be slow. — Fabio, Jun 18 '18 at 19:11
@Fabio, the project is on stage of architecture.But it's possible to receive one message per second and I need to make an api request in order to got some additional data for that message. — Mihai Alexandru-Ionut, Jun 18 '18 at 19:17
Don't do premature optimization. If you worry about performance issues - run this part of the code. If you put call to the database behind abstraction, then later when you facing performance issues, you can always change implementation with one which will cache results. Abstraction give you possibility to change implementation without touching other code. — Fabio, Jun 18 '18 at 19:34
Possible duplicate of [Is micro-optimisation important when coding?](https://softwareengineering.stackexchange.com/questions/99445/is-micro-optimisation-important-when-coding) — gnat, Jun 18 '18 at 19:38
@gnat: This isn't micro-optimization. Micro-optimization is when you're worried about how many clock cycles a division operation takes. — Robert Harvey, Jun 18 '18 at 19:40
@Fabio: Within reason. If you're trying to pull live video over the Internet by retrieving each frame individually from a database, some "premature optimization" is probably in order. — Robert Harvey, Jun 18 '18 at 19:42
@MihaiAlexandru-Ionut: There isn't enough information in your question to make it reasonably answerable here. There are many ways to improve performance; which one you use will be highly dependent on your specific scenario. — Robert Harvey, Jun 18 '18 at 19:44
@RobertHarvey, agree. And you will notice that retrieving live video from database frame by frame need some optimization only after you will run application, other way you will not write this kind of code in the first place ;) — Fabio, Jun 18 '18 at 20:34
@Fabio: So you advocate writing the application first, and then rewriting it when you discover that your design is inadequate? — Robert Harvey, Jun 18 '18 at 21:01
@RobertHarvey, Yes, in little different words - First you write application designed based on your current knowledge. Then you will rewrite components which cause performance issues. — Fabio, Jun 18 '18 at 23:09
Your question says you are performing "many requests" but your comments suggest you will be performing one request per second. One request per second is not even remotely enough to cause a performance issue. — Sean Burton, Jun 19 '18 at 12:04
@Fabio: Do you consider the careful selection of data structures based on their performance characteristics "premature optimization?" — Robert Harvey, Jun 19 '18 at 14:27
@SeanBurton, yes, a have one request per second and that means I will need one database request per second which I think will cause a slow-down performance — Mihai Alexandru-Ionut, Jun 19 '18 at 16:03
@RobertHarvey, No, careful selection of data structures will not be "premature optimization". — Fabio, Jun 19 '18 at 19:15
@RobertHarvey, "carefully-considered" design should be based on the facts. Where "I think one database query per second is too slow" not enough for making design decision. That was mine main reason for saying - Don't do premature optimization. If you have requirements, for example, that response time should be less then 200 milliseconds, then you will consider it in your design. — Fabio, Jun 19 '18 at 20:42
You should first look at why you have poor query performance. Done right the DB is the correct place for the data 99% of the time. — paparazzo, Jun 22 '18 at 22:01

score 1 · Accepted Answer · answered Jun 18 '18 at 19:52

1

.net has a MemoryCache object which you can use to persist the results of a query inorder to limit the number of requests.

https://docs.microsoft.com/en-gb/dotnet/api/system.runtime.caching.memorycache?view=netframework-4.7.1

Typically you don't want to persist the object very long, say a few seconds, as this prevents a high volume of requests per second while limiting the problem of having to refresh the cache when the data changes and the amount of memory the cache uses.

There are various advanced options for cache invalidation other than an absolute timeout though. You can set a trigger to invalidate the cache, or update the cached version when you do an update if required.

Obviously this becomes more complicated if you have a farm of servers.

answered Jun 18 '18 at 19:52

Ewan

70,664
5
76
161

Thanks for your answer. I want to persist the list of rows from database table very long time. – Mihai Alexandru-Ionut Jun 19 '18 at 05:31
2

for that we have databases – Ewan Jun 19 '18 at 06:19
and if a need to request database every 1 second ? – Mihai Alexandru-Ionut Jun 19 '18 at 07:03
1

think about the problem, you want to store a lot of data a long time, you have to put it on disc. whats the fastest way to store data on a disc? a database – Ewan Jun 19 '18 at 08:25
@MihaiAlexandru-Ionut I don't understand what makes you think that 1 query per second would be a performance problem. Have you benchmarked the query to see how many you can perform per second? Is it a particularly large query that returns many megabytes of data? If these are relatively average-sized queries I would be surprised if your database couldn't handle *hundreds* of them per second. – Sean Burton Jun 20 '18 at 10:04

Avoiding multiple calls to SQL and persist data

1 Answers1