Slow Formatting of Cells DataGridView

[dv2020]

Hi All,

Trying to understand why formatting my datagridview is so slow with the code below.

Is there a more optimize way to perform the below?

 var currentFont = datagridProSpeedMap.DefaultCellStyle.Font;
string speedcol = "Speed";
decimal speedavg = 10;
decimal speedmax = 18;


for (int i = 0; i < datagridProSpeedMap.Rows.Count; i++)
                    {
                        DataGridViewRow row = datagridProSpeedMap.Rows[i];

                        if (Convert.ToDecimal(row.Cells[speedcol].FormattedValue) > speedavg && Convert.ToDecimal(row.Cells[speedcol].FormattedValue) != speedmax)
                        {
                            row.Cells[speedcol].Style.BackColor = cellnuetral;
                            row.Cells[speedcol].Style.ForeColor = Color.Black;
                            row.Cells[speedcol].Style.Font = new Font(currentFont, currentFont.Style | FontStyle.Bold);
                        }
}

I have found, when i remove the line below it speeds up a fair bit, I'm not to sure why this is occuring?
" row.Cells[speedcol].Style.Font = new Font(currentFont, currentFont.Style | FontStyle.Bold);"

 

[Skydiver]

Because you are creating a new font each time that line is executed. Creating a new font involves expending GDI resources. The Windows GDI is slow.

You should create one font outside the loop, and then pass in that font within your loop. Since you are basing your bold font on datagridProSpeedMap.DefaultCellStyle.Font, which shouldn't change dynamically, you should create your bold font at form creation time, or near the time when the data grid view is created, and just keep using that until the form is disposed.

Another source of overhead is this that you are doing this work twice: Convert.ToDecimal(row.Cells[speedcol].FormattedValue). You should do the conversion only once. Better yet, you should be using the the underlying value which should already be a decimal and not even do a conversion.

 

[dv2020]

Because you are creating a new font each time that line is executed. Creating a new font involves expending GDI resources. The Windows GDI is slow.

You should create one font outside the loop, and then pass in that font within your loop. Since you are basing your bold font on datagridProSpeedMap.DefaultCellStyle.Font, which shouldn't change dynamically, you should create your bold font at form creation time, or near the time when the data grid view is created, and just keep using that until the form is disposed.

Another source of overhead is this that you are doing this work twice: Convert.ToDecimal(row.Cells[speedcol].FormattedValue). You should do the conversion only once. Better yet, you should be using the the underlying value which should already be a decimal and not even do a conversion.

Thanks for replying,

I'm trying to only set the value in that specific cell to Bold, and re-use the existing Font and Size? " var currentFont = datagridProSpeedMap.DefaultCellStyle.Font;" is outside the loop?

 

[Skydiver]

Yes, you continue to set it only for specific cells, but you only create the font just once. Something like:

var currentFont = datagridProSpeedMap.DefaultCellStyle.Font;
var boldFont = new Font(currentFont, currentFont.Style | FontStyle.Bold);
:
for( ... )
{
    if ( ... )
    {
        :
        row.Cells[speedcol].Style.Font = boldFont;
    }
}

 

[dv2020]

Yes, you continue to set it only for specific cells, but you only create the font just once. Something like:

var currentFont = datagridProSpeedMap.DefaultCellStyle.Font;
var boldFont = new Font(currentFont, currentFont.Style | FontStyle.Bold);
:
for( ... )
{
    if ( ... )
    {
        :
        row.Cells[speedcol].Style.Font = boldFont;
    }
}

Hello,

thanks for the reply, unfortunately it has made no improvement to the loading speed. If I add the line "row.Cells[speedcol].Style.Font = boldFont;" then loading speed is very slow, when comment out that line, the loading is very fast.

 

[jmcilhinney]

