Collections:
SELECT with BIGINT on SQL Server 2016
What is the data read performance with the SELECT statement using BIGINT as the search criteria on SQL Server 2016?
✍: FYIcenter.com
To see the data read performance with the SELECT statement using BIGINT as the search criteria
on SQL Server 2016, you can run the SQL script, fyi_select_bigint.sql, presented in the last tutorial
with different @Count and @Size.
The following table shows the summary of the test results. Some fields are removed to save space. additional fields are added with calculations on existing fields.
@@version = Microsoft SQL Server 2016 - 13.0.5292.0 (X64) SIZE COUNT DURATION BYTES FILE_ID READS BYTES READ Throughput MB/s 256 100 13 25600 1 0 0 1.88 256 500 110 128000 1 0 0 1.11 256 1000 453 256000 1 0 0 0.54 256 5000 10323 1280000 1 0 0 0.12 256 10000 13760 2560000 1 0 0 0.18 512 10000 17293 5040000 1 0 0 0.28 1024 10000 22213 10160000 1 0 0 0.44 2048 10000 52806 20480000 1 0 0 0.37 4096 10000 95976 40960000 1 0 0 0.41 8192 10000 98683 81840000 1 0 0 0.79 16384 10000 108390 163760000 1 0 0 1.44 32768 10000 111546 327680000 1 0 0 2.80 32768 20000 468680 655360000 1 0 0 1.33
Comparing the result of SELECT statement with CURSOR, the SELECT statement with BIGINT as the search criteria is performing poorly.
The peak throughput using SELECT statement with CURSOR is 434.03 MB/s, while the peak throughput using SELECT statement with BIGINT as the search criteria is only 2.80 MB/s. This is expected, because the BIGINT column is used as a search criteria and it is not indexed.
Note that fn_virtualfilestats() view failed to report any changes on read counters.
Â
? SELECT with BIGINT on Azure SQL Database
? Test Script for SELECT with BIGINT
2019-04-29, 1061👍, 0💬
Popular Posts:
Where to find online test tools? FYIcenter.com has prepared a number of online test tools: Data Look...
How to turn on Chrome WebDriver logging with Selenium Python API? If you want to turn on logging on ...
How to Open and Close Internet Explorer with UFT script? One way to open and close Internet Explorer...
How to validate Mod 10 (Luhn Algorithm) checksum? In order to help your programming or testing tasks...
Where to find tutorials on Apache JMeter test tool? I want to know how to use Apache JMeter. Here is...