What is the data read performance with the SELECT statement using BIGINT as the search criteria on Azure SQL Database?

To see the data read performance with the SELECT statement using BIGINT as the search criteria on Azure SQL Database, 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 Azure (RTM) - 12.0.2000.8 May 2 2019

 SIZE  COUNT  DURATION      BYTES  FILE_ID  READS  BYTES READ  Throughput MB/s
  256    100        20      25600        1      0           0             1.22
  256    500       140     128000        1      0           0             0.87
  256   1000       680     256000        1      0           0             0.36
  256   5000     16640    1280000        1      0           0             0.07
  256  10000     74380    2560000        1      0           0             0.03
  512  10000     69057    5040000        1      0           0             0.07
 1024  10000     81633   10160000        1      0           0             0.12
 2048  10000    102860   20480000        1      0           0             0.19
 4096  10000    169646   40960000        1      0           0             0.23
 8192  10000    161714   81840000        1      0           0             0.48
16384  10000    180644  163760000        1      0           0             0.86
32768  10000    176377  327680000        1      0           0             1.77

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 269.40 MB/s, while the peak throughput using SELECT statement with BIGINT as the search criteria is only 1.77 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.

? Test Script for SELECT with PRIMARY KEY

? SELECT with BIGINT on SQL Server 2016

? I/O Tests on SELECT Statements

?? SQL Server Storage Tutorials