CODEWITHSUNDEEP
sql-serversql-server-2008joinindexingsql-execution-plan
Richard Hooper
Richard Hooper

Reputation: 819

Why is this join taking so long?

I have the following query that I am running on my database server but it takes about 30 seconds to run and I can't work out why this is.

SELECT *
FROM [dbo].[PackageInstance] AS packInst
  INNER JOIN [dbo].[PackageDefinition] AS packageDef 
    ON packInst.[PackageDefinitionID] = packageDef.[PackageDefinitionID]
  LEFT OUTER JOIN [dbo].[PackageInstanceContextDef] AS contextDef 
   ON packInst.[PackageInstanceID] = contextDef.[PackageInstanceID]

This produced the following execution plan which to me looks to be good....so I can't understand why it takes so much time to execute where the resulting data is only 100,000 records (which should be a walk in the park for SQL Server).

SQL Server Execution Plan

Any ideas what could be causing this long execution time?

I have looked at the query in Profiler to see what the stats where on it and they are as follows:

CPU - 4711
Reads - 744453
Writes - 9
Duration - 26329

The following are the table definitions:

CREATE TABLE [dbo].[PackageDefinition](
    [PackageDefinitionID] [int] IDENTITY(1,1) NOT NULL,
    [ts] [timestamp] NOT NULL,
    [ProgramID] [int] NULL,
    [VendorID] [int] NULL,
    [PackageExecutionTypeID] [int] NULL,
    [PackageDefinitionStatusID] [int] NOT NULL,
    [IsInternal] [bit] NOT NULL,
    [Name] [dbo].[D_Name] NOT NULL,
    [Description] [dbo].[D_Description] NOT NULL,
    [CreatedDate] [datetime] NOT NULL,
    [PublishedDate] [datetime] NULL,
    [OwnerUserGuid] [uniqueidentifier] NOT NULL,
    [ProcessDefinitionMainID] [int] NULL,
    [KeyInfoHtml] [nvarchar](max) NULL,
    [DescriptionHtml] [nvarchar](max) NULL,
    [WhatToExpectHtml] [nvarchar](max) NULL,
    [BestPracticesHtml] [nvarchar](max) NULL,
    [RecommendedJourneysHtml] [nvarchar](max) NULL,
    [RequiresSLAAgreement] [bit] NOT NULL,
    [SLAFileAssetID] [int] NULL,
    [ImageDataID] [int] NULL,
    [VideoHtml] [nvarchar](max) NULL,
    [VideoAssetID] [int] NULL,
    [UseMapCosts] [bit] NOT NULL,
    [CostMin] [money] NOT NULL,
    [CostMax] [money] NOT NULL,
    [LandingPageVisitCount] [int] NOT NULL,
    [IsDeleted] [dbo].[D_IsDeleted] NOT NULL,
    [CreatedByUserGuid] [uniqueidentifier] NOT NULL,
    [OrderHtml] [nvarchar](max) NULL,
 CONSTRAINT [PK_PackageDefinition] PRIMARY KEY CLUSTERED 
(
    [PackageDefinitionID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[PackageInstance](
    [PackageInstanceID] [int] IDENTITY(1,1) NOT NULL,
    [ts] [timestamp] NOT NULL,
    [PackageDefinitionID] [int] NOT NULL,
    [PackageStatusID] [int] NOT NULL,
    [Name] [dbo].[D_Description] NOT NULL,
    [CampaignID] [int] NULL,
    [MarketingPlanID] [int] NULL,
    [CountryID] [int] NULL,
    [DateEntered] [datetime] NULL,
    [DateExecuted] [datetime] NULL,
    [ProcessID] [int] NULL,
    [OrderedByUserGuid] [uniqueidentifier] NULL,
    [RequestedByUserGuid] [uniqueidentifier] NULL,
    [SLAEndDate] [datetime] NULL,
 CONSTRAINT [PK_PackageInstance] PRIMARY KEY CLUSTERED 
(
    [PackageInstanceID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[PackageInstanceContextDef](
    [PackageInstanceContextDefID] [int] IDENTITY(1,1) NOT NULL,
    [ts] [timestamp] NOT NULL,
    [PackageInstanceID] [int] NOT NULL,
    [ContextObjectDefID] [int] NOT NULL,
    [EnteredFieldValue] [varchar](max) NULL,
    [SelectedListValueID] [int] NULL,
    [AssetIdsString] [nvarchar](max) NULL,
    [SelectedListValueIdsString] [nvarchar](max) NULL,
    [ContextObjectFieldName] [nvarchar](30) NOT NULL,
 CONSTRAINT [PK_PackageInstanceContextDef] PRIMARY KEY CLUSTERED 
(
    [PackageInstanceContextDefID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Upvotes: 2

Views: 10338

Answers (3)

Richard Hooper
Richard Hooper

Reputation: 819

The answer turned out to be what @MartinSmith suggested. Because the PackageDefinition table contained about 8 NVARCHAR(MAX) columns, when the resulting join was created and that was over 100k rows, this was causing the varchar(max) values to be re-read over and over and they exist in out of row pages. Hence the large number of logical reads.

Thanks all for your support, just have to figure out to make the entity framework produce the query that I want.

Upvotes: 3

Martin Smith
Martin Smith

Reputation: 453406

What happens if you add the following index...

CREATE NONCLUSTERED INDEX ix ON  PackageDefinition(PackageDefinitionID)

...and try the following to reduce the width of the data going into the sort?

SELECT packInst.*,
       packageDef2.*,
       contextDef.*
FROM   [dbo].[PackageInstance] AS packInst
       INNER MERGE JOIN [dbo].[PackageDefinition] AS packageDef
         ON packInst.[PackageDefinitionID] = packageDef.[PackageDefinitionID]
       LEFT OUTER MERGE JOIN [dbo].[PackageInstanceContextDef] AS contextDef
        ON packInst.[PackageInstanceID] = contextDef.[PackageInstanceID]
       INNER MERGE JOIN [dbo].[PackageDefinition] AS packageDef2
        ON packageDef.[PackageDefinitionID] = packageDef2.[PackageDefinitionID]

OF course * should not be used as even if you need all columns you definitely won't need the same columns twice as the result of the JOIN but this is just to maintain the semantics of your original query.

Upvotes: 1

gbn
gbn

Reputation: 432311

Remove the * in SELECT *

It will always scan because you ask for all columns. And do you have clustered indexes?

Upvotes: 3

Related Questions