Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Grid control in SSMS

I notice in SSMS (SQL Server Management Studio 2016), the query results return within a blink of a second (above 10k+ rows). The result table/grid scroll perfectly smooth, and have an extremely low memory footprint (~80MB) on SSMS. This grid/view-like control way out perform either ListView (~200MB, 2-3 seconds) and DataGrid (~600MB, 8-10 seconds). Even if I turn off all visualization or tweak cancententscroll or fix its height to optimize the speed, they still perform far behind the grid in SSMS, still with sluggish scrolling and GUI operation.

What is behind the grid control used in SSMS that make it so smooth?

enter image description here

like image 425
KMC Avatar asked Jun 05 '17 02:06

KMC


People also ask

What does Alt F1 do in SQL Server?

ALT + F1 (Select any stored procedure on query editor and press ALT + F1) : It runs the sp_help system stored procedure. CTRL + 1: In the same way, it runs the sp_who system stored procedure. It will provide you the details like who created the SP, spid, host name, on which DB the SP was created and so on.

What is Ctrl L in SQL Server?

Typing CTRL+L executes either the selected text in the Query Editor window (or all the statements in the window if nothing is selected) and displays the execution plan.

What is SQL grid?

Use the GRID clause to specify the scope of a cross-server query whose data sources are tables of database servers that are nodes of a grid. The GRID clause is not valid unless the session is connected to a database within an existing grid.


1 Answers

SSMS grid is not C++, it's not a ListView nor a DataGrid, it does not uses Windows native controls, it's "just" a custom .NET control named GridControl (in a Microsoft.SqlServer.Management.UI.Grid namespace) that belongs to an assembly named Microsoft.SqlServer.GridControl.dll.

You can find it in various places: in the GAC, in %ProgramFiles(x86)%\Common Files\Microsoft Shared\SQL Server Developer Tools, in %ProgramFiles(x86)%\Microsoft SQL Server Management Studio 18\Common7\IDE, in Visual Studio files, etc.

It's not a redistributable binary AFAIK, so you're not supposed to ship it, it's not documented, and it's not a full-featured grid like others. However, as you found out, it's lightweight and it can be fast, as fast as your underlying data access.

If you want to play with it, here's a small Winforms C# sample (a 10000 x 256 grid, which is 2,5 million cells that opens instantly) that demonstrates how to use it:

using System;
using System.Drawing;
using System.Windows.Forms;
using Microsoft.SqlServer.Management.UI.Grid;

namespace WindowsFormsApp1
{
    public partial class Form1 : Form
    {
        private GridControl _control = new GridControl();

        public Form1()
        {
            InitializeComponent();

            for (int i = 0; i < 256; i++)
            {
                _control.AddColumn(new GridColumnInfo { HeaderType = GridColumnHeaderType.Text, IsUserResizable = true });
                _control.SetHeaderInfo(i, "Column " + i, null);
            }

            _control.Dock = DockStyle.Fill;
            _control.GridStorage = new GridStorage();
            Controls.Add(_control);
        }
    }

    // represents a datasource
    public class GridStorage : IGridStorage
    {
        public long EnsureRowsInBuf(long FirstRowIndex, long LastRowIndex)
        {
            return NumRows(); // pagination, dynamic load, virtualization, could happen here
        }

        public void FillControlWithData(long nRowIndex, int nColIndex, IGridEmbeddedControl control)
        {
            // for cell edition
            control.SetCurSelectionAsString(GetCellDataAsString(nRowIndex, nColIndex));
        }

        public string GetCellDataAsString(long nRowIndex, int nColIndex)
        {
            // get cell data
            return nRowIndex + " x " + nColIndex;
        }

        public int IsCellEditable(long nRowIndex, int nColIndex)
        {
            return 1; // 1 means yes, 0 means false
        }

        public long NumRows()
        {
            return 10000;
        }

        public bool SetCellDataFromControl(long nRowIndex, int nColIndex, IGridEmbeddedControl control)
        {
            // when a cell has changed, you're supposed to change your data here
            return true;
        }

        public Bitmap GetCellDataAsBitmap(long nRowIndex, int nColIndex) => throw new NotImplementedException();
        public void GetCellDataForButton(long nRowIndex, int nColIndex, out ButtonCellState state, out Bitmap image, out string buttonLabel) => throw new NotImplementedException();
        public GridCheckBoxState GetCellDataForCheckBox(long nRowIndex, int nColIndex) => throw new NotImplementedException();
    }
}

Here is what it looks like. You can scroll without any slowdown, on a decent computer.

enter image description here

like image 124
Simon Mourier Avatar answered Oct 09 '22 03:10

Simon Mourier