I'm not sure that this would account for too much but it is very poor practice to keep using the same complex expression over and over. How many times do you use row.Cells[speedcol] in that code? How many times row.Cells[speedcol].FormattedValue? How many times row.Cells[speedcol].Style? They should be once each. Evaluate an expression once only, assign the result to a variable and then use the variable multiple times.

 

[jmcilhinney]

As for the issue, how many rows are we talking about? What exactly constitutes "slow" in this case? What happens if you handle the CellFormatting event instead of using a loop?

 

[dv2020]

As for the issue, how many rows are we talking about? What exactly constitutes "slow" in this case? What happens if you handle the CellFormatting event instead of using a loop?

Only 10 rows of data.

If i remove " row.Cells[speedcol].Style.Font = boldFont;", loads in 0.001, add the line it, takes 1.2 seconds...

Have updated the code below.

var currentFont = datagridProSpeedMap.DefaultCellStyle.Font;
var boldFont = new Font(currentFont, currentFont.Style | FontStyle.Bold);
string speedcol = "Speed";
decimal speedavg = 10;
decimal speedmax = 18;


for (int i = 0; i < datagridProSpeedMap.Rows.Count; i++)
                    {
                        DataGridViewRow row = datagridProSpeedMap.Rows[i];
                        cellvalue = Convert.ToDecimal(row.Cells[speedcol].FormattedValue);

                        if (cellvalue  > speedavg && cellvalue  != speedmax)
                        {
                            row.Cells[speedcol].Style.BackColor = cellnuetral;
                            row.Cells[speedcol].Style.ForeColor = Color.Black;
                            row.Cells[speedcol].Style.Font = boldFont;
                        }

 

[Skydiver]

loading speed is very slow

If i remove " row.Cells[speedcol].Style.Font = boldFont;", loads in 0.001, add the line it, takes 1.2 seconds...

Huh? The code you presented at the beginning of this thread already assumes that the data is already loaded into the data grid view. What do you mean by "loading"? Are you reading in one row at a time and then re-running that code you have at the beginning of this thread?

 

[dv2020]

Huh? The code you presented at the beginning of this thread already assumes that t

var currentFont = datagridProSpeedMap.DefaultCellStyle.Font;
var boldFont = new Font(currentFont, currentFont.Style | FontStyle.Bold);
string speedcol = "Speed";
decimal speedavg = 10;
decimal speedmax = 18;


for (int i = 0; i < datagridProSpeedMap.Rows.Count; i++)
                    {
                        DataGridViewRow row = datagridProSpeedMap.Rows[i];
                        cellvalue = Convert.ToDecimal(row.Cells[speedcol].FormattedValue);

                        if (cellvalue  > speedavg && cellvalue  != speedmax)
                        {
                            row.Cells[speedcol].Style.BackColor = cellnuetral;
                            row.Cells[speedcol].Style.ForeColor = Color.Black;
                            row.Cells[speedcol].Style.Font = boldFont;
                        }

he data is already loaded into the data grid view. What do you mean by "loading"? Are you reading in one row at a time and then re-running that code you have at the beginning of this thread?

Hello,

The datagrid is already loaded earlier, only once. Sorry should have made that clear.

The above code runs only when a button is clicked.

I was referring to "loads" as, once the Button is clicked to run the above code, it takes 0.001 seconds to apply without the " row.Cells[speedcol].Style.Font = boldFont;".

If i add the code back in "row.Cells[speedcol].Style.Font = boldFont;", when i click the button it takes 1.2 seconds execute.

 

[Skydiver]

Ah "drawing", or "painting", not "loading".

 

[Skydiver]

I'm not seeing how you are getting into the 1 second zone to just modify 10 cells. I've been running the following on my machine built as a middle of the road PC back in 2012 (it not a screaming game PC), and the longest time I'm getting is 2 ms.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Drawing;
using System.Diagnostics;
using System.Xml;

namespace WinForms
{
    class MainForm : Form
    {
        DataGridView _dgv;

        MainForm()
        {
            var doIt = new Button()
            {
                Text = "Do It",
                AutoSize = true,
                AutoSizeMode = AutoSizeMode.GrowAndShrink,
                Dock = DockStyle.Bottom,
            };
            doIt.Click += (o, e) => Reformat();

            var noFont = new Button()
            {
                Text = "Do It (No Font)",
                AutoSize = true,
                AutoSizeMode = AutoSizeMode.GrowAndShrink,
                Dock = DockStyle.Bottom,
            };
            noFont.Click += (o, e) => ReformatNoFont();

            var doItFaster = new Button()
            {
                Text = "Do It Faster",
                AutoSize = true,
                AutoSizeMode = AutoSizeMode.GrowAndShrink,
                Dock = DockStyle.Bottom,
            };
            doItFaster.Click += (o, e) => ReformatFaster();

            var reset = new Button()
            {
                Text = "Reset",
                AutoSize = true,
                AutoSizeMode = AutoSizeMode.GrowAndShrink,
                Dock = DockStyle.Bottom,
            };
            reset.Click += (o, e) => Reset();

            _dgv = new DataGridView()
            {
                AutoSize = true,
                Dock = DockStyle.Fill,
                DataSource = GenerateData().ToList(),
            };

            Width = 400;
            Height = 600;
            Controls.Add(doIt);
            Controls.Add(noFont);
            Controls.Add(doItFaster);
            Controls.Add(reset);
            Controls.Add(_dgv);
        }

        void Reformat()
        {
            var stopwatch = new Stopwatch();
            stopwatch.Start();
            var currentFont = _dgv.DefaultCellStyle.Font;

            foreach(DataGridViewRow row in _dgv.Rows)
            {
                var cell = row.Cells["Speed"];
                var speed = (int) cell.Value;

                if (speed >= 150 && speed != 300)
                {
                    cell.Style.BackColor = Color.Green;
                    cell.Style.ForeColor = Color.Black;
                    cell.Style.Font = new Font(currentFont, currentFont.Style | FontStyle.Bold);
                }
            }
            stopwatch.Stop();
            Text = $"Elapsed {stopwatch.Elapsed}";
        }

        void ReformatNoFont()
        {
            var stopwatch = new Stopwatch();
            stopwatch.Start();

            foreach (DataGridViewRow row in _dgv.Rows)
            {
                var cell = row.Cells["Speed"];
                var speed = (int)cell.Value;

                if (speed >= 150 && speed != 300)
                {
                    cell.Style.BackColor = Color.Yellow;
                    cell.Style.ForeColor = Color.Black;
                }
            }
            stopwatch.Stop();
            Text = $"Elapsed {stopwatch.Elapsed}";
        }

        void ReformatFaster()
        {
            var stopwatch = new Stopwatch();
            stopwatch.Start();
            var currentFont = _dgv.DefaultCellStyle.Font;
            var boldFont = new Font(currentFont, currentFont.Style | FontStyle.Bold);

            foreach (DataGridViewRow row in _dgv.Rows)
            {
                var cell = row.Cells["Speed"];
                var speed = (int)cell.Value;

                if (speed >= 150 && speed != 300)
                {
                    cell.Style.BackColor = Color.Blue;
                    cell.Style.ForeColor = Color.Black;
                    cell.Style.Font = boldFont;
                }
            }
            stopwatch.Stop();
            Text = $"Elapsed {stopwatch.Elapsed}";
        }

        void Reset()
        {
            var stopwatch = new Stopwatch();
            stopwatch.Start();
            var currentFont = _dgv.DefaultCellStyle.Font;

            foreach (DataGridViewRow row in _dgv.Rows)
            {
                var cell = row.Cells["Speed"];
                var speed = (int)cell.Value;

                if (speed >= 150 && speed != 300)
                {
                    cell.Style.BackColor = Color.White;
                    cell.Style.ForeColor = Color.Black;
                    cell.Style.Font = currentFont;
                }
            }
            stopwatch.Stop();
            Text = $"Elapsed {stopwatch.Elapsed}";
        }

        class Value
        {
            public int Speed { get; set; }
            public int Direction { get; set; }
        }

        IEnumerable<Value> GenerateData()
        {
            for(int i = 0; i < 20; i++)
            {
                yield return new Value { Speed = i * 3 / 2 * 10, Direction = i * 10 };
            }
        }

        [STAThread]
        static void Main()
        {
            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);
            Application.Run(new MainForm());
        }
    }
}

 

[Skydiver]

Functionally same code as above, but with a lot less copy-and-pasting.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows.Forms;
using System.Drawing;
using System.Diagnostics;

namespace WinForms
{
    class MainForm : Form
    {
        DataGridView _dgv;

        MainForm()
        {
            AddButton("New Font", Reformat<NewFont>);
            AddButton("No Font", Reformat<NoFont>);
            AddButton("Shared Font", Reformat<SharedFont>);
            AddButton("Reset", Reformat<Reset>);

            _dgv = new DataGridView()
            {
                AutoSize = true,
                Dock = DockStyle.Fill,
                DataSource = GenerateData().ToList(),
            };

            Width = 400;
            Height = 600;
            Controls.Add(_dgv);
        }

        void AddButton(string text, Action action)
        {
            var button = new Button()
            {
                Text = text,
                AutoSize = true,
                AutoSizeMode = AutoSizeMode.GrowAndShrink,
                Dock = DockStyle.Bottom,
            };
            button.Click += (o, e) => action();

            Controls.Add(button);
        }

        void Reformat<T>() where T : Formatter, new()
        {
            var stopwatch = new Stopwatch();
            stopwatch.Start();
            var formatter = new T();

            formatter.Init(_dgv);
            foreach (DataGridViewRow row in _dgv.Rows)
            {
                var cell = row.Cells["Speed"];
                var speed = (int)cell.Value;

                if (speed >= 150 && speed != 300)
                    formatter.Apply(cell);
            }
            stopwatch.Stop();
            Text = $"Elapsed {stopwatch.Elapsed}";
        }

        abstract class Formatter
        {
            protected Font _currentFont;

            public virtual void Init(DataGridView dgv)
                => _currentFont = dgv.DefaultCellStyle.Font;

            public abstract void Apply(DataGridViewCell cell);
        }

        class NewFont : Formatter
        {
            public override void Apply(DataGridViewCell cell)
            {
                cell.Style.BackColor = Color.Green;
                cell.Style.ForeColor = Color.Black;
                cell.Style.Font = new Font(_currentFont, _currentFont.Style | FontStyle.Bold);
            }
        }

        class Reset : NewFont
        {
            public override void Apply(DataGridViewCell cell)
            {
                cell.Style.BackColor = Color.White;
                cell.Style.ForeColor = Color.Black;
                cell.Style.Font = _currentFont;
            }
        }

        class NoFont : Formatter
        {
            public override void Apply(DataGridViewCell cell)
            {
                cell.Style.BackColor = Color.Yellow;
                cell.Style.ForeColor = Color.Black;
            }
        }

        class SharedFont : Formatter
        {
            Font _boldFont;

            public override void Init(DataGridView dgv)
            {
                base.Init(dgv);
                _boldFont = new Font(_currentFont, _currentFont.Style | FontStyle.Bold);
            }

            public override void Apply(DataGridViewCell cell)
            {
                cell.Style.BackColor = Color.Blue;
                cell.Style.ForeColor = Color.Black;
                cell.Style.Font = _boldFont;
            }
        }

        class Value
        {
            public int Speed { get; set; }
            public int Direction { get; set; }
        }

        IEnumerable<Value> GenerateData()
        {
            for (int i = 0; i < 20; i++)
                yield return new Value { Speed = i * 3 / 2 * 10, Direction = i * 10 };
        }

        [STAThread]
        static void Main()
        {
            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);
            Application.Run(new MainForm());
        }
    }
}