sql - 表上的选择性能缓慢

Question

我正在使用 SQL Server，并且我有一个像这样的表

CREATE TABLE dbo.CompanyRolesExpanded (
  StaticId uniqueidentifier NOT NULL,
  UserId uniqueidentifier NULL,
  UserGroupId uniqueidentifier NULL,
  CompanyId uniqueidentifier NULL,
  CompanyGroupId uniqueidentifier NULL,
  CompanyAccessUnitRole uniqueidentifier NULL,
  PRIMARY KEY CLUSTERED (StaticId)
)
GO

目前，该表大约有 300 万行。像这样的简单选择大约需要 30 秒

SELECT  UserId,UserGroupId
       ,CompanyId,CompanyGroupId 
       ,CompanyAccessUnitRole                   
FROM CompanyRolesExpanded

有什么办法可以改善吗？

Answer 1

在这种情况下，从性能角度来看，我不认为指南是帐篷中的长杆。对远程服务器上的 3M 行选择运行下面的 PowerShell 测试，结果显示 int 测试平均快约 10%。假设您的环境中出现类似的结果，则使用 int 时将转换为 27 秒，而使用 guid 时则转换为 30 秒。我观察到大部分时间是由于客户端 CPU 处理大型结果集造成的。

这并不是说不需要考虑 guid，特别是在单磁盘旋转媒体存储上，但我想明确指出，问题在于大型结果集，而不是数据类型。

$connectionString = "Data Source=YourServer;Initial Catalog=tempdb;Integrated Security=SSPI;Application Name=PerformanceTestScript";

$guidSetupScript = @"
CREATE TABLE dbo.Example (
  StaticId uniqueidentifier NOT NULL,
  UserId uniqueidentifier NULL,
  UserGroupId uniqueidentifier NULL,
  CompanyId uniqueidentifier NULL,
  CompanyGroupId uniqueidentifier NULL,
  CompanyAccessUnitRole uniqueidentifier NULL,
  PRIMARY KEY CLUSTERED (StaticId)
);
WITH 
     t10 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n))
    ,t1k AS (SELECT 0 AS n FROM t10 AS a CROSS JOIN t10 AS b CROSS JOIN t10 AS c)
    ,t10m AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS num FROM t1k AS a CROSS JOIN t1k AS b CROSS JOIN t1k AS c CROSS JOIN t10 AS d)
INSERT INTO dbo.Example WITH(TABLOCKX) (
      StaticId
    , UserId
    , UserGroupId
    , CompanyId
    , CompanyGroupId
    , CompanyAccessUnitRole
    )
SELECT
      NEWID()
    , NEWID()
    , NEWID()
    , NEWID()
    , NEWID()
    , NEWID()
FROM t10m
WHERE num <= 3000000;
"@

$intSetupScript = @"
CREATE TABLE dbo.Example (
  StaticId int NOT NULL,
  UserId int NULL,
  UserGroupId int NULL,
  CompanyId int NULL,
  CompanyGroupId int NULL,
  CompanyAccessUnitRole int NULL,
  PRIMARY KEY CLUSTERED (StaticId)
);
WITH 
     t10 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n))
    ,t1k AS (SELECT 0 AS n FROM t10 AS a CROSS JOIN t10 AS b CROSS JOIN t10 AS c)
    ,t10m AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS num FROM t1k AS a CROSS JOIN t1k AS b CROSS JOIN t1k AS c CROSS JOIN t10 AS d)
INSERT INTO dbo.Example WITH(TABLOCKX) (
      StaticId
    , UserId
    , UserGroupId
    , CompanyId
    , CompanyGroupId
    , CompanyAccessUnitRole
    )
SELECT
      num
    , num
    , num
    , num
    , num
    , num
FROM t10m
WHERE num <= 3000000;
"@

try
{
    $values = [System.Array]::CreateInstance([System.Object], 6)
    $connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
    $command = New-Object System.Data.SqlClient.SqlCommand
    $command.CommandTimeout = 0
    $connection.Open()
    $command.Connection = $connection

    #Guid setup
    $command.CommandText = "IF OBJECT_ID(N'dbo.Example') IS NOT NULL DROP TABLE dbo.Example;"
    [void]$command.ExecuteNonQuery()
    $command.CommandText = $guidSetupScript
    [void]$command.ExecuteNonQuery()

    #guid test
    $testSw = [System.Diagnostics.StopWatch]::StartNew()
    Write-Host "Starting Guid test."
    $command.CommandText = "SELECT * FROM dbo.Example;"
    $reader = $command.ExecuteReader()
    while($reader.Read()) {
        $values = $reader.GetValues($values)
    }
    $reader.Close()
    $testSw.Stop()
    Write-Host "Guid test duration was $($testSw.Elapsed.ToString())"

    #int setup
    $command.CommandText = "IF OBJECT_ID(N'dbo.Example') IS NOT NULL DROP TABLE dbo.Example;"
    [void]$command.ExecuteNonQuery()
    $command.CommandText = $intSetupScript
    [void]$command.ExecuteNonQuery()

    #int test
    $testSw = [System.Diagnostics.StopWatch]::StartNew()
    Write-Host "Starting int test."
    $command.CommandText = "SELECT * FROM dbo.Example;"
    $reader = $command.ExecuteReader()
    while($reader.Read()) {
        $values = $reader.GetValues($values)
    }
    $reader.Close()
    $testSw.Stop()
    Write-Host "Int test duration was $($testSw.Elapsed.ToString())"

    $connedtion.Close()

}
catch [Exception]
{
    throw
}