A day without ( learning | laughter ) is a lost day
Farhad Zamani
I'm a .Net Core Back-End developer who love new technologies. On this website I want to share what I have learned with you
When you use Dapper to read information, add information, etc., in the Dapper library, your written query is stored in a ConcurrentDictionary, so that if the same query is run again, the process to run the query again will not run. The maximum query stored in this ConcurrentDictionary is 1000 records, and if more than that is entered in ConcurrentDictionary , ConcurrentDictionary will be reset and all keys will be deleted (Github). The key created for this ConcurrentDictionary is equal to the query you wrote. For example, in the following code, a new Key is added to ConcurrentDictionary for each loop run:
const string connectionString = "Data Source=.; Initial Catalog=DapperTest; Integrated Security=True;";
static async Task Main()
{
using var connection = new SqlConnection(connectionString);
await RunWithoutParameter(connection);
}
private static async Task RunWithoutParameter(SqlConnection connection)
{
for (int i = 0; i < 1000; i++)
{
string username = Guid.NewGuid().ToString();
var query = $@"SELECT Id, Username, Email FROM Users With(NOLOCK)
Where Username = '{username}'";
var result = await connection.QueryAsync<UserInformation>(query);
}
}In the above code, a new Key is added to ConcurrentDictionary each time the loop is executed, which has no advantage. Only the number of keys in ConcurrentDictionary is added. But if we pass the value of the username variable that is passed to SQL as a parameter, only one query is created per 1000 times the loop is executed.
static async Task Main()
{
using var connection = new SqlConnection(connectionString);
await RunWithParameter(connection);
}
private static async Task RunWithParameter(SqlConnection connection)
{
for (int i = 0; i < 1000; i++)
{
string username = Guid.NewGuid().ToString();
var query = $@"SELECT Id, Username, Email FROM Users With(NOLOCK)
Where Username = @username";
var result = await connection.QueryAsync<UserInformation>(query, new { username });
}
}If you look at the keys created in Dapper, a key is created as follows:
SELECT Id, Username, Email FROM Users With(NOLOCK)
Where Username = @usernameBut if you run the first query, for each run a Key is added as follows in ConcurrentDictionary :
SELECT Id, Username, Email FROM Users With(NOLOCK)
Where Username = '0fdfff64-41d5-4321-aebd-8637ae2d3314'
SELECT Id, Username, Email FROM Users With(NOLOCK)
Where Username = '2718efce-d352-4ea9-b25b-9ce5050828b4'
SELECT Id, Username, Email FROM Users With(NOLOCK)
Where Username = '7149faa0-4f77-4d17-9b34-a420efcb4c51'
SELECT Id, Username, Email FROM Users With(NOLOCK)
Where Username = 'f8f4426a-e7c2-49c6-a207-5fff3f656a4e'
SELECT Id, Username, Email FROM Users With(NOLOCK)
Where Username = 'edbddc29-690c-423d-9a8c-bf934eaf7bcb'
SELECT Id, Username, Email FROM Users With(NOLOCK)
Where Username = 'ee9bf534-7f35-4d1f-b172-29458666b178'So it is better to always use the parameter to send variables to SQL using Dapper.
;)
Powered by Froala Editor
What's the reasoning for dapper's 1000 key limit on its query cache? Do lookups in a concurrent dictionary become less performant as more entries are added to it?
Posted at Thursday, May 2, 2024