trjhtr

I'm looking for any possible improvements that could be made to run the mysql piece of code faster.

I created a simple test winform application that creates two docker databases :

• Sqlserver on linux latest




• Mysql 8.0.11

Once the instances are created, it creates a similar table on both. I then use a stored procedure to insert a Category as fast as possible in both setups.

Here's the definition of a category :

public class Category

 public int Id get; set; 
 [System.ComponentModel.DataAnnotations.StringLength(75)]
 public string CategoryName get; set; 
 [System.ComponentModel.DataAnnotations.StringLength(300)]
 public string Description get; set; 
 public DateTime CreationTime get; set; 

The test results are the following:

100k items
MySql Inserted 100000 items in 2955ms
MySql Inserted 100000 items in 2801ms
MySql Inserted 100000 items in 2706ms
MySql Inserted 100000 items in 2512ms
MySql Inserted 100000 items in 2850ms
SqlServer Inserted 100000 items in 1004ms
SqlServer Inserted 100000 items in 902ms
SqlServer Inserted 100000 items in 858ms
SqlServer Inserted 100000 items in 1421ms
SqlServer Inserted 100000 items in 905ms

600k items
MySql Inserted 600000 items in 21849ms
MySql Inserted 600000 items in 17089ms
MySql Inserted 600000 items in 16776ms
SqlServer Inserted 600000 items in 5677ms
SqlServer Inserted 600000 items in 4635ms
SqlServer Inserted 600000 items in 5474ms

Here is the setup for MySql

MySql Stored procedure :

USE `BenchmarkDb`;
DROP procedure IF EXISTS `BenchmarkDb`.`CategoriesInsertWithoutId`;

DELIMITER $$
USE `BenchmarkDb`$$
CREATE DEFINER=`root`@`%` PROCEDURE `CategoriesInsertWithoutId`(IN JsonPayload LONGTEXT)
BEGIN
 insert into BenchmarkDb.Categories
(Category,
Description)
 SELECT tt.CategoryName,tt.Description
FROM
 JSON_TABLE(
 JsonPayload
 ,"$[*]"
 COLUMNS(
 Id int PATH "$.Id",
 CategoryName VARCHAR(75) PATH "$.CategoryName",
 Description VARCHAR(300) PATH "$.Description",
 CreationTime DateTime PATH "$.CreationTime"
 )
 ) AS tt;
 END$$

DELIMITER ;
;

Using the latest 8.0 NPM mysql driver. Sends a large Json string containing all the data. The stored procedure will then turn it into a table and insert from that.

MySql c# code :

Stopwatch stopwatch = new Stopwatch();
string JsonPayload = JsonConvert.SerializeObject(
 TestingDataHelpers.GenerateTestingCategories(100000)
 ,new IsoDateTimeConverter() DateTimeFormat= "yyyy-MM-dd HH:mm:ss" );
stopwatch.Start();

var parameters=new List<MySqlParameter>()
 
 new MySqlParameter()
 
 MySqlDbType=MySqlDbType.LongText,
 ParameterName="JsonPayload",
 Value=JsonPayload
 
 ;
DataSet ResultsDataset = new DataSet();
using (var connection = new MySqlConnection("Server=localhost;Uid=root;Pwd=password1234;"))

 using (var command = connection.CreateCommand())
 
 command.CommandText = "BenchmarkDb.CategoriesInsertWithoutId";
 command.CommandType = CommandType.StoredProcedure;
 if (parameters != null && parameters.Count() > 0)
 
 foreach (var parameter in parameters)
 
 command.Parameters.Add(parameter);
 
 
 using (var dataAdapter = new MySqlDataAdapter(command))
 
 dataAdapter.Fill(ResultsDataset);
 
 

stopwatch.Stop();

Here is the similar SqlServer code that uses Structured datasets and Table-valued parameters to send data to the stored procedure.

SqlServer Stored procedure:

IF EXISTS ( SELECT *
 FROM sys.objects
 WHERE object_id = OBJECT_ID(N'CategoriesInsertWithoutId')
 AND type IN ( N'P', N'PC' ) ) 
 DROP PROCEDURE [dbo].[CategoriesInsertWithoutId]
IF type_id('[dbo].[CategoryType]') IS NOT NULL
 DROP TYPE [dbo].[CategoryType];
