CODEWITHSUNDEEP
sqlsql-serverperformance
Angela
Angela

Reputation: 507

Slow select performance on a table

I am using SQL Server and I have a table like this one

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

For now, this table around 3 millions rows. A simple select like this one takes around 30 seconds

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

Is there a way to improve it?

Upvotes: 2

Views: 1108

Answers (2)

Dan Guzman
Dan Guzman

Reputation: 46425

I don't think guids are the long pole in the tent from a performance perspective in this case. Running the PowerShell test below of a 3M row select from a remote server, the results showed the int test is about 10% faster on average. Assuming similar results in your environment, that would translate to 27 seconds with int versus 30 with guid. I observed most of the time was due to client CPU processing of the large result set.

This isn't to say there aren't considerations with guids, especially on a single-disk spinning media storage, but I wanted to make it clear that it's the large result set that is the issue rather than the data type.

$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
}

Upvotes: 3

DhruvJoshi
DhruvJoshi

Reputation: 17146

While the full context of question ( like execution plan, indexes ) are unknown, I'm tempted to put down a rather large list of downfalls associated with GUIDs as my answer.

All column in your table have a GUID.

  StaticId uniqueidentifier NOT NULL,
  UserId uniqueidentifier NULL,
  UserGroupId uniqueidentifier NULL,
  CompanyId uniqueidentifier NULL,
  CompanyGroupId uniqueidentifier NULL,
  CompanyAccessUnitRole uniqueidentifier NULL

To quote the cons from a source where author favors GUIDs

GUID Cons

  1. It is a whopping 4 times larger than the traditional 4-byte index value; this can have serious performance and storage implications if you're not careful
  2. Cumbersome to debug where userid='{BAE7DF4-DDF-3RG-5TY3E3RF456AS10}'
  3. The generated GUIDs should be partially sequential for best performance (eg, newsequentialid() on SQL 2005) and to enable use of clustered indexes

Your data will be spread across more pages and will have more physical reads compared to using say int for Key.

If you do many number of inserts/updates/deletes, your indexes will be highly fragmented. This is so because the GUIDs are generated at random and it takes a toll at engine to update index for organizing them in a sequential order.

My bet is that your indexes need to be rebuilt. Here's an article which compares GUID with INT column indexes and reflects that GUID is slower than INT but can be improved and brought at par on index rebuilding.

If you think that GUID are the culprit, I'd suggest that you should look at bigint as an option

Upvotes: 2

Related Questions