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;
SQL.getState(q, w).Result;completely kills all asynchrony. - BulsonLocalDB. 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