I wrote a class for working with a database from SQL, however, in the course of work, a child process is created from SQL, which loads the processor by 20% and consumes 170 MB of RAM even in idle time.

What's wrong?

static class SQL { public class State { public State(string name, string lastMessage, int messageCount, int messageLetterCount) { this.name = name; this.lastMessage = lastMessage; this.messageCount = messageCount; this.messageLetterCount = messageLetterCount; } public string name, lastMessage; public int messageCount, messageLetterCount; } static string connectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\My Documents\Visual Studio 2017\Project\ARRA_bot_GUI\ARRA_bot_GUI\MessageDatabase.mdf;Integrated Security=True"; public static async Task Insert(string name, string message, string channel) { using (var sqlConnection = new SqlConnection(connectionString)) { await sqlConnection.OpenAsync(); if (!await IsCreatedTable(channel)) await createTeble(channel); int messageCountNew = 0, messageCountLetterNew = 0; SqlCommand command = new SqlCommand($@"update [{channel.ToLower()}] set [LastMessage] = @LastMessage, [MessageCount] = @MessageCount, [MessageLetterCount] = @MessageLetterCount where [Name] = @Name; if @@rowcount = 0 insert into [{channel.ToLower()}] (Name, LastMessage, MessageCount, MessageLetterCount) values (@Name, @LastMessage, @MessageCount, @MessageLetterCount);", sqlConnection); State state = await getState(name, channel); if (state != null) { messageCountNew += state.messageCount; messageCountLetterNew += state.messageLetterCount; } command.Parameters.AddWithValue("Name", name.ToLower()); command.Parameters.AddWithValue("LastMessage", message); command.Parameters.AddWithValue("MessageCount", messageCountNew + 1); command.Parameters.AddWithValue("MessageLetterCount", messageCountLetterNew + message.Length); await command.ExecuteNonQueryAsync(); } } public static async Task<State> getState(string name, string channel) { using (var sqlConnection = new SqlConnection(connectionString)) { await sqlConnection.OpenAsync(); if (!await IsCreatedTable(channel)) return null; SqlCommand command = new SqlCommand($"SELECT * FROM [{channel.ToLower()}] WHERE [Name] = @Name", sqlConnection); command.Parameters.AddWithValue("Name", name.ToLower()); using (SqlDataReader dataReader = await command.ExecuteReaderAsync()) { State state = null; try { dataReader.Read(); state = new State(name, Convert.ToString(dataReader["LastMessage"]), Convert.ToInt32(dataReader["MessageCount"]), Convert.ToInt32(dataReader["MessageLetterCount"])); } catch (Exception) { } return state; } } } private static async Task<bool> IsCreatedTable(string name) { using (var sqlConnection = new SqlConnection(connectionString)) { await sqlConnection.OpenAsync(); SqlCommand command = new SqlCommand($"SELECT count(*)FROM information_schema.TABLES WHERE (TABLE_NAME = '{name.ToLower()}')", sqlConnection); var result = await command.ExecuteScalarAsync(); return (int)result == 1; } } private static async Task createTeble(string channel) { using (var sqlConnection = new SqlConnection(connectionString)) { await sqlConnection.OpenAsync(); SqlCommand command = new SqlCommand($@"CREATE TABLE [dbo].[{channel.ToLower()}] ([Name] NVARCHAR(50) NOT NULL, [LastMessage] NVARCHAR(MAX) NOT NULL, [MessageCount] INT NOT NULL, [MessageLetterCount] INT NOT NULL)", sqlConnection); await command.ExecuteNonQueryAsync(); } } } 

If anything, I appeal to him like this:

 SQL.Insert(q, w, e).GetAwaiter(); SQL.getState(q, w).Result; 
  • one
    Here is this thing: SQL.getState(q, w).Result; completely kills all asynchrony. - Bulson
  • Regarding the child process: everything is correct. You are using LocalDB . It must be run to work, it happens automatically in a separate process. Memory consumption is quite normal, and even small for the SQL engine. Regarding the load on the processor, you need to look at how often you access it and what kind of requests you are doing. In general, I don’t see much crime, but without a profiler it’s hard to say something more meaningful. - rdorn
  • Alternatively, try using SQL Express, it may be better optimized older. - rdorn

0