SELECT Test Summary on SQL Server 2016

Q

What are performance differences of running the SELECT statement with different criteria and table storage options on SQL Server 2016?

✍: FYIcenter.com

A

Here is a summary of performance differences of running the SELECT statement with different criteria and table storage options on SQL Server 2016. Throughput is calculated as number of bytes retrieved per second in unit of MB/s.

@@version = Microsoft SQL Server 2016 - 13.0.5292.0 (X64) 

              |                Throughput MB/s
 Size   Count | CURSOR  BIGINT  PRIMARY KEY  MEMORY_OPTIMIZED
------------------------------------------------------------- 
  256     100 |   1.53    1.88         8.14           
  256     500 |   9.39    1.11         3.39              7.63
  256    1000 |   7.40    0.54         2.37             15.26
  256    5000 |   7.83    0.12        19.38             16.06
  256   10000 |   8.25    0.18        19.85             14.11
  512   10000 |  15.60    0.28        38.15             25.84
 1024   10000 |  28.47    0.44        69.21             62.11
 2048   10000 |  54.25    0.37       125.20            114.89
 4096   10000 |  83.11    0.41       250.40            229.78
 8192   10000 | 119.09    0.79       384.48            384.48
16384   10000 | 227.77    1.44       557.76            769.33
32768   10000 | 434.03    2.80       868.06           1250.00
32768   20000 | 425.17    1.33       889.05           1329.79
32768   50000 |                      945.25           1284.95
32768  100000 |                      888.79           1235.18
32768  500000 |                      794.92           1101.13

As you can see:

The worst case is the BIGINT case, where the SELECT statement is executed with a criteria column that is not the primary key or indexed.
The best case is the MEMORY_OPTIMIZED case, where the SELECT statement is executed with the primary key as the criteria on an in-memory (MEMORY_OPTIMIZED) table.
Actually, the MEMORY_OPTIMIZED table only improve performance on tests larger than 10,000 records of 16,384 bytes per record.

Â

? SQL Server Storage Tutorials

? SELECT with MEMORY_OPTIMIZED Table on SQL Server 2016

? I/O Tests on SELECT Statements

?? SQL Server Storage Tutorials

2019-05-15, 905👍, 0💬

What Is WebDriver
What Is WebDriver? WebCriver is a remote control communication interface that enables introspection and control of user agents. It provides a platform- and language-neutral wire protocol as a way for out-of-process programs to remotely instruct the behavior of web browsers. WebDriver interface is de... 2019-12-02, 1532👍, 0💬

Introduction to Selenium
Where to find tutorials on Introduction to Selenium? Here is a list of tutorials to answer many frequently asked questions compiled by FYIcenter.com team on Introduction to Selenium. What Is Selenium Â â‡’ What Is Selenium â‡ Selenium Tutorials â‡‘â‡‘ Selenium Tutorials 2019-09-14, 1260👍, 1💬

WebDriver and WebDriver Servers
Where to find tutorials on WebDriver and WebDriver Servers for different browsers? Here is a list of tutorials to answer many frequently asked questions compiled by FYIcenter.com team on WebDriver and WebDriver Servers for different browsers. What Is WebDriver Google ChromeDriver WebDriver Server St... 2019-06-15, 1180👍, 0💬

What Is Selenium
What Is Selenium? Selenium is a set of open-source software tools each with a different approach to supporting test automation. The entire suite of tools results in a rich set of testing functions specifically geared to the needs of testing of web applications of all types. These operations are high... 2019-05-28, 1137👍, 0💬

SELECT Test Summary on SQL Server 2016
What are performance differences of running the SELECT statement with different criteria and table storage options on SQL Server 2016? Here is a summary of performance differences of running the SELECT statement with different criteria and table storage options on SQL Server 2016. Throughput is calc... 2019-05-15, 906👍, 0💬

SELECT with MEMORY_OPTIMIZED Table on SQL Server 2016
What is the data read performance with the SELECT statement with a MEMORY_OPTIMIZED table on SQL Server 2016? To see the data read performance with the SELECT statement with a MEMORY_OPTIMIZED table on SQL Server 2016, you can run the SQL script, fyi_select_in_memory.sql, presented in the last tutor... 2019-04-17, 899👍, 0💬

Test Script for SELECT with MEMORY_OPTIMIZED
Where can I get a SQL script to do I/O performance test with SELECT statement on a MEMORY_OPTIMIZED table? Here is a SQL script to do I/O performance test with SELECT statement using BIGINT PRIMARY KEY on a MEMORY_OPTIMIZED table. -- fyi_select_in_memory.sql -- Copyright (c) FYIcenter.com -- DECLARE... 2019-04-23, 895👍, 0💬

Test Script for SELECT with PRIMARY KEY
Where can I get a SQL script to do I/O performance test with SELECT statement using BIGINT PRIMARY KEY as the search criteria? Here is a SQL script to do I/O performance test with SELECT statement using BIGINT PRIMARY KEY as the search criteria. It uses the fn_virtualfilestats() function table to ca... 2019-05-18, 891👍, 0💬

SELECT with PRIMARY KEY on SQL Server 2016
What is the data read performance with the SELECT statement with a PRIMARY KEY field on SQL Server 2016? To see the data read performance with the SELECT statement with a PRIMARY KEY field on SQL Server 2016, you can run the SQL script, fyi_select_in_memory.sql, presented in the last tutorial with d... 2019-04-24, 878👍, 0💬

All rights in the contents of this web site are reserved by the individual author. fyicenter.com does not guarantee the truthfulness, accuracy, or reliability of any contents.