Query cache does not clean and causes memory leak

[denisenko93]

In my case i`m executed dynamic generated queries uses Dapper. In production my service uses a lot of memory (~9 GB). I took a memory dump of the process and saw that dapper saved more than a million requests in the query cache. Based on this dump, I calculated the statistics of the number of objects depending on the hitCount https://gist.github.com/denisenko93/9155653319c43b846ace302d6aa3581c

I suggest using MemoryCache as a query cache and making the cache lifetime a dapper setting. Or make the COLLECT_HIT_COUNT_MIN constant available for user change

[giammin]

do you use query parameters or generate query concatenating strings?

https://github.com/DapperLib/Dapper#limitations-and-caveats

Dapper caches information about every query it runs, this allows it to materialize objects quickly and process parameters quickly. The current implementation caches this information in a ConcurrentDictionary object. Statements that are only used once are routinely flushed from this cache. Still, if you are generating SQL strings on the fly without using parameters it is possible you may hit memory issues.

[denisenko93]

I use both query parameters and string concatenation. Example request:

for (int i = 0; i < parts.Length; i++)
{
    parameters.Add(GetParamName(i), $"%{MySqlHelper.EscapeString(parts[i])}%", DbType.String);
}

sb.Append(" AND ");

sb.AppendLine(string.Join(" AND ", parts.Select((_, i) => $"P.`name` LIKE {GetParamName(i)}")));

[Juandavi1]

Hi ! The same issue here !

[denisenko93]

Hello. Perhaps you should add an option to disable the request cache altogether for cases when the service performs many different ones SQL queries to the database. In my case, I have to add the NoCache flag to each such place. Otherwise, the request cache completely clogs the service's memory