CREATE TYPE CategoryType AS TABLE
( Id int,
CategoryName nvarchar(75),
Description nvarchar(300),
CreationTime DateTime); 
CREATE OR ALTER PROCEDURE [dbo].[CategoriesInsertWithoutId]
 @CategoriesToInsert CategoryType READONLY
AS
BEGIN
 SET NOCOUNT ON;
 insert into [dbo].[Categories] (Category,Description)
 select c.CategoryName,c.Description from @CategoriesToInsert c
END

SqlServer c# Code:

Stopwatch stopwatch = new Stopwatch();
var categories = SqlManagerHelpers.ToDataTable(TestingDataHelpers.GenerateTestingCategories(100000));
stopwatch.Start();
var parameters=new List<SqlParameter>()
 
 new SqlParameter()
 
 SqlDbType=SqlDbType.Structured,
 ParameterName="@CategoriesToInsert",
 Value=categories
 
 ;
DataSet ResultsDataset = new DataSet();
using (var connection = new SqlConnection("Data Source=.;User Id=sa;password=password1234;"))

 using (var command = connection.CreateCommand())
 
 command.CommandText = "dbo.CategoriesInsertWithoutId";
 command.CommandType = CommandType.StoredProcedure;
 if (parameters != null && parameters.Count() > 0)
 
 foreach (var parameter in parameters)
 
 command.Parameters.Add(parameter);
 
 
 using (var dataAdapter = new SqlDataAdapter(command))
 
 dataAdapter.Fill(ResultsDataset);
 
 

stopwatch.Stop();

Here are some extra helper classes that are referrenced above.

 static class TestingDataHelpers
 
 static Random rnd = new Random();

 public static List<Category> GenerateTestingCategories(int NumberOfEntriesToMake)
 
 List<Category> categories=new List<Category>();

 for(int i = 0; i < NumberOfEntriesToMake; i++)
 
 categories.Add(new Category()
 
 Id = i,
 CategoryName=CategoryNames[rnd.Next(CategoryNames.Count)],
 Description=CategoryDescriptions[rnd.Next(CategoryDescriptions.Count)],
 CreationTime=DateTime.Now
 );
 
 return categories;
 

 #region CategoryNames
 private static List<string> CategoryNames = new List<string>()
 
"Redacted data is redacted.. enjoy some redacted data",
"Redacted data is redacted.. enjoy some redacted data",
"Redacted data is redacted.. enjoy some redacted data",

 ;
 #endregion
 #region CategoryDescriptions
 private static List<string> CategoryDescriptions = new List<string>()
 
"Redacted data is redacted.. enjoy some redacted data",
"Redacted data is redacted.. enjoy some redacted data",
"Redacted data is redacted.. enjoy some redacted data",
 ;

 #endregion

 
 static class SqlManagerHelpers

 public static DataTable ToDataTable<T>(this IList<T> data)
 
 var props = typeof(T).GetProperties().Where(pi => pi.GetCustomAttributes(typeof(SkipPropertyAttribute), true).Length == 0).ToList();
 DataTable table = new DataTable();
 for(int i =0;i<props.Count;i++)
 
 var prop = props[i];
 table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
 StringLengthAttribute stringLengthAttribute= prop.GetCustomAttributes(typeof(StringLengthAttribute), false).Cast<StringLengthAttribute>().SingleOrDefault();
 if (stringLengthAttribute != null)
 
 table.Columns[i].MaxLength = stringLengthAttribute.MaximumLength;
 
 
 foreach (T item in data)
 
 DataRow row = table.NewRow();
 foreach (var prop in props)
 row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
 table.Rows.Add(row);
 
 return table;
 
 public class SkipPropertyAttribute : Attribute
 
 

Here are the required database schemas

MySql database and table definition

CREATE DATABASE `BenchmarkDb`;
CREATE TABLE `BenchmarkDb`.`Categories` (
 `Id` INT NOT NULL AUTO_INCREMENT,
 `Category` VARCHAR(75) NULL,
 `Description` VARCHAR(300) NULL,
 `CreationTime` DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
 PRIMARY KEY (`id`)) ENGINE=InnoDB;

SqlServer database and table definition

CREATE TABLE [BenchmarkDb].[dbo].[Categories] (
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [Category] [nvarchar](75) NULL,
 [Description] [nvarchar](300) NULL,
 [CreationTime] DATETIME NOT NULL DEFAULT GETDATE(),
 CONSTRAINT [PK_History] PRIMARY KEY CLUSTERED 
 (
 [Id] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
 ) ON [PRIMARY]